java - 如果不存在则创建一个新的 .xls 文件，如果存在则修改它 [Apache POI]

Question

我正在使用 Apache POI 创建和修改 Excel 文件。我希望我的程序创建一个具有给定名称的新 .xls 文件(如果目录中不存在该文件)。但是，如果它存在，我希望我的程序只打开该 .xls 文件并修改它。

我的程序能够创建新的 .xls 文件，但每当我尝试将文件作为 HSSFWorkbook 打开时，程序都会发出错误，指出“ header 签名无效；读取 0x0000000000000000，预期为 0xE11AB1A1E011CFD0 - 您的文件似乎不是有效的 OLE2 文档”

我用来创建新文件的代码是:

         File excelFile = new File("Test.xls");
         excelFile.createNewFile();
         wb = new HSSFWorkbook(new FileInputStream(excelFile.getName())); // Throws the error right here [wb is an HSSFWorkbook object from the Apache POI Library]

请注意，如果目录中已存在具有该名称的 .xls 文件，并且我仅使用 FileOutputStream 对象，它会成功将所有必需的数据写入文件中。

我用来在 Excel 文件中编写的代码是:

        FileOutputStream fileout = new FileOutputStream("Test.xls", true);
        wb.write(fileout);
        fileout.close();
        wb.close(); // wb is an HSSFWorkbook object from the Apache POI Library

带有完整堆栈跟踪的错误消息是:

org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header
signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:162) at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:112) at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.(NPOIFSFileSystem.java:302) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:393) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:374) at prototpye_2.FrameJScraperTool.processWorkbook(FrameJScraperTool.java:224) at prototpye_2.FrameJScraperTool.run(FrameJScraperTool.java:54) at prototpye_2.JFrameTest$Open.actionPerformed(JFrameTest.java:123) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.AbstractButton.doClick(Unknown Source) at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source) at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source) at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEventImpl(Unknown Source) at java.awt.EventQueue.access$500(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source)

此外，当生成此错误时，目录中会创建一个 .xls 文件，但其大小为 0 KB，如果我们尝试打开该文件，它会提示该文件已损坏。 Excel 中出现以下提示:

当您尝试打开生成的 .xls 时，Excel 中会出现提示:

让我知道是否有更好的方法来实现我想要做的事情，即如果 .xls 文件不存在，则创建它，然后写入其中，但如果它确实存在，则只需访问它并写入其中的数据。

感谢任何形式的帮助

谢谢

Answer 1

创建新的 Excel .xls 文件:

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FuSsA sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value
cell.setCellValue("Slim Shady");
    try {
        FileOutputStream out = 
                new FileOutputStream(new File("C:\\new.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

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

更新现有 Excel 文件(.xls 或 .xlsx)

try {
            File file = new File"C:\\update.xls");
            Workbook workbook = WorkbookFactory.create(file);
            Sheet 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);
            cell = sheet.getRow(3).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 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();
        }

有关更多详细信息，请检查此问题:Read / Write different Microsoft Office file formats using Apache POI