Java apache poi eclipse

Apache POI – Read and Write Excel files in java

This article shows you how to create and read excel files using the Apache POI. To Read and Write Excel files in Java, Apache provides a very popular library called Apache POI.

Microsoft Excel documents generally come in two different formats Excel 97(-2003) and Excel(2007+). ie XLS and XLSX formats. In this article, we will see the implementation of reading and writing for both Excel file formats.

Apache POI – API Basics to Read/Write Excel Files

The Apache POI library is capable to read and write both XLS and XLSX file formats of Excel. HSSF implementation is provided by the POI to read/write the XLS file. XSSF implementation is provided to read/write the XLSX file.

POI library provides various classes and interfaces for reading and writing Excel files. Let’s see the basic interfaces and classes here:

The basic interfaces are Workbook , Sheet , Row , Cell , CellStyle , Font , etc. And there are specific concrete classes for specific file formats (.xls and .xlsx).

For Excel file format 2003 (XLS): HSSFWorkbook , HSSFSheet , HSSFRow , HSSFCell , etc.

For Excel file format 2007 (XLSX): XSSFWorkbook , XSSFSheet , XSSFRow , XSSFCell , etc.

• HSSFWorkbook & XSSFWorkbook – used to create the workbook
• HSSFSheet & XSSFSheet – used to create the sheet for the excel file
• HSSFRow & XSSFRow – used to create the row in the sheet
• HSSFCell & XSSFCell – used to create the cell in the row

We recommend you use common interfaces to support both Excel format XLS and XLSX.

How to Write Excel file in java?

The Apache POI provides two major components to work with Excel files. HSSF component is provided to write the XLS file. XSSF component is provided to write the XLSX file.

Let’s see the implementations for both files format separately one by one and then we will see a generic implementation also for both formats.

Writing XLS file in java

Basic steps to writing data in an XLS file:

1. Create a HSSFWorkbook .
2. Create a HSSFSheet using createSheet().
3. Create a HSSFRow using createRow().
4. Create a HSSFCell using createCell().
5. Repeat the step-3 and step-4 until all data is processed.
6. Write workbook to an OutputStream .
7. Close the stream and workbook.

The below java code demonstrates writing data to an Excel(.xls) file:

package com.javacodepoint.excel; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class WriteExcelFile < // Main method public static void main(String[] args) < // Creating a xlsfile object with specific file path File xlsFile = new File("E:/Excel/employee.xls"); // Writing the xls file writeXLSFile(xlsFile); >// Method to write xls file public static void writeXLSFile(File xlsFile) < try < // Creating workbook HSSFWorkbook workbook = new HSSFWorkbook(); // Creating sheet HSSFSheet sheet = workbook.createSheet("Employee Records"); // Creating header of the excel sheet HSSFRow header = sheet.createRow(0); // Creating cell and setting the cell value header.createCell(0).setCellValue("Employee Id"); header.createCell(1).setCellValue("Employee Name"); header.createCell(2).setCellValue("Age"); header.createCell(3).setCellValue("Email ID"); header.createCell(4).setCellValue("Salary"); // Creating the 1st row to insert employee record HSSFRow row1 = sheet.createRow(1); // Inserting 1st employee record row1.createCell(0).setCellValue("101"); row1.createCell(1).setCellValue("John william"); row1.createCell(2).setCellValue("30"); row1.createCell(3).setCellValue("[email protected]"); row1.createCell(4).setCellValue("15000$"); // Creating the 2nd row HSSFRow row2 = sheet.createRow(2); // Inserting 2nd employee record row2.createCell(0).setCellValue("102"); row2.createCell(1).setCellValue("Harsh singh"); row2.createCell(2).setCellValue("35"); row2.createCell(3).setCellValue("[email protected]"); row2.createCell(4).setCellValue("20000$"); // Creating file output stream to write the workbook data in to the file FileOutputStream fos = new FileOutputStream(xlsFile); // Writing workbook workbook.write(fos); // Closing the output stream fos.close(); // Closing the workbook workbook.close(); // Printing the success message on the console System.out.println("Excel(.xls) file has been created successfully."); > catch (IOException e) < System.out.println("Exception while writting xls file"); e.printStackTrace(); >> >

Below is a screenshot of the created Excel file-

Writing XLSX file in java

Basic steps to writing data in an XLSX file:

1. Create a XSSFWorkbook .
2. Create a XSSFSheet using createSheet().
3. Create a XSSFRow using createRow().
4. Create a XSSFCell using createCell().
5. Repeat the step-3 and step-4 until all data is processed.
6. Write workbook to an OutputStream .
7. Close the stream and workbook.

The below java code demonstrates writing data to an Excel(.xlsx) file:

package com.javacodepoint.excel; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcelFile < // Main method public static void main(String[] args) < // Creating a xlsxfile object with specific file path File xlsxFile = new File("E:/Excel/employee.xlsx"); // Writing the xlsx file writeXLSXFile(xlsxFile); >// Method to write xlsx file public static void writeXLSXFile(File xlsxFile) < try < // Creating workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Creating sheet XSSFSheet sheet = workbook.createSheet("Employee Records"); // Creating header of the excel sheet XSSFRow header = sheet.createRow(0); // Creating cell and setting the cell value header.createCell(0).setCellValue("Employee Id"); header.createCell(1).setCellValue("Employee Name"); header.createCell(2).setCellValue("Age"); header.createCell(3).setCellValue("Email ID"); header.createCell(4).setCellValue("Salary"); // Creating the 1st row to insert employee record XSSFRow row1 = sheet.createRow(1); // Inserting 1st employee record row1.createCell(0).setCellValue("101"); row1.createCell(1).setCellValue("John william"); row1.createCell(2).setCellValue("30"); row1.createCell(3).setCellValue("[email protected]"); row1.createCell(4).setCellValue("15000$"); // Creating the 2nd row XSSFRow row2 = sheet.createRow(2); // Inserting 2nd employee record row2.createCell(0).setCellValue("102"); row2.createCell(1).setCellValue("Harsh singh"); row2.createCell(2).setCellValue("35"); row2.createCell(3).setCellValue("[email protected]"); row2.createCell(4).setCellValue("20000$"); // Creating file output stream to write the workbook data in to the file FileOutputStream fos = new FileOutputStream(xlsxFile); // Writing workbook workbook.write(fos); // Closing the output stream fos.close(); // Closing the workbook workbook.close(); // Printing the success message on the console System.out.println("Excel(.xlsx) file has been created successfully."); > catch (IOException e) < System.out.println("Exception while writting xlsx file"); e.printStackTrace(); >> >

[Generic logic] Writing both(XLS and XLSX) file in java

The following java code demonstrates writing data to an Excel(.xls and .xlsx) file to support both formats:

package com.javacodepoint.excel; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcelFile < // Main method public static void main(String[] args) < // Creating a xlsxfile object with specific file path File xlsxFile = new File("E:/Excel/employee.xlsx"); // Common method to write excel file based on file format writeExcelFile(xlsxFile); >// Method to write both excel file xls and xlsx format public static void writeExcelFile(File excelFile) < try < Workbook workbook = null; // Creating workbook based on file format if (excelFile.getName().endsWith(".xls")) < workbook = new HSSFWorkbook(); >else if (excelFile.getName().endsWith(".xlsx")) < workbook = new XSSFWorkbook(); >else < throw new IllegalArgumentException("The specified file is not supported"); >// Creating sheet Sheet sheet = workbook.createSheet("Employee Records"); // Creating header of the excel sheet Row header = sheet.createRow(0); // Creating cell and setting the cell value header.createCell(0).setCellValue("Employee Id"); header.createCell(1).setCellValue("Employee Name"); header.createCell(2).setCellValue("Age"); header.createCell(3).setCellValue("Email ID"); header.createCell(4).setCellValue("Salary"); // Creating the 1st row to insert employee record Row row1 = sheet.createRow(1); // Inserting 1st employee record row1.createCell(0).setCellValue("101"); row1.createCell(1).setCellValue("John william"); row1.createCell(2).setCellValue("30"); row1.createCell(3).setCellValue("[email protected]"); row1.createCell(4).setCellValue("15000$"); // Creating the 2nd row Row row2 = sheet.createRow(2); // Inserting 2nd employee record row2.createCell(0).setCellValue("102"); row2.createCell(1).setCellValue("Harsh singh"); row2.createCell(2).setCellValue("35"); row2.createCell(3).setCellValue("[email protected]"); row2.createCell(4).setCellValue("20000$"); // Creating file output stream to write the workbook data in to the file FileOutputStream fos = new FileOutputStream(excelFile); // Writing workbook workbook.write(fos); // Closing the output stream fos.close(); // Closing the workbook workbook.close(); // Printing the success message on the console System.out.println("Excel file has been created successfully."); > catch (IOException e) < System.out.println("Exception while writting excel file"); e.printStackTrace(); >> >

How to Read Excel file in java?

In order to read the Excel file, the above same components HSSF(denotes the API is for working with Excel 2003 and earlier) and XSSF(denotes the API is for working with Excel 2007 and later) of Apache POI will be used.

Let’s assume the sample excel file which we are going to read looks like the below screenshot:

The above excel sheet contains the information about Employees including the Employee ID, Employee Name, Age, Email ID, Salary.

Let’s see the separate implementation for both file formats.

Reading XLS file in java

Below is the java code example that read excel’s first sheet and cell value of each row one by one:

package com.javacodepoint.excel; 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.Row; public class ReadXLSExcelFile < public static void main(String[] args) throws IOException < // Creating a xls file object with specific file path to read File xlsFile = new File("E:/Excel/employee.xls"); // Creating input stream FileInputStream inputStream = new FileInputStream(xlsFile); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // Reading the first sheet of the excel file HSSFSheet sheet = workbook.getSheetAt(0); Iteratoriterator = sheet.iterator(); // Iterating all the rows while (iterator.hasNext()) < Row nextRow = iterator.next(); IteratorcellIterator = nextRow.cellIterator(); // Iterating all the columns in a row while (cellIterator.hasNext()) < Cell cell = cellIterator.next(); switch (cell.getCellType()) < case STRING: System.out.print(cell.getStringCellValue()); break; case BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case NUMERIC: System.out.print(cell.getNumericCellValue()); break; default: break; >System.out.print(" | "); > System.out.println(); > // Closing the workbook and input stream workbook.close(); inputStream.close(); > >

Employee Id | Employee Name | Age | Email ID | Salary |
101 | John william | 30 | [email protected] | 15000$ |
102 | Harsh singh | 35 | [email protected] | 20000$ |

Reading XLSX file in java

To read Excel 2007(.xlsx) file format, it is also almost similar to the Excel 2003(.xls). The only difference here is we have to use XSSFWorkbook and XSSFSheet classes instead of HSSFWorkbook and HSSFSheet .

See the below complete java code:

package com.javacodepoint.excel; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadXLSXExcelFile < public static void main(String[] args) throws IOException < // Creating a xlsx file object with specific file path to read File xlsxFile = new File("E:/Excel/employee.xlsx"); // Creating input stream FileInputStream inputStream = new FileInputStream(xlsxFile); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); // Reading the first sheet of the excel file XSSFSheet sheet = workbook.getSheetAt(0); Iteratoriterator = sheet.iterator(); // Iterating all the rows while (iterator.hasNext()) < Row nextRow = iterator.next(); IteratorcellIterator = nextRow.cellIterator(); // Iterating all the columns in a row while (cellIterator.hasNext()) < Cell cell = cellIterator.next(); switch (cell.getCellType()) < case STRING: System.out.print(cell.getStringCellValue()); break; case BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case NUMERIC: System.out.print(cell.getNumericCellValue()); break; default: break; >System.out.print(" | "); > System.out.println(); > // Closing the workbook and input stream workbook.close(); inputStream.close(); > >

[Generic logic] Reading both(XLS and XLSX) file in java

To read both file formats in a single program, we have to use common interfaces Workbook , Sheet instead of specific classes like HSSFWorkbook , XSSFWorkbook , HSSFSheet , XSSFSheet , etc.

Below is the java code example that read the first sheet and the cell value of each row one by one:

package com.javacodepoint.excel; import java.io.File; import java.io.FileInputStream; import java.util.Iterator; 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 org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadBothFormatExcelFile < public static void main(String[] args) throws Exception < // Creating a xlsx file object with specific file path to read File xlsxFile = new File("E:/Excel/employee.xlsx"); // Creating input stream FileInputStream inputStream = new FileInputStream(xlsxFile); Workbook workbook = null; // Based on type workbook will crate if (xlsxFile.getName().endsWith(".xlsx")) < workbook = new XSSFWorkbook(inputStream); >else if (xlsxFile.getName().endsWith(".xls")) < workbook = new HSSFWorkbook(inputStream); >else < inputStream.close(); throw new Exception("File not supported!"); >// Reading the first sheet of the excel file Sheet sheet = workbook.getSheetAt(0); Iterator iterator = sheet.iterator(); // Iterating all the rows while (iterator.hasNext()) < Row nextRow = iterator.next(); IteratorcellIterator = nextRow.cellIterator(); // Iterating all the columns in a row while (cellIterator.hasNext()) < Cell cell = cellIterator.next(); switch (cell.getCellType()) < case STRING: System.out.print(cell.getStringCellValue()); break; case BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case NUMERIC: System.out.print(cell.getNumericCellValue()); break; default: break; >System.out.print(" | "); > System.out.println(); > // Closing the workbook and input stream workbook.close(); inputStream.close(); > >

Creating password-protected Excel file in java

Use Biff8EncryptionKey.setCurrentUserPassword(String password) to specify the decryption password before saving or writing the workbook of the excel file.

Let’s see the below java code snippet to protect XLS files. We are setting “javacodepoint” as a password here,

// Creating a xlsfile object with specific file path File xlsFile = new File("E:/Excel/protected.xls"); // Setting the file password = javacodepoint // It should be called before saving the workbook Biff8EncryptionKey.setCurrentUserPassword("javacodepoint"); // Creating workbook HSSFWorkbook workbook = new HSSFWorkbook(); //Creating the sheet data. // Creating file output stream to write the workbook data FileOutputStream fos = new FileOutputStream(xlsFile); // Writing workbook workbook.write(fos);

See our complete guide to create a protected Excel file here: How to create password-protected Excel in java?

Reading password-protected Excel file in java

To read a password-protected XLSX file we need to follow simple steps. Below is the sample code snippet to understand this,

POIFSFileSystem fs = new POIFSFileSystem(xlsxFile); EncryptionInfo info = new EncryptionInfo(fs); Decryptor decryptor = Decryptor.getInstance(info); //Verifying the password if (!decryptor.verifyPassword("javacodepoint")) < throw new RuntimeException("Incorrect password: Unable to process"); >InputStream dataStream = decryptor.getDataStream(fs); // Now parse dataStream

Let’s see another article for the complete guide of reading password-protected XLS, XLSX, and both here: How to Read password-protected Excel in java?

Apache POI – Environment setup in Eclipse

There are two ways for installing Apache POI in Eclipse, based on your project type:

Note: In this article, we have used the Apache POI-4.0.1 version of the library to demonstrate.

Maven Project

If you are going to create a maven project then you have to add the following maven dependency in the pom.xml file of your project:

  org.apache.poi poi 4.0.1   org.apache.poi poi-ooxml 4.0.1  

Stand alone Java Project (Non Maven)

If you are going to create a standalone java project then you have to add the following jar files into your java build path:

1. poi-4.0.1.jar
2. poi-ooxml-4.0.1.jar
3. poi-ooxml-schemas-4.0.1.jar
4. xmlbeans-3.0.2.jar
5. curvesapi-1.05.jar
6. commons-codec-1.11.jar
7. commons-collections4-4.2.jar
8. commons-compress-1.18.jar
9. commons-math3-3.6.1.jar

You can easily download all the above jars in one place: Download Apache POI Jars

If you want to know the complete environment setup for Apache POI in Eclipse IDE, follow another article here: Apache POI – Getting Started

Conclusion

In this article, you have seen how to create, read and write an Excel document in Java using the Apache POI library. You have seen the HSSF and XSSF are two different components provided by POI used for specific Excel file formats(XLS and XLSX).

Related Articles:

You might like this:

Источник