Reputation: 6797

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?

Upvotes: 5

Answers (6)

Yash

Reputation: 9578

JXL API^wiki allows users to read, write, create, and modify sheets in an Excel(.xls) workbook at runtime. It doesn't support .xlsx format.

JXL API supports Excel documents with versions Excel 95, 97, 2000, XP, and 2003. These documents hold the extension .xls

Use the following function to Copy the JXL Workbook Sheet.

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

Full length example using Java Excel API » 2.6.12, Sample file used is 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);
        }
    }
}

Upvotes: 0

Colin Hebert

Reputation: 93177

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

Upvotes: 7

Saurabh Kumar

Reputation: 41

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

Upvotes: 4

Radek M

Reputation: 389

Check if readFormat is not null (as mentioned above)

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

Upvotes: 4

Samson

Reputation: 21

if (readFormat != null) {

    WritableCellFormat newFormat = new WritableCellFormat(readFormat);

    newCell.setCellFormat(newFormat);

    newSheet.addCell(newCell);

}

Upvotes: 2

Nivas

Reputation: 18354

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?

Upvotes: 3

Copy sheet with JXL in Java

Answers (6)

Related Questions