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)
int row = 7
for (int cellIndex = 0
Cell cell = sheet.getRow(row).getCell(cellIndex)
cell.setCellValue(cellIndex + 1)
}
for (int cellIndex = 0
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
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
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
worksheet.getRow(i).setHeight(worksheet.getRow(i-1).getHeight())
}
}