Упрощаем работу с Excel с помошью Apache POI: готовый шаблонных код

Доброе время!

В рамках нашего свежего курса «Работа с документами в Java» есть тема: «Работа с Excel с помощью Apache POI». Хочу поделиться здесь основными моментами кода этого занятия и решением проблем, с которыми пришлось столкнуться.

Пошаговое практическое руководство c преобразованием шаблона poiTemplate.xlsx в Excel документ с возможностью программной модификации страниц. Также код может послужить образцом применения паттерна стратегия.

Вывод пользователей в отдельные вкладки (excel sheet)

• Добавляем Apache POI зависимость в pom.xml. Для работы с новым xlsx форматом нужно добавить зависимость poi-ooxml:
  
  

  <!-- Apache POI --> 
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>${apache-poi.version}</version>
  </dependency>
  <!--    For xlsx format -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>${apache-poi.version}</version>
  </dependency>
  

  
  
• Добавляем шаблон  poiTemplate.xlsx, на основе которого будут генерироваться документы, с двумя типами плэйсхолдеров:  ${простая_подстановка_значения} и
  \{требуется_модификация_страницы}. Данными для вывода документа будут массив пользователей User с едой User.meals.
  
• Создаем общий ExcelPoiConverter, который будет на основе templateFile выводить документы в outputFile. Он создается один раз на каждый шаблон и переиспользуется при выводе документа. В конструктор XSSFWorkbook вместо File нужно подавать FileInputStream, иначе при выводе будет ошибка Unexpected end of ZLIB input stream. В передаваемом workbookComposer будет происходить наша обработка.
  
  

  public class ExcelPoiConverter {
      private final File templateFile;
  
      public ExcelPoiConverter(File templateFile) {
          this.templateFile = templateFile;
      }
  
      public void convert(File outputFile, Consumer<Workbook> workbookComposer) throws IOException {
          Files.copy(templateFile.toPath(), outputFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
          // https://stackoverflow.com/a/54695626/548473
          try (Workbook workbook = new XSSFWorkbook(new FileInputStream(outputFile))) {
              workbookComposer.accept(workbook);
              try (OutputStream outputStream = new BufferedOutputStream(Files.newOutputStream(outputFile.toPath()))) {
                  workbook.write(outputStream);
              }
          }
      }
  }
  

• Вывод будет выглядеть следующим образом: каждого пользователя создаем в своей вкладке с именем user.email, копируя первую старницу шаблона. Метода sheet.setName нет, поэтому приходится вести счетчик и использовать workbook.setSheetName
  
  

          List<User> users = ...;
          ExcelPoiConverter converter = new ExcelPoiConverter.of(new File("poiTemplate.xlsx"));
  
          converter.convert(outputFile, workbook -> {
              for (int i = 0; i < users.size(); i++) {
                  User user = users.get(i);
                  workbook.cloneSheet(0);
                  workbook.setSheetName(i + 1, user.getEmail());
              }
              workbook.removeSheetAt(0);
          });
  

Обработка паттерна ${простая_подстановка_значения}

• В конструкторе ExcelPoiConverter анализируем все ячейки poiTemplate.xlsx на совпадения с паттерном PLACEHOLDER_PATTERN и делаем список List<Placeholder> placeholders. Чтобы сам файл шаблона не портился, его также приходиться открывать через FileInputStream
  
  

  public class ExcelPoiConverter {
      private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{(\\w+)}");
      private final File templateFile;
      private final List<Placeholder> placeholders = new ArrayList<>();
  
      public record Placeholder(Matcher matcher, CellAddress cellAddress) {
      }
  
      public ExcelPoiConverter(File templateFile) throws IOException {
          this.templateFile = templateFile;
          try (Workbook workbook = new XSSFWorkbook(new FileInputStream((templateFile)))) {
              // find all placeholders on sheet 0
              workbook.getSheetAt(0).forEach(row ->
                      row.forEach(cell -> {
                          Matcher matcher = PLACEHOLDER_PATTERN.matcher(cell.getStringCellValue());
                          if (matcher.find()) {
                              placeholders.add(new Placeholder(matcher, cell.getAddress()));
                          }
                      }));
          }
      }
  

  
  
• Обрабатываем список плэйсхолдеров в ExcelPoiConverter.resolvePlaceholders, значения для замены передаем в Map<String,Supplier<Object>> supplierMap. Составные шаблоны (например Processing ${name} with email ${email} successfully) обрабатываем последовательно заменяя все вхождения Matcher
  
  

          ....
          converter.resolvePlaceholders(sheet,
               Map.of("name", user::getName,
                          "enabled", () -> user.isEnabled() ? "enabled" : "disabled",
                          "email", user::getEmail,
                          "caloriesPerDay", user::getCaloriesPerDay,
                          "registered", () -> DATE_TIME_FORMATTER.format(user.getRegistered()),
                          "roles", () -> user.getRoles().toString())
               );
  
      public void resolvePlaceholders(Sheet sheet, Map<String, Supplier<Object>> supplierMap) {
          placeholders.forEach(p -> {
              p.matcher.reset();
              //   https://stackoverflow.com/questions/38376584/548473
              StringBuilder sb = new StringBuilder();
              while (p.matcher.find()) {
                  String key = p.matcher.group(1);
                  Object obj = supplierMap.get(key).get();
                  p.matcher.appendReplacement(sb, obj.toString());
              }
              p.matcher.appendTail(sb);
              setCell(sheet, p.cellAddress, sb.toString());
          });
      }
  
      public static Cell getCell(Sheet sheet, CellAddress cellAddress) {
          return sheet.getRow(cellAddress.getRow()).getCell(cellAddress.getColumn());
      }
  
      public static void setCell(Sheet sheet, CellAddress cellAddress, Object value) {
          getCell(sheet, cellAddress).setCellValue(value.toString());
      }
  

  
  

PS: если значений для замены много, есть смысл подключить одну из библиотек Expression Language: Spring Expression Language (SpEL), Java Expression Language (JEXL) или, например, преобразовать объект User в Map через Jackson:

 <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.datatype</groupId>
            <artifactId>jackson-datatype-jsr310</artifactId>
        </dependency>

public class JsonUtil {
      private static final ObjectMapper MAPPER = new ObjectMapper();

      static {
           MAPPER.registerModule(new JavaTimeModule());  // support java.time
           MAPPER.disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS);
      }

      public static <T> Map<String, Object> asMap(T obj) {
          return MAPPER.convertValue(obj, new TypeReference<>() {});
      }
}

Обработка паттерна \{требуется_модификация_страницы}

• В конструкторе ExcelPoiConverter добавляем анализ PROCESSOR_PATTERN
• Добавляем обработчик ExcelPoiConverter.doProcessors, модификация страницы будет реализовываться в Map<String,Consumer<CellAddress>> consumerMap
  
  

      private static final Pattern PROCESSOR_PATTERN = Pattern.compile("\\\\\\{(\\w+)}");
  
      private final List<Processor> processors = new ArrayList<>();
      public record Processor(String key, CellAddress cellAddress) {
      }
  
      public ExcelPoiConverter(File templateFile) throws IOException {
              ...
              matcher = PROCESSOR_PATTERN.matcher(value);
              if (matcher.find()) {
                   processors.add(new Processor(matcher.group(1), cell.getAddress()));
              }
              ...
  
       public void doProcessors(Map<String, Consumer<CellAddress>> consumerMap) {
             processors.forEach(p -> consumerMap.get(p.key).accept(p.cellAddress));
       }
  

  
  
• Добавляем вспомогательные методы удаления и вставки строки на странице и сдвига адреса ячейки:
  
  

      public static void insertRow(Sheet sheet, int rowNum) {
          Row templateRow = sheet.getRow(rowNum);
          if (sheet.getLastRowNum() > rowNum) {
              sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), 1);
          }
          Row newRow = sheet.createRow(rowNum + 1);
          templateRow.cellIterator().forEachRemaining(
                  cell -> newRow.createCell(cell.getColumnIndex()).setCellStyle(cell.getCellStyle())
          );
      }
  
      public static void removeRow(Sheet sheet, int rowNum) {
          int lastRowNum = sheet.getLastRowNum();
          if (rowNum >= 0 && rowNum < lastRowNum) {
              sheet.shiftRows(rowNum + 1, lastRowNum, -1);
          } else if (rowNum == lastRowNum) {
              sheet.removeRow(sheet.getRow(rowNum));
          }
      }
  
      public static CellAddress nextRow(CellAddress ca, int shift) {
          return new CellAddress(ca.getRow() + shift, ca.getColumn());
      }
  
      public static CellAddress nextCell(CellAddress ca, int shift) {
          return new CellAddress(ca.getRow(), ca.getColumn() + shift);
      }
  

  
  
• В реализации обработки таблицы еды выводим данные, начиная с передаваемый в обработчик CellAddress:
  
  

           ...
           converter.doProcessors(Map.of(
                 "meals", ca -> {
                      if (user.getMeals() == null) {
                          removeRow(sheet, ca.getRow());
                      } else {
                          Iterator<Meal> iterator = user.getMeals().iterator();
                          Meal first = iterator.next();
                          writeMeal(first, sheet, ca);
                          while (iterator.hasNext()) {
                               insertRow(sheet, ca.getRow());
                               ca = nextRow(ca, 1);
                               writeMeal(iterator.next(), sheet, ca);
                           }
                      }
           }));
  
      private void writeMeal(Meal meal, Sheet sheet, CellAddress ca) {
          setCell(sheet, ca, DATE_TIME_FORMATTER.format(meal.getDateTime()));
          setCell(sheet, nextCell(ca, 1), meal.getValue());
          setCell(sheet, nextCell(ca, 2), meal.getCalories());
          setCell(sheet, nextCell(ca, 3), meal.getExcess());
      }
  

  
  

Итог: работа с Excel Apache POI мне понравилась больше, чем, например с шаблонами JasperReports (и вы ограничены только API модификации Workbook), хотя к их API есть претензии — иногда приходится повозиться, чтобы найти решение и нужны собственные утильные методы, которые просятся в родное API. Примеры использования Apache POI:

• Working with Microsoft Excel in Java
• Excel Write into Sheet using Java

Если код показался вам полезным, напишите в комментариях, я сделаю продолжение — работа в Apache POI с цветом (а также с другими видами документов из нашего курса).

Спасибо за внимание и успехов в кодировании!