How to copy a row from existing excel sheet to a new excel sheet using Apache POI

source :http://stackoverflow.com/questions/22627691/how-to-copy-a-row-from-existing-excel-sheet-to-a-new-excel-sheet-using-apache-po

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s