如何使用java填写Excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21116189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fill in Excel file using java
提问by Muratcan
I have the following code to fill in the Excel file, with information that I get from the Internet using Jsoup.
我有以下代码来填写 Excel 文件，其中包含我使用 Jsoup 从 Internet 获取的信息。
package knvbj;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.TextNode;
import org.jsoup.select.Elements;
public class KNVBJ {
private static int Clnummer=1;
public static void main(String[] args) throws IOException {
FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubInformation.xlsx");
List<String> urlList = ReadXlsx.readXlsx();
urlList.get(1);
for (String url : urlList) {
System.out.println("url: " + url);
}
for (int i = 0; i < urlList.size(); i++) {
Document doc = Jsoup.connect(urlList.get(i))
.data("query", "Java")
.userAgent("Mozilla")
.cookie("auth", "token")
.timeout(3000)
.post();
Element content1 = doc.getElementsByClass("details").first();
String body = content1.toString();
Document docb = Jsoup.parseBodyFragment(body);
Element bbd = docb.body();
String kkj = bbd.toString();
Document finalDocument = Jsoup.parse(kkj);
Element ClubName = finalDocument.getElementsByClass("title").first();
String NameOfClub = ClubName.text();
System.out.println(NameOfClub);
Element Adres = finalDocument.getElementsByClass("text").get(1);
String[] addressParts = Adres.html().split("<br />");
String SplitString;
String PlaatsName;
String Straat;
String telNo;
String Accommodatie;
String Postcode;
Accommodatie = addressParts[0].trim();
Straat = addressParts[1].trim();
SplitString = addressParts[2].trim();
telNo = addressParts[3].trim();
String splitted[]= SplitString.split(" ");
Postcode = splitted[0];
PlaatsName = splitted[1];
System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);
Elements anchors = finalDocument.getElementsByTag("a");
String email = anchors.get(1).text();
String fname = "/Users/muratcanpinar/Downloads/KNVBJ/src/knvbj/Voetbalclubs.xlsx";
InputStream inp = new FileInputStream(fname);
Workbook wb = new XSSFWorkbook(inp);
Sheet sheet = wb.getSheetAt(0);
Row r1 = sheet.getRow(0);
r1.createCell(Clnummer++).setCellValue(NameOfClub);
r1.createCell(Clnummer++).setCellValue(Accommodatie);
r1.createCell(Clnummer++).setCellValue(Straat);
r1.createCell(Clnummer++).setCellValue(Postcode);
r1.createCell(Clnummer++).setCellValue(PlaatsName);
r1.createCell(Clnummer++).setCellValue(telNo);
r1.createCell(Clnummer++).setCellValue(email);
wb.write(out);
}
out.close();
}
}
With this above code i can just fill one row, en then a get this error
使用上面的代码，我可以只填充一行，然后得到这个错误
Exception in thread "main" org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : The part /docProps/app.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@f46fdc1
at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:479)
at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1414)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:179)
at knvbj.KNVBJ.main(KNVBJ.java:101)
Caused by: org.apache.poi.openxml4j.exceptions.OpenXML4JException: The part /docProps/app.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@f46fdc1
at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:470)
... 3 more
Java Result: 1
Can somebody tell me what I am doing four? Thanks a lot.
有人能告诉我我在做什么四吗？非常感谢。
回答by eckes
Make a test program of the last 13 lines using fixed sane values. If this fails as well the problem is most likely the Input Template. If this works the problem are the values you get from Soup. Print them so see if there are any strange values.
使用固定的合理值制作最后 13 行的测试程序。如果这也失败了，则问题很可能是输入模板。如果这有效，问题是您从 Soup 获得的值。打印它们，看看是否有任何奇怪的值。
Posting only the smaller 13 lines program will also increase the chance of getting answers. And of course you can try to use another Voetbalclubs.xlsx file for fun, too.
仅发布较小的 13 行程序也会增加获得答案的机会。当然，您也可以尝试使用另一个 Voetbalclubs.xlsx 文件来获得乐趣。
回答by Hirak
In the current code, you are trying to write the ClubInformation.xlsxinto one sheet of Voetbalclubs.xlsx. Thus it is giving an error. (xlsx is a xml format, thus you get error while writing /docProps/app.xml).
在当前代码中，您试图将 写入ClubInformation.xlsx一张Voetbalclubs.xlsx. 因此它给出了一个错误。（xlsx 是 xml 格式，因此在编写时会出错/docProps/app.xml）。
I have modified your code as below. Change the line List<String> urlList = Arrays.asList("http://google.com");as per your need.
Let me know if this works
我已经修改了你的代码如下。List<String> urlList = Arrays.asList("http://google.com");根据您的需要更改线路 。让我知道这个是否奏效
public class KNVBJ {
private static int Clnummer=1;
public static void main(String[] args) throws IOException {
FileOutputStream out = new FileOutputStream("ClubInformation.xlsx");
List<String> urlList = Arrays.asList("http://google.com");
urlList.get(0);
for (String url : urlList) {
System.out.println("url: " + url);
}
String fname = "Voetbalclubs.xlsx";
FileOutputStream output = new FileOutputStream(fname);
for (int i = 0; i < urlList.size(); i++) {
Document doc = Jsoup.connect(urlList.get(i))
.data("query", "Java")
.userAgent("Mozilla")
.cookie("auth", "token")
.timeout(3000)
.post();
Element content1 = doc.getElementsByClass("details").first();
String body = content1.toString();
Document docb = Jsoup.parseBodyFragment(body);
Element bbd = docb.body();
String kkj = bbd.toString();
Document finalDocument = Jsoup.parse(kkj);
Element ClubName = finalDocument.getElementsByClass("title").first();
String NameOfClub = ClubName.text();
System.out.println(NameOfClub);
Element Adres = finalDocument.getElementsByClass("text").get(1);
String[] addressParts = Adres.html().split("<br />");
String SplitString;
String PlaatsName;
String Straat;
String telNo;
String Accommodatie;
String Postcode;
Accommodatie = addressParts[0].trim();
Straat = addressParts[1].trim();
SplitString = addressParts[2].trim();
telNo = addressParts[3].trim();
String splitted[]= SplitString.split(" ");
Postcode = splitted[0];
PlaatsName = splitted[1];
System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);
org.jsoup.select.Elements anchors = finalDocument.getElementsByTag("a");
String email = anchors.get(1).text();
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.getSheetAt(0);
Row r1 = sheet.getRow(0);
r1.createCell(Clnummer++).setCellValue(NameOfClub);
r1.createCell(Clnummer++).setCellValue(Accommodatie);
r1.createCell(Clnummer++).setCellValue(Straat);
r1.createCell(Clnummer++).setCellValue(Postcode);
r1.createCell(Clnummer++).setCellValue(PlaatsName);
r1.createCell(Clnummer++).setCellValue(telNo);
r1.createCell(Clnummer++).setCellValue(email);
wb.write(output);
}
out.close();
}
}
回答by David Williams
For me this appears to have been caused by a timeout in AWS, closing the output stream. the error message is not helpful and misleading. That's the best I could come up with so far from available info.
对我来说，这似乎是由 AWS 中的超时导致的，关闭了输出流。错误消息没有帮助和误导性。这是迄今为止我从可用信息中所能想到的最好的方法。
回答by flup
First: It's better to start with a working example and work your way from there. So start with the sample code that writes a simple string to a single cell a new sheet, then write to an existing sheet on a local filesystem, and only then write data you've parsed from the web. This way, when you run into problems, you've a better idea where to look for a solution.
第一：最好从一个工作示例开始，然后从那里开始。因此，从示例代码开始，将一个简单的字符串写入一个新工作表的单个单元格，然后写入本地文件系统上的现有工作表，然后才写入您从 Web 解析的数据。这样，当您遇到问题时，您就可以更好地了解去哪里寻找解决方案。
The exception you're listing is the generic exception that gets thrown by ZipPackagewhen saving fails:
您列出的异常是ZipPackage在保存失败时抛出的通用异常：
if (!defaultPartMarshaller.marshall(part, zos))
throw new OpenXML4JException("The part " + part.getPartName().getURI()
+ " fail to be saved in the stream with marshaller " + defaultPartMarshaller);
So the marshallmethod on the defaultPartMarshaller returns false and the internal exception which is the cause of the failure is lost.
The DefaultMarshaller does not do much, it simply asks the part to save itself to the OutputStream.
因此marshalldefaultPartMarshaller 上的方法返回 false 并且导致失败的内部异常丢失。DefaultMarshaller 没有做太多事情，它只是要求部件将自身保存到 OutputStream。
From there it gets a little less certain what kind of PackagePart is being saved. But for instance the ZipPartMarshallercatches any exceptions that occur and logs them before returning false:
从那里开始，不太确定正在保存哪种 PackagePart。但是，例如ZipPartMarshaller捕获发生的任何异常并在返回 false 之前记录它们：
try {
...
} catch (IOException ioe) {
logger.log(POILogger.ERROR,"Cannot write: " + part.getPartName() + ": in ZIP",
ioe);
return false;
}
So could you take a look at the rest of the output, see if any more relevant info gets logged before this exception?
那么您能否查看输出的其余部分，看看在此异常之前是否记录了更多相关信息？
If you cannot find more relevant logging, this is quite normal cause by default, the logger is a NullLogger which doesn't log a thing. Could you set the runtime property org.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger(for example by starting java with command line argument -Dorg.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger) and see if this produces more logging?
如果您找不到更多相关的日志记录，默认情况下这是很正常的原因，记录器是一个 NullLogger，它不记录任何内容。您能否设置运行时属性org.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger（例如通过使用命令行参数启动 java -Dorg.apache.poi.util.POILogger=org.apache.poi.util.SystemOutLogger）并查看这是否会产生更多日志记录？
回答by Jonathan Drapeau
The problem lies in your FileOutputStreamvariable outbeing used more than once for the same Workbook. Opening and closing the FileOutputStreamoutwithin the loop fix your exception. POI, and/or the xml/zip library, don't like to use the same stream more than once.
问题在于您的FileOutputStream变量out被多次用于同一个Workbook. FileOutputStreamout在循环内打开和关闭可修复您的异常。POI 和/或 xml/zip 库不喜欢多次使用相同的流。
If you use the same code you had with 1 loop, it works, with 2, it will crashes with the exception you have.
如果您使用与 1 个循环相同的代码，则它可以工作，如果使用 2，它会因您的异常而崩溃。
Here's a quick fix with a simple code to replace what the JSoup code did :
这是一个快速修复，用一个简单的代码来替换 JSoup 代码所做的事情：
private static int Clnummer = 1;
public static void main(String[] args) throws IOException {
for (int i = 0; i < 2; i++) {
FileOutputStream out = new FileOutputStream("yourfilePath");
String NameOfClub = "Potaoes club";
System.out.println(NameOfClub);
String PlaatsName;
String Straat;
String telNo;
String Accommodatie;
String Postcode;
Accommodatie = "123";
Straat = "Potatoes club street";
telNo = "123456789";
Postcode = "P0P0P0";
PlaatsName = "PotatoCity";
String email = "[email protected]";
String fname = "guessing this is a template file";
InputStream inp = new FileInputStream(fname);
Workbook wb = new XSSFWorkbook(inp);
Sheet sheet = wb.getSheetAt(0);
Row r1 = sheet.getRow(0);
r1.createCell(Clnummer++).setCellValue(NameOfClub);
r1.createCell(Clnummer++).setCellValue(Accommodatie);
r1.createCell(Clnummer++).setCellValue(Straat);
r1.createCell(Clnummer++).setCellValue(Postcode);
r1.createCell(Clnummer++).setCellValue(PlaatsName);
r1.createCell(Clnummer++).setCellValue(telNo);
r1.createCell(Clnummer++).setCellValue(email);
wb.write(out);
out.close();
}
}
}
回答by hfontanez
I was getting a similar error when the file used to create the output stream already had data. If you are looking to appenddata to the file, you must indicate so in the file output stream object:
当用于创建输出流的文件已经有数据时，我遇到了类似的错误。如果您希望将数据附加到文件，则必须在文件输出流对象中指明：
FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubInformation.xlsx", true);
When you do this, wb.write(out)should work as expected.
当你这样做时，wb.write(out)应该按预期工作。
回答by user5443819
package knvbj;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.TextNode;
import org.jsoup.select.Elements;
public class KNVBJ {
private static int Clnummer=1;
public static void main(String[] args) throws IOException {
List<String> urlList = ReadXlsx.readXlsx();
urlList.get(1);
for (String url : urlList) {
System.out.println("url: " + url);
}
for (int i = 0; i < urlList.size(); i++) {
Document doc = Jsoup.connect(urlList.get(i))
.data("query", "Java")
.userAgent("Mozilla")
.cookie("auth", "token")
.timeout(3000)
.post();
Element content1 = doc.getElementsByClass("details").first();
String body = content1.toString();
Document docb = Jsoup.parseBodyFragment(body);
Element bbd = docb.body();
String kkj = bbd.toString();
Document finalDocument = Jsoup.parse(kkj);
Element ClubName = finalDocument.getElementsByClass("title").first();
String NameOfClub = ClubName.text();
System.out.println(NameOfClub);
Element Adres = finalDocument.getElementsByClass("text").get(1);
String[] addressParts = Adres.html().split("<br />");
String SplitString;
String PlaatsName;
String Straat;
String telNo;
String Accommodatie;
String Postcode;
Accommodatie = addressParts[0].trim();
Straat = addressParts[1].trim();
SplitString = addressParts[2].trim();
telNo = addressParts[3].trim();
String splitted[]= SplitString.split(" ");
Postcode = splitted[0];
PlaatsName = splitted[1];
System.out.println(Accommodatie + " " + Straat + " " + " postcode " + Postcode + " Plaatsname " + PlaatsName+ " "+ telNo);
Elements anchors = finalDocument.getElementsByTag("a");
String email = anchors.get(1).text();
String fname = "/Users/muratcanpinar/Downloads/KNVBJ/src/knvbj/Voetbalclubs.xlsx";
InputStream inp = new FileInputStream(fname);
Workbook wb = new XSSFWorkbook(inp);
Sheet sheet = wb.getSheetAt(0);
Row r1 = sheet.getRow(0);
r1.createCell(Clnummer++).setCellValue(NameOfClub);
r1.createCell(Clnummer++).setCellValue(Accommodatie);
r1.createCell(Clnummer++).setCellValue(Straat);
r1.createCell(Clnummer++).setCellValue(Postcode);
r1.createCell(Clnummer++).setCellValue(PlaatsName);
r1.createCell(Clnummer++).setCellValue(telNo);
r1.createCell(Clnummer++).setCellValue(email);
}
FileOutputStream out = new FileOutputStream("/Users/muratcanpinar/Downloads/KNVBJ/build/classes/knvbj/ClubI nformation.xlsx",true);
wb.write(out);
out.close();
}
}
you need to create output Stream after you created all cells,then write them to the file. see the codes in details.
创建所有单元格后，您需要创建输出流，然后将它们写入文件。详见代码。
回答by sagar
Write below sentence outside for loop
在for循环外写下面的句子
wb.write(out);
wb.write(out);
