Java opening in office

Read and Write Excel file in Java using Apache POI

Follow us on our fanpages to receive notifications every time there are new articles. Facebook Twitter

1- What is Apache POI?

Apache POI is a Java open source library provided by Apache, it is the powerful library to support you in working with Microsoft documents such as Word, Excel, Power point, Visio.

POI stands for «Poor Obfuscation Implementation». File formats of Microsoft are closed. Apache’s engineers have to try to study it and they see that Microsoft has created complex formats unnecessarily. And the library name is derived from humour.

In the post, I will show you the way to use Apache POI when you work with Excel.

2- Apache POI Overview

Apache POI supports you in working with the formats of Microsoft, its classes are often prefixed with HSSF, XSSF, HPSF. Looking at the class prefix, you can know which format it supports.

Prefix	Description
1	HSSF (Horrible SpreadSheet Format)	reads and writes Microsoft Excel (XLS) format files.
2	XSSF (XML SpreadSheet Format)	reads and writes Office Open XML (XLSX) format files.
3	HPSF (Horrible Property Set Format)	reads “Document Summary” information from Microsoft Office files.
4	HWPF (Horrible Word Processor Format)	aims to read and write Microsoft Word 97 (DOC) format files.
5	HSLF (Horrible Slide Layout Format)	a pure Java implementation for Microsoft PowerPoint files.
6	HDGF (Horrible DiaGram Format)	an initial pure Java implementation for Microsoft Visio binary files.
7	HPBF (Horrible PuBlisher Format)	a pure Java implementation for Microsoft Publisher files.
8	HSMF (Horrible Stupid Mail Format)	a pure Java implementation for Microsoft Outlook MSG files
9	DDF (Dreadful Drawing Format)	a package for decoding the Microsoft Office Drawing format.

3- Apache POI Excel Overview

Apache POI provides you some interfaces: Workbook, Sheet, Row, Cell,... and inplementation classes are HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,. respectively.

4- Apache POI Library

If your project use Maven, you only need to declare the library in pom.xml in the simple way.

   org.apache.poi poi 3.17  org.apache.poi poi-ooxml 3.17  

  4.0.0 org.o7planning ApachePOIExcel 0.0.1-SNAPSHOT   org.apache.poi poi 3.17  org.apache.poi poi-ooxml 3.17    

5- Create and write Excel file

In the earlier versions of Microsoft Office (97-2003), the excel files have XLS format and in the current one, XSLX format is often used. In order to work with XSLX file, you need to use classes with the prefix XSSF.

Below is a simple example using POI to create a excel file. You can combine with the use of Style on Cells to create a more beautiful Excel document. POI Style is explicitly mentioned at the end of the post.

 package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.o7planning.apachepoiexcel.model.Employee; import org.o7planning.apachepoiexcel.model.EmployeeDAO; public class CreateExcelDemo < private static HSSFCellStyle createStyleForTitle(HSSFWorkbook workbook) < HSSFFont font = workbook.createFont(); font.setBold(true); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); return style; >public static void main(String[] args) throws IOException < HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Employees sheet"); Listlist = EmployeeDAO.listEmployees(); int rownum = 0; Cell cell; Row row; // HSSFCellStyle style = createStyleForTitle(workbook); row = sheet.createRow(rownum); // EmpNo cell = row.createCell(0, CellType.STRING); cell.setCellValue("EmpNo"); cell.setCellStyle(style); // EmpName cell = row.createCell(1, CellType.STRING); cell.setCellValue("EmpNo"); cell.setCellStyle(style); // Salary cell = row.createCell(2, CellType.STRING); cell.setCellValue("Salary"); cell.setCellStyle(style); // Grade cell = row.createCell(3, CellType.STRING); cell.setCellValue("Grade"); cell.setCellStyle(style); // Bonus cell = row.createCell(4, CellType.STRING); cell.setCellValue("Bonus"); cell.setCellStyle(style); // Data for (Employee emp : list) < rownum++; row = sheet.createRow(rownum); // EmpNo (A) cell = row.createCell(0, CellType.STRING); cell.setCellValue(emp.getEmpNo()); // EmpName (B) cell = row.createCell(1, CellType.STRING); cell.setCellValue(emp.getEmpName()); // Salary (C) cell = row.createCell(2, CellType.NUMERIC); cell.setCellValue(emp.getSalary()); // Grade (D) cell = row.createCell(3, CellType.NUMERIC); cell.setCellValue(emp.getGrade()); // Bonus (E) String formula = "0.1*C" + (rownum + 1) + "*D" + (rownum + 1); cell = row.createCell(4, CellType.FORMULA); cell.setCellFormula(formula); >File file = new File("C:/demo/employee.xls"); file.getParentFile().mkdirs(); FileOutputStream outFile = new FileOutputStream(file); workbook.write(outFile); System.out.println("Created file: " + file.getAbsolutePath()); > > 

 package org.o7planning.apachepoiexcel.model; public class Employee < private String empNo; private String empName; private Double salary; private int grade; private Double bonus; public Employee(String empNo, String empName,// Double salary, int grade, Double bonus) < this.empNo = empNo; this.empName = empName; this.salary = salary; this.grade = grade; this.bonus = bonus; >public String getEmpNo() < return empNo; >public void setEmpNo(String empNo) < this.empNo = empNo; >public String getEmpName() < return empName; >public void setEmpName(String empName) < this.empName = empName; >public Double getSalary() < return salary; >public void setSalary(Double salary) < this.salary = salary; >public int getGrade() < return grade; >public void setGrade(int grade) < this.grade = grade; >public Double getBonus() < return bonus; >public void setBonus(Double bonus) < this.bonus = bonus; >> 

 package org.o7planning.apachepoiexcel.model; import java.util.ArrayList; import java.util.List; public class EmployeeDAO < public static ListlistEmployees() < Listlist = new ArrayList(); Employee e1 = new Employee("E01", "Tom", 200.0, 1, null); Employee e2 = new Employee("E02", "Jerry", 100.2, 2, null); Employee e3 = new Employee("E03", "Donald", 150.0, 2, null); list.add(e1); list.add(e2); list.add(e3); return list; > > 

6- Read XSL and XSLX file

The example below reads a simple excel file and print information to Console screen. The excel file that is used to read is created in the example above.

Note: In the post, I use Apache POI 3.15, API has a lot of changes compared to the the older version. There are a lot of methods that will be removed in the future version (Apache POI 4.x). POI is moving forward to the usage of Enum to replace constants in its API.

 package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; public class ReadExcelDemo < public static void main(String[] args) throws IOException < // Read XSL file FileInputStream inputStream = new FileInputStream(new File("C:/demo/employee.xls")); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet IteratorrowIterator = sheet.iterator(); while (rowIterator.hasNext()) < Row row = rowIterator.next(); // Get iterator to all cells of current row IteratorcellIterator = row.cellIterator(); while (cellIterator.hasNext()) < Cell cell = cellIterator.next(); // Change to getCellType() if using POI 4.x CellType cellType = cell.getCellTypeEnum(); switch (cellType) < case _NONE: System.out.print(""); System.out.print("\t"); break; case BOOLEAN: System.out.print(cell.getBooleanCellValue()); System.out.print("\t"); break; case BLANK: System.out.print(""); System.out.print("\t"); break; case FORMULA: // Formula System.out.print(cell.getCellFormula()); System.out.print("\t"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); // Print out value evaluated by formula System.out.print(evaluator.evaluate(cell).getNumberValue()); break; case NUMERIC: System.out.print(cell.getNumericCellValue()); System.out.print("\t"); break; case STRING: System.out.print(cell.getStringCellValue()); System.out.print("\t"); break; case ERROR: System.out.print("!"); System.out.print("\t"); break; >> System.out.println(""); > > > 

7- Update existing excel file

 package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class UpdateExcelDemo < public static void main(String[] args) throws IOException < File file = new File("C:/demo/employee.xls"); // Read XSL file FileInputStream inputStream = new FileInputStream(file); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); HSSFCell cell = sheet.getRow(1).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); cell = sheet.getRow(2).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); cell = sheet.getRow(3).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); inputStream.close(); // Write File FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); >> 

8- Formulas and evaluation

If you have the knowledge of Excel, you will be easy to form a formula. For Apache POI, you can create a Cell with CellType.FORMULA, its value is calculated on a formula.

For example: Calculating the total of cells on the same column «C» from the second line to the 4th line:

 // Create Cell type of FORMULA cell = row.createCell(rowIndex, CellType.FORMULA); // Set formula cell.setCellFormula("SUM(C2:C4)"); 

 cell = row.createCell(rowIndex, CellType.FORMULA); cell.setCellFormula("0.1*C2*D3"); 

For a cell with FORMULA type, you can print out its formula and use FormulaEvaluator to calculate the cell value given by the formula.

 // Formula String formula = cell.getCellFormula(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); // CellValue CellValue cellValue = evaluator.evaluate(cell); double value = cellValue.getNumberValue(); String value = cellValue.getStringValue(); boolean value = cellValue.getBooleanValue(); // . 

9- Style

 package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.IndexedColors; public class StyleDemo < private static HSSFCellStyle getSampleStyle(HSSFWorkbook workbook) < // Font HSSFFont font = workbook.createFont(); font.setBold(true); font.setItalic(true); // Font Height font.setFontHeightInPoints((short) 18); // Font Color font.setColor(IndexedColors.RED.index); // Style HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); return style; >public static void main(String[] args) throws IOException < HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Style Demo"); HSSFRow row = sheet.createRow(0); // HSSFCell cell = row.createCell(0); cell.setCellValue("String with Style"); HSSFCellStyle style = getSampleStyle(workbook); cell.setCellStyle(style); File file = new File("C:/demo/style.xls"); file.getParentFile().mkdirs(); FileOutputStream outFile = new FileOutputStream(file); workbook.write(outFile); System.out.println("Created file: " + file.getAbsolutePath()); >> 

View more Tutorials:

These are online courses outside the o7planning website that we introduced, which may include free or discounted courses.

• Python 3 For Beginner — Object-Oriented Programming
• JavaScript For Beginners — Learn JavaScript From Scratch
• Struts 2 Framework for Beginners
• C, C++, Java; A Programming MegaPrimer for Beginners
• SwiftUI Quick Start Guide with iOS 13 and Xcode 11
• * * Basic Swift 4 and iOS 11 — Free Preview
• Unity Android Game Development : Build 7 2D & 3D Games
• JSP, Servlets and JDBC for Beginners: Build a Database App
• * * Cloning Amazon, Netflix & Spotify
• 2D Game Development With HTML5 Canvas, JS — Tic Tac Toe Game
• Advanced WordPress Theme Development with Bootstrap 4
• Servlets and JSPs Tutorial: Learn Web Applications With Java
• Java Fx Concepts With Practical Examples
• Beginner’s Swift 4 & Unreal Engine — Learn Xcode, Make Games
• Learning Oracle 12c — A Beginners Oracle Training Video
• Angularjs Live Project
• Create Dynamic web Forms with jQuery
• Creating Reports with SQL Server 2012 Reporting Services
• Build a Rental App with Flutter: iOS & Android Masterclass
• Spring & Hibernate for Beginners (includes Spring Boot)
• Create Android and iOS App using HTML, CSS and JS
• Create Ajax Chat App with PHP & Mysql
• MySQL Database Training for Beginners
• Learning Path: Master Web Development with Ruby on Rails 5
• Django in AWS (EC2): Fundamentals Course

Источник

How to open .xlsx files with POI SS?

I am trying to open .xlsx files with POI SS with this code (taken from http://poi.apache.org/spreadsheet/quick-guide.html#ReadWriteWorkbook):

InputStream inp = new FileInputStream("workbook.xls"); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 

Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException 

I add the xbean.jar to my library and to my run-time libraries. how can I resolve this exception? Thanks !

5 Answers 5

First: Fix the Exception

1. As Gagravarr already mentioned: you need dom4j to fix your exception.
2. As Jon already mentioned: you have to update your dependencies, so you don’t need dom4j anymore.

If you’re using Maven, you can add the necessary dependencies with: (Maybe check for newer versions at: Maven Repository: org.apache.poi)

 org.apache.poi poi-ooxml 3.12  org.apache.commons commons-lang3 3.4  

Then: Open the File

If you’ve fixed the exception, you can open your file.xlsx file with the following code:

String path = "Relative/Path/To/Your/File/file.xlsx"; File file = new File(path); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); // Use your sheet . 

Further tips

• Like Gagravarr, I also recommend to use a file instead of a file input stream.
• If you want to open a certain sheet you can use workbook.getSheet(String name);
• If you don’t know the relative path to your file according to your project, you can easily check it with System.out.println(«Relative path: » + System.getProperty(«user.dir»));

Источник