You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Modha Khammammettu <MK...@calcas.com> on 2007/07/23 06:21:21 UTC

Generated Excel - column count

Hi All
 
I am using POI 3.0 rc4
 
I get wrong column count when I read excel file I created.
 
I use the following method to get Column count:
 
public int getNumberOfColumns(int sheetNumber){
 HSSFRow row = getRow(sheetNumber,2);
 return row.getLastCellNum();
}
 
I always get 1 column less than what I have. If I have 6 columns I get only 5 as return value.
However, when I open the excel file and make a smal change (any change like adjusting column width etc) and save it, the same getNumberOfColumns method returns correct columns.
Is there any solution for this problem. Please let me know.

I create excel file programatically
 
ExcelFile excel = new ExcelFile();
excel.createWorkBook();

I add rows like this:
 
public void addRow(int sheetNumber, String content){
  HSSFSheet sheet = (HSSFSheet)wb.getSheetAt(sheetNumber);
  HSSFRow row = sheet.createRow(currentRowNumberBeingAdded);
  currentRowNumberBeingAdded = currentRowNumberBeingAdded + 1;
  
  StringTokenizer st = new StringTokenizer(content,"~");
  int cellNumber = 0;
  while (st.hasMoreTokens()){
       HSSFCell cell = row.createCell((short)cellNumber);
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       cell.setCellValue(st.nextToken());
       cellNumber = cellNumber + 1;
  }
  
 }

I save like this:
excel.saveWorkBook(destn);
 
I import these:
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
 
Thanks

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

RE: Generated Excel - column count

Posted by Anthony Andrews <py...@yahoo.com>.
an I ask what changes you are making to the file? Excel is quite picky and will 'see' that change has been made to a cell even if - to us - it seems completely empty. That may be the reason why you are seeing this problem.

Modha Khammammettu <MK...@calcas.com> wrote: In my test I have 6 columns.
After I programmatically create the file and read for no. of columns using getLastCellNum() I get 5
After I open and make a change and then read for the no of columns using getLastCellNum() I get 6
 
Note: This happens only for programmatically generated Excel files. Not the one that get created manually.

________________________________

From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Sun 7/22/2007 11:39 PM
To: POI Users List
Subject: Re: Generated Excel - column count



Just as with an array structure in Java, POI numbers columns starting from zero and not one - or A - as Excel does. Therefore, if you have six columns on the sheet these will be numbered 0 to 5 and that is why - at least I think that is why - the getNumberOfColumns() method seems to return an incorrect figure.

PS It helps if you are simply iterating through the columns using a loop to have this happen IMO.

Modha Khammammettu  wrote: Hi All

I am using POI 3.0 rc4

I get wrong column count when I read excel file I created.

I use the following method to get Column count:

public int getNumberOfColumns(int sheetNumber){
 HSSFRow row = getRow(sheetNumber,2);
 return row.getLastCellNum();
}

I always get 1 column less than what I have. If I have 6 columns I get only 5 as return value.
However, when I open the excel file and make a smal change (any change like adjusting column width etc) and save it, the same getNumberOfColumns method returns correct columns.
Is there any solution for this problem. Please let me know.

I create excel file programatically

ExcelFile excel = new ExcelFile();
excel.createWorkBook();

I add rows like this:

public void addRow(int sheetNumber, String content){
  HSSFSheet sheet = (HSSFSheet)wb.getSheetAt(sheetNumber);
  HSSFRow row = sheet.createRow(currentRowNumberBeingAdded);
  currentRowNumberBeingAdded = currentRowNumberBeingAdded + 1;
 
  StringTokenizer st = new StringTokenizer(content,"~");
  int cellNumber = 0;
  while (st.hasMoreTokens()){
       HSSFCell cell = row.createCell((short)cellNumber);
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       cell.setCellValue(st.nextToken());
       cellNumber = cellNumber + 1;
  }
 
 }

I save like this:
excel.saveWorkBook(destn);

I import these:

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


Thanks

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



      
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out. 


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



       
---------------------------------
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.

RE: Generated Excel - column count

Posted by Modha Khammammettu <MK...@calcas.com>.
In my test I have 6 columns.
After I programmatically create the file and read for no. of columns using getLastCellNum() I get 5
After I open and make a change and then read for the no of columns using getLastCellNum() I get 6
 
Note: This happens only for programmatically generated Excel files. Not the one that get created manually.

________________________________

From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Sun 7/22/2007 11:39 PM
To: POI Users List
Subject: Re: Generated Excel - column count



Just as with an array structure in Java, POI numbers columns starting from zero and not one - or A - as Excel does. Therefore, if you have six columns on the sheet these will be numbered 0 to 5 and that is why - at least I think that is why - the getNumberOfColumns() method seems to return an incorrect figure.

PS It helps if you are simply iterating through the columns using a loop to have this happen IMO.

Modha Khammammettu <MK...@calcas.com> wrote: Hi All

I am using POI 3.0 rc4

I get wrong column count when I read excel file I created.

I use the following method to get Column count:

public int getNumberOfColumns(int sheetNumber){
 HSSFRow row = getRow(sheetNumber,2);
 return row.getLastCellNum();
}

I always get 1 column less than what I have. If I have 6 columns I get only 5 as return value.
However, when I open the excel file and make a smal change (any change like adjusting column width etc) and save it, the same getNumberOfColumns method returns correct columns.
Is there any solution for this problem. Please let me know.

I create excel file programatically

ExcelFile excel = new ExcelFile();
excel.createWorkBook();

I add rows like this:

public void addRow(int sheetNumber, String content){
  HSSFSheet sheet = (HSSFSheet)wb.getSheetAt(sheetNumber);
  HSSFRow row = sheet.createRow(currentRowNumberBeingAdded);
  currentRowNumberBeingAdded = currentRowNumberBeingAdded + 1;
 
  StringTokenizer st = new StringTokenizer(content,"~");
  int cellNumber = 0;
  while (st.hasMoreTokens()){
       HSSFCell cell = row.createCell((short)cellNumber);
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       cell.setCellValue(st.nextToken());
       cellNumber = cellNumber + 1;
  }
 
 }

I save like this:
excel.saveWorkBook(destn);

I import these:

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


Thanks

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



      
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out. 


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

Re: Generated Excel - column count

Posted by Anthony Andrews <py...@yahoo.com>.
Just as with an array structure in Java, POI numbers columns starting from zero and not one - or A - as Excel does. Therefore, if you have six columns on the sheet these will be numbered 0 to 5 and that is why - at least I think that is why - the getNumberOfColumns() method seems to return an incorrect figure.

PS It helps if you are simply iterating through the columns using a loop to have this happen IMO.

Modha Khammammettu <MK...@calcas.com> wrote: Hi All
 
I am using POI 3.0 rc4
 
I get wrong column count when I read excel file I created.
 
I use the following method to get Column count:
 
public int getNumberOfColumns(int sheetNumber){
 HSSFRow row = getRow(sheetNumber,2);
 return row.getLastCellNum();
}
 
I always get 1 column less than what I have. If I have 6 columns I get only 5 as return value.
However, when I open the excel file and make a smal change (any change like adjusting column width etc) and save it, the same getNumberOfColumns method returns correct columns.
Is there any solution for this problem. Please let me know.

I create excel file programatically
 
ExcelFile excel = new ExcelFile();
excel.createWorkBook();

I add rows like this:
 
public void addRow(int sheetNumber, String content){
  HSSFSheet sheet = (HSSFSheet)wb.getSheetAt(sheetNumber);
  HSSFRow row = sheet.createRow(currentRowNumberBeingAdded);
  currentRowNumberBeingAdded = currentRowNumberBeingAdded + 1;
  
  StringTokenizer st = new StringTokenizer(content,"~");
  int cellNumber = 0;
  while (st.hasMoreTokens()){
       HSSFCell cell = row.createCell((short)cellNumber);
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       cell.setCellValue(st.nextToken());
       cellNumber = cellNumber + 1;
  }
  
 }

I save like this:
excel.saveWorkBook(destn);
 
I import these:
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
 
Thanks

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



       
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.