文章目录

Springboot使用POI读写excel

一、poi简单介绍

在这里插入图片描述

  • 依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

1. workbook工作簿

由于Excel存在xls以及xlsx两种格式，所以创建方式也有所不同。

  • 对于xls格式，需要使用HSSFWorkbook来创建对象；
  • 对于xlsx格式，需要使用XSSFWorkbook来创建工作薄；

需要注意HSSFWorkBook与XSSSFWorkbook两个类都是Workbook接口的实现类。

Workbook orkbook = null;
if(fileName.endsWith(".xls")) {
    orkbook = new HSSFWorkbook();
} else if(fileName.endsWith(".xlsx")) {
    orkbook = new XSSFWorkbook();
} else {
    throw new Exception("文件类型错误！");
}

2. Sheet表格

在这里插入图片描述

创建Sheet的时候，存在多种类型。所有我们使用其父类Sheet去处理对应的子类实现。

Sheet sheet = workbook.getSheetAt(sheetNum); //读取

Sheet sheet = workbook.createSheet(sheetName);  //创建

3. Row

作用是定位到特定的行。

  • sheet.getFirstRowNum() ：获取实际第一行
  • sheet.getPhysicalNumberOfRows()：返回有数据的行数，比如n行里有m个空行，返回n-m
  • sheet.getLastRowNum()：返回最后一行数据的下标，默认是从0开始
Row row = sheet.getRow(int index); //读取

Row row = sheet.createRow(int index);  //创建

4. Cell

定位到特定的表格

获取到cell上的数据，进行“业务处理”，当然不同的业务逻辑不同，这里写几个特殊的处理方法。

  • 获取cell对象，下标从0开始
Cell cell = row.getCell(int index);  //读取

Cell cell = row.createCell(int index); //创建
  • 关于cell对象的类型

在这里插入图片描述

cell.setCellType(Cell.CELL_TYPE_STRING);

  • 获取cell对象的内容
    在这里插入图片描述

  • 示例

// 第十九列 创建时间
cell = row.getCell(18);
if (cell.getCellType().equals(CellType.STRING)) {
    String value = cell.getStringCellValue();
    if (StringUtils.isNotBlank(value)) {
        resultData.setCreate(LocalDateTime.parse(value, DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")));
    }
} else if (cell.getCellType().equals(CellType.NUMERIC)) {
    LocalDateTime localDateTimeCellValue = cell.getLocalDateTimeCellValue();
    resultData.setCreate(localDateTimeCellValue);
}

二、Springboot导出excel

1. 创建controller

@RestController
@RequestMapping("/h5Questionnaire")
public class H5QuestionnaireController {

    @Autowired
    private H5QuestionnaireService h5QuestionnaireService;

    @GetMapping("/download")
    public void download(HttpServletResponse response) {
        Workbook workbook = h5QuestionnaireService.getWorkbook();

        OutputStream outputStream = null;
        String fileName = "问卷调查结果" + LocalDateTime.now().format(DateTimeFormatter.ISO_DATE_TIME) + ".xlsx";
        response.reset();
        try {
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"ISO-8859-1"));

            outputStream =  new BufferedOutputStream(response.getOutputStream());
            workbook.write(outputStream);
            outputStream.flush();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }
}

2. 创建service

关于对象，利用反射去做，就不用一列一列的去创建单元格啦，直接循环搞定

注意，对象的字段顺序和标题顺序要对上

@Service
@Slf4j
public class H5Questionnaire2Service {

    // 反射对象的私有字段
    private static Field[] declaredFields = H5Questionnaire.class.getDeclaredFields();

    @Autowired
    private H5Questionnaire2Mapper h5QuestionnaireMapper;
	
    // TODO 以下业务方法
}

    //导出 workwoob
    public Workbook getWorkbook(){

        log.info("开始创建工作簿...");

        // 创建工作簿
        Workbook workbook = new SXSSFWorkbook(); //生成.xlsx的excel
        // 创建工作表
        Sheet sheet = workbook.createSheet();

        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        cellStyle.setWrapText(true);//自动换行

        log.info("开始创建标题行...");        
        Row head = sheet.createRow(1);//列是从0开始计算的，我这里空了一行
        head.setHeight((short) (8 * 20 * 20)); //1/20th of a point    
        
        //第0列标题,用户id
        Cell cellUid = head.createCell(0);
        cellUid.setCellValue("UserID");
       
        //第1-119列标题
        Map<String, Integer> titleMap = QuestionnaireUtils.getTitleMap();
        int i = 1;
        for (Map.Entry<String, Integer> titie : titleMap.entrySet()) {
            Cell cell = head.createCell(i);
            cell.setCellValue(titie.getKey());
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, 5 * 2 * 256); //in units of 1/256th of a character width
            i++;
        }
        

		log.info("开始处理数据...");        
        List<H5Questionnaire> list = h5QuestionnaireMapper.selectQuestionnaire();
        int rowNum = 2; //从标题下一行开始

        for (Iterator<H5Questionnaire> it = list.iterator(); it.hasNext(); ) {
            H5Questionnaire data = it.next();
            if (data == null) {
                continue;
            }
            // 构建每行的数据内容
            Row row = sheet.createRow(rowNum++);
            convertDataToRow(h5QuestionnaireVO, row);
        }

        log.info("导出完成");
        return workbook;

    }

3. 处理每行数据

这里其实和 EasyExcel差不多，都是对每一行的数据进行处理

    private void convertDataToRow(H5Questionnaire data, Row row) {
        int cellNum = 0;
        Cell cell;

        // 第0列 用户id
        cell = row.createCell(cellNum++);
        if (data.getUserId() != null) {
            cell.setCellValue(data.getUserId());
        }

        // 第1-119列数据 利用反射
        Map<String, Integer> titleMap = QuestionnaireUtils.getTitleMap();
        for (Map.Entry<String, Integer> title : titleMap.entrySet()) {
            //log.info("第"+row.getRowNum()+"遍历");

            //通过 当前列 获取对应的对象的 属性值
            Field field = declaredFields[title.getValue() + 2];
            field.setAccessible(true);

            try {
                Object fieldValue = field.get(data);
                cell = row.createCell(cellNum);
                if (fieldValue != null) {
                    log.info("第" + row.getRowNum() + "行，第" + cellNum + "列，内容：" + fieldValue);

                    // TODO 处理单元格内容
                    content = fieldValue.toString();
                    cell.setCellValue(content);
                }

            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            cellNum++;
        }


    }

大部分情况下，直接 String content = fieldValue.toString(); 就结束了

4. 设置标题行的样式

	private CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置 左右居中，上下局上
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

5. 创建标题

新建工具类，将标题都添加进去
利用对象的反射，可以使用下标，也可以字段名

public class QuestionnaireUtils {
    private static Map<String, Integer> titleMap = null;

    public static Map<String, Integer> getTitleMap() {
        if (titleMap == null) {
            titleMap = new LinkedHashMap<>();
        }
        titleMap.put("1.您目前是否有贷款需求？", 1);
        titleMap.put("2.您打算贷多少钱？（万元）", 2);
        titleMap.put("3.您能承受的最高年化利率是多少？（%）", 3);
        // titleMap.put...
        return titleMap;
    }

    public static Map<String, String> getUserTable() {
        if (userTable == null) {
            userTable = new LinkedHashMap<>();
        }
        userTable.put("创建时间", "createtime");        
        userTable.put("姓名", "name");
        userTable.put("性别", "idNum");
        userTable.put("学历", "education");
        userTable.put("手机号", "contact");
        userTable.put("出生日期", "birthday");       
        // userTable.put...
        return userTable;
    }

}

6. 到了验证成果的时候啦

大功告成
swagger上的文件名是乱码不要紧，直接黏贴地址到浏览器不乱码就行


在这里插入图片描述

7. 本地导出版本

    //导出excel
    public static void export1(String fileName, Workbook workbook) {

        FileOutputStream fileOutputStream = null;
        try {
            File outFile = new File(fileName);
            if (!outFile.exists()) {
                outFile.createNewFile();
            }
            fileOutputStream = new FileOutputStream(outFile);
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
        } catch (Exception e) {
            log.warn("输出Excel时发生错误，错误原因：" + e.getMessage());
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                log.warn("关闭输出流时发生错误，错误原因：" + e.getMessage());
            }
        }
        
    }

三、poi读取excel

	@Test    
	public void read() {
        String fileName = "D:\\project\\2020.10.28-调查问卷.xlsx";

        creatWorkBook(fileName);
    }

1. 解析文件

	private void creatWorkBook(String fileName) {

        log.info("判断文件是否存在...");
        File excelFile = new File(fileName);
        if (!excelFile.exists()) {
            log.warn("指定的Excel文件不存在！");
            return;
        }

        log.info("创建 WorkBook ...");
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
        Workbook workbook = null;
        try {
            FileInputStream fileInputStream = new FileInputStream(excelFile);
            if (fileType.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fileInputStream); //生成.xls的excel
            } else if (fileType.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fileInputStream); //生成.xlsx的excel
            } else {
                log.warn("文件格式不对");
                return;
            }
            log.info("开始解析 WorkBook...");
            parseExcel(workbook);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

2. 解析WorkBook

    private void parseExcel(Workbook workbook) {
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum); // 获取表格
            log.info("sheet = {}", sheet.getSheetName());

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

           
            Row firstRow = sheet.getRow(sheet.getFirstRowNum()); // 获取第一行，一般是标题
            if (null == firstRow) {
                log.warn("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1; //标题下面的数据,数据起始行
            int rowEnd = sheet.getPhysicalNumberOfRows();//获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；

            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                log.info("第 {} 行", row.getRowNum());
                if (null == row) {
                    continue;
                }

                //处理Cell
                for (int cellNum = 1; cellNum < 146; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        String content = convertCellValueToString(cell);
                        log.info("第" + row.getRowNum() + "行，第" + cellNum + "列，内容：" + content);
                        // TODO 处理数据
                    }
                }

            }
            
            
        }        
    }

在这里插入图片描述

3. 处理单元格类型

    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String content = null;
        try {
            switch (cell.getCellType()) {
                case NUMERIC:   //数字或者时间
                    Double doubleValue = cell.getNumericCellValue();
                    // 格式化科学计数法，取一位整数
                    DecimalFormat df = new DecimalFormat("0");
                    content = df.format(doubleValue);
                    break;
                case STRING:    //字符串
                    content = cell.getStringCellValue();
                    break;
                case BOOLEAN:   //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    content = booleanValue.toString();
                    break;
                case BLANK:     // 空值
                    break;
                case FORMULA:   // 公式
                    content = cell.getCellFormula();
                    break;
                case ERROR:     // 故障
                    break;
                default:
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return content;
    }

四、Poi的版本差异

公司项目有点老，用的老版本的poi，然后使用过程中出现以下错误

  • 版本
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中 类好像还要对应

cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//设置边框
cellStyle.setBorderTop(CellStyle.BORDER_THIN);//设置边框
cellStyle.setBorderRight(CellStyle.BORDER_THIN);//设置边框
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//设置边框
  • 版本
cellStyle.setAlignment(HorizontalAlignment.CENTER);//居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直

cellStyle.setBorderBottom(BorderStyle.THIN); //下边框    
cellStyle.setBorderTop(BorderStyle.THIN); //上边框    
cellStyle.setBorderRight(BorderStyle.THIN); //右边框    
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框

五、Springboot使用EasyExcel读写excel

使用EasyExcel读写excel

# spring boot # java
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

开源成为金融领域创新发展的新动力引擎

开源成为金融领域创新发展的新动力引擎

avatar 开放原子开发者工作坊

开源成为推动城市数字化转型的核心动力

城市，不仅是人们工作与生活的基本载体，更是承载着亿万人民的希望与梦想。近年来，我国智慧城市建设如火如荼，开源技术的深度融合为其发展注入了新的活力。在民生、环保、公共安全、城市服务等多个领域，开源技术的应用不仅为民众带来了切实的便捷，而且促进了政府决策的科学化，显著提升了城市综合治理能力。

avatar 开放原子开发者工作坊

OpenLoong项目通过技术监督委员会（TOC）评审

OpenLoong项目通过技术监督委员会（TOC）评审

avatar 开放原子开发者工作坊