Apache POI: чтение и запись файлов Excel

От /

Научитесь читать Excel, писать Excel, оценивать ячейки формул и применять пользовательское форматирование к созданным файлам Excel с помощью библиотеки Apache POI с примерами.

Если мы создаем программное обеспечение для сферы HR или Finance, обычно есть требование для создания отчетов Excel на всех уровнях управления. Помимо отчетов, мы также можем ожидать, что некоторые входные данные для приложений будут поступать в виде таблиц Excel, и приложение должно поддерживать это требование.

Apache POI — это хорошо зарекомендовавшая себя библиотека среди многих других библиотек с открытым исходным кодом для обработки таких случаев использования, связанных с файлами Excel. Обратите внимание, что, кроме того, мы можем читать и записывать файлы MS Word и MS PowerPoint, также используя библиотеку Apache POI.

В этом руководстве по Apache POI будут рассмотрены некоторые повседневные операции Excel в реальных приложениях.

1. Знаток

Если мы работаем над проектом maven, мы можем включить зависимости Apache POI в файл pom.xml, используя следующее:

 <зависимость><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><версия>5.2.2</версия></зависимость><зависимость><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><версия>5.2.2</версия></зависимость>

2. Основные классы в библиотеке POI

  • Классы HSSF, XSSF и XSSF

    Основные классы Apache POI обычно начинаются с HSSF, XSSF или SXSSF.

    • HSSF – это реализация формата файла Excel 97(-2007) на чистом Java от проекта POI. Например, HSSFWorkbook, HSSFSheet.
    • XSSF — это реализация формата файла Excel 2007 OOXML(.xlsx) на чистом Java от проекта POI. Например, XSSFWorkbook, XSSFSheet.
    • SXSSF(начиная с версии 3.8-beta3) — это совместимое с API потоковое расширение XSSF, которое используется, когда необходимо создавать огромные электронные таблицы, а пространство кучи ограничено. Например, SXSSFWorkbook, SXSSFSheet. SXSSF достигает низкого потребления памяти за счет ограничения доступа к строкам в скользящем окне, в то время как XSSF предоставляет доступ ко всем строкам в документе.

  • Строка и ячейка

    Помимо вышеперечисленных классов, Row и Cell взаимодействуют с конкретной строкой и конкретной ячейкой в таблице Excel.

  • Классы, связанные со стилизацией

    Широкий спектр классов, таких как CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting и т. д., используется, когда вам нужно добавить форматирование к листу, в первую очередь на основе некоторых правил.

  • FormulaEvaluator

    Еще один полезный класс FormulaEvaluator используется для оценки ячеек формул в таблице Excel.

3. Запись в файл Excel

Я беру этот пример первым, чтобы мы могли повторно использовать таблицу Excel, созданную этим кодом, в дальнейших примерах.

Написание Excel с использованием POI очень просто и включает в себя следующие шаги:

  1. Создать рабочую книгу
  2. Создайте лист в рабочей книге
  3. Создайте строку на листе
  4. Добавить ячейки на лист
  5. Повторите шаги 3 и 4, чтобы записать больше данных.

Кажется очень просто, не правда ли? Давайте посмотрим на код, выполняющий эти шаги.

Программа Java для записи файла Excel с использованием библиотеки Apache POI.

 //Пустая рабочая тетрадьрабочая книга XSSFWorkbook = new XSSFWorkbook();//Создать чистый листXSSFSheet sheet = workbook.createSheet("Данные о сотрудниках");//Подготовка данных для записи в виде Object[]Карта данные = new TreeMap();data.put("1", new Object[] {"ID", "ИМЯ", "ФАМИЛИЯ"});data.put("2", new Object[] {1, "Амит", "Шукла"});данные.put("3", новый Объект[] {2, "Локеш", "Гупта"});data.put("4", new Object[] {3, "Джон", "Эдвардс"});data.put("5", new Object[] {4, "Брайан", "Шульц"});//Итерация по данным и запись на листУстановить keyset = data.keySet();int rownum = 0;для(строковый ключ : набор ключей) {Строка строка = лист.createRow(rownum++);Объект [] objArr = data.get(key);int cellnum = 0;для(Объект obj : objArr){Ячейка ячейки = row.createCell(cellnum++);если(объект экземпляр строки)ячейка.setCellValue((Строка)obj);иначе если(объект экземпляр целого числа)ячейка.setCellValue((Целое число)obj);}}//Записать книгу в файловую системупытаться {FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));рабочая книга.запись(выход);из.закрыть();System.out.println("howtodoinjava_demo.xlsx успешно записан на диск.");}поймать(Исключение e) {e.printStackTrace();}
poi-demo-write-file

См. также: Добавление строк в Excel

4. Чтение файла Excel

Чтение файла Excel с помощью POI также очень просто, если разделить этот процесс на шаги.

  1. Создать экземпляр рабочей книги из листа Excel
  2. Перейти на нужный лист
  3. Увеличить номер строки
  4. Перебрать все ячейки в строке
  5. Повторяйте шаги 3 и 4, пока все данные не будут считаны.

Давайте рассмотрим все вышеперечисленные шаги в коде. Я пишу код для чтения файла Excel, созданного в примере выше. Он прочитает все имена столбцов и значения в нем – ячейка за ячейкой.

Программа Java для чтения файла Excel с использованием библиотеки Apache POI.

 FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));//Создать экземпляр Workbook, содержащий ссылку на файл .xlsxрабочая книга XSSFWorkbook = new XSSFWorkbook(файл);//Получить первый/нужный лист из рабочей книгиXSSFSheet лист = workbook.getSheetAt(0);//Проходим по каждой строке одну за другойИтератор rowIterator = sheet.iterator();пока(rowIterator.hasNext()) {Строка row = rowIterator.next();//Для каждой строки перебираем все столбцыИтератор cellIterator = row.cellIterator();пока(cellIterator.hasNext()) {Ячейка cell = cellIterator.next();//Проверьте тип ячейки и формат соответственнопереключатель(cell.getCellType()) {случай Ячейка.CELL_TYPE_NUMERIC:System.out.print(cell.getNumericCellValue() + "t");перерыв;случай Ячейка.CELL_TYPE_STRING:System.out.print(cell.getStringCellValue() + "t");перерыв;}}System.out.println("");}файл.закрыть();

Вывод программы:

 ИДЕНТИФИКАЦИОННОЕ ИМЯ ФАМИЛИЯ1.0 Амит Шукла2.0 Локеш Гупта3.0 Джон Эдвардс4.0 Брайан Шульц

См. также: Apache POI – чтение файла Excel с помощью SAX Parser

5. Добавьте и оцените ячейки формулы

При работе со сложными таблицами Excel мы сталкиваемся со множеством ячеек с формулами для расчета их значений. Это ячейки с формулами. Apache POI также имеет отличную поддержку для добавления ячеек с формулами и оценки уже существующих ячеек с формулами.

Давайте рассмотрим один пример того, как добавить ячейки с формулами в Excel?

На листе четыре ячейки в ряду и четвертая в умножении всех предыдущих 3 строк. Таким образом, формула будет: A2*B2*C2(во втором ряду)

Программа Java для добавления формул в файл Excel с использованием библиотеки Apache POI. 

XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");Row header = sheet.createRow(0);header.createCell(0).setCellValue("Pricipal");header.createCell(1).setCellValue("RoI");header.createCell(2).setCellValue("T");header.createCell(3).setCellValue("Interest(P r t)");Row dataRow = sheet.createRow(1);dataRow.createCell(0).setCellValue(14500d);dataRow.createCell(1).setCellValue(9.25);dataRow.createCell(2).setCellValue(3d);dataRow.createCell(3).setCellFormula("A2*B2*C2");try {FileOutputStream out = new FileOutputStream(new File("formulaDemo.xlsx"));workbook.write(out);out.close();System.out.println("Excel with foumula cells written successfully");} catch(FileNotFoundException e) {e.printStackTrace();} catch(IOException e) {e.printStackTrace();}

Аналогично мы хотим прочитать файл с ячейками формул и использовать следующую логику для оценки ячеек формул.

Программа Java для оценки формул в файле Excel с использованием библиотеки Apache POI. 

FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx"));//Create Workbook instance holding reference to .xlsx fileXSSFWorkbook workbook = new XSSFWorkbook(file);FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();//Get first/desired sheet from the workbookXSSFSheet sheet = workbook.getSheetAt(0);//Iterate through each rows one by oneIterator<Row> rowIterator = sheet.iterator();while(rowIterator.hasNext()) {Row row = rowIterator.next();//For each row, iterate through all the columnsIterator<Cell> cellIterator = row.cellIterator();while(cellIterator.hasNext()) {Cell cell = cellIterator.next();//Check the cell type after eveluating formulae//If it is formula cell, it will be evaluated otherwise no change will happenswitch(evaluator.evaluateInCell(cell).getCellType()) {case Cell.CELL_TYPE_NUMERIC:System.out.print(cell.getNumericCellValue() + "tt");break;case Cell.CELL_TYPE_STRING:System.out.print(cell.getStringCellValue() + "tt");break;case Cell.CELL_TYPE_FORMULA://Not againbreak;}}System.out.println("");}file.close();

Вывод программы:

 Основной процент по рентабельности инвестиций(P rt)14500,0 9,25 3,0 402375,0
poi-demo-write-formula

6. Форматирование ячеек

До сих пор мы видели примеры чтения/записи и файлов Excel с использованием Apache POI. Но при создании отчета в файле Excel важно добавить форматирование ячеек, которое соответствует любым заранее определенным критериям.

Это форматирование может иметь различную окраску в зависимости от определенного диапазона значений, срока действия и т. д.

В приведенных ниже примерах мы рассмотрим несколько примеров форматирования ячеек для различных целей.

6.1 Значение ячейки в определенном диапазоне

Этот код окрасит любую ячейку в диапазоне, значение которого находится в заданном диапазоне. [например, от 50 до 70] 

static void basedOnValue(Sheet sheet){//Creating some random valuessheet.createRow(0).createCell(0).setCellValue(84);sheet.createRow(1).createCell(0).setCellValue(74);sheet.createRow(2).createCell(0).setCellValue(50);sheet.createRow(3).createCell(0).setCellValue(51);sheet.createRow(4).createCell(0).setCellValue(49);sheet.createRow(5).createCell(0).setCellValue(41);SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();//Condition 1: Cell Value Is greater than 70 (Blue Fill)ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");PatternFormatting fill1 = rule1.createPatternFormatting();fill1.setFillBackgroundColor(IndexedColors.BLUE.index);fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);//Condition 2: Cell Value Is less than 50 (Green Fill)ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");PatternFormatting fill2 = rule2.createPatternFormatting();fill2.setFillBackgroundColor(IndexedColors.GREEN.index);fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A6")};sheetCF.addConditionalFormatting(regions, rule1, rule2);}
poi-demo-форматирование-1

6.2 Выделение повторяющихся значений

Выделите все ячейки, которые имеют повторяющиеся значения в наблюдаемых ячейках. 

static void formatDuplicates(Sheet sheet) {sheet.createRow(0).createCell(0).setCellValue("Code");sheet.createRow(1).createCell(0).setCellValue(4);sheet.createRow(2).createCell(0).setCellValue(3);sheet.createRow(3).createCell(0).setCellValue(6);sheet.createRow(4).createCell(0).setCellValue(3);sheet.createRow(5).createCell(0).setCellValue(5);sheet.createRow(6).createCell(0).setCellValue(8);sheet.createRow(7).createCell(0).setCellValue(0);sheet.createRow(8).createCell(0).setCellValue(2);sheet.createRow(9).createCell(0).setCellValue(8);sheet.createRow(10).createCell(0).setCellValue(6);SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();// Condition 1: Formula Is =A2=A1 (White Font)ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");FontFormatting font = rule1.createFontFormatting();font.setFontStyle(false, true);font.setFontColorIndex(IndexedColors.BLUE.index);CellRangeAddress[] regions = {CellRangeAddress.valueOf("A2:A11")};sheetCF.addConditionalFormatting(regions, rule1);sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " +"Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)");}
poi-demo-форматирование-2

6.3. Чередуйте ряды разных цветов

Простой код для окрашивания каждой чередующейся строки в другой цвет. 

static void shadeAlt(Sheet sheet) {SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();// Condition 1: Formula Is =A2=A1 (White Font)ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");PatternFormatting fill1 = rule1.createPatternFormatting();fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:Z100")};sheetCF.addConditionalFormatting(regions, rule1);sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)");}
poi-demo-форматирование-3

6.4. Цветовые суммы, срок действия которых истекает в течение следующих 30 дней

Удобный код для финансовых проектов, который отслеживает сроки. 

static void expiryInNext30Days(Sheet sheet){CellStyle style = sheet.getWorkbook().createCellStyle();style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));sheet.createRow(0).createCell(0).setCellValue("Date");sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");sheet.createRow(2).createCell(0).setCellFormula("A2+1");sheet.createRow(3).createCell(0).setCellFormula("A3+1");for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style);SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();// Condition 1: Formula Is =A2=A1 (White Font)ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");FontFormatting font = rule1.createFontFormatting();font.setFontStyle(false, true);font.setFontColorIndex(IndexedColors.BLUE.index);CellRangeAddress[] regions = {CellRangeAddress.valueOf("A2:A4")};sheetCF.addConditionalFormatting(regions, rule1);sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");}
poi-demo-форматирование-4

Я заканчиваю это руководство по Apache Poi, чтобы не выходить за рамки поста.

7. Заключение

В этом уроке мы научились читать Excel, писать в Excel, задавать и оценивать ячейки формул, а также форматировать ячейки с помощью цветовой кодировки с помощью библиотеки Apache POI.

Исходный код на Github

Похожие посты

Прокрутить вверх