移动xlsx文件的行

I'm reading an Excel file with some rows through POI. I have to delete the row of the file if it is correctly saved on the DB.

我正在通过POI读取包含一些行的Excel文件。如果文件正确保存在数据库中,我必须删除该文件的行。

Until a few day ago, I was working with a .xls file (HSSFSheet) and everything worked fine. Removing a row from a HSSFSHEET

直到几天前,我正在使用.xls文件(HSSFSheet),一切正常。从HSSFSHEET中删除行

Now I need some more of the 256 columns of the .xls file and then I changed it to a XSSFSheet. But when I use it I get this error:

现在我需要更多的.xls文件的256列,然后我将其更改为XSSFSheet。但是当我使用它时,我收到此错误:

java.lang.RuntimeException: not implemented yet
at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:117)
at org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:613)
at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:508)
at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1117)
at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1077)
at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1064)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1424)
at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1524)
at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1508)
at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1465)
at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1049)
at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:934)
at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:556)
at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:427)
at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1117)
at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1077)
at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1064)
at org.apache.poi.ss.formula.FormulaParser.parseUnary(FormulaParser.java:1129)
at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1102)
at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1077)
at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1064)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1424)

I'm using these POI dependencies:

我正在使用这些POI依赖项:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.7</version>
</dependency>

I saw Shift rows exception.

我看到Shift行异常。

In this post you say that also the last release of POI hasn't implemented the method yet.

在这篇文章中,你说POI的最后一个版本还没有实现该方法。

This is my code.

这是我的代码。

public void import(){

    Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(byteUploaded));   
    List<Row> rowsToRemoved = new ArrayList<Row>();
    Sheet mySheet =  wbUploaded.getSheetAt(0);

    for(Row row: mySheet){

        try{

            MyDTO dto = checkRow(row);
            importService.save(dto);
            rowsToRemoved.add(row);

        }catch(Exception e){
            //do something
        }
    }
    deleteRowSavedFromSheet(mySheet,rowsToRemoved);

}

public void deleteRowSavedFromSheet(Sheet sheet, List<Row> rowsToRemoved ) {

    for(Row row : rowsToRemoved){

        sheet.removeRow(row); 
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex>=0&&rowIndex<lastRowNum){
            sheet.shiftRows(rowIndex+1,lastRowNum, -1); //here i get the exception
        }
        if(rowIndex==lastRowNum){
            Row removingRow=sheet.getRow(rowIndex);
            if(removingRow!=null){
                sheet.removeRow(removingRow);
            }
        }   
    }

    rowsToRemoved.clear();
}

How can I use the shift method?

我该如何使用shift方法?

2 个解决方案

#1

From Apache poi documentation

来自Apache poi文档

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

HSSF是POI项目的Excel '97(-2007)文件格式的纯Java实现。 XSSF是POI Project的Excel 2007 OOXML(.xlsx)文件格式的纯Java实现。

You can not use XSSF with a xls file.

您不能将XSSF与xls文件一起使用。

#2

In case you don't have consecutive rows use the following method

如果您没有连续的行,请使用以下方法

public static void deleteRowSavedFromSheet(Sheet sheet, List<Row> rowsToRemoved){
    for(Row row : rowsToRemoved){
        sheet.shiftRows(row.getRowNum()+1, sheet.getLastRowNum(), -1);
    }

}

and if you have consecutive rows, simply use

如果你有连续的行,只需使用

sheet.shiftRows(lastIndexOfRowToRemove +1, LastIndexOfSheet, -CountOfRowsToRemove)

It may cause problem for last row deletion... so for that case, you should use delete row

它可能会导致最后一行删除问题...因此,对于这种情况,您应该使用删除行

#1