user user - 2 years ago 134
Java Question

Copy sheet with JXL in Java

I would like to copy a sheet from an existing XLS document to a new one to a new location.

How could I do this with JXL?

Workbook w1 = Workbook.getWorkbook(new File("ExistingDocument.xls"), settings);

WritableWorkbook w2 = Workbook.createWorkbook(new File("NewDocument.xls"));

/* So here, I would like copy the first sheet from w1 to the second sheet of w2 ... */

w2.write();
w2.close();

w1.close();


edit:

w1.getSheet(0).getCell(0, 0)
is not a
WritableCell
, so I couldn't use the
copyTo
method.

Is there any way to add a cell/sheet from
w1
to
w2
workbook?

edit2:

So do I have to create a writable copy of the workbook to an other file?

(edit3: Or is there any other free lib which can do this?)




Update:

When I run this code, I get
jxl.common.AssertionFailed
exceptions on line

WritableCellFormat newFormat = new WritableCellFormat(readFormat);


If I remove this line and change the code to

newCell.setCellFormat(readFormat);


then the cell styles aren't copied (the fonts, the cell borders, etc.).

try {
Workbook sourceDocument = Workbook.getWorkbook(new File("C:\\source.xls"));
WritableWorkbook writableTempSource = Workbook.createWorkbook(new File("C:\\temp.xls"), sourceDocument);
WritableWorkbook copyDocument = Workbook.createWorkbook(new File("C:\\copy.xls"));
WritableSheet sourceSheet = writableTempSource.getSheet(0);
WritableSheet targetSheet = copyDocument.createSheet("sheet 1", 0);

for (int row = 0; row < sourceSheet.getRows(); row++) {
for (int col = 0; col < sourceSheet.getColumns(); col++) {
WritableCell readCell = sourceSheet.getWritableCell(col, row);
WritableCell newCell = readCell.copyTo(col, row);
CellFormat readFormat = readCell.getCellFormat();
/* exception on the following line */
WritableCellFormat newFormat = new WritableCellFormat(readFormat);
newCell.setCellFormat(newFormat);
targetSheet.addCell(newCell);
}
}
copyDocument.write();
copyDocument.close();
writableTempSource.close();
sourceDocument.close();
} catch (Exception e) {
e.printStackTrace();
}


How could I copy the cell styles too to the new cell?

Answer Source

How can I copy a worksheet in one workbook to a new worksheet in another workbook?

This can be done, but requires a little work. Firstly, you have to copy it cell (within a couple of nested for loops). For each cell you need to invoke the copyTo() method, which will produce a deep copy. However the format is only shallow copied, so you will need to get the cell format and use the copy constructor of that, and then call setCellFormat on the cell you have just copied. Then add the duplicate cell to the new spreadsheet

The code might look as follows:

 for (int i = 0 ; i < numrows ; i++){
    for (int j = 0 ; j < numcols ; j++){
        readCell = sheet.getCell(i, j);
        newCell = readCell.copyTo(i, j);
        readFormat = readCell.getCellFormat();
        newFormat = new WritableCellFormat(readFormat);
        newCell.setCellFormat(newFormat);
        newSheet.add(newCell);
    }
}

Resources :

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download