poi操作excel的一个小demo以及easypoi的简单使用

apache commons poi 操作excel的一个小demo

apache commons poi 官网

maven

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

代码

从txt的文本文件写入到cexel

private static final String[] headers = new String[]{"身份证","姓名","性别", "年龄", "地址"};
public static boolean txt2Excel(String srcPath, String srcFile, String targetPath, String targetFile) {
        try {
            // 创建工作薄
            Workbook wb = new HSSFWorkbook();
            try (OutputStream fileOut = new FileOutputStream(targetPath + targetFile)) {
                // 创建表
                Sheet sheet = wb.createSheet("new sheet");
                List<String> lines = FileUtils.readLines(new File(srcPath + srcFile));
                // 创建行
                Row createRow = sheet.createRow(0);
                Map<String, Object> properties = new HashMap<String, Object>();
                // 背景颜色
                properties.put(CellUtil.FILL_FOREGROUND_COLOR, HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
                // 设置单元格为文本格式
                properties.put(CellUtil.WRAP_TEXT, CellType.STRING);
                properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = createRow.createCell(i);
                    CellUtil.setCellStyleProperties(cell, properties);
                    cell.setCellValue(headers[i]);
                    //                    CellStyle cellStyle = wb.createCellStyle();
                    //                    cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
                    //                    cellStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
                    //                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    //                    cell.setCellStyle(cellStyle);

                }
                Map<String, Object> properties1 = Collections.singletonMap(CellUtil.WRAP_TEXT, CellType.STRING);
                for (int i = 0; i < lines.size(); i++) {
                    String rowStr = lines.get(i);
                    Row row = sheet.createRow(i + 1);
                    String[] split = rowStr.split("\t");
                    for (int j = 0; j < split.length; j++) {
                        Cell cell = row.createCell(j);
                        if (j == 0) {
                        	// 身份证 这一列要设置为文本, 否者会变成科学计数法, 导致数据丢失
                            CellUtil.setCellStyleProperties(cell, properties1);
                        }
                        cell.setCellValue(split[j]);
                    }

                }
                wb.write(fileOut);
            }
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;

    }

FILL_FOREGROUND_COLOR 和FILL_BACKGROUND_COLOR的区别?

FILL_FOREGROUND_COLOR 是前景色, FILL_BACKGROUND_COLOR是背景色, 但是在用的时候, FILL_BACKGROUND_COLOR一直设置不了颜色, 用了前景色才能设置。

easypoi的使用

由于apache commons poi操作比较复杂, 我们使用easypoi来快捷完成上面的demo

easypoi github

详细教程

• maven

<!-- 如果需要和springboot整合 -->
<dependency>
	  <groupId>cn.afterturn</groupId>
	  <artifactId>easypoi-spring-boot-starter</artifactId>
	  <version>4.2.0</version>
 </dependency>
 <dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>4.2.0</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-web</artifactId>
	<version>4.2.0</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-annotation</artifactId>
	<version>4.2.0</version>
</dependency>

• user.java

public class User implements Serializable{
    @Excel(name = "身份证", type = 1)
    private String id;
    @Excel(name = "学生姓名")
    private String name;
    @Excel(name = "性别")
    private String sex;
    @Excel(name = "年龄")
    private int age;
    @Excel(name = "地址")
    private String address;
	// ...省略getter setter 构造
}

• ExcelStyle.java

实现ExcelExportStylerDefaultImpl 默认的样式类

这边有点不太明白, 表头居然是用getTitleStyle 来设置, 而不是getHeaderStyle?

// 样式
public class ExcelStyle extends ExcelExportStylerDefaultImpl{

    public ExcelStyle(Workbook workbook) {
        super(workbook);
    }
//
    @Override
    public CellStyle getTitleStyle(short color) {
         CellStyle headerStyle = this.workbook.createCellStyle();
//        headerStyle.setFillBackgroundColor(IndexedColors.ORANGE.getIndex());
//        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return headerStyle;
    }

//    @Override
//    public CellStyle getHeaderStyle(short color) {
//        CellStyle headerStyle = this.workbook.createCellStyle();
////        headerStyle.setFillBackgroundColor(IndexedColors.ORANGE.getIndex());
////        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        headerStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
//        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        return headerStyle;
//    }


}

• getHeaderStyle.java

public static void main(String[] args) throws Exception {
    // 伪造数据
    ArrayList<Object> datas = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        User user = new User("1221234323452341234134", "xdb", "男", 20, "乌托邦");
        datas.add(user);
    }
    // 设置样式
    ExportParams exportParams = new ExportParams(null, "用户", ExcelType.HSSF);
    exportParams.setStyle(ExcelStyle.class);
    Workbook wb = ExcelExportUtil.exportExcel(exportParams, User.class, datas);
    wb.write(new FileOutputStream(new File("d://user.xls")));
}