excel-poi读取excel

excel-poi读取excel 1. 代码前准备

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

		
			org.apache.poi
			poi
			4.1.2
		
		
			org.apache.poi
			poi-ooxml
			4.1.2
		

2. 公共实体

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


@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();
        
        int rowNum = 0;
        List list = new ArrayList<>();

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

            

            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);
    }

    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) {
        
        List list = new ArrayList<>();
        list.add(new User("张三", "男", "22", "13133265542", "是个帅哥"));
        list.add(new User("张二", "男", "22", "13552112222", "是张三的弟弟"));
        list.add(new User("张一", "", "21", "13133265542", "是张二的弟弟"));

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

        Workbook workbook = null;
        OutputStream out = null;

        //场景二
        FileOutputStream fileOut = null;

        try {
            
            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());
            }

            
//            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文件时，同时设置单元格的样式