poi

excel insert line

private void test8() throws IOException {
    InputStream inputStream = this
            .getClass()
            .getClassLoader()
            .getResourceAsStream(
                    "template/AdvancedStudyStudentAttendance.xls");
    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = workbook.getSheetAt(0);
    copyRow(workbook, sheet, 7, 8);//index從1開始的第8行
    int row = 7;
    for (int cellIndex = 0; cellIndex < 14; cellIndex++) {
        Cell cell = sheet.getRow(row).getCell(cellIndex);
        cell.setCellValue(cellIndex + 1);
    }
    for (int cellIndex = 0; cellIndex < 14; cellIndex++) {
        Cell cell = sheet.getRow(8).getCell(cellIndex);
        cell.setCellValue(cellIndex + 1);
    }
    FileOutputStream output_file = new FileOutputStream(new File(
            "C:\\simple.xls"));
    workbook.write(output_file);
    output_file.close();
}

private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet,
        int sourceRowNum, int destinationRowNum) {
    HSSFRow newRow = worksheet.getRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.getRow(sourceRowNum);
    if (newRow != null) {
        worksheet
                .shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {
        newRow = worksheet.createRow(destinationRowNum);
    }

    System.out.println("sourceRow.getLastCellNum():"
            + sourceRow.getLastCellNum());

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        HSSFCell oldCell = sourceRow.getCell(i);
        HSSFCell newCell = newRow.createCell(i);
        if (oldCell == null) {
            newCell = null;
            continue;
        }
        HSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }
        newCell.setCellType(oldCell.getCellType());
        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;
        }
    }
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        int firstRow = cellRangeAddress.getFirstRow();
        int lastRow = cellRangeAddress.getLastRow();
        int firstCol = cellRangeAddress.getFirstColumn();
        int lastCol = cellRangeAddress.getLastColumn();
        String result = String.format(
                "firstRow:%d, lastRow:%d, firstCol:%d, lastCol:%d",
                firstRow, lastRow, firstCol, lastCol);
        System.out.println(result);

        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                    newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress
                            .getFirstRow())), cellRangeAddress
                            .getFirstColumn(), cellRangeAddress
                            .getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    int sheetRows = worksheet.getPhysicalNumberOfRows();
    for (int i = sheetRows-1; i > sourceRowNum; i--) {
        worksheet.getRow(i).setHeight(worksheet.getRow(i-1).getHeight());
    }
}