Danny,
I will include as much as possible while being as bandwidth friendly as
possible.
Below is the code to my merge() methods:
/**
* Merge the source to the workbook identified by the given path & file
name. The output begins on the worksheet
* named in the sheetName parameter.
*/
public boolean merge(String xlFile, String sheetName)
{
try
{
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(xlFile));
wkbk = new HSSFWorkbook(fs);
if(null!=sheetName)
sheet = wkbk.getSheet(sheetName);
return convert();
}
catch(Exception e) {log(e); return false;}
}
/**
* Merge the source to the workbook identified by the given path & file
name. The output begins on the worksheet
* numbered in the sheetNum parameter. A sheetNum value less than 0
forces a new sheet to be created.
*/
public boolean merge(String xlFile, int sheetNum)
{
try
{
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(xlFile));
wkbk = new HSSFWorkbook(fs);
//Sheets have zero based indexes...
int numSheets = wkbk.getNumberOfSheets() - 1;
if(numSheets < 0 || sheetNum < 0)
sheet = null; //Force sheet to null so convert can create
a new sheet
else if(sheetNum >= 0 && numSheets >= sheetNum)
sheet = wkbk.getSheetAt(sheetNum);
return convert();
}
catch(Exception e) {log(e); return false;}
}
These setup the shared workbook and worksheet objects for the rest of the
class.
Below is my convert() method excluding the error handling catch blocks:
/**
* This method does the actual work of converting the source to an Excel
spreadsheet. It 1st checks to ensure
* it has parameters set from the setParameters() method and makes sure
it has data records to use for the
* conversion. It uses a JDBC Resultset object to read the data into the
resulting spreadsheet. It also uses
* HSSF objects from the POI API to create an Excel spreadsheet as
output. POI is an open-source API library
* that was created as an Apache-Jakarta subproject.
*/
public boolean convert()
{
try
{
if(null==fromCoords || null==toCoords) throw new
RuntimeException("Spreadsheet coordinates not supplied.");
if( null==data && ! getData() ) throw new RuntimeException(
"No data retrieved for conversion.");
if(null==data) throw new RuntimeException("No data retrieved
for conversion.");
if(null==wkbk) wkbk = new HSSFWorkbook();
if(null==sheet) sheet = wkbk.createSheet();
//Set the starting row...
short start = fromCoords[1].shortValue();
int rowcount = sheet.getPhysicalNumberOfRows();
//Create the rows that aren't there before the starting
row...
for(; (short)rowcount < start; rowcount++)
row = sheet.createRow((short)rowcount);
if(rowcount < start + 1)
row = sheet.createRow(start);
else
row = sheet.getRow((int)start);
short stop =toCoords[1].shortValue();
//Conditionally write column headers...
if(columnHeaderStyle != CH_STYLE_NO_COLUMN_HEADERS)
writeRow(COLUMNHEADERS);
row = nextRow();
while(data.next())
{
if(stop >= 0 && row.getRowNum()+1 > stop) break;
if (! writeRow(TYPEDCOLUMNVALUES) ) return false;
row = nextRow();
}//End of while(data.next())
//Output...
FileOutputStream fs = new FileOutputStream(output);
wkbk.write( fs );
fs.close();
return true;
}//End of try
Below is the nextRow() method used to iterate over the existing rows and/or
create new ones as necessary:
private HSSFRow nextRow()
{
//If number of available rows (indexed at 0) less than the next
available row
if(sheet.getPhysicalNumberOfRows() - 1 < row.getRowNum() + 1)
return sheet.createRow( (short)(row.getRowNum() + 1) );
else
return sheet.getRow(row.getRowNum() + 1);
}
Below is the writeRow() methods that perform the actual work of the class:
private boolean writeRow() {return writeRow(COLUMNVALUES);}
private boolean writeRow(int columnOutput)
{
try
{
//start/stop are used to control where the output starts &
stops in the spreadsheet.
short start = fromCoords[0].shortValue();
short stop = toCoords[0].shortValue();
HSSFCell cell;
//Create the cells that aren't there before the starting
cell...
for(int cellcount = row.getPhysicalNumberOfCells(); (short
)cellcount < start; cellcount++)
row.createCell((short)cellcount);
int dataColumns= data.getMetaData().getColumnCount();
cell = row.createCell(start);
for(int i = 1; i <= dataColumns; i++)
{
if(! formatCell(cell, row.getRowNum(), columnOutput) )
return false;
switch(columnOutput)
{
case COLUMNHEADERS :
//String columnLabel = data.getMetaData
().getColumnName(i);
String columnLabel = data.getMetaData
().getColumnLabel(i);
//Auto size columns to display size of column in
result set...
short colSize = (short)(data.getMetaData
().getColumnDisplaySize(i));
colSize = colSize * 256 > (short
)(columnLabel.length()*chWidthAdj) ? (short)(colSize*256) : (short
)(columnLabel.length()*chWidthAdj);
sheet.setColumnWidth((short) (i-1),colSize);
cell.setCellValue(columnLabel);
break;
case COLUMNVALUES :
cell.setCellValue(data.getString(i));
break;
case TYPEDCOLUMNVALUES :
switch(data.getMetaData().getColumnType(i))
{
case Types.CHAR :
case Types.VARCHAR :
cell.setCellValue(data.getString(i));
break;
case Types.DATE :
cell.setCellValue(data.getDate(i));
break;
case Types.DOUBLE :
cell.setCellValue(data.getDouble(i));
break;
default :
cell.setCellValue(data.getString(i));
break;
}
break;
case EMPTYVALUES :
cell.setCellValue(" ");
break;
}//End of switch(columnOutput)
if(stop >= 0 && (i + start) > stop) break;
cell = row.createCell( (short)(i + start) );
}//Enf of for(int i = 1; i <= dataColumns; i++)
}//End of try
I apologize if this comes across in a horribly formatted way. There's only
so much you can do with an email. My email client (Notes V5) is actually
maintaining the formatting from my editor (Eclipse) as I paste the
different sections. The issue is how to handle the case where the user
wants to merge data to a new empty sheet in an existing workbook. If Excel
doesn't actually create sheets until data is written to them then how do I
programmatically detect this when POI reports these phantom sheets in the
getNumberOfSheets() method? Thank you for all of your help.
-Cliff
Danny Mui
<[EMAIL PROTECTED] To: POI Users List <[EMAIL
PROTECTED]>
m> cc:
Subject: Re: Cannot write to extra
sheets
06/16/2003 11:07
AM
Please respond to
"POI Users List"
can you provide a snippet of the code so we can help you diagnose your
problem?
[EMAIL PROTECTED] wrote:
>But POI says the sheets already exist. Workbook.getNumberOfSheets()
>includes them in the count. I apologize but I don't think we understand
one
>another. I'm trying to figure out why the data is not retained on new
>sheets that have not been touched. Once I touch the sheet then the data
>seems to "stick". If the sheet is brand new and untouched then the value
of
>each cell after it is set disappears or is not retained once the workbook
>is serialized. If I so much as key a single space character on the sheet
>then the data "sticks". Is there some sort of hidden attribute that is set
>once a worksheet is touched? Are you saying that Excel doesn't create the
>sheets until data is written to them? If so then that would explain my
>problem. If this is the case, then how do I test for this condition? In
>other words, how would I program for when Workbook.getNumberOfSheets()
>returns 3 and only one sheet has data on it? My code needs to know under
>this circumstance to create sheets instead of retrieving these sheets that
>Excel "only creates the space for". Help!
>
>Thank you for your assistance,
>Cliff
>
>
>
> "Steve"
> <[EMAIL PROTECTED] To: "POI Users List"
<[EMAIL PROTECTED]>
> M> cc:
> Subject: Re: Cannot write
to extra sheets
> 06/16/2003 10:34
> AM
> Please respond to
> "POI Users List"
>
>
>
>
>
>
>it doesn't create extra sheets. only the space for them.
>you need to create all sheets that you want,
>as far as I know.
>
>----- Original Message -----
>From: <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Monday, June 16, 2003 8:59 AM
>Subject: Cannot write to extra sheets
>
>
>
>
>>Hello all,
>>
>>I am having a problem writing to the extra sheets that Excel creates by
>>default when you create a new workbook. I'm using the
>>jakarta-poi1.8.0-dev-20020919.jar version. I've developed a conversion
>>program that allows data to be merged with existing spreadsheets. The
>>
>>
>merge
>
>
>>works when I attempt to write on a sheet that has already been written
>>
>>
>on.
>
>
>>However, whenever I attempt to write to one of the blank sheets (sheets 2
>>and 3) that Excel creates automatically with an empty workbook it doesn't
>>work. I can adjust the width of cells in these sheets but they do not
>>retain any info from the cell.setCellValue() method calls. I verified in
>>debug that these methods are not failing. My debugger (Eclipse) allows me
>>to see the cell values before and after the method calls. I've attempted
>>many tests where I enter the exact same parameters to merge data to an
>>existing workbook changing only the sheet number. The data is retained
>>
>>
>only
>
>
>>on the 1st sheet where I've already added data. This is the only variance
>>in my test runs. In all cases I obtain references to the sheet, row and
>>cell objects the same way. I do a Workbook.getSheetAt(int num) to get the
>>existing sheet. (I've tried with Workbook.getSheet(String name) as well.)
>>
>>
>I
>
>
>>am using Worksheet.createRow(short row) to create the row and
>>Row.createCell(short cell) to create cell references. In my code I verify
>>that the rows and cells do not exist prior to creating. In the latter
>>
>>
>case
>
>
>>I use the corresponding methods to obtain references to the existing
>>objects. What am I missing?
>>
>>-Cliff
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
