java - 根据单元格内容将新行插入 Apache POI 电子表格

Question

注意:我到处搜索过，这不是重复的问题。

我正在尝试使用 Apache POI (poi-4.1.0) 和 Java，根据同一行中单元格的内容在电子表格中创建/插入固定数量的"new"行 - 请参阅下图(“|”代表分栏符):

1 Foo | 1001、1002、1003 |是
2 酒吧 | 1010 |是
3 自 | 2500、1500、5200 |是

本质上，会发生的情况是，我将在第 1 行和第 2 行之间插入两个新行，并在第 3 行之后再次插入，复制源行中的所有数据，只不过不是具有三个值(或者可能有多个值) )在第二列中，只有一个 - 请参见下图(“|”代表分栏符):

1 Foo | 1001 |是
2 Foo1 | 1002 |是
3 Foo2 | 1003 |是
4 酒吧 | 1001 |是
5 自已 | 2500 |是
6 Slf1 | 6 1500 |是
7 Slf2 | 7 5200 |是

仅在具有多个值的单元格上重复此过程，直到读取并处理文件中的所有行。我应该注意，新行将附加在同一文件中。

这是我的代码(我使用 this page 上的代码作为模板，并尝试更新它以匹配我正在使用的 POI 的当前版本):

public class XLSXFileAdd {
private static final String prpfile = "src/main/resources/fileinfo/directories.properties";

public static void main(String[] args) throws Exception{      
    File infile = new File(XLSXFileAdd.getCIFile()); //Gets the fully-qualified path to the input file.
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(infile));
    XSSFSheet sheet = workbook.getSheet("Sheet1"); //Opens the XLSX document at the specified worksheet.
    String msNum; //The string that will hold the contents of the cell with multiple values.
    XLSXFileAdd xfa = new XLSXFileAdd();
    for(int i = 1; i <= sheet.getLastRowNum(); i++){
        Row row = sheet.getRow(i);            
        msNum = String.valueOf(row.getCell(2));
        if(i == 2 && msNum.length() > 4){ //If the current column in the row is equal to the cell that could contain multiple values and the number of values in the cell are greater than 1 (length is greater than 4 for multiple values)
            xfa.copyRows(workbook,sheet,i, i);
        }else{
            //Read and parse the file normally (the method used here works without issue so I have not copied it to reduce clutter and confusion).
        }               
    }
}

private static String getCIFile(){
    File propfile = new File(prpfile);
    Properties properties = new Properties();
    try{
        FileInputStream fis = new FileInputStream(propfile);
        properties.load(fis);
    }catch(IOException ex){
        Logger.getLogger(XLSXFileAdd.class.getName()).log(Level.SEVERE, null, ex);
}
    String filename = (String)properties.get("xlsx.input.custdata");
    return filename;
}

private void copyRows(XSSFWorkbook workbook,XSSFSheet worksheet,int sourceRowNum, int destinationRowNum){
    //Get source & destination row
    Row newRow = worksheet.getRow(destinationRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    //Check if the row will be overwritten; if the row is populated, push down all rows by one and 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++){
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        //If the old is not populated (null), jump to next cell
        if(oldCell == null){
            newCell = null;
            continue;
        }

        //Set newly created cells to the style of the source cell
        newCell.setCellStyle(oldCell.getCellStyle());

        //Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        //Set the value of the cell
        switch(oldCell.getCellType()){
            case _NONE:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case BLANK:
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;       
        }           
    }

    //Check for merged regions and copy said regions 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);
        }
    }        
}

当我运行代码时，它运行正常并表示已成功完成，但是，当我尝试打开修改后的文件时，它会提示数据损坏并从文件中删除除两行之外的所有内容(如果我允许 MS Excel 尝试来修复它)。我还尝试将输出重定向到不同的文件，但结果是相同的 - 它损坏了数据并且只显示两行，它们之间有一个空白行。

所以我的问题是:1)有没有更好的方法来做我想做的事？2)如果没有[更好的方法]，我做错了什么导致它破坏了我试图写入的所有数据。

Answer 1

所以我设法解决了我的问题。我没有尝试附加到源行下方，而是决定将该行附加到文件末尾，这使得逻辑更加容易。这是我为解决该问题而创建的代码:

public void addAddtlRows(Sheet sheet,Workbook workbook,DataFormatter formatter, ImportDataFormatter fmt, File file){

     //Loads and parses the regular expression into memory and creates a new StringBuilder() instance.
    final Pattern p = Pattern.compile(regex);
    StringBuilder sb = new StringBuilder();

    //Create the array which holds all the entries from a cell that contains multiple entries
    String[] sysNumber;

    //The number of the last row in the sheet.
    int lastRow = sheet.getLastRowNum();

    //Instantiates an integer that will be assigned the length of the array later
    int arrayLength;

    //Loops through the each row in the sheet 
    for(int r = 1; r < lastRow; r++){
        Row row = sheet.getRow(r);
        String cellData = formatter.formatCellValue(row.getCell(2));
        String active = formatter.formatCellValue(row.getCell(4));


        if((cellData.length() > 4) && (active.equals("Yes"))){

             /** Checks whether or not we are on the cell containing the
             * numbers and whether or not they are currently active.
             * If we are, get values for all cells in the row
             */
            String an = formatter.formatCellValue(row.getCell(0));
            String cn = formatter.formatCellValue(row.getCell(1));
            String ca = formatter.formatCellValue(row.getCell(3));
            String es = formatter.formatCellValue(row.getCell(4));
            String i10 = formatter.formatCellValue(row.getCell(5));
            String i9 = formatter.formatCellValue(row.getCell(6));
            String ia = formatter.formatCellValue(row.getCell(7));
            String rp = formatter.formatCellValue(row.getCell(8));

            /**
             * Checks the contents of the cell for more than one entry
             * If the cell contains more than one number, process
             * the data accordingly
             */

            fmt.setSysNum(cellData);
            String[] sys = String.valueOf(fmt.getSysNum()).split(",");

            /**
             * Assign the length value of the 'sysNumber' array to
             * the integer 'arrayLength'
             */
            arrayLength = sys.length;

            /**
             * Loop through each entry in the string array, creating
             * a new row on each iteration and pasting the data from
             * the old cells to the new ones
             */
            for(int n = 0; n < arrayLength; n++){
                Row nRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
                nRow.createCell(0).setCellValue(an);
                nRow.createCell(1).setCellValue(cn);
                nRow.createCell(2).setCellValue(sys[n]);
                nRow.createCell(3).setCellValue(ca);
                nRow.createCell(4).setCellValue(es);
                nRow.createCell(5).setCellValue(i10);
                nRow.createCell(6).setCellValue(i9);
                nRow.createCell(7).setCellValue(ia);
                nRow.createCell(8).setCellValue(rp);

            } 
        }              
    }

    //Writes the newly added contents of the worksheet to the workbook.
    try {
    workbook.write(new FileOutputStream(file));
    } catch (FileNotFoundException ex) {
    Logger.getLogger(MapMultipleSNToDBFields.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
    Logger.getLogger(MapMultipleSNToDBFields.class.getName()).log(Level.SEVERE, null, ex);
    }
}