Reading spreadsheet in java

Read XLSX file in Java [closed]

Closed. This question is seeking recommendations for books, tools, software libraries, and more. It does not meet Stack Overflow guidelines. It is not currently accepting answers.

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

I need to read an Excel 2007 XLSX file in a Java application. Does anyone know of a good API to accomplish this task?

13 Answers 13

Apache POI 3.5 have added support to all the OOXML (docx, xlsx, etc.)

If you’re concerned about size, or «obfuscated» API that Josh mentioned, or performance in general then I would suggest trying out GemBox.Spreadsheet for Java, it’s able to easily read .xls, .xlsx, .ods, .csv with a single API.

AFAIK there are no xlsx-libraries available yet. But there are some for old xls:

One library is jxls which internally uses the already mentioned POI.

Take a look also on this one: code.google.com/p/sjxslx It doesn’t seems to be maintained since Jan 2013 but I loved the code. It’s great for streaming large Excel files — it loads row by row into memory + the shared strings, not the entire spreadsheet.

Might be a little late, but the beta POI now supports xlsx.

 public Workbook getTemplateData(String xlsxFile) < Workbook workbook = new Workbook(); parseSharedStrings(xlsxFile); parseWorkesheet(xlsxFile, workbook); parseComments(xlsxFile, workbook); for (Worksheet worksheet : workbook.sheets) < worksheet.dimension = manager.getDimension(worksheet); >return workbook; > private void parseComments(String tmpFile, Workbook workbook) < try < FileInputStream fin = new FileInputStream(tmpFile); final ZipInputStream zin = new ZipInputStream(fin); InputStream in = getInputStream(zin); while (true) < ZipEntry entry = zin.getNextEntry(); if (entry == null) break; String name = entry.getName(); if (name.endsWith(".xml")) < //$NON-NLS-1$ if (name.contains(COMMENTS)) < parseComments(in, workbook); >> zin.closeEntry(); > in.close(); zin.close(); fin.close(); > catch (FileNotFoundException e) < System.out.println(e); >catch (IOException e) < e.printStackTrace(); >> private void parseComments(InputStream in, Workbook workbook) < try < DefaultHandler handler = getCommentHandler(workbook); SAXParser saxParser = getSAXParser(); saxParser.parse(in, handler); >catch (Exception e) < e.printStackTrace(); >> private DefaultHandler getCommentHandler(Workbook workbook) < final Worksheet ws = workbook.sheets.get(0); return new DefaultHandler() < String lastTag = ""; private Cell ccell; @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException < lastTag = qName; if (lastTag.equals("comment")) < String cellName = attributes.getValue("ref"); int r = manager.getRowIndex(cellName); int c = manager.getColumnIndex(cellName); Row row = ws.rows.get(r); if (row == null) < row = new Row(); row.index = r; ws.rows.put(r, row); >ccell = row.cells.get(c); if (ccell == null) < ccell = new Cell(); ccell.cellName = cellName; row.cells.put(c, ccell); >> > @Override public void characters(char[] ch, int start, int length) throws SAXException < String val = ""; if (ccell != null && lastTag.equals("t")) < for (int i = start; i < start + length; i++) < val += ch[i]; >if (ccell.comment == null) ccell.comment = val; else < ccell.comment += val; >> > >; > private void parseSharedStrings(String tmpFile) < try < FileInputStream fin = new FileInputStream(tmpFile); final ZipInputStream zin = new ZipInputStream(fin); InputStream in = getInputStream(zin); while (true) < ZipEntry entry = zin.getNextEntry(); if (entry == null) break; String name = entry.getName(); if (name.endsWith(".xml")) < //$NON-NLS-1$ if (name.startsWith(SHARED_STRINGS)) < parseStrings(in); >> zin.closeEntry(); > in.close(); zin.close(); fin.close(); > catch (FileNotFoundException e) < System.out.println(e); >catch (IOException e) < e.printStackTrace(); >> public void parseWorkesheet(String tmpFile, Workbook workbook) < try < FileInputStream fin = new FileInputStream(tmpFile); final ZipInputStream zin = new ZipInputStream(fin); InputStream in = getInputStream(zin); while (true) < ZipEntry entry = zin.getNextEntry(); if (entry == null) break; String name = entry.getName(); if (name.endsWith(".xml")) < //$NON-NLS-1$ if (name.contains("worksheets")) < Worksheet worksheet = new Worksheet(); worksheet.name = name; parseWorksheet(in, worksheet); workbook.sheets.add(worksheet); >> zin.closeEntry(); > in.close(); zin.close(); fin.close(); > catch (FileNotFoundException e) < System.out.println(e); >catch (IOException e) < e.printStackTrace(); >> public void parseWorksheet(InputStream in, Worksheet worksheet) throws IOException < // read sheet1 sharedStrings // styles, strings, formulas . try < DefaultHandler handler = getDefaultHandler(worksheet); SAXParser saxParser = getSAXParser(); saxParser.parse(in, handler); >catch (SAXException e) < e.printStackTrace(); >catch (ParserConfigurationException e) < e.printStackTrace(); >> 

public class Workbook < Integer Listsheets = new ArrayList();> 

public class Worksheet < public Integer String name = null; public String dimension = null; public Maprows = new TreeMap(); public Map columns = new TreeMap(); public List spans = new ArrayList();> 

public class Row < public Integer Integer index = null; public Row tmpRow = null; public Style style = null; public Double height = null; public Mapcells = new TreeMap(); public String spans = null; public Integer customHeight = null;> 

Источник

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.

Источник