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

l6v0b_kjirravtwoftee6_wxite.png
Доброе время!

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

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

© Habrahabr.ru