Как вставить строку между двумя строками в существующем Excel с помощью HSSF (Apache POI)
Каким-то образом мне удается создать новые строки между двумя строками в существующем файле Excel. Проблема в том, что некоторые из форматирования не были включены во время смещения строк.
Одним из них является то, что ряды, которые скрыты, относительно не проходят во время смены. Я имею в виду (например), строки от 20 до 30 скрыты, но при создании новых строк форматирование все еще там. Скрытые строки также должны перемещаться во время вставки / создания новых строк, это должно быть от 21 до 31.
Другое дело, другой объект на листе, которого нет в ячейке. Как текстовое поле не перемещаться после создания новой строки. Это как положение этих объектов фиксированы. Но я хочу, чтобы он двигался, так же, как я вставляю новую строку или вставляю строку в Excel. Если есть функция вставки новой строки, пожалуйста, дайте мне знать.
Это то, что у меня есть сейчас, просто фрагмент из моего кода.
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);
Если копирование и вставка строк возможно, это будет большой помощью. Но я уже спрашиваю это здесь и не могу найти решение. Поэтому я решил создать строку в качестве временного решения. Я с этим покончил, но возникла такая проблема.
Любая помощь будет высоко ценится. Спасибо!
8 ответов
Вспомогательная функция для копирования строк бесстыдно адаптирована отсюда
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);
}
}
}
}
Для людей, которые хотят вставить строку между двумя строками в существующем Excel с XSSF (Apache POI), в XSSFSheet уже реализован метод "copyRows".
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();
}
}
Ссылаясь на ответ 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;
}
}
}
Ссылка на ответ Qwerty, если destRow не равен 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.
Я объединил некоторые другие ответы и комментарии в следующей реализации, протестированной с Apache POI v3.9.
У меня есть только один 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<String> fArray = new ArrayList<String>();
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(), когда в документе есть скрытые строки.