We will learn to read, write excel, evaluate formula in cells and do custom formatting to the generated excel file by using Apache POI library. Read and write excel file in java is very simple using some libraries which we will discuss below.
Reading and Writing excel by using program is often required in software projects.
Apache POI is very useful library among many other open source libraries to use in such ways involving excel files. Using POI, we can Read and write excel file in java. It can be used to read and write MS Word and MS PowerPoint also.
Apache POI β Maven Dependency
If you are using maven project then you can include the POI dependency in pom.xml
as:
<dependencies> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.0</version> </dependency> </dependencies>
If you are not using the maven project then you can download below jar files Apache POI – Download Release Artifacts:
- poi-4.1.0.jar
- commons-codec-1.12.jar
- commons-collections4-4.3.jar
- commons-math3-3.6.1.jar
If you are reading and writing Excel 2007 format then you have to include the following files:
- poi-ooxml-VERSION.jar
- poi-ooxml-schemas-VERSION.jar
- xmlbeans-VERSION.jar
Apache POI API Basics
HSSF: API for Excel 2003 and earlier.
XSSF: API for Excel 2007 and later.
Workbook: Broad level representation for Excel workbook. Implementations are HSSFWorkbook and XSSFWorkbook.
Sheet: Broad level representation for Excel worksheet. Implementations classes are HSSFSheet and XSSFSheet.
Row: Broad level representation for row in a spreadsheet. HSSFRow and XSSFRow are two implementing classes.
Cell: Broad level representation for cell in a spreadsheet row. HSSFCell and XSSFCell are the implementing classes.
Style Classes : Classes such as CellStyle, BuiltinFormats, ConditionalFormattingRule,ComparisonOperator, IndexedColors, PatternFormatting, FontFormatting, SheetConditionalFormatting etc. are used when you are formatting in a sheet based on rules.
FormulaEvaluator: Class FormulaEvaluator is used for evaluating the formula in cells of excel sheet.
Apache POI β Write an excel file
Writing excel involves following steps:
- Create a spreadsheet
- Create a row in spreadsheet
- Add cells in spreadsheet
- Repeat step 3 and 4 to write more data
Java Program for Writing Excel File:
package com.techblogstation; import java.io.File; import java.io.FileOutputStream; import java.util.Map; import java.util.Set; import java.util.TreeMap; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcelFile { private static String[] columns = {"ID", "FirstName", "LastName"}; public static void main(String[] args) { //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Cricketer Data"); //This data needs to be written (Object[]) Map<String, Object[]> data = new TreeMap<String, Object[]>(); data.put("2", new Object[] {1, "Sachin", "Tendulkar"}); data.put("3", new Object[] {2, "Saurav", "Ganguly"}); data.put("4", new Object[] {3, "Rahul", "Dravid"}); data.put("5", new Object[] {4, "Virat", "Kohli"}); //Iterate over data and write to sheet Set<String> keyset = data.keySet(); int rownum = 1; // Create a Font for styling header cells Font headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short) 14); headerFont.setColor(IndexedColors.RED.getIndex()); // Create a CellStyle with the font CellStyle headerCellStyle = workbook.createCellStyle(); headerCellStyle.setFont(headerFont); // Create a Header Row Row headerRow = sheet.createRow(0); // Create cells for(int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(columns[i]); cell.setCellStyle(headerCellStyle); } 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("TechBlogStation.xlsx")); workbook.write(out); out.close(); System.out.println("****File written successfully*****"); } catch (Exception e) { e.printStackTrace(); } } }
Created Excel File from above Program :
Apache POI β Read an excel file
Reading an excel file using POI has these steps.
- Create workbook from spreadsheet
- Increment row number
- iterate over all cells in a row
- repeat step 3 and 4 until all data is read
It will read all the column names and the values cell by cell.
Java program to read excel file using apache POI api –
package com.techblogstation; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.Iterator; import java.util.Map; import java.util.Set; import java.util.TreeMap; import org.apache.poi.ss.usermodel.Cell; //import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadExcelFile { public static void main(String[] args) { try { FileInputStream file = new FileInputStream(new File("TechBlogStation.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 Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case NUMERIC : System.out.print(cell.getNumericCellValue() + "\t"); break; case STRING: System.out.print(cell.getStringCellValue() + "\t"); break; } //Check the cell type and format accordingly } System.out.println("\n"); } file.close(); } catch (Exception e) { e.printStackTrace(); } } }
Conclusion
Thatβs all techies! In this article, You have learnt how to read excel files in Java using Apache POI library.