博客信息

导入Excel工具类

0
发布时间:『 2019-08-13 01:40』  博客类别:util类  阅读(462) 评论(0)

话不多说:通用excel导入,基于poi的包。

public class ImportExcel {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    //判断Excel的版本,获取Workbook
    public static Workbook getWorkbok(InputStream in,File file) throws IOException{
        Workbook wb = null;
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    //判断文件是否是excel
    public static void checkExcelVaild(File file) throws Exception{
        if(!file.exists()){
            throw new Exception("文件不存在");
        }
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){
            throw new Exception("文件不是Excel");
        }
    }

    /**
     * 由指定的Sheet导出至List
     * @param url       excel文件地址
     * @param sheetnum  sheet页
     * @return
     * @throws Exception
     */
    public  Map<String,String> exportListFromExcel(String url,int sheetnum) throws Exception {
        List<Map<String,String>> list=new ArrayList<>();
        Map<String,String> map=new HashMap<>();
        // 同时支持Excel 2003、2007
        File excelFile = new File( url);
        FileInputStream is = new FileInputStream(excelFile);
        //效验文件是否存在
        checkExcelVaild(excelFile);
        Workbook workbook = getWorkbok(is,excelFile);

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            // Sheet的数量
            int sheetCount = workbook.getNumberOfSheets();
            // 遍历第一个Sheet 设置当前excel中sheet的下标:0开始
            Sheet sheet = workbook.getSheetAt(sheetnum);
            // 为跳过第一行目录设置count
            int count = 0;
            for (Row row : sheet) {
                // 跳过第一行的目录
                if(count == 0){
                    count++;
                    continue;
                }else{
                    //存入map的键标识
                    count++;
                }
                // 如果当前行没有数据,跳出循环
                if(row==null){
                    break;
                }
                String rowValue = "";

                //for (Cell cell : row) {
                int cellType=0;
                for (int j=0;j<row.getLastCellNum();j++) {
                    Cell cell=row.getCell(j);
                    try{
                        //如果列是空就报错
                        cellType = cell.getCellType();
                    }catch(Exception e){
                        //默认为空
                        cellType=Cell.CELL_TYPE_BLANK;
                    }
//                    if(cell == null){
//                        continue;
//                    }
                    String cellValue = "";
                    switch (cellType) {
                        // 文本
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getRichStringCellValue().getString() + "#";
                            break;
                        // 数字、日期
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()) + "#";
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";
                            }
                            break;
                        // 布尔型
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(cell.getBooleanCellValue()) + "#";
                            break;
                        // 空白
                        case Cell.CELL_TYPE_BLANK:
                            cellValue =  "null#";
                            break;
                        // 错误
                        case Cell.CELL_TYPE_ERROR: // 错误
                            cellValue = "error#";
                            //cellValue = String.valueOf(cell.getBooleanCellValue()) + "#";
                            break;
                        // 公式
                        case Cell.CELL_TYPE_FORMULA:
                            // 得到对应单元格的公式
                            //cellValue = cell.getCellFormula() + "#";
                            // 得到对应单元格的字符串
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";
                            break;
                        default:
                            cellValue = "null#";
                    }
                    //System.out.print(cellValue);
                    rowValue += cellValue;
                }
//                System.out.println(rowValue);
//                System.out.println();
                map.put(String.valueOf(count),rowValue);
                return map;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }




    public static void main(String[] args) throws Exception {
        ImportExcel importExcel=new ImportExcel();
        //出港航班
        Map<String,String> omap=importExcel.exportListFromExcel( "C:\\Users\\issuser\\Desktop\\123.xlsx", 0);
        //进港航班
        Map<String,String> imap=importExcel.exportListFromExcel( "C:\\Users\\issuser\\Desktop\\123.xlsx", 1);


    }
}


运行main方法得到map返回值,解析入库即可、




关键字:     excel  
博主信息

勿扰
简介:对自己狠一点,社会才会对你好一点!
4年1月
QQ
热门文章
Powered by 勿扰 V2.0 湘ICP备18002237号-2     Copyright © 2016-2018 勿扰个人博客 版权所有