Работа с excel из java

Apache POI – Read and Write Excel File in Java

Learn to read excel, write excel, evaluate formula cells and apply custom formatting to the generated excel files using Apache POI library with examples.

If we are building software for the HR or Finance domain, there is usually a requirement for generating excel reports across management levels. Apart from reports, we can also expect some input data for the applications coming in the form of excel sheets and the application is expected to support this requirement.

Apache POI is a well-trusted library among many other open-source libraries to handle such usecases involving excel files. Please note that, in addition, we can read and write MS Word and MS PowerPoint files also using the Apache POI library.

This Apache POI tutorial will discuss some everyday excel operations in real-life applications.

If we are working on a maven project, we can include the Apache POI dependencies in pom.xml file using this:

 org.apache.poi poi 5.2.2  org.apache.poi poi-ooxml 5.2.2 

2. Important Classes in POI Library

HSSF, XSSF and XSSF classes

• HSSF – is the POI Project’s pure Java implementation of the Excel 97(-2007) file format. e.g., HSSFWorkbook, HSSFSheet.
• XSSF – is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g., XSSFWorkbook, XSSFSheet.
• SXSSF (since 3.8-beta3) – is an API-compatible streaming extension of XSSF to be used when huge spreadsheets have to be produced and heap space is limited. e.g., SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows within a sliding window, while XSSF gives access to all rows in the document.

Row and Cell

Styling Related Classes

FormulaEvaluator

I am taking this example first so we can reuse the excel sheet created by this code in further examples.

Writing excel using POI is very simple and involves the following steps:

1. Create a workbook
2. Create a sheet in workbook
3. Create a row in sheet
4. Add cells to sheet
5. Repeat steps 3 and 4 to write more data

It seems very simple, right? Let’s have a look at the code doing these steps.

Java program to write an excel file using Apache POI library.

package com.howtodoinjava.demo.poi; //import statements public class WriteExcelDemo < public static void main(String[] args) < //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Employee Data"); //This data needs to be written (Object[]) Mapdata = new TreeMap(); data.put("1", new Object[] ); data.put("2", new Object[] ); data.put("3", new Object[] ); data.put("4", new Object[] ); data.put("5", new Object[] ); //Iterate over data and write to sheet Set keyset = data.keySet(); int rownum = 0; for (String key : keyset) < Row row = sheet.createRow(rownum++); Object [] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) < Cell cell = row.createCell(cellnum++); if(obj instanceof String) cell.setCellValue((String)obj); else if(obj instanceof Integer) cell.setCellValue((Integer)obj); >> try < //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx")); workbook.write(out); out.close(); System.out.println("howtodoinjava_demo.xlsx written successfully on disk."); >catch (Exception e) < e.printStackTrace(); >> >

Reading an excel file using POI is also very simple if we divide this into steps.

1. Create workbook instance from an excel sheet
2. Get to the desired sheet
3. Increment row number
4. iterate over all cells in a row
5. repeat steps 3 and 4 until all data is read

Let’s see all the above steps in code. I am writing the code to read the excel file created in the above example. It will read all the column names and the values in it – cell by cell.

Java program to read an excel file using Apache POI library.

package com.howtodoinjava.demo.poi; //import statements public class ReadExcelDemo < public static void main(String[] args) < try < FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one IteratorrowIterator = sheet.iterator(); while (rowIterator.hasNext()) < Row row = rowIterator.next(); //For each row, iterate through all the columns IteratorcellIterator = row.cellIterator(); while (cellIterator.hasNext()) < Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) < case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "t"); break; >> System.out.println(""); > file.close(); > catch (Exception e) < e.printStackTrace(); >> >

ID NAME LASTNAME 1.0 Amit Shukla 2.0 Lokesh Gupta 3.0 John Adwards 4.0 Brian Schultz 

5. Add and Evaluate Formula Cells

When working on complex excel sheets, we encounter many cells with formulas to calculate their values. These are formula cells. Apache POI also has excellent support for adding formula cells and evaluating already present formula cells.

Let’s see one example of how to add formula cells in excel?

The sheet has four cells in a row and the fourth one in the multiplication of all the previous 3 rows. So the formula will be: A2*B2*C2 (in the second row)

Java program to add formula in an excel file using Apache POI library.

public static void main(String[] args) < 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(); >>

Similarly, we want to read a file with formula cells and use the following logic to evaluate formula cells.

Java program to evaluate formula in an excel file using Apache POI library.

public static void readSheetWithFormula() < try < FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one IteratorrowIterator = sheet.iterator(); while (rowIterator.hasNext()) < Row row = rowIterator.next(); //For each row, iterate through all the columns IteratorcellIterator = 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 happen switch (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 again break; >> System.out.println(""); > file.close(); > catch (Exception e) < e.printStackTrace(); >>

Pricipal RoI T Interest (P r t) 14500.0 9.25 3.0 402375.0 

So far we have seen examples of reading/writing and excel files using Apache POI. But, when creating a report in an excel file, it is essential to add formatting on cells that fit into any pre-determined criteria.

This formatting can be a different coloring based on a specific value range, expiry date limit etc.

In the below examples, we are taking a couple of such cell formatting examples for various purposes.

6.1. Cell value in a specific range

This code will color any cell in a range whose value is between a configured range. [e.g., between 50 and 70]

static void basedOnValue(Sheet sheet) < //Creating some random values sheet.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); >

6.2. Highlight Duplicate Values

Highlight all cells which have duplicate values in observed cells.

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("1 (Blue Font)"); >

6.3. Alternate Color Rows in Different Colors

A simple code to color each alternate row in a different color.

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)"); >

6.4. Color amounts that are going to expire in the next 30 days

A handy code for financial projects which keeps track of deadlines.

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 =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"); >

I am ending this apache poi tutorial here to keep the post within a limit.

In this tutorial, we learned to read excel, write excel, set and evaluate formula cells, and format the cells with color codings using the Apache POI library.

Источник

Работа с таблицей Excel из Java

Собственно возникла проблема — обработать данные из таблицы и на их основе получить другую таблицу.

1. Макрос — единственной проблемой является VBA, на изучение которого времени нет совершенно, да и не нравится его синтаксис
2. Приложение на C# тут вроде все хорошо, но к машине на которой будет выполняться данное приложение сразу предъявляется много дополнительных требований:
   • .NET Framework
   • Установленный офис
   • установленная основная сборка взаимодействия (PIA) для приложения Office

3. связка Java и библиотека Apache POI—на этом способе я и хочу остановиться подробнее

1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19)—идет работа над поддержкой формата Office 2007
2. POI 3.2-FINAL Released (2008-10-19) — последний стабильный релиз

Я расскажу о работе с версией 3.2
Основным классом для работы с таблицей Excel является класс HSSFWorkbook пакета org.apache.poi.hssf.usermodel, представляющий книгу Excel.

Для чтения книги из файла можно применить следующий код:

public static HSSFWorkbook readWorkbook(String filename) < try < POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb; >catch (Exception e) < return null; >>

Метод возвращает объект класса HSSFWorkbook если все удачно и null в другом случае.

Для сохранения изменений можно применить следующий метод:

public static void writeWorkbook(HSSFWorkbook wb, String fileName) < try < FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); >catch (Exception e) < //Обработка ошибки >>

Метод записывает книгу wb в файл fileName

• По имени
  HSSFSheet sheet= wb.getSheet(«Лист 3»)
• По номеру (нумерация начинается с 0)
  HSSFSheet sheet= wb.getSheet(0)
• Создание нового листа
  HSSFSheet sheet= wb.createSheet([«имя листа»])

• По индексу (индексация начинается с 0)
  HSSFRow row = sheet.getRow(index)
• Через итератор

Iterator rowIter = sheet.rowIterator(); while (rowIter.hasNext())

• По индексу ячейки (индексация начинается с 0)
  HSSFCell cell = row.getCell(0);
• Через итератор

Iterator cellIter = row.cellIterator(); while (cellIter.hasNext())

• Логическое значение
  boolean b = cell.getBooleanCellValue();
  cell.setCellValue(b);
• Дата
  Date date = cell.getDateCellValue();
  cell.setCellValue(date);
• Числовое значение
  double d = cell.getNumericCellValue();
  cell.setCellValue(d);
• Строковое значение
  String str = cell.getRichStringCellValue().getString();
  cell.setCellValue(new HSSFRichTextString(str));
• Формула
  String formula = cell.getCellFormula();
  cell.setCellFormula(formula);

Этих знаний достаточно чтобы обрабатывать простые таблицы.
Библиотека также предоставляет богатые возможности по форматированию ячеек, по их слиянию, заморозке и т.д.
Подробное описание функций можно найти на их сайте.
Данный способ прежде всего ценен тем, что не требует установки самого офиса и пакета PIA.

Источник

Как работать с Excel файлами в Java?

Часто ли вам приходилось на живую работать с Excel? Думаю, что несколько раз приходилось. Так вот, на днях у меня для собственного проекта появилась необходимость распарсить Excel файл.

Как известно то формат Excel файла *.xsl, но после выхода Microsoft Office 2007 появился новый формат Excel файла *.xlsx, так вот для того, чтобы иметь возможность парсить Excel я использую библиотеку Apache POI все мои пожелания по поводу Excel файла она удовлетворила.

Шаг 1

Для начало создадим Maven проект и добавим следующую зависимость:

Шаг 2

Теперь создадим Excel файл и добавим в него несколько записей.

Файл прикреплен к исходнику.

Шаг 3

Теперь попробуем распарсить файл testfile.xls, для этого напишем парсер.

Создаем класс Parser.java и в нем создадим статический метод parse(String name);

package com.devcolibri.excel; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Iterator; public class Parser < public static String parse(String name) < String result = ""; InputStream in = null; HSSFWorkbook wb = null; try < in = new FileInputStream(name); wb = new HSSFWorkbook(in); >catch (IOException e) < e.printStackTrace(); >Sheet sheet = wb.getSheetAt(0); Iterator it = sheet.iterator(); while (it.hasNext()) < Row row = it.next(); Iteratorcells = row.iterator(); while (cells.hasNext()) < Cell cell = cells.next(); int cellType = cell.getCellType(); switch (cellType) < case Cell.CELL_TYPE_STRING: result += cell.getStringCellValue() + "="; break; case Cell.CELL_TYPE_NUMERIC: result += "[" + cell.getNumericCellValue() + "]"; break; case Cell.CELL_TYPE_FORMULA: result += "[" + cell.getNumericCellValue() + "]"; break; default: result += "|"; break; >> result += "\n"; > return result; > >

Шаг 4

Теперь давайте проверим все это, создаем класс Main.java со следующим содержимым:

package com.devcolibri; import com.devcolibri.excel.Parser; public class Main < public static void main(String. args)< System.out.println(Parser.parse("testfile.xls")); >>

Шаг 5

Запускаем все это дело, и получаем следующий результат:

Александр Барчук=[5000.0] Виктор Пупкин=[10000.0] Дмитрий Федкин=[1500.0] Максим Панков=[300.0] Данил Муев=[8000.0] Анастасия Валяева=[8900.0] Екатерина Максимова=[7000.0] Company=[40700.0]

Источник