Java 读写 Excel

1. POI

添加依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.0</version>
</dependency>

POI 中的组件

组件	含义
HSSFWorkbook	excel的文档对象
HSSFSheet	excel的表单
HSSFRow	excel的行
HSSFCell	excel的列
HSSFFont	excel的字体
HSSFDataFormat	日期格式
HSSFHeader	sheet的头
HSSFFooter	sheet的尾

一个Excel文件对应于一个HSSFWorkbook对象，一个HSSFWorkbook对象可以有多个HSSFSheet对象组成，一个HSSFSheet对象是由多个HSSFRow对象组成，一个HSSFRow对象是由多个HSSFCell对象组成。

1.1 写入

填充方式，前景色和背景色

1. 未指定填充：即使设置了前景色和背景色，也不会显示
2. 设置填充，前景色，背景色：显示填充，前景色和背景色混合颜色
3. 设置填充，前景色：显示填充，前景色
4. 设置填充，背景色：显示填充，不显示背景色

@Test
public void writeTest(){
    HSSFWorkbook workbook = new HSSFWorkbook();

    //列样式
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    //水平居中
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //垂直居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    //设置填充方式
    cellStyle.setFillPattern(FillPatternType.DIAMONDS);
    //设置前景色
    cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
    //设置背景色
    cellStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());

    //左边框
    cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
    cellStyle.setBorderLeft(BorderStyle.DOTTED);
    //右边框
    cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
    cellStyle.setBorderRight(BorderStyle.THICK);
    //上边框
    cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
    cellStyle.setBorderTop(BorderStyle.DASHED);
    //下边框
    cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.PINK.getIndex());
    cellStyle.setBorderBottom(BorderStyle.HAIR);

    //字体样式
    HSSFFont font = workbook.createFont();
    font.setFontName("黑体");
    font.setFontHeightInPoints((short) 15);
    font.setColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
    cellStyle.setFont(font);

    for(int sheetNum = 0; sheetNum <= 2 ; sheetNum++){
        HSSFSheet sheet = workbook.createSheet("测试sheet" + sheetNum);

        //行高
        sheet.setDefaultRowHeightInPoints(40);
        //指定列宽
        sheet.setColumnWidth(0,256 * 15);
        sheet.setColumnWidth(1,256 * 30);
        sheet.setColumnWidth(2,256 * 30);

        //标题
        HSSFRow titleRow = sheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("测试标题");
        titleCell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));

        //头部
        HSSFRow headRow = sheet.createRow(1);
        headRow.createCell(0).setCellValue("序号");
        headRow.getCell(0).setCellStyle(cellStyle);
        headRow.createCell(1).setCellValue("测试1");
        headRow.getCell(1).setCellStyle(cellStyle);
        headRow.createCell(2).setCellValue("测试2");
        headRow.getCell(2).setCellStyle(cellStyle);

        //设值
        for(int i = 2 ; i <= 10 + sheetNum ; i++){
            HSSFRow row = sheet.createRow(i);
            for(int j = 0 ; j <= 2 ; j++){
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                if(j == 0){
                    cell.setCellValue(i - 1);
                }else{
                    cell.setCellValue("设值 行: " + (i - 1) + " 列: " + (j + 1));
                }
            }
        }
    }

    try(
        FileOutputStream outputStream = new FileOutputStream("D:\\测试.xls");
    	){
        workbook.write(outputStream);
    }catch (IOException e){
        e.printStackTrace();
    }
}

效果

1.2 读取

@Test
public void readTest(){
    Map<String, List<Object>> resultMap = new HashMap<>(16);

    try(
        FileInputStream inputStream = new FileInputStream("D:\\测试.xls");
    	){
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        for(int sheetNum = 0 ; sheetNum < workbook.getNumberOfSheets() ; sheetNum++){
            HSSFSheet sheet = workbook.getSheetAt(sheetNum);
            for(int rowNum = 2 ; rowNum < sheet.getPhysicalNumberOfRows() ; rowNum++){
                HSSFRow row = sheet.getRow(rowNum);
                List<Object> cellList = new ArrayList<>();
                for(int cellNum = 0 ; cellNum < row.getPhysicalNumberOfCells() ; cellNum++){
                    HSSFCell cell = row.getCell(cellNum);
                    switch (cell.getCellType()) {
                        case NUMERIC:
                            cellList.add(cell.getNumericCellValue());
                            break;
                        case STRING:
                            cellList.add(cell.getStringCellValue());
                        default:
                    }
                }
                StringBuilder keyStringBuilder = new StringBuilder();
                resultMap.put(keyStringBuilder
                        .append("sheet")
                        .append(sheetNum)
                        .append("row")
                        .append(rowNum - 1).toString(),cellList);
            }
        }
    }catch (IOException e){
        e.printStackTrace();
    }
}

2. easyexcel

添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

2.1 写入

// 头背景
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
// 头字体
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 11, color = 8)
// 内容背景
@ContentStyle(fillPatternType = FillPatternType.NO_FILL, fillForegroundColor = 9)
// 内容字体
@ContentFontStyle(fontName = "等线", fontHeightInPoints = 11, color = 8)
public class UserStyle {

    @ExcelProperty(value = "用户名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;

    @ExcelProperty(value = "身份证", index = 2)
    private String code;

    @ExcelProperty(value = "地址", index = 3)
    private String address;
    // getter and setter
}

@Test
public void writeUserStyle() {
    List<UserStyle> dataList = new ArrayList<>();
    dataList.add(new UserStyle("张一",18,"350111199912121212","鼓楼区XXX"));
    dataList.add(new UserStyle("张二",19,"350111200012121212","晋安区XXX"));
    dataList.add(new UserStyle("张三",20,"3501111200112121212","台江区XXX"));
    dataList.add(new UserStyle("张四",21,"350111200212121212","仓山区XXX"));

    String fileName = "D:\\注解自定义样式.xlsx";
    // 指定类并写到第一个sheet中，然后文件流会自动关闭
    EasyExcel.write(fileName, UserStyle.class).sheet().doWrite(dataList);
}

2.2 读取

public class UserListener extends AnalysisEventListener<User> {
    private static final Logger logger = LoggerFactory.getLogger(UserListener.class);

    List<User> list = new ArrayList<>();

    /**
     * 每一条数据解析都会来调用
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(User data, AnalysisContext context) {
        logger.info("获取一条数据:{}", data);
        list.add(data);
    }

    /**
     * 所有数据解析完成调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        logger.info("所有数据解析完成！");
    }
}

@Test
public void read() {
    String fileName = "D:\\测试.xlsx";
    List<User> list = EasyExcel.read(fileName, User.class, new UserListener()).sheet().doReadSync();
    System.out.println(list);
}

参考：
HSSFWorkbook 模版使用
GitHub alibaba/easyexcel