一种操作复杂样式Excel的思路

基于 上一篇 提到的想法，今天抽了时间实现出来了。

先贴截图。

运行结果

个人觉得填充底色，合并单元格，各种设置格式之流的操作用纯代码来实现还是蛮耗费精力的。但是，假如是基于下面这样已经调好格式的模板，只要要往里填充数据的话，工作量要小很多。

模板

模板通过2个sheet拼接完成。代码填充好第一个sheet的数据之后，再把第二个sheet的内容拷贝到第一个sheet的最后。

数据是通过POJO和自己设置注解配合完成的。目前注解主要用于取得单元格所对应的行，后期还会增加其余属性。时间关系，POJO的属性暂时也只支持了String一种类型。

注解定义如下：

/** * @author nathan * @date 2019/3/24 */@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelProperties {    int index();}

一个示例POJO如下：

/** * @author nathan * @date 2019/3/24 */@Getter@Setterpublic class FooDTO {    @ExcelProperties(index = 0)    private String empty1;    @ExcelProperties(index = 1)    private String busiName;    @ExcelProperties(index = 2)    private String busiType;    @ExcelProperties(index = 3)    private String empty2;    @ExcelProperties(index = 4)    private String value1;    @ExcelProperties(index = 5)    private String value2;    @ExcelProperties(index = 6)    private String empty3;}

一个可以运行的Demo：

/** * @author nathan * @date 2019/3/24 */public class RptUtil {    public static void main(String[] args) {        // 从第1行，第4列开始，客户指定        int startRow = 0;        int startCol = 4;        List<FooDTO> foos = getFooList();        int sheet2StartRow = 1;        int sheet2StartCol = 2;        File template = new File("/tmp/template.xlsx");        File output = new File("/tmp/test.xlsx");        // 从template文件读取2个sheet        // sheet1从第0行，第4列开始插入数据        // sheet2从第1行，第2列开始读取        write(template, output, foos, startRow, startCol, true, sheet2StartRow, sheet2StartCol);    }    private static List<FooDTO> getFooList() {        FooDTO foo1 = new FooDTO();        foo1.setBusiName("测试名称1");        foo1.setBusiType("测试类型1");        foo1.setValue1("50.12");        foo1.setValue2("12.86");        return Lists.newArrayList(foo1);    }    public static <T> void write(File template, File output, List<T> data, int startRow, int startCol, boolean copySheet2, int sheet2StartRow, int sheet2StartCol) {        FileInputStream in = null;        FileOutputStream out = null;        try {            in = new FileInputStream(template);            XSSFWorkbook source = new XSSFWorkbook(in);            // 取得sheet1的内容            XSSFSheet sheet1 = source.getSheetAt(0);            // 插入主体数据1            insertData(data, sheet1, startRow, startCol);            if (copySheet2) {                copyFromSheet2(source, sheet1, startCol + data.size() - sheet2StartCol, sheet2StartRow, sheet2StartCol);            }            // 写入到output            out = new FileOutputStream(output);            source.write(out);        } catch (Exception e) {            e.printStackTrace();        } finally {            close(in);            close(out);        }    }    /**     * 关闭资源     *     * @param closeable     */    private static void close(Closeable closeable) {        if(closeable != null) {            try {                closeable.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    /**     * 插入数据（List形式）     *     * @param data     * @param sheet     * @param startRow     * @param startCol     * @param <T>     * @throws Exception     */    private static <T> void insertData(List<T> data, XSSFSheet sheet, int startRow, int startCol) throws Exception {        for (T t : data) {            insertOne(t, sheet, startRow, startCol);            startCol++;        }    }    /**     * 插入数据（单条）     *     * @param data     * @param sheet     * @param startRow     * @param startCol     * @param <T>     * @throws Exception     */    private static <T> void insertOne(T data, XSSFSheet sheet, int startRow, int startCol) throws Exception {        XSSFCell cell;        XSSFRow row;        for (Field field : data.getClass().getDeclaredFields()) {            int index = -1;            String value = "";            if (field.isAnnotationPresent(ExcelProperties.class)) {                ExcelProperties props = field.getAnnotation(ExcelProperties.class);                field.setAccessible(true);                if (field.getGenericType().toString().equals("class java.lang.String")) {                    String name = field.getName();                    name = name.substring(0, 1).toUpperCase() + name.substring(1);                    Method m = data.getClass().getMethod("get" + name);                    String val = (String) m.invoke(data);                    index = props.index();                    value = val;                }            }            if (index != -1) {                row = sheet.getRow(startRow + index);                // 找到最后一个cell                Cell lastCell = row.getCell(row.getLastCellNum() - 1);                // 创立一个cell                cell = row.createCell(startCol);                // 拷贝样式                cell.setCellStyle(lastCell.getCellStyle());                // 设置值                cell.setCellValue(value);            }        }    }    /**     * 把sheet2的内容拷贝到sheet1     *     * @param source     * @param sheet     * @param startCol     * @param sheet2StartRow     * @param sheet2StartCol     */    private static void copyFromSheet2(XSSFWorkbook source, XSSFSheet sheet, int startCol, int sheet2StartRow, int sheet2StartCol) {        // 从sheet2取得剩余部分内容，并插入到sheet1        XSSFCell cell;        XSSFRow row;        // 插入sheet2的内容        XSSFSheet sheet2 = source.getSheetAt(1);        Iterator<Row> iterator = sheet2.iterator();        // 跳过若干行        while (sheet2StartRow > 1) {            iterator.next();            sheet2StartRow--;        }        int count = 0;        while (iterator.hasNext()) {            XSSFRow current = (XSSFRow) iterator.next();            row = sheet.getRow(count);            for (int j = sheet2StartCol; j < current.getLastCellNum(); j++) {                cell = row.createCell(startCol + j);                cell.copyCellFrom(current.getCell(j), new CellCopyPolicy());            }            count++;        }        // 删除sheet2        source.removeSheetAt(1);    }}

maven依赖：

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