用Java中的JXL复制工作表

Question

我想将工作表从现有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();

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

---

更新：

当我运行这段代码时，我在第行得到jxl.common.AssertionFailed个异常

WritableCellFormat newFormat = new WritableCellFormat(readFormat);

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

newCell.setCellFormat(readFormat);

然后不复制单元格样式（字体、单元格边框等）

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

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

Answer 1

你必须一个接一个地遍历单元格，并将它们添加到新的工作表中

请参见this，有疑问的是，如何将一个工作簿中的工作表复制到另一个工作簿中的新工作表

Answer 2

jxlapi^wiki允许用户在运行时读取、写入、创建和修改Excel（.xls）工作簿中的工作表。它不支持.xlsx格式

• JXL API支持版本为Excel 95, 97, 2000, XP, and 2003的Excel文档。这些文档包含扩展名.xls

使用以下功能复制JXL工作簿工作表

public static void copySheetToWritableSheet(jxl.Sheet srcSheet, jxl.write.WritableSheet destSheet) throws JXLException {
    int numrows = srcSheet.getRows();
    int numcols = srcSheet.getColumns();
    
    System.out.println("Rows:"+numrows+", Col:"+numcols);
    for (int rowIdx = 0 ; rowIdx < numrows ; rowIdx++) {
        for (int colIdx = 0 ; colIdx < numcols ; colIdx++) {
            System.out.println("--- Rows:"+rowIdx+", Col:"+colIdx);
            jxl.Cell srcCell = srcSheet.getCell(colIdx, rowIdx);
            CellType type = srcCell.getType();
            jxl.format.CellFormat format = srcCell.getCellFormat();
            String cellValue = srcCell.getContents();
            
            WritableCellFormat cf = null;
            WritableCell newCell = null;
            
            if (format != null) {
                Colour backgroundColour = format.getBackgroundColour();
                Font font = format.getFont();
                WritableFont wf = new WritableFont(font);
                cf = new WritableCellFormat(wf);
                
                int value = backgroundColour.getValue();
                String description = backgroundColour.getDescription();
                System.out.println("Format Not Null Val:"+value+", Desc:"+description);
                if (value != 192) { // Val: 192, Desc:default background  [Dark Black]
                    cf.setBackground(backgroundColour);
                }
                
                cf.setAlignment(format.getAlignment());
                cf.setBorder(jxl.format.Border.RIGHT, format.getBorderLine(Border.RIGHT));
                cf.setBorder(Border.LEFT, format.getBorderLine(Border.LEFT));
                cf.setBorder(Border.BOTTOM, format.getBorderLine(Border.BOTTOM));
                cf.setBorder(Border.TOP, format.getBorderLine(Border.TOP));
                cf.setWrap(format.getWrap());
                
                if (type == CellType.NUMBER) {
                    newCell = new Number(colIdx, rowIdx, ((NumberCell) srcCell).getValue(), cf);
                } else {
                    newCell = new Label(colIdx, rowIdx, cellValue, cf);
                }
                CellView cellView = srcSheet.getColumnView(colIdx);
                destSheet.setColumnView(colIdx, cellView);
                destSheet.addCell(newCell);
            } else {
                WritableFont wf = new WritableFont(ARIAL_10_PT);
                // for position column we are not applying the display format
                if (type == CellType.NUMBER) {
                    cf = new WritableCellFormat(wf, displayFormat);
                    newCell = new Number(colIdx, rowIdx, ((NumberCell) srcCell).getValue(), cf);
                } else {
                    cf = new WritableCellFormat(wf);
                    newCell = new Label(colIdx, rowIdx, cellValue, cf);
                }
                CellView cellView = srcSheet.getColumnView(colIdx);
                destSheet.setColumnView(colIdx, cellView);
                destSheet.addCell(newCell); // https://stackoverflow.com/a/64675987/5081877
            }
        }
    }
    //Merge - MergedCells
    Range[] mergedCells = srcSheet.getMergedCells();
    for (int i = 0; i < mergedCells.length; i++) {
        System.out.println("mergedCells:"+i);
        Cell tl = mergedCells[i].getTopLeft();
        Cell br = mergedCells[i].getBottomRight();
        destSheet.mergeCells(tl.getColumn(), tl.getRow(), br.getColumn(), br.getRow());
    }
    
    SheetSettings srcSettings = srcSheet.getSettings();
    SheetSettings destSettings = destSheet.getSettings();
    destSettings.setZoomFactor(srcSettings.getZoomFactor());
}

使用Java Excel API » 2.6.12的完整示例，使用的示例文件是JXLWorkbook.xls

public class JXL_XLS_Report {
    static String filePath = "C:/Yash/",
            sourceFile = filePath+"JXLWorkbook.xls", sourceFileSheetName = "FormatAbbrSheet",
            destinationFile = filePath+"JXLWorkbook_Copy.xls";
    
    public static void main(String[] args) throws Exception {
        File sourceDST = new File(destinationFile);
        jxl.write.WritableWorkbook workbook = Workbook.createWorkbook(sourceDST);
        int numberOfSheets = workbook.getNumberOfSheets();
        System.out.println("Number of Sheets:"+numberOfSheets);
        // create the empty sheet
        jxl.write.WritableSheet writableSheet = workbook.createSheet(sourceFileSheetName+"_777", numberOfSheets + 1);
        
        File source = new File(sourceFile);
        InputStream fileInStream = new FileInputStream(source);
        jxl.Workbook templateWorkbook = Workbook.getWorkbook(fileInStream, getDefaultWorkbookSettings());
        jxl.Sheet srcSheet = templateWorkbook.getSheet(sourceFileSheetName);
        
        copySheetToWritableSheet(srcSheet, writableSheet);
        
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setRationalization(false);
        
        closeWorkbook(workbook);
    }
    static jxl.biff.DisplayFormat displayFormat = new NumberFormat("0.000");
    static WritableFont ARIAL_10_PT = new WritableFont(WritableFont.ARIAL);
    //static WritableFont DataFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
    public static void copySheetToWritableSheet(jxl.Sheet srcSheet, jxl.write.WritableSheet destSheet) throws JXLException {
        // ...
    }
    public static void closeWorkbook(WritableWorkbook workbook) throws IOException, JXLException {
        if (workbook == null)
            return;
        if (workbook.getNumberOfSheets() == 0) {
            workbook.createSheet("No data", 0); // otherwise pointer error
        }
        //Writes out the data held in this workbook in Excel format
        workbook.write(); 
        //Close and free allocated memory 
        workbook.close(); 
    }
    public static WorkbookSettings getDefaultWorkbookSettings() {
        WorkbookSettings workbookSettings = new WorkbookSettings();
        workbookSettings.setEncoding("ISO-8859-15");
        workbookSettings.setLocale(Locale.GERMANY);
        workbookSettings.setCharacterSet(1200);
        workbookSettings.setExcelRegionalSettings("UK");
        workbookSettings.setExcelDisplayLanguage("US");
        workbookSettings.setPropertySets(false);
        return workbookSettings;
    }
    
    public static void copyCellValue(Sheet srcSheet, int srcCol, int srcRow, WritableSheet destSheet, int destCol, int destRow) throws JXLException {
        Cell srcCell = srcSheet.getCell(srcCol, srcRow);
        CellType type = srcCell.getType();
        WritableCell newCell = null;
        if (type == CellType.LABEL) {
            newCell = new Label(destCol, destRow, ((LabelCell) srcCell).getString());
        } else if (type == CellType.NUMBER) {
            newCell = new Number(destCol, destRow, ((NumberCell) srcCell).getValue());
        } else if (type == CellType.BOOLEAN) {
            newCell = new jxl.write.Boolean(destCol, destRow, ((BooleanCell) srcCell).getValue());
        } else if (type == CellType.DATE) {
            newCell = new DateTime(destCol, destRow, ((DateCell) srcCell).getDate());
        } else if (type == CellType.EMPTY) {
            newCell = new EmptyCell(destCol, destRow);
        } else if (type == CellType.NUMBER_FORMULA
                || type == CellType.STRING_FORMULA
                || type == CellType.BOOLEAN_FORMULA) {
            String formula = ((FormulaCell) srcCell).getFormula();
            newCell = new Formula(destCol, destRow, formula);
        } else {
            String cellValue = srcCell.getContents();
            newCell = new Label(destCol, destRow, cellValue);
        }
        
        // Set Column Size
        CellView cellView = srcSheet.getColumnView(srcCol);
        destSheet.setColumnView(srcCol, cellView);
        
        destSheet.addCell(newCell);
    }
    public static void copyCellFormat(Sheet srcSheet, int srcCol, int srcRow, WritableSheet destSheet, int destCol, int destRow)throws JXLException {
        CellFormat format = srcSheet.getCell(srcCol, srcRow).getCellFormat();
        if (format == null) return;
        WritableCell destCell = destSheet.getWritableCell(destCol, destRow);
        if (destCell.getType() == CellType.EMPTY) {
            WritableCell newCell = new Label(destCol, destRow, "");
            newCell.setCellFormat(format);
            destSheet.addCell(newCell);
        } else {
            destCell.setCellFormat(format);
        }
    }
}

Answer 3

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

2. 注意警告“超出了格式记录的最大数量。使用默认格式<尝试使用类似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 4

if (readFormat != null) {

    WritableCellFormat newFormat = new WritableCellFormat(readFormat);

    newCell.setCellFormat(newFormat);

    newSheet.addCell(newCell);

}

Answer 5

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

如何将一个工作簿中的工作表复制到另一个工作簿中的新工作表

这是可以做到的，但需要做一些工作。首先，您必须复制它的单元格（在几个嵌套for循环中）。对于每个单元格，您需要调用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);
    }
}

---

资源：

• JExcel API Frequently Asked Questions