Java解析Excel文件

Java解析excel

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

1.依赖导入

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

  org.apache.poi  poi  3.10-FINAL  org.apache.poi  poi-ooxml  3.10-FINAL  org.apache.poi  poi-ooxml-schemas  3.10-FINAL

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);        // 解析数据        ListHashMap> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);        assert maps != null;        log.info("size:{}", maps.size());        log.info("data:{}", JSON.tojsONString(maps));    }

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

       static List> 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> 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 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各种类型值

     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> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);        assert maps != null;        // 取10条写入到其他文件        List> maps1 = maps.subList(0, 10);        // 新建工作簿        XSSFWorkbook wb = new XSSFWorkbook();        // 新建Sheet        XSSFSheet xssfSheet = wb.createSheet("testSheet");        // 获取标题 前面用的是linkedhashmap 直接获取map中的标题 且和读取的excel列相同        List 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 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>  = new ArrayList<>();        // 入库的数据        List 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> 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> 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版本处理

      static List> 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> 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 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版本处理

     static List> 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> 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 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及以上的版本的

        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;    }        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 invoicePartHis = findInvoicePartHis(queryParamModel);        String excelName = "test数据.xlsx";        String sheetName = "testSheet";        List titleList = new ArrayList<>();        titleList.add("单据号");        titleList.add("行号");        titleList.add("订单状态");        titleList.add("客户【描述】");        titleList.add("订单类型【描述】");    // 整理数据data        List> datasList = new ArrayList<>();        if (invoicePartHis.size()>0){            for (TRPAInvoicePartHisVO partHisModel : invoicePartHis) {                List 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的样式

     public static void createSheetExcel(String excelName,String sheetName,List title, List> 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

     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操作，都可以根据自己的业务来封装。

来源地址：https://blog.csdn.net/Jeck_wu/article/details/128040333