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