How to copy a row from existing excel sheet to a n

2019-05-21 07:49发布

问题:

I want to compare two excel sheets and find if any row differs, if it does I want to copy that single row which differs from that excel to a new row in a new excel. Below is a code snippet I took from the net and tried but the problem if I copy the 10th row of the existing excel(for example) to the first row of new excel, 1st row is properly copied in new excel but all the remaining rows from first excel are also copied to the new one. I think the problem is with the way I write to new excel [workbook.write(out)] Please help!! Thanks in advance!!

    public class RowCopy {

public static void main(String[] args) throws Exception{
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("c:/input.xls"));
    HSSFSheet sheet = workbook.getSheet("Sheet1");
    copyRow(workbook, sheet, 0, 1);
    FileOutputStream out = new FileOutputStream("c:/output.xls");
    workbook.write(out);
    out.close();
}

private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    HSSFRow newRow = worksheet.getRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {
        newRow = worksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        HSSFCell oldCell = sourceRow.getCell(i);
        HSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        HSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
        }
    }
}

回答1:

Just modify the copyRow method, add parameter HSSFSheet resultSheet then modify newRow variable in the method to get that from resultSheet like this

private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, HSSFSheet resultSheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    HSSFRow newRow = resultSheet.getRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

take that resultSheet from your destination "output.xls"