Copy sheet with JXL in Java

Question

I would like to copy a sheet from an existing XLS document to a new one in a new location.
How can I do this using 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();

change is
w1.getSheet(0).getCell(0, 0) not WritableCell, so I could not use the method copyTo.
Is there a way to add a cell / sheet from w1to a w2book?
edit2:
So should I create a writable copy of the book in another file?
( edit3: Or is there another free library that can do this?)

---

Update:

When I run this code, I get jxl.common.AssertionFailedexceptions in the line

WritableCellFormat newFormat = new WritableCellFormat(readFormat);

If I delete this line and change the code to

newCell.setCellFormat(readFormat);

then cell styles are not copied (fonts, 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 can I copy cell styles to a new cell?

Answer 1

?

, . -, ( ). copyTo(), . , , setCellFormat , .

:

 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);
    }
}

---

:

• API JExcel

Answer 2

• , readFormat null ( )

• " . . ' sth Map<CellFormat,WritableCellFormat> WritableCellFormat.

  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);
          }
      }
  }
  

Answer 3

, "copyto" , : , , , , .

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;
    }

Answer 4

.

. this, . ?

Answer 5

if (readFormat != null) {

    WritableCellFormat newFormat = new WritableCellFormat(readFormat);

    newCell.setCellFormat(newFormat);

    newSheet.addCell(newCell);

}