如何使用Apache POI将数据写入现有的Excel文件

Question

我的 Excel 表格包含 5 行和 2 列。我想在这个表格中添加一列。但是当我使用 WorkbookFactory 时，它显示错误。我导入了 poi-3.8.jar 和 poi-ooxml-3.5-beta5.jar。它给出以下错误信息：Exception in thread "main" java.lang.Error: Unresolved compilation problem: WorkbookFactory 无法解析。请帮我解决这个问题。

- sarmila

---

请发布您的代码和完整的异常跟踪。 - longhua

Excel数据 用户名 密码 状态 abc 123 def 456 ghi 789 jkl 101 - sarmila

请发布您的Java代码和编译信息。看起来您还没有导入某些包。 - longhua

谢谢你，lhuang，它正在工作，但我又遇到了另一个问题。在主线程中出现异常：“java.lang.NoSuchMethodError: org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(Ljava/io/InputStream;)Z”，位于org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:69)。请帮忙解决。 - sarmila

如何在 JFind lhuang 中检查 POIFSFileSystem 类。 - sarmila

Answer 1

try this


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
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;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExample {

    public static void main(String[] args) throws IOException {

        try {

            FileInputStream file = new FileInputStream(new File("C:\\test.xls"));

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Update the value of cell
            cell = sheet.getRow(1).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(2).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            Row row = sheet.getRow(0);
            row.createCell(3).setCellValue("Value 2");

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

- Biswajit

Answer 2

请查看Apache POI组件和依赖项页面了解详细信息。您缺少一些JAR文件，因此发生编译错误。

如果您想使用HSSF(.xls)和XSSF(.xlsx)，并且您正在讨论WorkbookFactory，那么您需要包括主POI JAR和POI-OOXML JAR，以及它们的所有依赖项。有了这些jar在您的类路径上，您就可以解决问题了。

此外，您可能还想考虑使用像Apache Maven或Apache Ivy这样的工具来处理依赖关系，这样您就可以避免像这样的缺失jar问题。

- Gagravarr

Answer 3

您正在使用Maven吗？

如果是的话，请参考以下链接中的最后一条评论：

http://apache-poi.1045710.n5.nabble.com/Where-is-WorkbookFactory-td2307412.html

- Prahalad Deshpande

---

我正在使用Apache POI而不是Maven。 - sarmila

Answer 4

我正在上传我的程序供您参考。经过一些努力，我已经克服了这个问题。 Jars细节：dom4j-1.6.1.jar，poi-3.9.jar，poi-ooxml-3.9.jar，poi-ooxml-schemas-3.11.jar，xmlbeans-2.6.0.jar 请确保您至少拥有上述或更新版本。我包含了导入的详细信息，以便您不必头疼。希望您会发现它有用。

    ***Pojo: Employee.java***

        public class Employee {
         private int id;
         private String firstName;
         private String lastName;

         public Employee(){}

        public Employee(int id, String firstName, String lastName) {
            super();
            this.id = id;
            this.firstName = firstName;
            this.lastName = lastName;
        }

        public String getFirstName() {
            return firstName;
        }
        public void setFirstName(String firstName) {
            this.firstName = firstName;
        }
        public String getLastName() {
            return lastName;
        }
        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        }

***Write Class: ApachePOIExcelWrite.java***

    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.Map;
    import java.util.Set;
    import java.util.TreeMap;        
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class ApachePOIExcelWrite {
                public static void main(String[] args) 
                {
                    //Blank workbook
                    XSSFWorkbook workbook = new XSSFWorkbook(); 

                    //Create a blank sheet
                    XSSFSheet sheet = workbook.createSheet("Employee Data");

                    //This data needs to be written (Object[])
                    Map<String, Object[]> data = new TreeMap<String, Object[]>();
                    data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
                    data.put("2", new Object[] {1, "Amit", "Shukla"});
                    data.put("3", new Object[] {2, "Lokesh", "Gupta"});
                    data.put("4", new Object[] {3, "John", "Adwards"});
                    data.put("5", new Object[] {4, "Brian", "Schultz"});

                    //Iterate over data and write to sheet
                    Set<String> keyset = data.keySet();
                    int rownum = 0;
                    for (String key : keyset)
                    {
                        Row row = sheet.createRow(rownum++);
                        Object [] objArr = data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr)
                        {
                           Cell cell = row.createCell(cellnum++);
                           if(obj instanceof String)
                                cell.setCellValue((String)obj);
                            else if(obj instanceof Integer)
                                cell.setCellValue((Integer)obj);
                        }
                    }
                    try
                    {
                        //Write the workbook in file system
                        FileOutputStream out = new FileOutputStream(new File("/home/ohelig/eclipse/New Microsoft Office Excel Worksheet.xlsx"));
                        workbook.write(out);
                        out.close();
                        System.out.println("Write Successfully.");
                    } 
                    catch (Exception e) 
                    {
                        e.printStackTrace();
                    }
                }
    }

***Update Class: UpdateExcel.java***

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UpdateExcel {

    public static void main(String[] args) {
        XSSFWorkbook workbook=null;
        XSSFSheet sheet;
        try{
        FileInputStream file = new FileInputStream(new File("/home/ohelig/eclipse/New Excel Worksheet.xlsx"));

          //Create Workbook instance holding reference to .xlsx file
          workbook = new XSSFWorkbook(file);

          //Get first/desired sheet from the workbook
          //Most of people make mistake by making new sheet by looking in tutorial
          sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());

          Employee ess = new Employee(6,"Yanish","Pradhananga");
          //Get the count in sheet
          int rowCount = sheet.getLastRowNum()+1;
          Row empRow = sheet.createRow(rowCount);
          System.out.println();
          Cell c1 = empRow.createCell(0);
          c1.setCellValue(ess.getId());
          Cell c2 = empRow.createCell(1);
          c2.setCellValue(ess.getFirstName());
          Cell c3 = empRow.createCell(2);
          c3.setCellValue(ess.getLastName());
          }
        catch (Exception e) 
        {
            e.printStackTrace();
        }
         try
          {
              //Write the workbook in file system
              FileOutputStream out = new FileOutputStream(new 
                  File("/home/ohelig/eclipse/New Excel Worksheet.xlsx"));
              workbook.write(out);
              out.close();
              System.out.println("Update Successfully");
          } 
          catch (Exception e) 
          {
              e.printStackTrace();
          }
      }
}

- Yanish Pradhananga