You may conscious that Excel file now comes with two codecs, XLS file which is an OLE format, and XLSX format, which is often known as OpenXML format. Apache POI helps each format however you would wish completely different JAR recordsdata to learn/write XLS and XLSX recordsdata. You want poi-3.12.jar to learn XLS file and poi-ooxml-3.12.jar to learn XLSX file in Java.
You’ll be able to write completely different OLE codecs utilizing poi-3.12.jar for instance you may also use this JAR to learn Microsoft Phrase recordsdata witch .DOC extension and Microsoft PowerPoint recordsdata with .PPT extension in Java. Equally you’ll be able to learn different OpenXML codecs e.g. DOCX and PPTX utilizing poi-ooxml-3.12.jar file.
Apache POI JARs to Learn/Write Excel File in Java
Although there are couple of open supply library accessible to learn and write from Excel file in Java e.g. JXL, essentially the most characteristic wealthy and hottest one is Apache POI library. You’ll be able to learn each forms of Excel file format utilizing this library. With the intention to use this library both you must obtain POI JAR recordsdata and add into your Eclipse’s construct path manually or you’ll be able to use Maven to obtain dependency for you.
In case you are utilizing Maven then embrace following two dependencies to make use of Apache POI in your Java program :
<dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <model>3.12</model> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <model>3.12</model> </dependency> </dependencies>
Fundamental benefit of utilizing Maven is that it not solely downloads direct dependency e.g. poi.jar and poi-ooxml.jar but in addition obtain transitive dependency e.g. JARS on which POI library is internally dependent. For instance, I’ve simply specified Apache POI JAR recordsdata however Maven will even obtain xmlbeans-2.6.0.jar, stax-api-1.0.1.jar, poi-ooxml-schemas-3.12.jar and commons-codec-1.9.jar.
JAR Dependencies :
In case you are extra comfy by downloading JAR recordsdata by your self, you’ll be able to obtain Apache POI JARS from right here . It will obtain entire bundle so that you need not fear, however make certain it incorporates following JAR recordsdata in case your utility goes to help each XLS and XLSX format.
- poi-3.12.jar
- commons-codec-1.9.jar
- poi-ooxml-3.12.jar
- poi-ooxml-schemas-3.12.jar
- xmlbeans-2.6.0.jar
- stax-api-1.0.1.jar
POI is for studying OLE format e.g. XLS, DOC and .PPT format, whereas poi-ooxml.jar is to learn XLSX, DOCX and .PPTX format. Do not obtain simply POI jar, all the time embrace transitive dependency.
How one can learn from Excel File in Java? Instance
Suppose you’ve got a cell in your excel file which incorporates a date e.g. birthdate? how do you learn it? Most of you’ll say that you’ll learn that cell by first making a Workbook, then getting a sheet from that workbook, then getting the cell from that sheet which is containing date worth and eventually getting cell worth from that cell.
Cool, these are the steps to learn information from Excel file in Java, however you forgot one factor you must discover the cell kind earlier than getting cell worth, in any other case you’ll be get error studying that cell. Studying date values are much more difficult. To your shock, there is no such thing as a date cell kind in Excel (each XLS and XLSX), as an alternative of Excel shops date as numeric kind.
So you must examine the cell kind with HSSFCell.CELL_TYPE_NUMERIC in case you are studying XLS file and XSSFCell.CELL_TYPE_NUMERIC in case you studying XLSX file, however story would not finish right here, in case you simply print the cell worth through the use of getNumericCellValue(), you’ll not get any error however you will note an arbitrary quantity.
In our instance, we are going to create an excel file which incorporates one row and two columns. The primary column will include a String kind, the place we are going to retailer identify and the second column can be of date kind, the place we are going to date of beginning. Later, we are going to learn the identical excel file in our Java program to show identify and date values into the console.
How one can learn/write from XLS file in Java
That is our first instance to learn String and date values from Excel recordsdata in Java. On this instance, we’re first creating an previous Excel file format i.e. XLS file birthdays.xls and later we are going to learn from the identical file. As soon as we run our program, you’ll be able to see this excel file created in your Eclipse venture listing, as proven beneath.
Steps to jot down Knowledge into XLS file in Java
- Embrace poi-3.12.jar in your Java program’s classpath
- Create an object of HSSFWorkBook
- Create a Sheet on that workbook by calling createSheet() technique
- Create a Row on that sheet by calling createRow() technique
- Create a Cell by calling createCell() technique
- Set worth to that cell by calling setCellValue() technique.
- Write workbook content material into File utilizing FileOutputStream object.
- Shut the workbook object by calling shut() technique
These steps are high quality for writing String and Numeric values however in an effort to write date values into Excel file, you must observe following extra steps :
- Create a DataFormat
- Create a CellStyle
- Set format into CellStyle
- Set CellStyle into Cell
- Write java.util.Date into Cell
Step to learn information from XLS file in Java
- Embrace poi-3.12.jar in your Java program’s classpath
- Create an object of HSSFWorkBook by opening excel file utilizing FileInputStream
- Get a Sheet from workbook by calling getSheet() technique, you’ll be able to cross identify or sheet index
- Get a Row from that sheet by calling getRow() technique, you’ll be able to cross index
- Get a Cell by calling getCell() technique
- Get the Cell kind by calling getCellType() technique.
- Relying upon Cell kind, name getStringCellValue(), getNumericCellValue() or getDateCellValue() technique to get worth.
- Shut the workbook object by calling shut() technique
In case you are studying date values then only one thing more to do not forget that there is no such thing as a cell with date kind and Excel shops date as a numeric kind. So all the time examine kind of a cell with date worth to a numeric cell kind.
On this program, studying and writing logic are encapsulated into two static utility strategies readFromExcel() and writeIntoExcel(), so you may also check out them for precise code for studying writing XLS file in Java.
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFCell; 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.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; /** * Easy Java Program to learn and write dates from Excel file in Java. * This instance significantly learn Excel file in OLE format i.e. * Excel file with extension .xls, often known as XLS recordsdata. * * @creator WINDOWS 8 * */ public class ExcelDateReader { public static void major(String[] args) throws FileNotFoundException, IOException { writeIntoExcel("birthdays.xls"); readFromExcel("birthdays.xls"); } /** * Java technique to learn dates from Excel file in Java. * This technique learn worth from .XLS file, which is an OLE * format. * * @param file * @throws IOException */ public static void readFromExcel(String file) throws IOException{ HSSFWorkbook myExcelBook = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays"); HSSFRow row = myExcelSheet.getRow(0); if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){ String identify = row.getCell(0).getStringCellValue(); System.out.println("identify : " + identify); } if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ Date birthdate = row.getCell(1).getDateCellValue(); System.out.println("birthdate :" + birthdate); } myExcelBook.shut(); } /** * Java technique to jot down dates from Excel file in Java. * This technique write worth into .XLS file in Java. * @param file, identify of excel file to jot down. * @throws IOException * @throws FileNotFoundException */ @SuppressWarnings("deprecation") public static void writeIntoExcel(String file) throws FileNotFoundException, IOException{ Workbook e book = new HSSFWorkbook(); Sheet sheet = e book.createSheet("Birthdays"); // first row begin with zero Row row = sheet.createRow(0); // we are going to write identify and birthdates in two columns // identify can be String and birthday can be Date // formatted as dd.mm.yyyy Cell identify = row.createCell(0); identify.setCellValue("John"); Cell birthdate = row.createCell(1); // steps to format a cell to show date worth in Excel // 1. Create a DataFormat // 2. Create a CellStyle // 3. Set format into CellStyle // 4. Set CellStyle into Cell // 5. Write java.util.Date into Cell DataFormat format = e book.createDataFormat(); CellStyle dateStyle = e book.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy")); birthdate.setCellStyle(dateStyle); // It is very trick technique, deprecated, do not use // yr is from 1900, month begins with zero birthdate.setCellValue(new Date(110, 10, 10)); // auto-resizing columns sheet.autoSizeColumn(1); // Now, its time to jot down content material of Excel into File e book.write(new FileOutputStream(file)); e book.shut(); } } Output identify : John birthdate :Wed Nov 10 00:00:00 GMT+08:00 2010
In our program, we’ve first created an excel file with String and date columns and later learn from the identical file and displayed the values into the console. Now let’s confirm output of this program. It is appropriately show the date worth, although not formatted, which implies the excel file was created efficiently and later Java was in a position to learn from it.
It’s because I have never included sheet.autoSizeColumn(1) technique name in first run and since column width shouldn’t be sufficient to show the date in requested format e.g. dd.mm.yyyy it simply shows ######.
With the intention to remedy this drawback of date not displaying correctly, all you must do is allow autosizing of columns in Excel by calling sheet.autoSizeColumn(1) technique, the place column index is the column you wish to resize mechanically.
Apache POI Instance to learn XLSX file in Java
Studying and writing into new excel file format XLSX can also be the identical, all you must do is embrace poi-ooxml.jar and substitute all HSFF lessons with XSSF lessons e.g. as an alternative of utilizing HSSFWorkbook, use XSSFWorkbook, as an alternative of utilizing HSFFSheet use XSSFSheet, as an alternative of utilizing HSSFRow use XSSFRow and as an alternative of utilizing HSSFCell simply use XSSFCell class.
Remainder of the code and steps can be similar. In following Java program, I’ll present you find out how to learn XLSX file in Java. On this program additionally we’re first creating an excel file and writing string and date values into it and later studying from the identical excel file and displaying information into the console, solely distinction this time can be as an alternative of making an XLS file, our program will create an XLSX file.
right here is our java program to learn XLSX recordsdata utilizing Apache POI library.
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; 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.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * This program learn date values from XLSX file in Java utilizing Apache POI. * * @creator WINDOWS 8 * */ public class ExcelDateReader { public static void major(String[] args) throws FileNotFoundException, IOException { writeIntoExcel("birthdays.xlsx"); readFromExcel("birthdays.xlsx"); } public static void readFromExcel(String file) throws IOException{ XSSFWorkbook myExcelBook = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays"); XSSFRow row = myExcelSheet.getRow(0); if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){ String identify = row.getCell(0).getStringCellValue(); System.out.println("NAME : " + identify); } if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ Date birthdate = row.getCell(1).getDateCellValue(); System.out.println("DOB :" + birthdate); } myExcelBook.shut(); } @SuppressWarnings("deprecation") public static void writeIntoExcel(String file) throws FileNotFoundException, IOException{ Workbook e book = new XSSFWorkbook(); Sheet sheet = e book.createSheet("Birthdays"); Row row = sheet.createRow(0); Cell identify = row.createCell(0); identify.setCellValue("Gokul"); Cell birthdate = row.createCell(1); DataFormat format = e book.createDataFormat(); CellStyle dateStyle = e book.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy")); birthdate.setCellStyle(dateStyle); birthdate.setCellValue(new Date(115, 10, 10)); sheet.autoSizeColumn(1); e book.write(new FileOutputStream(file)); e book.shut(); } } NAME : Gokul DOB :Tue Nov 10 00:00:00 GMT+08:00 2015
That is all about find out how to learn and write from Excel file in Java. You’ve got now realized find out how to learn and write each String and Date from XLS in addition to XLSX recordsdata in Java. You are able to do much more utilizing the Apache POI library however this information will make it easier to be taught and shortly use this library. As soon as once more I counsel to use Maven for together with POI dependency and in case you are downloading JAR, be sure to obtain transitive dependency e.g. xmlbeans.