在 Java 中使用 JXL 复制工作表

Copy sheet with JXL in Java

提问by user

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?

我想将现有 XLS 文档中的工作表复制到新位置的新工作表中。
我怎么能用 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 copyTomethod.
Is there any way to add a cell/sheet from w1to w2workbook?
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?)

编辑：
w1.getSheet(0).getCell(0, 0)不是WritableCell，所以我无法使用该copyTo方法。
有没有办法将单元格/工作表添加w1到w2工作簿？
edit2：
那么我是否必须将工作簿的可写副本创建到其他文件中？
（edit3：或者有没有其他免费的库可以做到这一点？）

---

---

Update:

更新：

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

当我运行此代码时，我在线收到jxl.common.AssertionFailed异常

WritableCellFormat newFormat = new WritableCellFormat(readFormat);

If I remove this line and change the code to

如果我删除这一行并将代码更改为

newCell.setCellFormat(readFormat);

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?

如何将单元格样式也复制到新单元格？

采纳答案by Colin Hebert

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

这可以完成，但需要做一些工作。首先，您必须复制它的单元格（在几个嵌套的 for 循环内）。对于每个单元格，您需要调用该copyTo()方法，这将产生一个深拷贝。但是格式只是浅复制，因此您需要获取单元格格式并使用它的复制构造函数，然后在刚刚复制的单元格上调用 setCellFormat 。然后将重复的单元格添加到新电子表格中

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

• JExcel API 常见问题

回答by Nivas

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?

看到这个，在问题下如何将一个工作簿中的工作表复制到另一个工作簿中的新工作表？

回答by Samson

if (readFormat != null) {

    WritableCellFormat newFormat = new WritableCellFormat(readFormat);

    newCell.setCellFormat(newFormat);

    newSheet.addCell(newCell);

}

回答by Radek M

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 WritableCellFormatinstances.

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

1. 检查 readFormat 是否不为空（如上所述）

2. 当心警告'超出最大格式记录数。使用默认格式。' 尝试使用 sth likeMap<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);
           }
       }
   }
   

回答by Saurabh Kumar

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.

只是一个更新，“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;
    }