You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Go...@AmericanSystems.com on 2007/08/04 21:42:12 UTC

[[nosig]] How can I copy rows or cells and preserve formatting

I am using POI to generate reports from a Java application. For various
reasons, I would like to be able to tweak the format of the report using
excel rather than POI. My approach to this is to create a report
template using excel. My application would then open the template and,
for each row in the report, simply copy a row from the template onto the
report, and then fill in the values. However, I have not found a way to
copy a row or cell via POI in such a way that preserves formatting. Is
it possible to do this with POI? The copying of rows or cells would be
between worksheets but within the same workbook.

 

Thanks in advance,

Gordon

 


Re: [[nosig]] How can I copy rows or cells and preserve formatting

Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 3 Sep 2007, ArtemGr wrote:
> Perhaps that one, from Andrej Golovnin:
> http://thread.gmane.org/gmane.comp.jakarta.poi.user/2355/focus=2357
>
> It would be great to have such method directly in the library.

If someone fancies writing a unit test to go with that method, I'd be 
happy to commit it to svn. Just open a bug with the unit test + the patch

Nick

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


Re: [[nosig]] How can I copy rows or cells and preserve formatting

Posted by ArtemGr <ar...@bizlink.ru>.
Anthony Andrews <pythonaddict <at> yahoo.com> writes:

> 
> I think - and it is think so forgive me if I am mistaken - that someone has
written a utility that will
> accomplish this sort of task. Have you searched through the archive of
messages posted to the group yet?
> You may be able to find it.

Perhaps that one, from Andrej Golovnin:
http://thread.gmane.org/gmane.comp.jakarta.poi.user/2355/focus=2357

It would be great to have such method directly in the library.


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


RE: How can I copy rows or cells and preserve formatting

Posted by Lenni <le...@cs.tamk.fi>.
I'm having similar problem, and despite checking the links you've provided I
haven't been able to copy cell styles from one workbook to another, but only
the content of the cell. I understood that this should copy the style, but
apparently it doesn't work:

cellNew.setCellStyle(cellOld.getCellStyle())   (in my copyCell() method)

Any advice would be appreciated.

        private void copyFile (){
               
                wbNew.createSheet(wbOld.getSheetName(0));
                sheetOld = wbOld.getSheetAt(0);
                sheetNew = wbNew.getSheetAt(0);
                int rows = sheetOld.getLastRowNum();
               
                for (int r = 0; r < rows; r++) {
                        rowOld = sheetOld.getRow (r);
                       
                        rowNew = sheetNew.createRow(r);
                        rowNew.setHeight( rowOld.getHeight() );
                       
                        if ( rowOld != null ) {
                                int cells = rowOld.getLastCellNum();
                                for (short c = 0; c < cells; c++){
                                        cellOld = rowOld.getCell(c);

                                        if ( cellOld != null ) {
                                                cellNew =
rowNew.createCell(c);
                                       
                                                copyCell(cellOld, cellNew);
                                               
cellNew.setCellType(cellOld.getCellType());

                                                String content = checkType
(cellOld);
                                               
cellNew.setCellValue(content);
                                        }
                                }
                        }
                }
        }
       
        private void copyCell (HSSFCell cellOld, HSSFCell cellNew){
                cellNew.setCellStyle(cellOld.getCellStyle());
                cellNew.setEncoding(cellOld.getEncoding());
                switch (cellOld.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                               
cellNew.setCellValue(cellOld.getStringCellValue());
                                break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                               
cellNew.setCellValue(cellOld.getNumericCellValue());
                                break;
                        case HSSFCell.CELL_TYPE_BLANK:
                               
cellNew.setCellValue(HSSFCell.CELL_TYPE_BLANK);
                                break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                               
cellNew.setCellValue(cellOld.getBooleanCellValue());
                                break;
                }
        }

       
        private String checkType (HSSFCell cell){
                String value = null;
                switch (cell.getCellType()){
                case HSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                        value = Double.toString(cell.getNumericCellValue());
                        break;
                case HSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                }
                return value;
        }


-Lenni


Gordon.Colburn wrote:
> 
> I found one that looks promising - jXLS (http://jxls.sourceforge.net/).
> 
> Thanks,
> Gordon
> 
> -----Original Message-----
> From: Anthony Andrews [mailto:pythonaddict@yahoo.com] 
> Sent: Sunday, August 05, 2007 6:08 AM
> To: POI Users List
> Subject: Re: [[nosig]] How can I copy rows or cells and preserve
> formatting
> 
> I think - and it is think so forgive me if I am mistaken - that someone
> has written a utility that will accomplish this sort of task. Have you
> searched through the archive of messages posted to the group yet? You
> may be able to find it.
> 
> Gordon.Colburn@AmericanSystems.com wrote: I am using POI to generate
> reports from a Java application. For various
> reasons, I would like to be able to tweak the format of the report using
> excel rather than POI. My approach to this is to create a report
> template using excel. My application would then open the template and,
> for each row in the report, simply copy a row from the template onto the
> report, and then fill in the values. However, I have not found a way to
> copy a row or cell via POI in such a way that preserves formatting. Is
> it possible to do this with POI? The copying of rows or cells would be
> between worksheets but within the same workbook.
> 
>  
> 
> Thanks in advance,
> 
> Gordon
> 
>  
> 
> 
> 
>        
> ---------------------------------
> Got a little couch potato? 
> Check out fun summer activities for kids.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/--nosig---How-can-I-copy-rows-or-cells-and-preserve-formatting-tf4217899.html#a14128551
Sent from the POI - User mailing list archive at Nabble.com.


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


RE: How can I copy rows or cells and preserve formatting

Posted by Go...@AmericanSystems.com.
I found one that looks promising - jXLS (http://jxls.sourceforge.net/).

Thanks,
Gordon

-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com] 
Sent: Sunday, August 05, 2007 6:08 AM
To: POI Users List
Subject: Re: [[nosig]] How can I copy rows or cells and preserve
formatting

I think - and it is think so forgive me if I am mistaken - that someone
has written a utility that will accomplish this sort of task. Have you
searched through the archive of messages posted to the group yet? You
may be able to find it.

Gordon.Colburn@AmericanSystems.com wrote: I am using POI to generate
reports from a Java application. For various
reasons, I would like to be able to tweak the format of the report using
excel rather than POI. My approach to this is to create a report
template using excel. My application would then open the template and,
for each row in the report, simply copy a row from the template onto the
report, and then fill in the values. However, I have not found a way to
copy a row or cell via POI in such a way that preserves formatting. Is
it possible to do this with POI? The copying of rows or cells would be
between worksheets but within the same workbook.

 

Thanks in advance,

Gordon

 



       
---------------------------------
Got a little couch potato? 
Check out fun summer activities for kids.

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


Re: [[nosig]] How can I copy rows or cells and preserve formatting

Posted by Anthony Andrews <py...@yahoo.com>.
I think - and it is think so forgive me if I am mistaken - that someone has written a utility that will accomplish this sort of task. Have you searched through the archive of messages posted to the group yet? You may be able to find it.

Gordon.Colburn@AmericanSystems.com wrote: I am using POI to generate reports from a Java application. For various
reasons, I would like to be able to tweak the format of the report using
excel rather than POI. My approach to this is to create a report
template using excel. My application would then open the template and,
for each row in the report, simply copy a row from the template onto the
report, and then fill in the values. However, I have not found a way to
copy a row or cell via POI in such a way that preserves formatting. Is
it possible to do this with POI? The copying of rows or cells would be
between worksheets but within the same workbook.

 

Thanks in advance,

Gordon

 



       
---------------------------------
Got a little couch potato? 
Check out fun summer activities for kids.