我想填写表格模板excel文件。我想插入行并填充它们。我使用Java Apache POI库访问excel文件。首先,我创建了一个新文件,并在第1行至第10列的A列中填充了1..10数字并保存了该文件。然后,我读取了文件并尝试使用sheet.shiftRows()方法插入单个空行。我尝试了下面的代码,但输出文件在打开(读取)时出现问题,第5、6、7行为空,并且未发生移动。
sheet.shiftRows()
InputStream inputStream = new FileInputStream("TestIn-1.xlsx"); Workbook workbookIn = new XSSFWorkbook(inputStream); Sheet sheetIn = workbookIn.getSheet("Sheet1"); sheetIn.shiftRows(4,5,1); OutputStream outputStream = new FileOutputStream("TestOut.xlsx"); workbookIn.write(outputStream); outputStream.close();
您shiftRows尝试将第5行(索引4)和第6行(索引5)之间的行向下移动一行。但是第7、8、9和10行呢?如果需要获取新的空行5,则需要在第5行和最后一行之间向下移动一行。
shiftRows
使用apache poi版本,3.17这很简单:
apache poi
3.17
import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import java.io.FileInputStream; import java.io.FileOutputStream; class ExcelReadShiftRowsAndWrite { public static void main(String[] args) throws Exception { //String fileIn= "TestIn.xls"; //String fileOut= "TestOut.xls"; String fileIn= "TestIn.xlsx"; String fileOut= "TestOut.xlsx"; try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn)); FileOutputStream out = new FileOutputStream(fileOut)) { Sheet sheet = workbook.getSheet("Sheet1"); sheet.shiftRows(4, sheet.getLastRowNum(), 1); //shifts rows between row 5 (index 4) and last row one row down workbook.write(out); } } }
但是apache poi大于的版本在使用中3.17也4.1.0有错误。在那里,移位后,单元格中的引用保持旧状态,而不是被调整为新的行。例如,参考文献,…保持向下移位,而不是得到调整后,…shiftRows``XSSF``A5``A6``A6``A7
4.1.0
shiftRows``XSSF``A5``A6``A6``A7
因此,必须更正此错误:
import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import java.io.FileInputStream; import java.io.FileOutputStream; class ExcelReadShiftRowsAndWrite { public static void main(String[] args) throws Exception { //String fileIn= "TestIn.xls"; //String fileOut= "TestOut.xls"; String fileIn= "TestIn.xlsx"; String fileOut= "TestOut.xlsx"; try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn)); FileOutputStream out = new FileOutputStream(fileOut)) { Sheet sheet = workbook.getSheet("Sheet1"); sheet.shiftRows(4, sheet.getLastRowNum(), 1); //shifts rows between row 5 (index 4) and last row one row down if (sheet instanceof XSSFSheet) { XSSFSheet xSSFSheet = (XSSFSheet)sheet; // correcting bug that shiftRows does not adjusting references of the cells // if row 3 is shifted down, then reference in the cells remain r="A3", r="B3", ... // they must be adjusted to the new row thoug: r="A4", r="B4", ... // apache poi 3.17 has done this properly but had have other bugs in shiftRows. for (int r = xSSFSheet.getFirstRowNum(); r < sheet.getLastRowNum() + 1; r++) { XSSFRow row = xSSFSheet.getRow(r); if (row != null) { long rRef = row.getCTRow().getR(); for (Cell cell : row) { String cRef = ((XSSFCell)cell).getCTCell().getR(); ((XSSFCell)cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef); } } } // end correcting bug } workbook.write(out); } } }