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

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

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

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

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

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

    org.apache.poi
    poi
    ${apache-poi.version}



    org.apache.poi
    poi-ooxml
    ${apache-poi.version}
```
Добавляем шаблон 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 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 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 placeholders. Чтобы сам файл шаблона не портился, его также приходиться открывать через FileInputStream

public class ExcelPoiConverter {
    private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{(\\w+)}");
    private final File templateFile;
    private final List 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> 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> 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:

 
            com.fasterxml.jackson.core
            jackson-databind
        
        
            com.fasterxml.jackson.datatype
            jackson-datatype-jsr310

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  Map asMap(T obj) {
          return MAPPER.convertValue(obj, new TypeReference<>() {});
      }
}

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

В конструкторе ExcelPoiConverter добавляем анализ PROCESSOR_PATTERN

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

    private static final Pattern PROCESSOR_PATTERN = Pattern.compile("\\\\\\{(\\w+)}");

    private final List 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> 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 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:

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

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