excel-poi读取excel

1. 代码前准备

在项目poi.xml中导入excel-poi相关的依赖

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

2. 公共实体

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @Author: jingwenjuan
 * @Description:
 */
@Data
public class User {

    @ApiModelProperty("姓名")
    private String name;

    @ApiModelProperty("性别")
    private String sex;

    @ApiModelProperty("年龄")
    private String age;

    @ApiModelProperty("手机号")
    private String mobile;

    @ApiModelProperty("备注")
    private String remarks;


    public User() {}

    public User(String name,String sex,String age,String mobile,String remarks) {
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.mobile = mobile;
        this.remarks  = remarks;
    }
    
}

3. 读取excel

@ApiOperation("上传excel-读取excel内容")
    @PostMapping("/readExcel")
    public ApiRes readExcel(MultipartFile file) {
        ApiRes resp = new ApiRes();
        /**
         * 1. 读取数据源
         * 2. 如有需要，校验表头
         * 3. 按行开始读取excel中的内容
         */
        int rowNum = 0;
        List<User> list = new ArrayList<>();

        try {
            InputStream fis = file.getInputStream();
            Workbook book = WorkbookFactory.create(fis);
            Sheet sheet = book.getSheet("sheet1");

            /**
             * 获取excel
             * 1. 根据名称获取excel book.getSheet("sheet1");
             * 2. 直接获取第一个excel book.getSheetAt(0); 序列从0开始
             *
             * 表格行从0开始
             * 表格列从0开始
             *
             * 获取行：sheet.getRow(第n行);
             * 获取列：row.getCell(第n列);
             *
             * sheet的最后一行序号(总行数=序号+1)：sheet.getLastRowNum();
             * row的列数（！=最后一列的序号）：row0.getLastCellNum();
             *
             */

            if (sheet == null) {
                resp.fail("找不到sheet1表格");
                return resp;
            }

            //校验表头
            List titles = Arrays.asList("姓名","性别","年龄","手机号","备注");
            Row row0 = sheet.getRow(0);
            for (int i = 0; i < row0.getLastCellNum(); i++) {
                Cell cell = row0.getCell(i);
                String val = getCellStringValue(cell);
                if (!val.equals(titles.get(i))) {
                    resp.fail("该表格表头与模板不一致：" + val + "!=" + titles.get(i));
                    return resp;
                }
            }

            //读取数据
            for (rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);

                User user = new User();
                user.setName(getCellStringValue(row.getCell(0)));
                user.setSex(getCellStringValue(row.getCell(1)));
                user.setAge(getCellStringValue(row.getCell(2)));
                user.setMobile(getCellStringValue(row.getCell(3)));
                user.setRemarks(getCellStringValue(row.getCell(4)));

                list.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
            resp.fail("第" + (rowNum + 1) + "行数据解析错误");
            return resp;
        }

        if (!CollectionUtils.isEmpty(list)) {
            list.forEach(System.out::println);
        }

        return resp.ok(list);
    }

/**
     * 解析单元格
     *
     * @param cell
     * @return
     */
    public static String getCellStringValue(Cell cell) {
        String cellValue = "";

        if (cell == null) {
            return cellValue;
        }

        switch (cell.getCellType()) {
            //1. 字符串类型
            case STRING:
                cellValue = cell.getStringCellValue();
                if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) {
                    cellValue = " ";
                }
                break;
            //2. 数值类型
            case NUMERIC:
//                cell.getNumericCellValue();cell.getDateCellValue();
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
                break;
            //3. 表达式类型
            case FORMULA:
//                cell.getCellFormula();
                cell.setCellType(CellType.NUMERIC);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BLANK:
                //4. 空
                break;
            case BOOLEAN:
                //5. 布尔类型
//                cell.getBooleanCellValue();
                break;
            case ERROR:
                //6. 异常类型
//                cell.getErrorCellValue();
                break;
            default:
                break;
        }
        return cellValue;
    }

4. 写入数据，导出为excel文件

@ApiOperation("输出excel-生成excel")
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) {
        /**
         * 1. 准备要导入到excel中的数据（一般是从数据库中查询到的实体列表）
         * 2. 写入数据到工作簿中
         */
        List<User> list = new ArrayList<>();
        list.add(new User("张三", "男", "22", "13133265542", "是个帅哥"));
        list.add(new User("张二", "男", "22", "13552112222", "是张三的弟弟"));
        list.add(new User("张一", "", "21", "13133265542", "是张二的弟弟"));

        List<String> titles = Arrays.asList("姓名", "性别", "年龄", "手机号", "备注");

        Workbook workbook = null;
        OutputStream out = null;

        //场景二
        FileOutputStream fileOut = null;

        try {
            /**
             * 1. 生成xlsx的excel
             * 2. 生成sheet表，写入第一行的表头
             * 3. 写表头
             * 4. 写入其他数据
             */
            workbook = new SXSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            Row row0 = sheet.createRow(0);
            for (int i = 0; i < titles.size(); i++) {
                Cell cell = row0.createCell(i);
                cell.setCellValue(titles.get(i));
            }

            int rowNum = 1;
            for (User user : list) {
                Row row = sheet.createRow(rowNum++);
                int cellNum = 0;

                Cell cell = row.createCell(cellNum++);
                cell.setCellValue(user.getName());

                cell = row.createCell(cellNum++);
                cell.setCellValue(user.getSex());

                cell = row.createCell(cellNum++);
                cell.setCellValue(user.getAge());

                cell = row.createCell(cellNum++);
                cell.setCellValue(user.getMobile());

                cell = row.createCell(cellNum++);
                cell.setCellValue(user.getRemarks());
            }

            /**
             * 场景一：在前端导出excel文件，然后下载
             * 场景二：在本地文件夹中生成excel文件
             */
//            String excelName = "excel导出";
//            String fileName = excelName + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx";
//            fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
//            response.setContentType("application/x-download");
//            response.setCharacterEncoding("UTF-8");
//            response.addHeader("Pargam", "no-cache");
//            response.addHeader("Cache-Control", "no-cache");
//            response.flushBuffer();
//
//            out = response.getOutputStream();
//            workbook.write(out);
//            out.flush();

            //场景二
            String filePath = "F:\\jwork\\excel\\excel文件\\writeExcel.xlsx";
            File exportFile = new File(filePath);
            if (!exportFile.exists()) {
                exportFile.createNewFile();
            }

            fileOut = new FileOutputStream(filePath);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            System.out.println("写入Excel过程出错，错误原因：" + e.getMessage());
        } finally {
//            try {
//                if (null != workbook) {
//                    workbook.close();
//                }
//                if (null != out) {
//                    out.close();
//                }
//            } catch (Exception e) {
//                System.out.println("关闭workbook或者outputStream出错" + e.getMessage());
//            }

            //场景二
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                System.out.println("关闭workbook或者FileOutputStream出错" + e.getMessage());
            }
        }

    }

5. 扩展-生成excel文件时，同时设置单元格的样式