读取excel文件并更新添加新行

Question

我是java的初学者，我正在尝试做一个小的java程序，读取一个excel文件，并在文件末尾添加一个新行来更新文件。然后再次读取并使用新行ecc,ecc进行更新。

这是我读取的文件（boh.xls）：

点击这里查看图片

当他转到“workbook.write（）”时，它会向我抛出这个错误：

> XLS FILE -----> Start reading...
0
 CELL: 0 [ Hi ]  CELL: 1 [ empty cell ]  CELL: 2 [ Roby ] 1
 CELL: 0 [ Ciao ]  CELL: 1 [ bobbi ] 2
 CELL: 0 [ empty cell ]  CELL: 1 [ Great ]  CELL: 2 [ Job ] 
3
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/collections4/bidimap/TreeBidiMap
    at org.apache.poi.hpsf.Section.<init>(Section.java:178)
    at org.apache.poi.hpsf.MutableSection.<init>(MutableSection.java:41)
    at org.apache.poi.hpsf.PropertySet.init(PropertySet.java:494)
    at org.apache.poi.hpsf.PropertySet.<init>(PropertySet.java:196)
    at org.apache.poi.hpsf.MutablePropertySet.<init>(MutablePropertySet.java:44)
    at org.apache.poi.hpsf.SpecialPropertySet.<init>(SpecialPropertySet.java:47)
    at org.apache.poi.hpsf.DocumentSummaryInformation.<init>(DocumentSummaryInformation.java:99)
    at org.apache.poi.hpsf.PropertySetFactory.create(PropertySetFactory.java:116)
    at org.apache.poi.POIDocument.getPropertySet(POIDocument.java:236)
    at org.apache.poi.POIDocument.getPropertySet(POIDocument.java:197)
    at org.apache.poi.POIDocument.readPropertySet(POIDocument.java:175)
    at org.apache.poi.POIDocument.readProperties(POIDocument.java:158)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.updateEncryptionInfo(HSSFWorkbook.java:2295)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1506)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1428)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1414)
    at excel_01.Read_03.readExcel_03(Read_03.java:91)
    at excel_01.Read_03.main(Read_03.java:108)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections4.bidimap.TreeBidiMap
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 18 more

有什么建议吗？谢谢你们！

    package excel_01;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Read_03 {

    private static final String FILE_NAME = "/EXCEL_PER_ECLIPSE/boh.xls";

    public void readExcel_03() {

        try {

            FileInputStream input = new FileInputStream(FILE_NAME);
//          Workbook workBook = WorkbookFactory.create(input);
            Workbook workBook = new HSSFWorkbook(input);
            System.out.println("XLS FILE" + " -----> " + "Start reading...");
            Sheet sheet = workBook.getSheetAt(0);
            System.out.println(sheet.getPhysicalNumberOfRows());

            Row row = null;

            for ( int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {

                row = sheet.getRow(j);
                System.out.println(row.getRowNum());

                for (int i = 0; i < row.getLastCellNum(); i++) {

                    if (row.getCell(i) != null) {

                        Cell cell = row.getCell(i/*, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK*/);

                        System.out.print(" CELL: " + i + " [ " + cell.toString() + " ] ");
                    } 

                    else {

                        System.out.print(" CELL: " + i + " [ empty cell ] ");
                    }
                }
            }

            int physicalRow = sheet.getPhysicalNumberOfRows(); 
            Row lastPhysicalRow = sheet.getRow(physicalRow);
            int lastRow = sheet.getLastRowNum();
            //Row rowprova1 = sheet.getRow(prova1);

            if (physicalRow > lastRow && lastPhysicalRow == null /*here I check if the last row is null*/) {

                System.out.println();
                System.out.println(physicalRow);

                Object[][] bookData = {

                        {"Bau"},

                };

                int rowCount = sheet.getPhysicalNumberOfRows();

                for (Object[] newElement : bookData) {

                    Row newRow = sheet.createRow(++rowCount);
                    int columnCount = 0;                     
                    Cell newCell = newRow.createCell(columnCount);
                    newCell.setCellValue(rowCount);

                    for (Object field : newElement) {

                        newCell = newRow.createCell(++columnCount);

                        if (field instanceof String) {
                            newCell.setCellValue((String) field);                        
                        }
                    }

                }

            }

            input.close();

            FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
            workBook.write(outputStream);
            workBook.close();
            outputStream.close();
            System.out.println("End reading!");
        }

        catch (Exception e) {

            System.out.println("Error");
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {

        Read_03 read_03 = new Read_03();
        read_03.readExcel_03();

    }
}

Answer 1

Apache Commons集合似乎不在您的类路径上。你应该把它加进去。

您可能希望考虑使用诸如Gradle或Maven这样的依赖关系管理框架来降低这种意外的可能性。