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

В рамках нашего свежего курса «Работа с документами в 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 с цветом (а также с другими видами документов из нашего курса).

UPDATE: добавил в курс «Работа с документами в Java» работу с Jxls

Шаблон для обработки jxlsTemplate.xlsx (команды задаются в комментариях ячеек) позволяет просто выполнит все преобразования, за исключением цвета для еды пользователя. Атрибуты динамических значений можно менять через Area Listener, причем удалось скрестить Excel mark-up c програмным кодом. Приглашаю на курс!

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