At times you might be required to read data from an Excel file. We will see how to read XLSX file in Java using Apache POI in this post. It works on similar principles that we saw in. It is just that we have different classes to import and a tad different mannerism to read data here. Apache POI is one of those renowned s that is used for grabbing text from different resources. We are going to make use of its classes to achieve our goal here.
With this example we will show you how to align text in an excel file using Apache POI in Java language. The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).
It has its very own set of functions that can be used to grab data from different resources. An XLSX file is nothing but a Microsoft Excel Open XML Spreadsheet File that has been created by Microsoft Excel 2007 and later versions. Let’s see how to read XLSX file in Java using Apache POI.
Contents. Reading an Excel XLSX File In your existing code you need to first specify the location of your XLSX file. I have saved an XLSX file titled “dc” in the D: drive. I have also put some data in it to be retrieved. Here’s what my dc.xlsx file has: We will read both the rows herein. Even before we begin we will need Apache POI jars so that Eclipse retains all the required classes beforehand. How to Download Apache POI Jars Here are the steps to download Apache POI Jars.
It is better to be prepared beforehand than to go looking for it when time comes. Step 1: Navigate to the following URL: Step 2: Click on the following zip file located in Binary Distribution section: They generally showcase their latest versions here. Step 3: Doing so will take you to the following page. Click on the first link which will start your download. Step 4: Extract the folder and its contents. You will get the following jar files: Step 5: Go to your project in Eclipse and right click on it.
Then put your mouse cursor on Build Path then click on Configure Build Path as shown in the figure below: Step 6: It will open Properties for your project dialog box. Select Libraries tab and then click on Add External Jars Doing so will open a dialog box to select the jar files. Step 7: Select all the jar files that you had obtained from Apache POI’s site and then click on Open. Step 8: The Java Build Path will now begin to show all the jar files that you had selected.
Just click on Apply and then Ok. Steps on How to Read XLSX File in Java Now that we have imported the pre-requisite Apache POI Files it is time to move on to our coding bit. Step 1: Type the following piece of code specifying the location of the path: File file = new File(PATHNAME); Replace the PATHNAME with the actual location of your file. So mine would be something like this: File file = new File('D: dc.xlsx'); Step 2: As soon as you type the above your Eclipse might flare an error on File Class.
Simply import the package import java.io.File; Step 3: We need to instantiate FileInputStream to read from the file specified. Type the following: FileInputStream fis = new FileInputStream(file); It might redden again. Just import the following package to get rid of the errors. Or simply manually type the below where you have written your previous import: import java.io.FileInputStream; Even though that error might no longer be there, you might face another towards the RHS.
On keeping the mouse there you realize the following error message flaring: Just click on Surround with try/catch to handle it. If you wish to see how exceptions are handled you could as well.
Sweet mother of Eclipse! It takes care of everything. XSSFWorkbook Class Step 4: Now time to make use of the XSSFWorkbook class. Just type the following inside the try block immediately after your FileInputStream code: XSSFWorkbook wb = new XSSFWorkbook(fis); Finding the error underline? Just keep your mouse cursor on it.
Step 5: Click on Import XSSFWorkbook to import the following packages: import org.apache.poi.xssf.usermodel.XSSFWorkbook; Step 6: The XSSFWorkbook class on the RHS might be still flaring an error. You need to catch the exception. Just click on it and it will automatically create an extra catch block handling the IOException. As you can see it has automatically put a catch IOException in a separate catch block.
XSSFSheet Class Now we need to address the Sheet and bring the focus to the required area. We can do so by using the XSSFSheet Class. Let’s instantiate it and use the XSSFWorkbook’s getSheetAt(index) method.
Step 6: Type the following code now: XSSFSheet sheet1 = wb.getSheetAt(0); Here writing index as 0 will bring the control towards the first Sheet, i.e. If you find an error red line flaring underneath XSSFSheet, just import the following package: or just type the following in the import area: import org.apache.poi.xssf.usermodel.XSSFSheet; Time to use the big gun (XSSFSheet instance) which we have declared as “sheet1” to actually start retrieving the actual cell data. Popular Methods to Read Data from XLSX File We will make use of the following methods to access data from the XLSX file:. getRow(int rownum). getCell(int cellnum).
getStringCellValue. getNumericCellValue Follow up the aforementioned code by using the sheet1 instance. Step 7: Just type the following to grab the cell value from column A and Row 1: System.out.println(sheet1.getRow(0).getCell(0).getStringCellValue); Here getRow(0) will look into the first row, and getCell(0) will look into the first column, i.e.
To retrieve the String value we are making use of getStringCellValue in the end. So the whole code must look something like this: If you run the above code you will get the following result: URL As you already know that’s the data on A1 in our sheet1 of dc.xlsx. Let’s retrieve the remaining data by including the following code in the existing one: System.out.println(sheet1.getRow(0).getCell(1).getStringCellValue); System.out.println(sheet1.getRow(1).getCell(0).getStringCellValue); System.out.println(sheet1.getRow(1).getCell(1).getNumericCellValue); As you can see we have used getNumericCellValue method for retrieving a numeric value. If you use getStringCellValue there it wouldn’t work.
Running the code will give you now: URL Number 46.0 Don’t like the “.0” in the end? You can remove it by using downcasting in Java as explained in the.
System.out.println((int) sheet1.getRow(1).getCell(1).getNumericCellValue); Now that you will run the code you will get exactly what you were aiming for: URL Number 46 Here’s a glimpse at our final code: I have just removed the extra Exceptions and replaced it with simply Exception to make our code look less ugly. 😛 Not working for you? Try using a different Apache POI version jar file. Alright now that you know how to read xlsx file in Java, we will see how to write in one. We will see that in the next chapter about.
24 January, 2013, 17:25 Hi, there, please help me. I am trying to merge excel files. I read above code. Switch(cell.getCellType) case Cell.CELLTYPEBOOLEAN: System.out.print(cell.getBooleanCellValue + ' t t'); break; case Cell.CELLTYPENUMERIC: System.out.print(cell.getNumericCellValue + ' t t'); break; case Cell.CELLTYPESTRING: System.out.print(cell.getStringCellValue + ' t t'); break; but my cell type is BLANK and I added a new switch case like this: case Cell.CELLTYPEBLANK: System.out.print(cell.getStringCellValue + ' t t'); break; but it doesn’t display me value.
How to get value of cell with the type Blank.?????????? I tryied to use cell.getRichStringValue; but unfortunalely it doesn’t help me. That is urgent. Thanks in advance. 26 February, 2013, 12:06 Hi Viral, I need a code for the following problem. I am working on a java application in which i fetch data from multiple tables in the database.
Apache Poi Download 3.11
The consolidated data is presented in the form of a table in which each row is having an individual checkbox in front. There is a checkbox at the top also. If user clicks on the topmost checkbox, then all rows get selected. Now i want to export the selected rows(which have data from multiple tables) to an existing excel. The excel has a sheet for each table in the database with columns mapped to the fields in table. The data of the corresponding table should get exported to the corresponding sheets.
In addition, each sheet has some conditional formatting and if the data from the databse doesn’t satisfy that condition then that particular cell should turn red. A cell has a dropdown list of 3 values. Suppose the data fetched from the database is some other value then that particular cell should turn red. Please provide me code for that. 28 February, 2013, 5:30 Hi All, I have been trying to edit an existing.xlsx file with XSSFWorkbook, getting stuck with this exception when it’s trying to open it. XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(“existing.xlsx”))); It’s throwing the following exception: java.lang.NumberFormatException: For input string: “A$1” Seems to be that the XSSFWorkbook is complaining about some kind of formatting, or formula from the existing file!? Any pointers would be greatly appreciated!
18 April, 2013, 21:13 Thanks for the nice examples how to use POI. But I suggest not to create a Workbook for an existing file per new. Instead you should use WorkbookFactory.
The important difference is that this approach works with all supported file types – WorkbookFactory creates the appropriate Workbook-Object, either HFFSWorkbook or XSSFWorkbook. Also never use the “HFFS.”-classnames, just use the interface names (without the HFFS-Prefix). // Get the matching workbook instance for any supported spreadsheet format Workbook workbook = WorkbookFactory.create(file); // Get first sheet from the workbook Sheet sheet = workbook.getSheetAt(0); Best regards, Raimund. 9 May, 2013, 17:02 Hi Patel, It might be helpful if you can explain how to setup this library to some beginners:) let me help you here. You need to follow following steps to get started with above examples 1.
You need to download poi-2.5.1.jar(or in this case 3.8) jar file 2. You need to include this file in your projects class path 3. Create new java project in eclipse with auto generated main function 4. Just copy and paste above code or and press ctrl+Shift+o and your ready to go Hope this helps:) Amol Patil findnetinfo.com. 7 October, 2013, 19:54 Hi, Very good information.But i would like to know how to work with multiple sheets in a Workbook. For this again we have to write the total code.I am unable to work with multiple sheets.I am able to read the data by copying the same code and changing the sheet number.But it is showing duplicate code(More lines of code).
Can you please suggest me how to do? //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator rowIterator = sheet.iterator; //Get iterator to all cells of current row Iterator cellIterator = row.cellIterator.
4 January, 2014, 1:41 This info is excelent, From this article, I have the complete code that writes a new excel with dummy data. What I need to do in order to work with the same stuff within a JSP web Page? I need to have a link somewhere which points to the JSP that generates the excel file, so the excel file will be generated on the fly, what I need to do?
I need a popUp message saying if you want to save or open the excel file, I do not need and I do not want to have a local path like C: Could you please help me? Thanks in advance. 22 February, 2014, 21:57 Hi Viral, Thank you very much for the very well explanation on Apache POI. Got to learn so many basic things on excel are. I am executing the code what you have provide above(Reading excel), but getting below error.
Tried a lot to fix the issue, but couldn’t find it my self. Can you please help me in fixing this issue.
“The type HTMLDocument.Iterator is not generic; it cannot be parameterized with arguments ” “The type HTMLDocument.Iterator is not generic; it cannot be parameterized with arguments ” The error observed in the below two lines: Iterator rowIterator = sheet.iterator; Iterator cellIterator = row.cellIterator. 11 July, 2014, 10:18 Hi Virat, While executing my excel reading code i am getting error as ‘ java.lang.NoSuchMethodError: org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(Ljava/io/InputStream;)Z’. But in my jar ‘poi-ooxml-3.9-20121203.jar’ above ‘org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(‘ is available. Not able to get the solution for this. It worked before. What the change i have done is once i removed jars from lib and placed again thats it.
Please give me the solution for this issue. 15 December, 2014, 14:27 Problem statement: I want to fetch data from two different websites, and write this data into workbook.
Sheet 1 and sheet 2 am looking for solution to perform excel comparison, looking for ur expert guidance. Sheet 1 is as followed Ticker— Out standing Shares– Value Of Holdings–Increased Positions–Decreased–Positions—New Positions—-Sold Out Positions XMN 608,911,000—–3,741—–362 —-312—-76—–70 DWD 608,911,000—–3,741—–362——312—-76—–70 Sheet 2 is as followed Ticker— Out standing Shares– Value Of Holdings–Increased Positions–Decreased–Positions—New Positions—-Sold Out Positions XMN 608,911,000—–3,741—–362 —-312—-76—–70 DWD 608,911,000—–3,741—–362——312—-76—–70 Thanks & regards.