如何使用 HSSF (Apache POI) 在现有 excel 中的两行之间插入一行

问题描述

不知何故，我设法在现有 Excel 文件的两行之间创建了新行.问题是，某些格式不包括在行的移动中.

Somehow I manage to create new rows between two rows in an existing excel file. The problem is, some of the formatting were not include along the shifting of the rows.

其中之一，是隐藏的行在轮班期间不相对进行.我的意思是(例如)，从 20 到 30 的行是隐藏的，但是当创建新行时，格式仍然存在.在插入/创建新行的过程中，隐藏的行也必须移动，应该是 21 到 31.

One of this, is the row that are hide are not relatively go along during the shift. What I mean is(ex.), rows from 20 to 30 is hidden, but when a create new rows the formating still there. The hidden rows must also move during the insertion/creation of new rows, it should be 21 to 31.

另一件事是，工作表中不在单元格中的另一个对象.就像创建新行后文本框不会移动一样.就像这些物体的位置是固定的一样.但我希望它移动，就像我在 excel 中插入新行或粘贴行一样.如果有插入新行的功能，请告诉我.

Another thing is, the other object in the sheet that are not in the cell. Like the text box are not move along after the new row is created. Its like the position of these object are fixed. But I want it to move, the same thing as I insert a new row or paste row in excel. If there is a function of inserting a new row, please let me know.

这是我现在所拥有的，只是我代码中的一个片段.

This what I have right now, just a snippet from my code.

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);

如果可以复制和粘贴行，那将有很大帮助.但我已经在这里问了，找不到解决方案.所以我决定创建一行作为临时解决方案.我已经完成了，但遇到了这样的问题.

If copy and paste of rows is possible that would be big help. But I already ask it here and can't find a solution. So I decided to create a row as an interim solution. I'm done with it but having a problem like this.

任何帮助将不胜感激.谢谢！

Any help will be much appreciated. Thanks!

推荐答案

无耻地复制行的辅助函数改编自 这里

Helper function to copy rows shamelessly adapted from here

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);
            }
        }
    }
}

这篇关于如何使用 HSSF (Apache POI) 在现有 excel 中的两行之间插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！