简介

sharp-excel 是一个操作Excel、Word工具的java实现。依赖apache的 POI。目前只开发了Excel的部分，Word部分还没有实现。
pom.xml

<dependency>
    <groupId>com.rick.office</groupId>
    <artifactId>sharp-excel</artifactId>
    <version>1.0-SNAPSHOT</version>
    <scope>compile</scope>
</dependency>

方法测试

Excel读取

@Test
public void testRead() throws Exception {
    File file = new File("/Users/rick/Documents/1.xlsx");

    ExcelReader.readExcelContent(new FileInputStream(file), (index, data, sheetIndex, sheetName) -> {
        System.out.print("index: " + index + ", ");
        for (Object d : data) {
            System.out.print(d + ", ");
        }
        System.out.println();

        return true;
    });
}

控制台输出

index: 0, 姓名, 年龄,
index: 1, rick, 23.0,
index: 2, jim, 88.0,

Excel写入

基本使用：分别构建

分别构建「表头」和「数据」，两者没有关联

@Test
public void testBase() throws IOException {
    File file = new File("/Users/rick/Documents/11.xlsx");
    TableColumn column1 = new TableColumn("姓名");
    TableColumn column2 = new TableColumn("年龄");

    List<TableColumn> columnList = Lists.newArrayList(column1, column2);
    List<Object[]> rows = Lists.newArrayList(new Object[]{"rick", 23}, new Object[] {"jim", 88});
    GeneralExportTable excelTable =  new GeneralExportTable(columnList, rows);

    excelTable.write(new FileOutputStream(file));
}

基本使用：Map构建数据

分别构建「表头」和「数据」，两者通过key关联

@Test
public void testBaseMap() throws IOException {
    File file = new File("/Users/rick/Documents/2.xlsx");

    MapTableColumn column1 = new MapTableColumn("name", "姓名");
    MapTableColumn column2 = new MapTableColumn("age", "年龄");
    List<MapTableColumn> columnList = Lists.newArrayList(column1, column2);

    Map<String, Object> map1 = Maps.newHashMapWithExpectedSize(2);
    map1.put("name", "rick");
    map1.put("age", 12);

    Map<String, Object> map2 = Maps.newHashMapWithExpectedSize(2);
    map2.put("name", "jim");
    map2.put("age", 88);

    List<Map<String, Object>> rows = Lists.newArrayList(map1, map2);
    MapExcelTable excelTable =  new MapExcelTable(columnList, rows);

    excelTable.write(new FileOutputStream(file));
    }

基本使用+设置数据单元格样式

构建表头和数据

@Test
public void testBaseWithStyle() throws IOException {
    File file = new File("/Users/rick/Documents/22.xlsx");

    TableColumn column1 = new TableColumn("姓名");
    TableColumn column2 = new TableColumn("年龄");

    List<TableColumn> columnList = Lists.newArrayList(column1, column2);
    List<Object[]> rows = Lists.newArrayList(new Object[]{"rick", 23}, new Object[] {"jim", 88});

    GeneralExportTable excelTable =  new GeneralExportTable(columnList, rows);

    XSSFCellStyle cellStyle = excelTable.getExcelWriter().getBook().createCellStyle();
    // 填充色
    XSSFColor color = new XSSFColor(Color.GREEN, new DefaultIndexedColorMap());
    cellStyle.setFillForegroundColor(color);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    // 数据部分样式
    excelTable.setRowStyle(cellStyle);

    // 表头部分样式
//         excelTable.setColumnStyle(cellStyle);

    excelTable.write(new FileOutputStream(file));
}

• setColumnStyle 表头部分样式
• setRowStyle 数据部分样式

HTML中table解析导出

解析table标签，然后导出

@Test
public void testHtmlExport() throws IOException {
    HtmlExcelTable excelTable = new HtmlExcelTable();

    excelTable.getExcelWriter().getActiveSheet().setColumnWidth(0, 8000);

    excelTable.write("<table class=\"table table-responsive-sm table-bordered table-sm table-stock\">\n" +
            "                <tbody>\n" +
            "                    <tr>\n" +
            "                        <th colspan=\"4\">国内仓</th>\n" +
            "                        <th rowspan=\"2\">香港仓</th>\n" +
            "                        <th rowspan=\"2\">香港直运仓</th>\n" +
            "                        <th rowspan=\"2\">供应链未完成数量</th>\n" +
            "                    </tr>\n" +
            "                    <tr>\n" +
            "                        <th>深圳成品仓</th>\n" +
            "                        <th>苏州成品仓</th>\n" +
            "                        <th>深圳待处理仓</th>\n" +
            "                        <th>苏州样品仓</th>\n" +
            "                    </tr>\n" +
            "                    <tr>\n" +
            "                        <td>13,558</td>\n" +
            "                        <td>0</td>\n" +
            "                        <td>18</td>\n" +
            "                        <td>0</td>\n" +
            "                        <td rowspan=\"3\">0</td>\n" +
            "                        <td rowspan=\"3\">0</td>\n" +
            "                        <td rowspan=\"3\">\n" +
            "                            <a href=\"javascript:;\" id=\"otherDetailBtn\">1,000,500</a>\n" +
            "                            \n" +
            "                        </td>\n" +
            "                    </tr>\n" +
            "                    <tr>\n" +
            "                        <th colspan=\"4\">国内仓锁货数量</th>\n" +
            "                    </tr>\n" +
            "                    <tr>\n" +
            "                        <td colspan=\"4\">\n" +
            "                            <a href=\"javascript:;\" id=\"reserveDetailBtn\">12,000</a>\n" +
            "                            \n" +
            "                        </td>\n" +
            "                    </tr>\n" +
            "                </tbody>\n" +
            "            </table>", new FileOutputStream(new File("/Users/rick/Documents/a.xlsx")));
}

集成GridService导出

在Spring上下文中，通过 ExportUtils，集成 GridService 获取数据源，导出Excel。
提供了方便的导出操作。只需要根据查询SQL，直接导出excel文件。

pom.xml 添加依赖

<dependency>
    <groupId>com.rick.db</groupId>
    <artifactId>sharp-excel</artifactId>
    <version>1.0-SNAPSHOT</version>
</dependency>

public static final void export(String sql, Map<String, ?> params, OutputStream outputStream, List<MapTableColumn> columnList)

测试代码

/**
 * 查询sql导出
 */
@Test
public void testSqlExport() throws IOException {
    Map params = new HashMap<>();
    // 业务参数
    params.put("title", "version");

    ExportUtils.export("SELECT id, title FROM t_demo WHERE title like :title", params,
            new FileOutputStream("/Users/rick/Downloads/export/t_demo.xlsx"),
            Lists.newArrayList(
                    new MapTableColumn("title", "标题") // 导出文件中第一列为标题，且仅导出标题，忽略id
//                        new MapTableColumn("id", "ID")
            ));
}