Java解析Excel文件

Java解析Excel

​ Java解析Excel可以说在日常工作中必不可少的一个需求。解析Excel有很多方式，主流有poi，easyexcel等技术，本文使用springboot+poi技术来实现解析excel文件。其中包含解析本地文件和web端导入导出文件，且支持2003和2007及以上版本。

1.依赖导入

依赖版本不同可能会出现报错，整篇文章使用以下依赖版本进行编写。

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.10-FINAL</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.10-FINAL</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>3.10-FINAL</version>
</dependency>

2.本地导入导出

定义要读取和存放Excel文件的路径

// 要读取的本地文件
String localExcelPath = "/Users/jeckwu/Desktop/test/test.xlsx";
// 把数据写入excel放到本地路径
String writeDateToExcelPath = "/Users/jeckwu/Desktop/test/";

解析本地excel：解析指定目录中的excel数据

 // 读取本地excel数据
    @Test
    public void getLocalExcelData() throws IOException {
        // 获取文件流
        FileInputStream fileInputStream = new FileInputStream(localExcelPath);
        // 解析数据
        List<LinkedHashMap<String, Object>> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);
        assert maps != null;
        log.info("size:{}", maps.size());
        log.info("data:{}", JSON.toJSONString(maps));

    }

这里简单的封装了一下，操作本地文件只针对xlsx格式文件，web端的处理包括xls类型。

   /**
     * excel2007及以上版本
     *
     * @param file
     * @return
     * @throws IOException
     */
    static List<LinkedHashMap<String, Object>> xlsxImportExcel(FileInputStream file, Integer titleNo, Integer dataNo, Integer sheetNo) throws IOException {
        log.info("excel2007及以上版本");
        // 读取流中的excel数据 这里用到的是poi中的XSSFWorkbook 用来处理xlsx(版本在2007以后的excel文件) HSSFWorkbook对象用来处理xls类型 版本为2003的文件
        XSSFWorkbook xwb = new XSSFWorkbook(file); //获取excel工作簿
        XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet
        if (xssfSheet == null) {
            return null;
        }
        // 防止数据列错乱
        List<LinkedHashMap<String, Object>> mapList = new ArrayList<>();
        Row rowTitle = xssfSheet.getRow(titleNo);
        //循环获取excel每一行
        for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow == null) {
                continue;
            }
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            //循环获取excel每一行的每一列
            for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) {
                XSSFCell xssCell = xssfRow.getCell(cellNum);
                if (xssCell == null) {
                    // 防止单元格为空
                    map.put(rowTitle.getCell(cellNum).toString(), "");
                    continue;
                }
                try {
                    map.put(rowTitle.getCell(cellNum).toString(), getXSSFValue(xssCell));
                } catch (Exception e) {
                    log.error("");
                }
            }
            mapList.add(map);  //将excel每一行的数据封装到map对象,并将map对象添加到list
        }
        return mapList;
    }

处理excel各种类型值

 /**
     * excel值处理
     *
     * @param hssfCell
     * @return
     */
    public static Object getXSSFValue(XSSFCell hssfCell) {
        Object result = null;
        int cellType = hssfCell.getCellType();
        switch (hssfCell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: //数字
                if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
//                    需要对日期这一列进行设置样式，否则无法识别是日期类型还是数值类型
//                    默认不支持中文日期类型，需要设置成纯英语日期类型，不要包含年月日等汉字
//                    最好是使用这种格式 2019/10/10 0:00
                    SimpleDateFormat sdf = new SimpleDateFormat(
                            "yyyy-MM-dd HH:mm:ss");
                    result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
                    break;
                }
                result = hssfCell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
                result = hssfCell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_ERROR: //故障
                result = hssfCell.getErrorCellValue();
                break;
            case HSSFCell.CELL_TYPE_FORMULA: //公式
                result = hssfCell.getCellFormula();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default: //字符串
                result = hssfCell.getStringCellValue();
        }
        return result;
    }

Excel下载到本地：根据指定数据集生成excel到指定目录中

这里用到了上面的excel数据作为测试 实际根据自己项目封装即可。

 // 把数据集写入到excel中 放到本地服务器
    @Test
    public void writeDataToLocalExcel() throws IOException {
        // 获取文件流-就拿本地数据源
        FileInputStream fileInputStream = new FileInputStream(localExcelPath);
        List<LinkedHashMap<String, Object>> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);
        assert maps != null;
        // 取10条写入到其他文件
        List<LinkedHashMap<String, Object>> maps1 = maps.subList(0, 10);

        // 新建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 新建Sheet
        XSSFSheet xssfSheet = wb.createSheet("testSheet");
        // 获取标题 前面用的是linkedhashmap 直接获取map中的标题 且和读取的excel列相同
        List<String> titleList = maps1.get(0).keySet().stream().map(s -> s).collect(Collectors.toList());
        //标题行
        XSSFRow titleRow = xssfSheet.createRow(0);
        for (int i = 0; i < maps1.get(0).keySet().size(); i++) {
            XSSFCell titleCell = titleRow.createCell(i);
            titleCell.setCellValue(titleList.get(i));
        }
        // 数据行
        for (int i = 0; i < maps1.size(); i++) {
            XSSFRow xssfRow = xssfSheet.createRow(i + 1);
            Map<String, Object> stringObjectMap = maps1.get(i);
            for (int i1 = 0; i1 < titleList.size(); i1++) {
                XSSFCell xssfCell = xssfRow.createCell(i1);
                xssfCell.setCellValue(stringObjectMap.get(titleList.get(i1)).toString());
            }
        }
        log.info("size:{}", maps1.size());
        log.info("data:{}", JSON.toJSONString(maps1));
        // 写入本地文件中 将文件保存到指定的位置
        try {
            FileOutputStream fos = new FileOutputStream(writeDateToExcelPath+"写入本地excel.xlsx");
            wb.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

3.Web端导入导出

导入Excel：在网页上导入Excel，然后处理业务逻辑

下面我们编写测试代码，导入数据Excel之后做一下基本的判断，就开始业务处理

// 根据参数选择解析文件格式数据
    public ApiResult analysisPcsData(PcsDataIMportParamModel pcsDataIMportParamModel){
        if (pcsDataIMportParamModel.getMultipartFile() == null) return new ApiResult(true,"请上传文件");
        log.info("{}-导入PCS数据,参数：{}",DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss"),pcsDataIMportParamModel.toString());
        // 根据参数选择解析
        String originalFilename = pcsDataIMportParamModel.getMultipartFile().getOriginalFilename();
        assert originalFilename != null;
        // 解析的数据
        List<Map<String, Object>>  = new ArrayList<>();
        // 入库的数据
        List<TJPcsDataModel> pcsDataModelList = new ArrayList<>();
       try {
           String expandName = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
           if ((expandName.equals("xls") || expandName.equals("xlsx") ) && "EP".equals(pcsDataIMportParamModel.getCustomer_factory())){
               mapList = ImportExcel.importExcel(pcsDataIMportParamModel.getMultipartFile(),1,2,0);
               pcsDataModelList.addAll(analysisExcel(mapList,pcsDataIMportParamModel));
           }else {
               return new ApiResult(false,"上传的文件格式不正确，只支持xls，xlsx，csv!或者参数选择错误！");
           }
           if (CollectionUtils.isEmpty(mapList)) return new ApiResult(false,"传入的文件数据为空，请检查后再上传！！");
           log.info("导入PCS解析数据数量:{}",mapList.size());
           log.info("PCS入库数据数量:{}",pcsDataModelList.size());
           return new ApiResult(true,"导入数据成功！！文件名："+originalFilename+"，成功条数："+i);
       }catch (Exception e){
           log.error("导入PCS数据解析失败！！！原因：{}",e.getMessage(),e);
           return new ApiResult(false,"导入PCS数据解析失败!!!");
       }

这里区分了Excel版本

 // 判断excel版本
    public static List<Map<String,Object>> importExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
        String fileName = file.getOriginalFilename();  //获得上传的excel文件名
        assert fileName != null;
        String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1);  //获取上传的excel文件名后缀
        List<Map<String,Object>> mapList = null;
        if ("xlsx".equals(fileSuffix)) {
            mapList = xlsxImportExcel(file,titleNo,dataNo,sheetNo);
        } else if ("xls".equals(fileSuffix)) {
            mapList = xlsImportExcel(file,titleNo,dataNo,sheetNo);
        }
        return mapList;
    }

Excel2007版本处理

  /**
     * excel2007及以上版本
     *
     * @param file
     * @return
     * @throws IOException
     */
    static List<Map<String,Object>> xlsxImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
        log.info("excel2007及以上版本");
        XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream()); //获取excel工作簿
        XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet
        if (xssfSheet == null) {
            return null;
        }
        List<Map<String,Object>> mapList = new ArrayList<>();
        Row rowTitle = xssfSheet.getRow(titleNo);
        //循环获取excel每一行
        for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow == null) {
                continue;
            }
            Map<String,Object> map = new HashMap<>();
            //循环获取excel每一行的每一列
            for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) {
                XSSFCell xssCell = xssfRow.getCell(cellNum);
                if (xssCell == null) {
                    continue;
                }
                try {
                    map.put(rowTitle.getCell(cellNum).toString(),getXSSFValue(xssCell));
                }catch (Exception e){
                    log.error("");
                }
            }
            mapList.add(map);  //将excel每一行的数据封装到map对象,并将map对象添加到list
        }
        return mapList;
    }

Excel2003版本处理

 /**
     * @param file
     * @return excel2003版本
     * @throws IOException
     */
    static List<Map<String,Object>> xlsImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
        log.info("excel2003版本");
        Workbook wb = new HSSFWorkbook(file.getInputStream()); //获取excel工作簿
        Sheet sheet = wb.getSheetAt(sheetNo);  //获取excel的sheet
        if (sheet == null) {
            return null;
        }
        List<Map<String,Object>> list = new ArrayList<>();
        Row rowTitle = sheet.getRow(titleNo);
        //循环获取excel每一行
        for (int rowNum = dataNo; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            Map<String,Object> map = new HashMap<>();
            //循环获取excel每一行的每一列
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (cell == null) {
                    continue;
                }
                map.put(rowTitle.getCell(cellNum).toString(),getValue(cell));
            }
            list.add(map);    //将excel每一行的数据封装到map对象,并将map对象添加到list
        }
        return list;
    }

Excel中各种类型值处理，包括2003和2007及以上的版本的

    /**
     * excel值处理 2007
     *
     * @param hssfCell
     * @return
     */
    public static Object getXSSFValue(XSSFCell hssfCell) {
        Object result = null;
        int cellType = hssfCell.getCellType();
        switch (hssfCell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: //数字
                if (HSSFDateUtil.isCellDateFormatted(hssfCell))
                {
//                    需要对日期这一列进行设置样式，否则无法识别是日期类型还是数值类型
//                    默认不支持中文日期类型，需要设置成纯英语日期类型，不要包含年月日等汉字
//                    最好是使用这种格式 2019/10/10 0:00
                    SimpleDateFormat sdf = new SimpleDateFormat(
                            "yyyy-MM-dd HH:mm:ss");
                    result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
                    break;
                }
                result = hssfCell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
                result = hssfCell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_ERROR: //故障
                result = hssfCell.getErrorCellValue();
                break;
            case HSSFCell.CELL_TYPE_FORMULA: //公式
                result = hssfCell.getCellFormula();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default: //字符串
                result = hssfCell.getStringCellValue();
        }
        return result;
    }

    /**
     * excel值处理 2003
     *
     * @param cell
     * @return
     */
    public static Object getValue(Cell cell){
        //判断是否为null或空串
        if  (cell== null  || cell.toString().trim().equals( "" )) {
            return null ;
        }
        String cellValue;
        int  cellType=cell.getCellType();
        switch  (cellType) {
            case  Cell.CELL_TYPE_STRING:
                cellValue= cell.getStringCellValue().trim();
                cellValue= StringUtils.isEmpty(cellValue) ?  ""  : cellValue;
                break ;
            case  Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break ;
            case  Cell.CELL_TYPE_NUMERIC:
                if  (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
                }  else  {
                    cellValue = new DecimalFormat( "#.######" ).format(cell.getNumericCellValue());
                }
                break ;
            default :
                cellValue = null ;
                break ;
        }
        return cellValue == null ? null : cellValue.replaceAll("\\xa0", "");
    }

导出Excel：在网页上导出Excel

// 导出excel
    public void invoicePartHisExcelExport(QueryParamModel queryParamModel, HttpServletResponse response){
        long startTime = System.currentTimeMillis();
        log.info("----开始导出Excel:"+startTime);
        //导出全部数据
        queryParamModel.setPageSize(-1);
        queryParamModel.setPageSize(-1);
        //根据条件查询业务数据
        List<TRPAInvoicePartHisVO> invoicePartHis = findInvoicePartHis(queryParamModel);
        String excelName = "test数据.xlsx";
        String sheetName = "testSheet";
        List<String> titleList = new ArrayList<>();
        titleList.add("单据号");
        titleList.add("行号");
        titleList.add("订单状态");
        titleList.add("客户【描述】");
        titleList.add("订单类型【描述】");
    		// 整理数据data
        List<List<String>> datasList = new ArrayList<>();
        if (invoicePartHis.size()>0){
            for (TRPAInvoicePartHisVO partHisModel : invoicePartHis) {
                List<String> list = new ArrayList<>();
                list.add(Strings.isNotBlank(partHisModel.getInvoice_no())?partHisModel.getInvoice_no():"");
                list.add(Strings.isNotBlank(partHisModel.getLine_no())?partHisModel.getLine_no():"");
                list.add(Strings.isNotBlank(partHisModel.getOrder_status_desc())?partHisModel.getOrder_status_desc():"");
                list.add(Strings.isNotBlank(partHisModel.getCustomer_name())?partHisModel.getCustomer_name():"");
                list.add(Strings.isNotBlank(partHisModel.getOrder_type_desc())?partHisModel.getOrder_type_desc():"");
                datasList.add(list);
            }
        }
        ExportExcel.createSheetExcel(excelName,sheetName,titleList,datasList,response);
        log.info("----导出Excel结束,所用时间："+(System.currentTimeMillis()-startTime));
    }

这里可以设置Excel的样式

 /**
     * excel下载单个sheet
     * @param excelName --Excel名称
     * @param sheetName --sheet名称--sheet为null或者空字符串表示默认sheet名
     * @param title --表头
     * @param datas --数据 list,一行一个list
     * @param response --对服务器的响应
     */
    public static void createSheetExcel(String excelName,String sheetName,List<String> title, List<List<String>> datas, HttpServletResponse response) {
        try{
            int rowNum = datas.size() + 1;
            int colNum = title.size();

            //创建工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            //创建一个sheet
            XSSFSheet sheet = wb.createSheet();
            if(sheetName!=null||!"".equals(sheetName)){
                wb.setSheetName(0, sheetName);
            }
            sheet.setDefaultColumnWidth(16);

            // 创建单元格样式
            XSSFCellStyle style1 = wb.createCellStyle();
            style1.setFillForegroundColor((short) 1); //设置要添加表背景颜色
            style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //solid 填充
            style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); //文字水平居中
            style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//文字垂直居中
            style1.setBorderBottom(BorderStyle.THIN); //底边框加黑
            style1.setBorderLeft(BorderStyle.THIN);  //左边框加黑
            style1.setBorderRight(BorderStyle.THIN); // 有边框加黑
            style1.setBorderTop(BorderStyle.THIN); //上边框加黑
            //为单元格添加背景样式
            sheet=setTitleStyle(sheet,rowNum,colNum,style1,null);
            //tian入数据
            sheet=setSheetData(sheet,rowNum,colNum,title, datas);
            //将数据写入文件
            writeData(excelName,response,wb);
        }catch (Exception e){
            log.error("excel下载单个sheet报错:"+e);
            throw GlobalException.serverErrException(500,"下载出错");
        }
    }

最后把流放进响应中，浏览器自动解析流，并且下载Excel

 /**
     *生成excel
     * */
    public static void writeData(String excelName, HttpServletResponse response, XSSFWorkbook wb){
        OutputStream outputStream = null;
        try{
            excelName = new String(excelName.getBytes(), StandardCharsets.UTF_8);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取
            response.setHeader("Content-Disposition", "attachment;filename="+ excelName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }catch (Exception e){
            log.error("输出excel报错:"+e);
            throw GlobalException.serverErrException(500,"下载出错");
        }finally {
            if(outputStream!=null){
                try{
                    outputStream.close();
                }catch (Exception e){
                    log.error("流关闭报错:"+e);
                    throw GlobalException.serverErrException(500,"下载出错");
                }
            }
        }
    }

最后实际对Excel操作，都可以根据自己的业务来封装。