Apache POI seeing columns in empty spreadsheet?

apache poi autosizecolumn
apache poi example
apache poi merge cells
apache poi cell style
apache poi row
how to read empty cell in excel using java
apache poi set column width example
apache poi documentation

I have an empty spreadsheet, but when I'm accessing it with Apache POI (version 3.10), it says it has 1024 columns and 20 physical columns. I really deleted all the cells, only some formatting remains, but no content. And if I delete some columns with LibreOffice Calc (version 4.1.3.2), the number of columns only increases! What's going on? Is there a reliable way to get the real number of columns (or cells in a row)?

import java.net.URL;
import org.apache.poi.ss.usermodel.*;

public class Test {
    public static void main(final String... args) throws Exception {
        final URL url = new URL("http://aditsu.net/empty.xlsx");
        final Workbook w = WorkbookFactory.create(url.openStream());
        final Row r = w.getSheetAt(0).getRow(0);
        System.out.println(r.getLastCellNum());
        System.out.println(r.getPhysicalNumberOfCells());
    }
}

After some more investigation, I think I figured out what's happening.

First, some terminology from POI: there are some cells that don't actually exist at all in the spreadsheet - those are called missing, or undefined/not defined. Then there are some cells that are defined, but have no value - those are called blank cells. Both types of cells appear empty in a spreadsheet program and can't be distinguished visually.

My spreadsheet has some blank cells that LibreOffice added at the end of the row (possibly a bug). When I delete columns, LibreOffice seems to shift the subsequent cells (including the blank ones) to the left, and adds more blank cells at the end (up to 1024).

And now the key part: neither getLastCellNum() nor getPhysicalNumberOfCells() ignore blank cells. getLastCellNum() gives the last defined cell, and getPhysicalNumberOfCells() gives the number of defined cells, both including blank cells. There doesn't seem to be any method available that skips blank cells. The javadoc for getPhysicalNumberOfCells() is somewhat misleading - "if only columns 0,4,5 have values then there would be 3", but it's actually counting blank cells too, which don't really have values.

So the only solution I found is to loop through the cells and check if they are blank.

Side note: getLastRowNum() and getFirstCellNum() are 0-based but getLastCellNum() is 1-based, wtf?

Deal with empty or blank cell in excel file using apache poi, Create an excel file info.xlsx with below information. Obviously you see there are many empty or blank cells under few columns like Mobile,  This chapter explains how to create a spreadsheet and manipulate it using Java. Spreadsheet is a page in an Excel file; it contains rows and columns with specific names. After completing this chapter, you will be able to create a spreadsheet and perform read operations on it. Spreadsheets have a


Most likely you have some kind of formatting applied for you row. I have an empty xlsx file created with excel and method getRow produces null for empty rows.

Busy Developers' Guide to HSSF and XSSF Features, private static void createCell(Workbook wb, Row row, int column, Use a MissingCellPolicy to control how blank or null cells are handled. using absolute references like '$A$1:$B$1' avoids this, see also this discussion. Here's my approach for reading the contents of an Excel spreadsheet into a Two-Dimensional Array using the Apache POI. First, I specified the location of my test spreadsheet and then I used the FileInputStream, HSSFWorkbook and HSSFSheet classes to access the correct sheet in my test spreadsheet.


@aditsu as per https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Row.html, getLastCellNum() gets the index of the last cell contained in this row PLUS ONE.

+1 for libreOffice strugle! it's a bug, and in my opinion is very random. I'm getting null randomly, and often helps if I delete EMPTY rows (bellow) and EMPTY columns (on the right side). ...

XSSFSheet (POI API Documentation) - Apache POI, public class XSSFSheet extends POIXMLDocumentPart implements Sheet void, autoSizeColumn(int column, boolean useMergedCells) were set to empty later might still be counted as rows by Excel and Apache POI, so the result of representing a row in the sheet; See Also: removeRow(org.apache.poi.ss.​usermodel. Apache POI Excel â Spreadsheets - This chapter explains how to create a spreadsheet and manipulate it using Java. Spreadsheet is a page in an Excel file; it contains rows and columns with specif


XSSFRow (POI API Documentation) - Apache POI, High level representation of a row of a spreadsheet. Copy the cells from srcRow to this row If this row is not a blank row, this will merge the two rows, 0 based column number; Returns: the cell at the given (0 based) index; See Also: Row. Get the number of the first cell contained in this row. Note: cells which had content before and were set to empty later might still be counted as cells by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be lower than expected!


Sheet (POI API Documentation) - Apache POI, Get the default column width for the sheet (if the columns do not define their own to empty later might still be counted as rows by Excel and Apache POI, so the level Row object representing a row in the sheet; See Also: removeRow(Row)  Named Cell is a degenerate case of Named Range in that the 'group of cells' contains exactly one cell. You can create as well as refer to cells in a workbook by their named range. When working with Named Ranges, the classes org.apache.poi.ss.util.CellReference and org.apache.poi.ss.util.AreaReference are used.


Groovy Extract Data Spreadsheet Poi Columns Rows, Apache poi toolkit for a groovy spreadsheet columns and click the editing text or share a method. Asset returns row data with groovy extract data spreadsheet poi rows, see if it? Needed at a blank columns rows in your build it in oracle. Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program.