执行我的 java apache poi 程序后无法打开 Excel 文件，我正在使用文件输出流 [英] Unable to open Excel file after excecuting my java apache poi program and i am using file output stream

问题描述

我正在使用 Apache poi 将 Mysql 数据提取到 Excel 文件中.代码运行正常，但是当我尝试打开 excel 文件时，它显示错误.

I am using Apache poi to extract Mysql data to an Excel file. The code is running correctly but when I am trying to open the excel file it is showing error.

package com.telkomsel.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.telkomsel.configuirator.Configurator;
import com.telkomsel.dbconnection.DBConnection;
import com.telkomsel.service.TelkomselEntities;

public class TelkomselExcel {

    DBConnection db = new DBConnection();
    static Configurator configurator = null;
    Connection conn = null;
    static Statement statement = null;
    static ResultSet resultSet = null;

    public static HashMap<Integer, TelkomselEntities> getTelkomselData(Statement statement) {

        configurator = new Configurator();
        String Query = configurator.getProperty("sql_query1");

        HashMap<Integer, TelkomselEntities> all = null;
        TelkomselEntities smsModel = null;

        try {
            all = new HashMap<Integer, TelkomselEntities>();
            resultSet = statement.executeQuery(Query);
            while (resultSet.next()) {

                int hour = resultSet.getInt("hour(timestamp)");
                String count = resultSet.getString("count(1)");

                smsModel = new TelkomselEntities(hour, count, count, count);
                all.put(hour, smsModel);
            }

            smsModel = new TelkomselEntities();

            FileInputStream fis = new FileInputStream(new File("Tracker.xlsx"));

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet worksheet = workbook.getSheetAt(0);

            XSSFRow row = null;
            XSSFCell cell;
            int i = 1;

            for (Integer l : all.keySet()) {
                TelkomselEntities us = all.get(l);

                row = worksheet.createRow(i);

                cell = row.createCell(2);
                cell.setCellValue(us.getHour());
                cell = row.createCell(3);
                cell.setCellValue(us.getCharge_Count());

                i++;
            

            }
            fis.close();
            FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),true);
            
            
            
            System.out.println("SUCCESS");
            
            workbook.write(output_file);
            workbook.close();
            output_file.flush();

            output_file.close();
            

        } catch (Exception e) {

            System.out.println(e);
        }
        return all;

    }

}

我认为文件输出流在将数据转换为字节码时会产生问题.我尝试了一切，但没有用.我的 excel 文件不起作用

I think file output stream is creating problem as it converts data into byte codes. i tried every thing but doesn't work. my excel file is not working

推荐答案

如你所料，问题隐藏在行内:

As you supposed, the problem hides inside the line:

FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),true);

当从现有的 excel(您要更新)创建新的 XSSFWorkbook Java 对象时，该 XSSFWorkbook 最初是根据您的 excel 文件内容创建的，那么它完全独立于它.证明是对XSSFWorkbookJava对象的所有更改都不会影响原始的excel文件.Apache Poi 就是这样工作的！

When creating a new XSSFWorkbook Java Object from an existing excel (which you want to update), that XSSFWorkbook is initially created based on your excel file content, then it is totally independent from it.The proof of this is that all changes to the XSSFWorkbook Java Object ARE NOT going to affect the original excel file at all. Apache Poi works that way!

这就是为什么一旦你完成编辑你的 XSSFWorkbook 你必须将它保存为一个新的 excel 文件(使用 FileOutputStream)覆盖 原始文件(从某种意义上说，您现在用所有更改更新您的 excel 文件).

This is the reason why once you're done editing your XSSFWorkbook you have to save it as a new excel file (using a FileOutputStream) overriding the original one (in a sense, you're now updating your excel file with all your changes).

但是作为 文档 说，你告诉 FileOutputStream 不要用新的和更新的文件覆盖原始的 excel 文件 但是 将第二个附加到第一个， upsi dupsi！您正在创建一个文件，其中包含原始旧文件的所有字节和新更新文件的所有字节！

But as the docs says, you're telling FileOutputStream not to override the original excel file with the new and updated one but to append the second to the first one, upsi dupsi! You're creating a single file which contains both all the bytes of the original old file and all the bytes of the new updated one!

要解决问题，只需改用:

To solve the problem, just use instead:

FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),false);

或

FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"));

大功告成！

看来您使用 FileOutputStream 是错误的，因为您不知道 Apache Poi 是如何工作的以及如何使用它.在使用它之前，您可能想对其进行一些研究，网络上有完整的示例和教程！他们在这里 Apache Poi 本身提供的一些示例，您可能想看看它们.

It seems that you're using FileOutputStream wrong because you don't know how Apache Poi works and how to use it. You might want to study a little bit about it before using it, the web is full of examples and tutorials! Here they are some examples provided by Apache Poi itself, you might want to have a look at them.

正如我之前所说的，XSSFWorkbook 是用原始 excel 文件的所有内容初始化的.因此，如果您从第二行开始填充 XSSFSheet(这就是您实际对代码所做的事情)，您实际上是在要求您的 XSSFWorkbook 用新的数据覆盖现有数据一个.

As I said before, the XSSFWorkbook is initialized with all the content of your original excel file. So if you start filling your XSSFSheet from the second line (that's what you're actually doing with your code) you are literally asking to your XSSFWorkbook to override existing data with new one.

您必须改进您的代码，搜索行和单元格中已有的数据，如果您不想，不要覆盖它.

XSSFWorkbook 的每个 XSSFSheet 的行和单元格使用 0-based 索引编号(这就是为什么您的代码开始从索引 1 开始填充行，从 第二个 开始填充行).

Rows and cells of each XSSFSheet of your XSSFWorkbook are numbered using 0-based indexes (that's the reason why your code, which starts filling rows from index 1, is filling rows starting from the second one).

使用方法 XSSFSheet#getRow(int rownum) 您可以从当前 XSSFSheet 中检索任何行，指示其 0-based 索引.如果此方法返回 null，那么您要求的行从未被使用过，您必须使用方法 XSSFSheet#createRow(int rownum).如果没有，那么您要求的行已经被使用，并且在其某些单元格中包含一些数据.

With the method XSSFSheet#getRow(int rownum) you can retreive any row from the current XSSFSheet indicating its 0-based index. If this method returns null, then the row you're asking for has never been used and you have to create it using the method XSSFSheet#createRow(int rownum). If it doesn't, then the row you're asking for has already been used and contains some data in some of its cells.

使用方法 XSSFRow#getCell(int cellnum) 您可以从当前 XSSFRow 中检索任何单元格，指示其 0-based 索引.如果此方法返回 null，那么您要求的单元格从未被使用过，您必须使用方法 XSSFRow#createCell(int cellnum, CellType celltype)).如果没有，那么您要求的单元格已经被使用并且其中包含一些数据.

With the method XSSFRow#getCell(int cellnum) you can retrieve any cell from the current XSSFRow indicating its 0-based index. If this method returns null, then the cell you're asking for has never been used and you have to create it using the method XSSFRow#createCell(int cellnum, CellType celltype). If it doesn't, then the cell you're asking for has already been used and contains some data in it.

您可以使用方法 XSSFCell#getCellType().

You can retrieve the CellType of an existing XSSFCell with the method XSSFCell#getCellType().

您可以使用诸如 XSSFCell#getStringCellValue(), XSSFCell#getNumericCellValue() 或 XSSFCell#getBooleanCellValue().

You can retreive the content of an existing XSSFCell (on the basis of its CellType) using such methods as XSSFCell#getStringCellValue(), XSSFCell#getNumericCellValue() or XSSFCell#getBooleanCellValue().

其他有用的方法是XSSFSheet#getLastRowNum() 和 XSSFRow#getLastCellNum().第一个返回工作表内最后一个已经使用的行的索引，第二个返回您行内第一个未使用的单元格的索引.

Other useful methods are XSSFSheet#getLastRowNum() and XSSFRow#getLastCellNum(). The first one returns the index of the last already used row inside your sheet, the second one returns the index of the first not used cell inside your row.

这是您的示例(在最后一个现有工作表之后填充工作表的 42 行):

Here it is an example for you (filling 42 rows of your sheet after the last existing one):

public static void main(String[] args) throws EncryptedDocumentException, FileNotFoundException, IOException {
    
        // Step 1: load your excel file as a Workbook
    
        String excelFilePath = "D:\\Desktop\\textExcel.xlsx";
        XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(excelFilePath));
        
        // Step 2: modify your Workbook as you prefer
        
        XSSFSheet sheet = workbook.getSheetAt(0);
        int firstUnusedRowIndex = sheet.getLastRowNum() + 1;
        for (int rowIndex = firstUnusedRowIndex ; rowIndex < firstUnusedRowIndex + 42 ; rowIndex++) {
            sheet.createRow(rowIndex).createCell(0, CellType.STRING).setCellValue("New Row n°" + (rowIndex - firstUnusedRowIndex + 1));
        }
        
        // Step 3: update the original excel file
        
        FileOutputStream outputStream = new FileOutputStream(excelFilePath);
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
}

这篇关于执行我的 java apache poi 程序后无法打开 Excel 文件，我正在使用文件输出流的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！