Home arrow static arrow Java Programming [Archive] - POI : Read in several workbook worksheets, and write to one file
Warning: Creating default object from empty value in /www/htdocs/w008deb8/wiki/components/com_staticxt/staticxt.php on line 51
Java Programming [Archive] - POI : Read in several workbook worksheets, and write to one file
This topic has 5 replies on 1 page.

Posts:6
Registered: 4/30/98
POI : Read in several workbook worksheets, and write to one file  
Jun 30, 2004 3:09 PM



 
Hi,

I'm struggling with the POI api ... I'd like to read in worksheet 1 from file A, and then worksheet 1 from file B, and then write their data to a single worksheet in file C.

Any ideas?

Thanks much,

Gene
gkasrel@yahoo.com
 

Posts:111
Registered: 12/21/97
Re: POI : Read in several workbook worksheets, and write to one file  
Jul 1, 2004 12:56 AM (reply 1 of 5)



 
Didn't test the code, but it should be something like the following:
public void merge(final String[] srcFiles, final String destFile) throws Exception {    HSSFWorkbook destWb = new HSSFWorkbook();    HSSFSheet destSheet = destWb.createSheet("Sheet1");    int rowIndexModifier = 0;        for (int i = 0; i < srcFiles.length; i++) {        InputStream in = new FileInputStream(srcFile[i]);        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(in));        HSSFSheet ws = wb.getSheet(0);                for (Iterator j = ws.rowIterator(); j.hasNext(); ) {            HSSFRow srcRow = (HSSFRow) j.next();            HSSFRow destRow = destSheet.createRow(srcRow.getRowNum() + rowIndexModifier);                        for (Iterator k = srcRow.cellIterator(); k.hasNext(); ) {                HSSFCell srcCell = (HSSFCell) k.next();                HSSFCell destCell = destRow.createCell(srcCell.getCellNum());                destCell.setCellStyle(srcCell.getCellStyle());                destCell.setEncoding(srcCell.getEncoding());                                switch (srcCell.getCellType()) {                    case HSSFCell.CELL_TYPE_NUMERIC:                        destCell.setCellValue(srcCell.getNumericCellValue());                        break;                    case HSSFCell.CELL_TYPE_STRING:                        destCell.setCellValue(srcCell.getStringCellValue());                        break;                    case HSSFCell.CELL_TYPE_BLANK:                        break;                    case HSSFCell.CELL_TYPE_BOOLEAN:                        destCell.setCellValue(srcCell.getBooleanCellValue());                        break;                    case HSSFCell.CELL_TYPE_ERROR:                        destCell.setCellValue(srcCell.getErrorCellValue());                        break;                }            }        }                rowIndexModifier += ws.getLastRowNum() + 1;        in.close();    }        FileOutputStream out = new FileOutputStream(destFile);    destWb.write(out);    out.close();}
 

Posts:111
Registered: 12/21/97
Re: POI : Read in several workbook worksheets, and write to one file  
Jul 1, 2004 12:58 AM (reply 2 of 5)



 
Sorry, the switch statement have to include one more case:
case HSSFCell.CELL_TYPE_FORMULA:    destCell.setCellFormula(srcCell.getCellFormula());    break;
 

Posts:111
Registered: 12/21/97
Re: POI : Read in several workbook worksheets, and write to one file  
Jul 1, 2004 1:17 AM (reply 3 of 5)



 
You could also replace the switch block with the following:
if (srcCell.getCellType == HSSFCell.CELL_TYPE_FORMULA) {    destCell.setCellFormula(srcCell.getCellFormula());}else {    destCell.setCellValue(srcCell.getStringCellValue());    destCell.setCellType(srcCell.getCellType());}
 

Posts:18
Registered: 7/1/04
Re: POI : Read in several workbook worksheets, and write to one file  
Jul 1, 2004 2:47 AM (reply 4 of 5)



 
hey u didnt mention in detail worksheet in the sense is it excel....

if it is so u hvae to do it manually r else if it is a white page then u can save it a text file r something and u can access thru C using files...right
 

Posts:6
Registered: 4/30/98
Re: POI : Read in several workbook worksheets, and write to one file  
Jul 20, 2004 5:24 PM (reply 5 of 5)



 
Thank you very much for the thorough response.

Unfortunately, the copying of the cell style does not work, because apparently you need to create all cell styles inside the destination sheet first.

Even more unfortunate, I can't seem to figure this step out either. I've resorted to creating a new cell style in the destSheet, and then copying all the src styles (one by one) into the destStyle, and then setting the destCell with the destStyle ... and this didn't work?!

Here's the basic code:

protected void cellWrite ( HSSFSheet destSheet, int srcRow, HSSFCell srcCell, Object cellValue ) {
HSSFRow destRow = ...
HSSFCell destCell = destRow.createCell(srcCell.getCellNum());
HSSFCellStyle srcStyle = srcCell.getCellStyle();

HSSFCellStyle destStyle = destWb.createCellStyle();

copyCellStyle ( srcStyle, destStyle);

destCell.setCellValue(cellValue.toString());
destCell.setCellStyle(destStyle);
}

private void copyCellStyle (HSSFCellStyle srcStyle, HSSFCellStyle destStyle) {

HSSFFont fontRec = wb.getFontAt(srcStyle.getFontIndex());

destStyle.setFont(fontRec);

destStyle.setAlignment(srcStyle.getAlignment());
destStyle.setBorderBottom(srcStyle.getBorderBottom());
// etc ... with the other styles: destStyle.setXYZ(srcStyle.getXYZ());
}

// wb is the source HSSFWorkbook
// destWb is the destination HSSFWorkbook

Thanks.

Back to the research & experimentation :),

Gene
gkasrel@yahoo.com
 
This topic has 5 replies on 1 page.