You will discover lot of examples of learn how to do with Excel utilizing Apache POI on-line, which suggests you’ll by no means really feel alone and has prompt Google assist in case you caught there. On this article, we’ll be taught learn how to learn and write excel information in Java.
Thankfully Apache POI helps each format, and you’ll simply create, learn, write and replace Excel information utilizing this library. It makes use of phrases like workbook, worksheet, cell, row to maintain itself aligned with Microsoft Excel and that is why it is rather straightforward to make use of. Apache POI additionally supplies completely different implementation lessons to deal with each XLS and XLSX file format.
One attention-grabbing factor with POI is that (I do not know whether or not it is intentional, by chance or actual) it’s got some actually humorous names for its workbook implementations like
- XSSF (XML SpreadSheet Format) – Used to studying and writing Open Workplace XML (XLSX) format information.
- HSSF (Horrible SpreadSheet Format) – Use to learn and write Microsoft Excel (XLS) format information.
- HWPF (Horrible Phrase Processor Format) – to learn and write Microsoft Phrase 97 (DOC) format information.
- HSMF (Horrible Silly Mail Format) – pure Java implementation for Microsoft Outlook MSG information
- HDGF (Horrible DiaGram Format) – One of many first pure Java implementation for Microsoft Visio binary information.
- HPSF (Horrible Property Set Format) – For studying “Doc Abstract” data from Microsoft Workplace information.
- HSLF (Horrible Slide Structure Format) – a pure Java implementation for Microsoft PowerPoint information.
- HPBF (Horrible PuBlisher Format) – Apache’s pure Java implementation for Microsoft Writer information.
- DDF (Dreadful Drawing Format) – Apache POI package deal for decoding the Microsoft Workplace Drawing format.
It is crucial that you already know full type of these acronyms, in any other case it will be troublesome to maintain observe of which implementation is for which format. If you’re solely involved about studying Excel information then at-least keep in mind XSSF and HSSF lessons e.g. XSSFWorkBook and HSSFWorkBook.
Learn how to Learn Excel File (XLSX) in Java
In our first instance, we’ll find out about studying the present fashionable Excel file format i.e. file with extension .XLSX. This can be a XML spreadsheet format and different spreadsheet software program like OpenOffice and LiberOffice additionally use this format. With a view to learn Excel file, you want to first obtain Apache POI Jar information, with out these your code will neither compiler nor execute.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <model>3.11-beta2</model> </dependency>
By the best way, as Norbert identified, The lessons for OOXML format (equivalent to XSSF for studying .xlsx format) are in a special Jar file. You’ll want to embody the poi-ooxml jar in your challenge, together with the dependencies for it. Once you add poi-ooxml JAR as dependency by way of Maven, it would additionally add different required dependencies by itself. For instance, including beneath XML snippet in pom.xml will obtain 4 JAR information
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <model>3.11-beta2</model> </dependency>
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
If you’re not utilizing Maven then add the next JAR information in your Java program’s classpath
poi-3.11-beta2.jar
commons-codec-1.9.jar
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
Right here is how our pattern Excel 2013 File seems to be like, keep in mind this has saved in .xlsx format.
add right here is code to learn that Excel file. The primary two strains are quite common, they’re to learn file from the file system in Java, actual code begins from third line. Right here we’re passing a binary InputStream to create an occasion of XSSFWorkBook class, which represents an Excel workbook.
The following line provides us a worksheet from the guide, and from there we’re simply going via every row after which every column. Cell represents a block in Excel, also called cell. That is the place we learn or write information.
A cell may be any kind e.g. String, numeric or boolean. Earlier than studying the worth you should confirm the proper kind of cell. After that simply name corresponding worth methodology e.g. getStringValue() or getNumericValue() to learn information from cell.
File myFile = new File("C://temp/Worker.xlsx"); FileInputStream fis = new FileInputStream(myFile); // Finds the workbook occasion for XLSX file XSSFWorkbook myWorkBook = new XSSFWorkbook (fis); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all of the rows in present sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over every row of XLSX file whereas (rowIterator.hasNext()) { Row row = rowIterator.subsequent(); // For every row, iterate via every columns Iterator<Cell> cellIterator = row.cellIterator(); whereas (cellIterator.hasNext()) { Cell cell = cellIterator.subsequent(); change (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "t"); break; default : } } System.out.println(""); }
Let me know when you’ve got bother understanding any of strains. They’re quite simple and self-explanatory however in case you want extra element, simply drop us a remark.
Learn how to write XLSX File in Java
Writing into Excel file can also be just like studying, The workbook and worksheet lessons will stay similar, all you’ll do is to create new rows, columns and cells. As soon as you might be carried out creating new rows in your Excel file in reminiscence, you want to open an output stream to write down that information into your Excel File.
It will save all updates you made in an present file or in a brand new file which is created by Java’s File class.
Here’s a step-by-step code of updating an present Excel file in Java. In first couple of strains we’re creating rows in type of object array and storing them as values in HashMap with key as row quantity.
// Now, let’s write some information into our XLSX file
Map<String, Object[]> information = new HashMap<String, Object[]>(); information.put("7", new Object[] {7d, "Sonya", "75K", "SALES", "Rupert"}); information.put("8", new Object[] {8d, "Kris", "85K", "SALES", "Rupert"}); information.put("9", new Object[] {9d, "Dave", "90K", "SALES", "Rupert"}); // Set to Iterate and add rows into XLS file Set<String> newRows = information.keySet(); // get the final row quantity to append new information int rownum = mySheet.getLastRowNum(); for (String key : newRows) { // Creating a brand new Row in present XLSX sheet Row row = mySheet.createRow(rownum++); Object [] objArr = information.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 Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } } } // open an OutputStream to save lots of written information into XLSX file FileOutputStream os = new FileOutputStream(myFile); myWorkBook.write(os); System.out.println("Writing on XLSX file Completed ..."); Output Writing on XLSX file Completed ...
Right here is how our up to date Excel file takes care of including three extra rows
Learn how to learn Excel (XLS) file in Java
Studying XLS file is not any completely different than studying an XLSX format file, all you want to do is to make use of right workbook implementation for XLS format e.g. as an alternative of utilizing XSSFWorkbook and XSSFSheet , you want to use HSSFWorkbook and HSSFSheet lessons from Apache POI library. As I mentioned earlier than, POI has obtained some actually humorous names for various XLS codecs e.g. Horrible SpreadSheet Format to signify previous Microsoft Excel file format (.xls). Remembering them may be onerous however you’ll be able to all the time discuss with their on-line Javadoc. You may reuse remainder of code given on this instance, for instance you need to use similar code snippet to iterate over rows, columns and from studying/writing into a specific cell. Given they’re two completely different format, some options won’t be obtainable on XLS file processors however all primary stuff stay similar.
Error and Exception
If you happen to occur to make use of incorrect lessons e.g. as an alternative of utilizing XSSFWorkbook to learn XLSX file, in case you use HSSFWorkbook then you will note following error :
Exception in thread "foremost" org.apache.poi.poifs. filesystem.OfficeXmlFileException: The equipped information seems to be within the Workplace 2007+ XML. You are calling the a part of POI that offers with OLE2 Workplace Paperwork. You must name a special a part of POI to course of this information (eg XSSF as an alternative of HSSF) at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:131) at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104) at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:128) at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:361) at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:342) at App.foremost(App.java:25)
Java Program to Learn/Write Excel Information utilizing Apache POI
Right here is our full Java program to learn/write from present Excel information in Java. If you’re utilizing Eclipse IDE, simply create a Java Challenge, copy the code and paste it there. No must create correct package deal construction and Java supply file with the identical title, Eclipse will handle that. If in case you have Maven and Eclipse plugins put in, as an alternative create a Maven Java challenge, this may even allow you to to obtain Apache POI Jar information.
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Date; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.Set; 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; /** * Pattern Java program to learn and write Excel file in Java utilizing Apache POI * */ public class XLSXReaderWriter { public static void foremost(String[] args) { strive { File excel = new File("C://temp/Worker.xlsx"); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook guide = new XSSFWorkbook(fis); XSSFSheet sheet = guide.getSheetAt(0); Iterator<Row> itr = sheet.iterator(); // Iterating over Excel file in Java whereas (itr.hasNext()) { Row row = itr.subsequent(); // Iterating over every column of Excel file Iterator<Cell> cellIterator = row.cellIterator(); whereas (cellIterator.hasNext()) { Cell cell = cellIterator.subsequent(); change (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "t"); break; default: } } System.out.println(""); } // writing information into XLSX file Map<String, Object[]> newData = new HashMap<String, Object[]>(); newData.put("7", new Object[] { 7d, "Sonya", "75K", "SALES", "Rupert" }); newData.put("8", new Object[] { 8d, "Kris", "85K", "SALES", "Rupert" }); newData.put("9", new Object[] { 9d, "Dave", "90K", "SALES", "Rupert" }); Set<String> newRows = newData.keySet(); int rownum = sheet.getLastRowNum(); for (String key : newRows) { Row row = sheet.createRow(rownum++); Object[] objArr = newData.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 Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } } } // open an OutputStream to save lots of written information into Excel file FileOutputStream os = new FileOutputStream(excel); guide.write(os); System.out.println("Writing on Excel file Completed ..."); // Shut workbook, OutputStream and Excel file to forestall leak os.shut(); guide.shut(); fis.shut(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException ie) { ie.printStackTrace(); } } } Output ID NAME SALARY DEPARTMENT MANGER 1.0 John 70K IT Steve 2.0 Graham 80K DATA Carl 3.0 Sodhi 60K IT Ram 4.0 Ram 100K IT Alex 5.0 Carl 150K DATA Alex 7.0 Sonya 75K SALES Rupert 9.0 Dave 90K SALES Rupert 8.0 Kris 85K SALES Rupert Writing on Excel file Completed ...
That is all about learn how to learn and write Excel information in Java. We have now discovered to learn/write each XLS and XLSX format in Java, which is vital to assist previous Microsoft Excel information created utilizing the Microsoft Workplace model previous to 2007. Although there are couple of different various libraries to learn Excel information from Java program, however Apache POI is one of the best one and it’s best to use it at any time when potential. Let me know in case you face any downside whereas operating this program in your Eclipse IDE or from command immediate. Simply be certain that to incorporate proper set of JAR in your CLASSPATH, alternatively used Maven to obtain JAR.
