java操作excel，docx，实现一键模板生成文档

概述

报销、申请格式统一的。可以制作模板，根据不同的信息自动生成。

demo设计

休假申请模板,大括号代表的是变量：

休假申请统计表

工程搭建

对于maven工程来说，只需要一个pom.xml就实现了工程的搭建，如下。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.0</version>
    <relativePath /> <!-- lookup parent from repository -->
  </parent>
  <groupId>org.yunzhong</groupId>
  <artifactId>commontool</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>commontool</name>
  <description>Demo project for Spring Boot</description>
  <properties>
    <java.version>11</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger2</artifactId>
      <version>2.9.2</version>
    </dependency>
    <dependency>
      <groupId>com.github.xiaoymin</groupId>
      <artifactId>swagger-bootstrap-ui</artifactId>
      <version>1.9.6</version>
    </dependency>
    <dependency>
      <groupId>com.deepoove</groupId>
      <artifactId>poi-tl</artifactId>
      <version>1.10.0</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>
    </plugins>
  </build>

</project>

基本工程可以通过https://start.spring.io/生成，其中包括了spring boot的基本支持。

• springfox-swagger2 ，swagger-bootstrap-ui
  swagger可以生成接口文档，接口调试等功能。swagger-bootstrap-ui则是在swagger的基础上进行了封装，提供了更加已用的界面。
• lombok
  降低无用代码的神器。虽然现在各种声音都有，对于练习工程来说必不可少。
• poi-tl ，poi
  在apache poi的基础上做了封装，实现了模板变量替换的功能。poi-tl已经依赖了poi，pom中不需要再显式引用。

swagger配置：

@Configuration
@EnableSwagger2
public class SwaggerConfiguration {

    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo()).select()
                .apis(RequestHandlerSelectors.basePackage("org.yunzhong.commontool")).paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder().title("tool").description("swagger-bootstrap-ui")
                .contact(new Contact("yunzhong", "yunzhong.com", "wangyunzhong@mail.com.cn")).version("1.0").build();
    }

}

excel 解析以及模板填充

    public String generate(InputStream excelInputStream, InputStream docInputStream, String date) throws IOException {
        Path templateFilePath = Paths.get("template.docx");
        if (Files.exists(templateFilePath)) {
            Files.delete(templateFilePath);
        }
        Files.copy(docInputStream, templateFilePath);

        Path logCsvpath = Paths.get("log" + System.currentTimeMillis() + ".csv");
        Files.createFile(logCsvpath);

        try (XSSFWorkbook workbook = new XSSFWorkbook(excelInputStream)) {
            XSSFSheet dateSheet = workbook.getSheet(date);
            int lastRowNum = dateSheet.getLastRowNum();
            XSSFRow dateRow = dateSheet.getRow(0);
            XSSFRow titleRow = dateSheet.getRow(1);
            short lastCellNum = titleRow.getLastCellNum();
            int rowIndex = 2;
            for (; rowIndex <= lastRowNum; rowIndex++) {
                XSSFRow dataRow = dateSheet.getRow(rowIndex);
                String numValue = dataRow.getCell(0).getRawValue();
                String nameValue = dataRow.getCell(1).getStringCellValue();
                if (StringUtils.isEmpty(numValue) || StringUtils.isEmpty(nameValue)) {
                    break;
                }
                int cellIndex = 2;
                for (; cellIndex < lastCellNum; cellIndex++) {
                    String dateValue = parseDateCell(dateRow.getCell(cellIndex));
                    String amValue = parseCell(dataRow.getCell(cellIndex));
                    String pmValue = parseCell(dataRow.getCell(++cellIndex));
                    if (StringUtils.isEmpty(dateValue)) {
                        break;
                    }
                    if (StringUtils.isEmpty(amValue) && StringUtils.isEmpty(pmValue)) {
                        continue;
                    }
                    System.out
                            .println("name:" + nameValue + " date:" + dateValue + " am:" + amValue + " pm:" + pmValue);
                    Double holidayCount = Double.valueOf(StringUtils.isEmpty(amValue) ? "0" : amValue)
                            + Double.valueOf(StringUtils.isEmpty(pmValue) ? "0" : pmValue);
                    Map<String, Object> paramMap = new HashMap<>();
                    String lastDate = getLastDate(dateValue);
                    paramMap.put("name", nameValue);
                    paramMap.put("dept", "开发部");
                    paramMap.put("apply_date", lastDate);
                    String holidayDetail = generateDetal(amValue, pmValue);
                    paramMap.put("holiday_detail", dateValue + holidayDetail);
                    paramMap.put("holiday_count", holidayCount);
                    paramMap.put("reason", "私事");
                    paramMap.put("instead", "组长");
                    paramMap.put("manager", "组长");
                    try (XWPFTemplate xwpfTemplate = XWPFTemplate.compile(templateFilePath.toFile()).render(paramMap)) {
                        String fileName = nameValue + dateValue + "休假申请.docx";
                        Path filePath = Paths.get(fileName);
                        Files.deleteIfExists(filePath);

                        xwpfTemplate.writeToFile("./" + fileName);
                        Files.writeString(logCsvpath,
                                nameValue + "," + dateValue + "," + holidayDetail + ", success\r\n",
                                StandardOpenOption.APPEND);
                    } catch (Exception e) {
                        Files.writeString(logCsvpath, nameValue + "," + dateValue + "," + holidayDetail + ", error, "
                                + e.getLocalizedMessage(), StandardOpenOption.APPEND);
                    }
                }

            }
        }
        return null;
    }

main参数解析

下面的代码摘抄自flink：

public abstract class ParameterTool {

    protected static final String NO_VALUE_KEY = "__NO_VALUE_KEY";

    private ParameterTool() {

    }

    public static Map<String, String> fromArgs(String[] args) {
        final Map<String, String> map = new HashMap<>(args.length / 2);

        int i = 0;
        while (i < args.length) {
            final String key = getKeyFromArgs(args, i);

            if (key.isEmpty()) {
                throw new IllegalArgumentException(
                        "The input " + Arrays.toString(args) + " contains an empty argument");
            }

            i += 1; // try to find the value

            if (i >= args.length) {
                map.put(key, NO_VALUE_KEY);
            } else if (NumberUtils.isCreatable(args[i])) {
                map.put(key, args[i]);
                i += 1;
            } else if (args[i].startsWith("--") || args[i].startsWith("-")) {
                map.put(key, NO_VALUE_KEY);
            } else {
                map.put(key, args[i]);
                i += 1;
            }
        }

        return map;
    }

    public static String getKeyFromArgs(String[] args, int index) {
        String key;
        if (args[index].startsWith("--")) {
            key = args[index].substring(2);
        } else if (args[index].startsWith("-")) {
            key = args[index].substring(1);
        } else {
            throw new IllegalArgumentException(
                    String.format("Error parsing arguments '%s' on '%s'. Please prefix keys with -- or -.",
                            Arrays.toString(args), args[index]));
        }

        if (key.isEmpty()) {
            throw new IllegalArgumentException("The input " + Arrays.toString(args) + " contains an empty argument");
        }

        return key;
    }
}

cmd 启动脚本

java -jar commontool-0.0.1-SNAPSHOT.jar --excel 2021.xlsx --doc 2021.docx --date 202108
parse