Ok, before I left this morning, I had the chance to put together some code
very quickly, and now can post it for you.
public static void copySheet(Sheet fromSheet, Sheet toSheet, int fromColNum,
int fromRowNum, int toColNum, int toRowNum)
{
Row fromRow = null;
Row toRow = null;
Cell fromCell = null;
Cell toCell = null;
// Step through all of the rows that are to be copied.
for(int i = fromRowNum; i < toRowNum; i++) {
// Get a row from the sheet that is being copied, create a
// corresponding row in the sheet we are creating and set the
// height of the 'new' row.
fromRow = fromSheet.getRow(i);
toRow = toSheet.createRow(i);
toRow.setHeight(fromRow.getHeight());
// Step through the cells on the rwo we are copying and insert
// corresponding cells into the row we are creating on the 'new'
// sheet.
for(int j = fromColNum; j < toColNum; j++) {
fromCell = fromRow.getCell(j);
toCell = toRow.createCell(j);
toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
// This seems not to copy the comments successfully even
// though it ought to. If it matters, I will look into this
// problem further.
if(fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// Copy the cells type, style and contents across.
switch(fromCell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
toCell.setCellType(fromCell.getCellType());
toCell.setCellStyle(fromCell.getCellStyle());
toCell.setCellValue(fromCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
toCell.setCellType(fromCell.getCellType());
toCell.setCellStyle(fromCell.getCellStyle());
toCell.setCellErrorValue(fromCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
toCell.setCellType(fromCell.getCellType());
toCell.setCellStyle(fromCell.getCellStyle());
toCell.setCellFormula(fromCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
toCell.setCellType(fromCell.getCellType());
toCell.setCellStyle(fromCell.getCellStyle());
toCell.setCellValue(fromCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
toCell.setCellType(fromCell.getCellType());
toCell.setCellStyle(fromCell.getCellStyle());
toCell.setCellValue(fromCell.getRichStringCellValue());
break;
}
}
}
}
and here is how you might call the method;
File file = new File("For Copying.xlsx");
FileInputStream fis = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(fis);
// Get the sheet that will be the source of the
// cells the method must copy. Of course, it
// does not have to be the first sheet in the
// Workbook.
Sheet fromSheet = workbook.getSheetAt(0);
// You can either copy to a sheet that already exists
// or to a new sheet that you have created. Note that if you are
// copying to an existing sheet, then it ought to be empty.
//Sheet toSheet = workbook.getSheetAt(1);
Sheet toSheet = workbook.createSheet();
fis.close();
fis = null;
this.copySheet(fromSheet, toSheet, 1, 1, 3, 7);
FileOutputStream fos = new java.io.FileOutputStream(file);
workbook.write(fos);
This copies cells 'in place' so to speak; by this I mean that cell B1 will
be copied from one sheet and become cell B1 on another. All you would need
to do is you want to relocate the positions of the cells is to provide an
offset for both row and column numbers and that should be quite simple to
accomplish.
I do not know if cell comments matter to you as the code does not copy them
for some reason though I will try to find out why. Also, the code I have
posted does not check for Cell.CELL_TYPE_BLANK, I reasoned that a blank cell
should be created but nothing else done to it; even though this does mean
that a couple of lines of code appear on numerous occasions within the
switch statement.
Give it a go and see what happens. As with everything, try it in a test
environment and test the code until you are happy it is 'safe'.
Yours
Mark B
dgv123 wrote:
>
> Hi Mark,
>
> Thanks for your reply. I think hiding the columns is a much easier way of
> handling this. I'll just hide a couple of columns to the right of my
> column break.
>
> I am not populating this excel sheet. The doc is created by a tool known
> as Business Objects (i am thinking it uses a template). The
> 'getlastrownum'
> returns 50..but the print preview has way more than 50 rows showing up (4
> pages). the
> 'removerow' does not help either. I even tried doing the setFitHeight
> (PrintSetup class) to 1...that didnt help either. I am trying to use POI
> to format the excel documents that come out of this particular tool
> to make it circulation ready. Apparently Business Objects is not good at
> formatting documents.
>
> Do you know of any way to copy a couple of rows (say A1 - A5)
> to say A46-A51?
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
View this message in context:
http://www.nabble.com/POI-%28Excel%29-usage-questions.-tp24116860p24163721.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
