Copy sheet with JXL in Java

Question

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?

Solution 1

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 :

• JExcel API Frequently Asked Questions

Solution 2

1. Check if readFormat is not null (as mentioned above)

2. Beware of warning 'Maximum number of format records exceeded. Using default format.' Try use sth like Map<CellFormat,WritableCellFormat> to controll number of WritableCellFormat instances.

   public static void createSheetCopy(WritableWorkbook workbook, int from, int to, String sheetName) throws WriteException {
       WritableSheet sheet = workbook.getSheet(from);
       WritableSheet newSheet = workbook.createSheet(sheetName, to);
       // Avoid warning "Maximum number of format records exceeded. Using default format."
       Map<CellFormat, WritableCellFormat> definedFormats = new HashMap<CellFormat, WritableCellFormat>();
       for (int colIdx = 0; colIdx < sheet.getColumns(); colIdx++) {
           newSheet.setColumnView(colIdx, sheet.getColumnView(colIdx));
           for (int rowIdx = 0; rowIdx < sheet.getRows(); rowIdx++) {
               if (colIdx == 0) {
                   newSheet.setRowView(rowIdx, sheet.getRowView(rowIdx));
               }
               WritableCell readCell = sheet.getWritableCell(colIdx, rowIdx);
               WritableCell newCell = readCell.copyTo(colIdx, rowIdx);
               CellFormat readFormat = readCell.getCellFormat();
               if (readFormat != null) {
                   if (!definedFormats.containsKey(readFormat)) {
                       definedFormats.put(readFormat, new WritableCellFormat(readFormat));
                   }
                   newCell.setCellFormat(definedFormats.get(readFormat));
               }
               newSheet.addCell(newCell);
           }
       }
   }
   

Solution 3

Just an update, the "copyto" function does not work with a cell, some modified code: This takes a readable workbook, index number of the sheet to be copied, the writable workbook and the index number where the sheet needs to be copied, works fine for copying a sheet from one workbook to another.

private static WritableSheet createSheetCopy(Workbook w, int from, int to,
            WritableWorkbook writeableWorkbook) throws WriteException {
        Sheet sheet = w.getSheet(from);
        WritableSheet newSheet = writeableWorkbook.getSheet(to);
        // Avoid warning
        // "Maximum number of format records exceeded. Using default format."
        Map<CellFormat, WritableCellFormat> definedFormats = new HashMap<CellFormat, WritableCellFormat>();
        for (int colIdx = 0; colIdx < sheet.getColumns(); colIdx++) {
            newSheet.setColumnView(colIdx, sheet.getColumnView(colIdx));
            for (int rowIdx = 0; rowIdx < sheet.getRows(); rowIdx++) {
                if (colIdx == 0) {
                    newSheet.setRowView(rowIdx, sheet.getRowView(rowIdx));
                }
                Cell readCell = sheet.getCell(colIdx, rowIdx);
                Label label = new Label(colIdx, rowIdx, readCell.getContents());
                CellFormat readFormat = readCell.getCellFormat();
                if (readFormat != null) {
                    if (!definedFormats.containsKey(readFormat)) {
                        definedFormats.put(readFormat, new WritableCellFormat(
                                readFormat));
                    }
                    label.setCellFormat(definedFormats.get(readFormat));
                }
                newSheet.addCell(label);
            }
        }
        return newSheet;
    }

Solution 4

You have to loop through the cells one by one and add them to the new sheet.

See this, under question How can I copy a worksheet in one workbook to a new worksheet in another workbook?

Solution 5

if (readFormat != null) {

    WritableCellFormat newFormat = new WritableCellFormat(readFormat);

    newCell.setCellFormat(newFormat);

    newSheet.addCell(newCell);

}

View more solutions