Apache POI

A category to collect articles around Apache POI.

If you have to determine the last row in a Microsoft Excel file you will face some trouble.

For whatever reason there is no reliable method available in Excel VBA to retrieve the last row with data. Since Microsoft developers seem to have decided to treat formatting and data the same way when it comes to determining the so called UsedRange in a worksheet, we are officially screwed.

Here is what POI developers are stating in their API docs regarding this problem :0).

"Gets the number last row on the sheet. Owing to idiosyncrasies in the excel file format, if the result of calling this method is zero, you can't tell if that means there are zero rows on the sheet, or one at position zero. For that case, additionally call getPhysicalNumberOfRows() to tell if there is a row at position zero or not." 

Found at http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#getLastRowNum()

I fully agree: Excel's core implementation and resulting behavior is nuts many times.

Hits: 27914

It is fairly easy to utilize the Apache POI API in Eclipse.

Download the most current version of POI from Apache and include the jars from the following directories in the Java Build Path in an Eclipse project's properties. "./" is the root directory of the downloaded API. 

  • ./lib
  • ./ooxml-lib
  • ./

After you have included all external libraries in the Java Build Path of your project, you can start using the API.

Hits: 9934

Hi there.

Recently I was sitting in a bad place again. I had to automate one process step in a workflow, which was entirely based on processing files of different type, layout and size.

I love automating things by developing software, but I hate doing so whenever it is based entirely on file systems.

To make matters worse, I had to process Excel files ... again! Being a very experienced VBA programmer, in which I do take shame, not pride, I knew it will give me trouble.

Since I do not want to code in VBA anymore, I decided to use Java and the Apache POI API.

Started to use the API and was happy. See the following articles for reference.

How to setup an eclipse project to use POI.

Identifying the last row in Excel files can be hard. Depends on what you define as last row ;0).

A high level wrapper class around POI for processing Excel files.

And here the problem ...

Hits: 21076
  1. Reading and writing MS Excel files using POI