SpringBoot整合poi实现Excel文件的导入和导出

SpringBoot整合poi实现Excel文件的导入和导出

1、引入Maven依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.16</version>
    </dependency>

2、添加ExcelFormatUtil工具类

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;

public class ExcelFormatUtil {
    //单元格数据格式判断

    public static String getValue(XSSFCell cell){
        if (null != cell) {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: // 数字
                    if (DateUtil.isCellDateFormatted(cell)){
                        // 日期
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        return sdf.format(cell.getDateCellValue());
                    }
                    // 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    return bd.toPlainString();
                case STRING: // 字符串
                    return cell.getStringCellValue() + "";
                case BOOLEAN: // Boolean
                    return cell.getBooleanCellValue() + "";
                case FORMULA: // 公式
                    return cell.getCellFormula() + "";
                case BLANK: // 空值
                    return "";
                case ERROR: // 故障
                    return "";
                default:
                    return "未知类型";
            }
        }else{
            return "";
        }
    }

}

3、Excel导入应用案例

//excel导入解析
    @PostMapping("uploadList")
    public BaseRespBo uploadList(@RequestParam("file") MultipartFile file){
        BaseRespBo rsp = new BaseRespBo();
        //String contentType = file.getContentType();
        //String fileName = file.getOriginalFilename();
        //logger.info("获取文件类型和文件名" + contentType + fileName);
        if (file == null) {
            rsp.setCode(ErrorCodeEnum.FILE_IS_EMPTY.getCode());
            rsp.setMsg(ErrorCodeEnum.FILE_IS_EMPTY.getMessage());
            return rsp;
        }
        try {
            //根据路径获取这个操作excel的实例
            XSSFWorkbook wb = null;
            try {
                wb = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
               logger.error("Operation excel error:", e);
            }
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            //实体类集合
            List<FocusPersonDomain> importData = new ArrayList<>();
            XSSFRow row = null;
            //循环sheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                FocusPersonDomain data = new FocusPersonDomain();
//                data.setName(row.getCell(0).getStringCellValue());
//                data.setIdCard(row.getCell(1).getStringCellValue());
//                data.setSex(row.getCell(2).getStringCellValue());
//                data.setStatus(row.getCell(3).getStringCellValue());
//                data.setNation(row.getCell(4).getStringCellValue());
//                data.setHometown(row.getCell(5).getStringCellValue());
//                data.setDistrict(row.getCell(6).getStringCellValue());
//                data.setAddress(row.getCell(7).getStringCellValue());
//                data.setEmployer(row.getCell(8).getStringCellValue());
//                data.setPosition(row.getCell(9).getStringCellValue());
//                data.setResidence(row.getCell(10).getStringCellValue());
//                data.setAddReason(row.getCell(11).getStringCellValue());
                data.setName(ExcelFormatUtil.getValue(row.getCell(0)));
                data.setIdCard(ExcelFormatUtil.getValue(row.getCell(1)));
                data.setSex(ExcelFormatUtil.getValue(row.getCell(2)));
                data.setStatus(ExcelFormatUtil.getValue(row.getCell(3)));
                data.setNation(ExcelFormatUtil.getValue(row.getCell(4)));
                data.setHometown(ExcelFormatUtil.getValue(row.getCell(5)));
                data.setDistrict(ExcelFormatUtil.getValue(row.getCell(6)));
                data.setAddress(ExcelFormatUtil.getValue(row.getCell(7)));
                data.setEmployer(ExcelFormatUtil.getValue(row.getCell(8)));
                data.setPosition(ExcelFormatUtil.getValue(row.getCell(9)));
                data.setResidence(ExcelFormatUtil.getValue(row.getCell(10)));
                data.setAddReason(ExcelFormatUtil.getValue(row.getCell(11)));
                importData.add(data);
            }
            Boolean result = focusPersonService.batchSaveOrUpdateFocusPerson(NoRepeatUtil.removeRepeatByFocusPerson(importData));
        } catch (Exception e) {
            rsp.setCode(ErrorCodeEnum.OPERATION_EXCEL_ERROR.getCode());
            rsp.setMsg(ErrorCodeEnum.OPERATION_EXCEL_ERROR.getMessage());
            return rsp;
        }
        return rsp;
    }

4、Excel导出应用案例

 @RequestMapping("exportFocus")
    public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String file ="重点人员信息.xlsx";
        response.setContentType(request.getServletContext().getMimeType(file));
        response.setHeader("Content-Disposition",
                "attachment; filename=\"" + file + "\"; filename*=utf-8' ' " + URLEncoder.encode(file, "utf-8"));
        //声明一个工作簿
        XSSFWorkbook hwb = new XSSFWorkbook();
        //声明一个单子并命名
        XSSFSheet sheet = hwb.createSheet("重点人信息");
        //给单子名称一个长度
        sheet.setDefaultColumnWidth((short)15);
        //生成一个样式
        XSSFCellStyle style = hwb.createCellStyle();
        //创建第一行（也可以成为表头）
        XSSFRow row = sheet.createRow(0);
        //样式字体居中
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //给表头第一行一次创建单元格
        XSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("姓名(XM)");
        cell.setCellStyle(style);

        cell = row.createCell((short)1);
        cell.setCellValue("身份证号(GMSFHM)");
        cell.setCellStyle(style);

        cell = row.createCell((short)2);
        cell.setCellValue("性别(XB)");
        cell.setCellStyle(style);

        cell = row.createCell((short)3);
        cell.setCellValue("本省管控状态(GKZT)");
        cell.setCellStyle(style);

        cell = row.createCell((short)4);
        cell.setCellValue("民族(MZ)");
        cell.setCellStyle(style);

        cell = row.createCell((short)5);
        cell.setCellValue("籍贯(JG_XZQH)");
        cell.setCellStyle(style);

        cell = row.createCell((short)6);
        cell.setCellValue("户籍地行政区划(HJD_XZQH)");
        cell.setCellStyle(style);

        cell = row.createCell((short)7);
        cell.setCellValue("户籍地详址(HJDXXDZ)");
        cell.setCellStyle(style);

        cell = row.createCell((short)8);
        cell.setCellValue("工作单位(GZDW)");
        cell.setCellStyle(style);

        cell = row.createCell((short)9);
        cell.setCellValue("职务(ZW)");
        cell.setCellStyle(style);

        cell = row.createCell((short)10);
        cell.setCellValue("境内常驻扎地(JZDXZ)");
        cell.setCellStyle(style);

        cell = row.createCell((short)11);
        cell.setCellValue("入库原因(RKYY)");
        cell.setCellStyle(style);



        List<Integer> idList = new ArrayList<>();
        idList.add(53);
        idList.add(54);
        List<FocusPersonDomain> focusPersonDomains = focusPersonMapper.selectBatchIds(idList);
        for(short i=0;i<focusPersonDomains.size();i++){
            row =sheet.createRow(i+1);
            row.createCell(0).setCellValue(focusPersonDomains.get(i).getName());
            row.createCell(1).setCellValue(focusPersonDomains.get(i).getIdCard());
            row.createCell(2).setCellValue(focusPersonDomains.get(i).getSex());
            row.createCell(3).setCellValue(focusPersonDomains.get(i).getStatus());
            row.createCell(4).setCellValue(focusPersonDomains.get(i).getNation());
            row.createCell(5).setCellValue(focusPersonDomains.get(i).getHometown());
            row.createCell(6).setCellValue(focusPersonDomains.get(i).getDistrict());
            row.createCell(7).setCellValue(focusPersonDomains.get(i).getAddress());
            row.createCell(8).setCellValue(focusPersonDomains.get(i).getEmployer());
            row.createCell(9).setCellValue(focusPersonDomains.get(i).getPosition());
            row.createCell(10).setCellValue(focusPersonDomains.get(i).getResidence());
            row.createCell(11).setCellValue(focusPersonDomains.get(i).getAddReason());
        }
        hwb.write(response.getOutputStream());
        hwb.close();
    }