Как вставить строку между двумя строками в существующем excel с HSSF (Apache POI)

Как-то мне удается создать новые строки между двумя строками в существующем файле excel. Проблема в том, что некоторые форматирования не включались в сдвиг строк.

Одна из них – это строка, которая является скрытой, относительно не перемещается во время смены. Я имею в виду (например), строки с 20 до 30 скрыты, но когда создаются новые строки, формация все еще там. Скрытые строки также должны перемещаться во время вставки / создания новых строк, это должно быть от 21 до 31.

Другое дело, другой объект на листе, который не находится в ячейке. Подобно тому, как текстовое поле не перемещается после создания новой строки. Это похоже на положение этого объекта. Но я хочу, чтобы он двигался, так же, как я вставляю новую строку или вставку строки в excel. Если есть функция вставки новой строки, пожалуйста, дайте мне знать.

Это то, что у меня есть сейчас, просто fragment моего кода.

HSSFWorkbook wb = new HSSFWorkbook(template); //template is the source of file HSSFSheet sheet = wb.getSheet("SAMPLE"); HSSFRow newRow; HSSFCell cellData; int createNewRowAt = 9; //Add the new row between row 9 and 10 sheet.shiftRows(createNewRowAt, sheet.getLastRowNum(), 1, true, false); newRow = sheet.createRow(createNewRowAt); newRow = sheet.getRow(createNewRowAt); 

Если копирование и вставка строк возможны, это будет большой помощью. Но я уже спрашиваю об этом здесь и не могу найти решение. Поэтому я решил создать ряд в качестве промежуточного решения. Я покончил с этим, но у меня такая проблема.

Любая помощь будет высоко ценится. Благодаря!

    Функция помощника для копирования строк, бесстыдно адаптированных отсюда.

     import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.util.CellRangeAddress; import java.io.FileInputStream; import java.io.FileOutputStream; 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; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } } 

    Ссылаясь на ответ Qwerty , вы можете избежать раздувания размера XL, повторно используя cellStyle . И когда тип CELL_TYPE_BLANK , getStringCellValue возвращает "" вместо null .

     private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destinationRowNum); Row 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 Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Use old cell style newCell.setCellStyle(oldCell.getCellStyle()); // If there is a cell comment, copy if (newCell.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: 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; } } } 

    Для людей, которые хотят вставить строку между двумя строками в существующем excel с XSSF (Apache POI), уже существует метод «copyRows», реализованный в XSSFSheet.

     import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.FileOutputStream; public class App2 throws Exception{ public static void main(String[] args){ XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("input.xlsx")); XSSFSheet sheet = workbook.getSheet("Sheet1"); sheet.copyRows(0, 2, 3, new CellCopyPolicy()); FileOutputStream out = new FileOutputStream("output.xlsx"); workbook.write(out); out.close(); } } 

    Я объединил некоторые другие ответы и комментарии в следующей реализации, протестированной с Apache POI v3.9.

    У меня есть только один параметр rownum потому что я перемещаюсь по целевой строке и rownum ее в новую пустую строку. Формулы обрабатываются, как ожидалось, они не копируются дословно, за одним исключением: ссылки на ячейки, которые находятся выше скопированной строки, не обновляются; обходной путь заключается в замене этих явных ссылок (если они есть) ссылками, рассчитанными с использованием INDIRECT() как предложено этой записью .

     protected void copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); } worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // 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 Cell oldCell = sourceRow.getCell(i); Cell newCell; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.getWorkbook().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: 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(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } 

    Я использую эту реализацию в производственном коде.

    Что касается «обновленных» формул в новой строке, так как все копирование происходит после смены, у старой строки (теперь один индекс вверх из новой строки) уже была сдвинута его формула, поэтому копирование ее в новую строку сделает новая строка ссылается на ячейки старых строк. Решение состояло бы в том, чтобы разобрать формулы перед сдвигом, а затем применить их (простой массив String выполнил бы эту работу. Я уверен, что вы можете закодировать это в нескольких строках).

    При запуске функции:

     ArrayList fArray = new ArrayList(); Row origRow = sheet.getRow(sourceRow); for (int i = 0; i < origRow.getLastCellNum(); i++) { if (origRow.getCell(i) != null && origRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) fArray.add(origRow.getCell(i).getCellFormula()); else fArray.add(null); } 

    Затем при применении формулы к ячейке:

     newCell.setCellFormula(fArray.get(i)); 

    Недавно я столкнулся с той же проблемой. Мне пришлось вставлять новые строки в документ со скрытыми строками и сталкиваться с теми же проблемами с вами. После некоторого поиска и некоторых сообщений электронной почты в списке apache poi это похоже на ошибку в shiftrows (), когда документ имеет скрытые строки.

    Ссылка на ответ Qwerty , если destRow isnot null, sheet.shiftRows () изменит ссылку destRow на следующую строку; поэтому мы должны всегда создавать новую строку:

     if (destRow != null) { sheet.shiftRows(destination, sheet.getLastRowNum(), 1); } destRow = sheet.createRow(destination); 

    Я реализовал это в Kotlin следующим образом:

     fun Sheet.buildRow ( rowNum:Int ) : Row { val templateRow = this.getRow( rowNum ) this.shiftRows( rowNum+1, sheet.lastRowNum, 1 ) val newRow = this.createRow( rowNum+1 ) templateRow.cellIterator().forEach { newRow.createCell( it.columnIndex ).cellStyle = it.cellStyle } return templateRow } 

    Он не копирует значения ячеек, просто формат. Должно быть применимо и к Java.

    Давайте будем гением компьютера.