В рамках нашего свежего курса «Работа с документами в 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:
Если код показался вам полезным, напишите в комментариях, я сделаю продолжение — работа в Apache POI с цветом (а также с другими видами документов из нашего курса).
UPDATE: добавил в курс «Работа с документами в Java» работу с Jxls
Шаблон для обработки jxlsTemplate.xlsx (команды задаются в комментариях ячеек) позволяет просто выполнит все преобразования, за исключением цвета для еды пользователя. Атрибуты динамических значений можно менять через Area Listener, причем удалось скрестить Excel mark-up c програмным кодом. Приглашаю на курс!
Спасибо за внимание и успехов в кодировании!