POI doesnot read the Blank cell

[srinivas ulu <nm...@gmail.com>]

Hi ,
 I wrote the below code but doesnot read the BlankCell(empty cell).Please
give me reply.


 <%@ page import="java.sql.*" %>
 <%@ page import="java.io.*" %>
 <%@ page import="java.util.*" %>

<%@ page language="java" %>
 <%@ page import="java.text.SimpleDateFormat"%>
 <%@ page import="org.apache.poi.ss.usermodel.Workbook"%>
 <%@ page import="org.apache.poi.ss.usermodel.DateUtil"%>
  <%@ page import="org.apache.poi.ss.usermodel.Sheet"%>
  <%@ page import="org.apache.poi.ss.usermodel.Cell"%>
<%@ page import="org.apache.poi.ss.usermodel.Row"%>
<%@ page import="java.util.Iterator"%>
<%@ page import ="org.apache.poi.ss.usermodel.WorkbookFactory"%>
<%@ page
import="org.apache.poi.openxml4j.exceptions.InvalidFormatException"%>
<%@page language="java" session="true" %>

       <%!

       String cellValue="";

       %>
                 <% String file="d:/seenu1/TestEx2.xlsx";
                      FileInputStream fis = new FileInputStream(file);

                  Workbook workbook = WorkbookFactory.create(fis);
                  Sheet sheet = workbook.getSheetAt(0);
                  Iterator rows = sheet.rowIterator();
                  int number=sheet.getLastRowNum();

                  System.out.println(" number of rows"+ number);
                  int j=0;
                  int n=1;

                  while (rows.hasNext())
                   {
                     Row row = ((Row) rows.next());
                     Iterator cells = row.cellIterator();
                     while(cells.hasNext())
                      {
                         Cell cell = (Cell) cells.next();
                         switch (cell.getCellType()) {

                         case Cell.CELL_TYPE_STRING:
                         cellValue = cell.getStringCellValue();
                         System.out.print(cellValue+"\t");
                         break;

                         case Cell.CELL_TYPE_FORMULA:
                         cellValue = cell.getCellFormula();
                         System.out.print(cellValue+"\t");
                         break;

                        case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                                SimpleDateFormat dateFormat = new
SimpleDateFormat(
                                        "dd/MM/yyyy");
                                cellValue =
dateFormat.format(cell.getDateCellValue());
                                System.out.print(cellValue+"\t");
                            } else {
                           System.out.print(cell.getNumericCellValue()+"\t");
                            }
                        break;

                         case Cell.CELL_TYPE_BLANK:
                         cellValue = "hi";
                         System.out.print(cellValue+"\t");
                         break;

                         case Cell.CELL_TYPE_BOOLEAN:
                         cellValue =
Boolean.toString(cell.getBooleanCellValue());
                         System.out.print(cellValue+"\t");

                       }
                    j++;
                      }
                   System.out.println();

                  } n++;
                %>

thanks in advance.........................

Re: POI doesnot read the Blank cell

[Nick Burch <ni...@alfresco.com>]

On Sat, 17 Sep 2011, srinivas ulu wrote:
> I wrote the below code but doesnot read the BlankCell(empty cell).

Does your file contain any blank cells though? That's the most common 
reason for not getting them...

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: POI doesnot read the Blank cell

[Nick Burch <ni...@alfresco.com>]

On Tue, 20 Sep 2011, Alex Panayotopoulos wrote:
> If a cell has never been created in the first place, the cell iterator 
> will just skip over it entirely; and when you specifically ask for it 
> with sheet.getRow(y).getCell(x) you'll get a null value back.

Unless you set a MissingCellPolicy, and then you can control how missing 
vs blank cells are handled. (Only applies to getCell(int) rather than the 
iterator though)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: POI doesnot read the Blank cell

[Alex Panayotopoulos <Al...@vianet.co.uk>]

You'll only see blank cells when they have been created, but they have no contents.
If a cell has never been created in the first place, the cell iterator will just skip over it entirely; and when you specifically ask for it with sheet.getRow(y).getCell(x) you'll get a null value back.

If you need to deal with these cases, try iterating numerically instead:
 
	for (int i = 0; i < row.getLastCellNum(); i++) {
		Cell c = row.getCell(i);
		if (c == null) {
			// ...
		} else {
			switch (c.getCellType()) {
				// ...
			}
		}
	}

Hope that helps!

-- 
Alex Panayotopoulos

<font size="2" face="Verdana" color="grey">If you wish to view Brulines Group email disclaimer please <a href="http://www.brulines.com/investors/emaildisclaimer.aspx">click here</a></font>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org