执行我的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文件内容 XSSFWorkbook Java对象的所有更改都不会影响原始的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).
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 会用 all 原始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的索引进行编号(这就是您的代码从头开始的原因填充从索引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的索引的任何行.如果此方法返回 null ，则您所要求的行从未使用过，您必须使用方法
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的索引的任何单元格.如果此方法返回 null ，则您要使用的单元格从未使用过，您必须使用方法
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#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屋！
