Apache Excel POI Utils : A Java Excel API java by devs5003 - December 10, 2021April 9, 20220 Last Updated on April 9th, 2022 Apache Excel POI Utils : A Java Excel API (No need to create row or cell each time) Table of Contents Toggle What is Apache POI ? Why is it so popular to utilize?Apache Excel POI custom update() Utility Method How to get/read existing cell value from excel ?How to upload excel data into database using Spring Boot ?How to convert excel data into List of Java Objects using Poiji API? What is Apache POI ? Why is it so popular to utilize? Apache POI is an open source Java API. It allows developers to create, modify, and display MS Office files using Java programs. Also, Apache POI is developed by Apache Software Foundation to operate on Microsoft Office files as a Java program. Furthermore, It is the most popular API and up to date to work with newer versions of MS Office files. On the other hand, some other available APIs are JEXCEL, JXLS, FILLO, APACHE COMMONS CSV etc. However, Apache POI provides stream-based processing which is suitable for large files & requires less memory. It supports both xls & xlsx formats of spreadsheets. HSSF is the Java implementation of the Excel ’97(-2007) file format & XSSF is the Java implementation of the Excel 2007 OOXML (. xlsx) file format under POI Project. Here in our article ‘Apache Excel POI Utils : A Java Excel API’, we have created some custom utility methods to work with an Excel sheet. Hence, you can use them directly to take advantage of particular functionality in the project. In addition, we have already tested them in the required Java environment and applied in real time project. Note : Apache POI does not actively support Java 9 or Java 10 any longer as those versions were obsoleted by Oracle already. Apache Excel POI custom update() Utility Method There is a POI library, also called ‘Apache POI’ to work on the excel operations. We know it as a Java Excel API. During project development, often we come across the scenarios where we need to work on excel operations. Here we are providing excel update utility method to automate the creation of rows/cells each time. In fact, we only need to pass row/cell number where we require a value to be updated. Hence, we will call them Apache Excel POI Utils to make sense of it. import java.text.DateFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.Map;import java.util.Properties;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.RichTextString;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.XSSFRichTextString;public class ExcelPOIUtils { private static void setCellValue(Cell myCell1, Object val, int dataType){ switch (dataType) { case 0://int if(val == null){ myCell1.setCellValue(getCellValue("")); }else if(Integer.parseInt(val.toString()) == 0){ myCell1.setCellValue(0); }else{ myCell1.setCellValue((Integer)val); } break; case 1://double if(val == null){ myCell1.setCellValue(getCellValue("")); }else if(Double.parseDouble(val.toString()) == 0.0){ myCell1.setCellValue(0); }else{ myCell1.setCellValue((Double)val); } break; case 2://date myCell1.setCellValue((Date)val); break; case 3://richtext myCell1.setCellValue(ExcelPOIUtils.getCellValue(val.toString())); break; default://string myCell1.setCellValue(getCellValue(val.toString())); break; } } public static void updateMyCell(Workbook reportWorkbook, Sheet sampleDataSheet, int row, int cell, Object val, int dataType){ Row excelRow = sampleDataSheet.getRow(row); if(excelRow== null){ excelRow = sampleDataSheet.createRow(row); } Cell myCell1 = excelRow.getCell(cell); if(myCell1 == null){ myCell1 = excelRow.createCell(cell); } if(val != null && val.toString().trim().length() > 0) { setCellValue(myCell1, val, dataType); } } public static RichTextString getCellValue(String value){ return new XSSFRichTextString(value); } } How to get/read existing cell value from excel ? To retrieve the value of a particular cell we can use below Utility method. public static String getExistingCellValue(Cell myCell) throws Exception { String value = null; switch (myCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // String myCell1 = Double.toString(myCell.getNumericCellValue()); value = myCell1.substring(0, myCell1.length() - 2); break; case Cell.CELL_TYPE_FORMULA: value = myCell.toString(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: value = ""; break; } return value; } How to upload excel data into database using Spring Boot ? Further to check this implementation, kindly visit the blog section from the menu or follow internal link. Software used in this implementation are: –STS (Spring Tool Suite) : Version-> 4.7.1.RELEASE –MySQL Database : Version ->8.0.19 MySQL Community Server –JDK8 or later versions (Extremely tested on JDK8, JDK9 and JDK14) and the latest Apache POI jar used is as below: <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> How to convert excel data into List of Java Objects using Poiji API? Further to check this implementation, kindly visit the blog section from the menu or follow internal link. Here, we have used Poiji API which is developed on top of Apache POI to convert excel data into List of Java Objects in very easy & fast manner. Equally important, this API allows us to use annotations to do the conversion easily. Furthermore, below is the dependency of poiji API and it is also available in the maven repository. <dependency> <groupId>com.github.ozlerhakan</groupId> <artifactId>poiji</artifactId> <version>3.0.0</version> </dependency> Related