如何使用 Java 从 Excel (XSLX) 构建 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6572156/
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 frame XML from Excel (XSLX) using Java
提问by user301016
I am reading an excel (XSLX) file using Java in netbeans 7.0. I am able to read the excel sheet contents and print it to output too.
我正在 netbeans 7.0 中使用 Java 读取 excel (XSLX) 文件。我能够阅读 Excel 表格内容并将其打印输出。
Now I have to convert the excel data into XML file. The tags will be the column headers and each row goes into the corresponding tags.
现在我必须将 excel 数据转换为 XML 文件。标签将是列标题，每一行都进入相应的标签。
This is the input worksheet in a xslx file. The ID, Variable, desc and notes are the column headers.
这是 xslx 文件中的输入工作表。ID、变量、描述和注释是列标题。
ID Variable Desc Notes B0001 VSI_C B0001 1 VSI_C_R B0001 2 VSI_C_P B0002 VSI_C_L B0003 VSI_C_H B0004 VSI_C_O
ID Variable Desc Notes B0001 VSI_C B0001 1 VSI_C_R B0001 2 VSI_C_P B0002 VSI_C_L B0003 VSI_C_H B0004 VSI_C_O
Now, I am converting this data into an XML file. The output I am expecting is,
现在，我正在将此数据转换为 XML 文件。我期待的输出是，
<?xml version="1.0" encoding="UTF-8"?> <Bin_code> <DCT> <ID>B0001</ID> <Variable/> <Desc>VSI_C</Desc> <Notes/> </DCT> <DCT> <ID>B0001</ID> <Variable/> <Desc>VSI_C_R</Desc> <Notes/> </DCT> ............ ............... </Bin_code>
<?xml version="1.0" encoding="UTF-8"?> <Bin_code> <DCT> <ID>B0001</ID> <Variable/> <Desc>VSI_C</Desc> <Notes/> </DCT> <DCT> <ID>B0001</ID> <Variable/> <Desc>VSI_C_R</Desc> <Notes/> </DCT> ............ ............... </Bin_code>
I tried until this. I know i have to use 'sheet' object. But I am not sure, how to use this.
我一直试到这个。我知道我必须使用“工作表”对象。但我不确定，如何使用它。
import java.io.File;
导入 java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.*;
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.ss.usermodel.WorkbookFactory;
public class XSLXReader {
public static void main(String[] args)
{
DataInputStream in = null;
BufferedReader br = null;
FileWriter fostream;
FileWriter fostreamBatch;
BufferedWriter out = null;
BufferedWriter outBatch = null;
String strOutputPath = "D:\Proj\Current_\";
String strFilePrefix = "Master 5.2-B";
String strLine;
try {
InputStream inputStream = new FileInputStream(new File("D:\Proj\Current_\Master A-B.xlsx"));
Workbook wb = WorkbookFactory.create(inputStream);
Sheet sheet = wb.getSheet("Bin-code");
in = new DataInputStream(inputStream);
br = new BufferedReader(new InputStreamReader(in));
fostream = new FileWriter(strOutputPath+"\"+strFilePrefix+".xml");
out = new BufferedWriter(fostream);
out.write("<Bin-code>");
while ((strLine = br.readLine()) != null)
{
out.write("<DCT>");
out.write("<ID>" + strLine.substring(1, strLine.length()) + "</ID>");
out.write("</DCT>");
}
out.write("</Bin-code>");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Please help me in framing out the input data in the xslx to the output data into xml as shown above.
请帮助我将 xslx 中的输入数据框定为 xml 中的输出数据，如上所示。
Thanks Ramm
谢谢拉姆
回答by g051051
You shouldn't use the InputStream to read the file directly. Once you've opened the workbook and accessed the Sheet, you can just iterate over all the rows in the sheet, then iterate the cells in the row. Example is on the Apache POI site at http://poi.apache.org/spreadsheet/quick-guide.html#Iterator. Then you can either print out your XML by hand like you're doing, or use one of the many XML libraries and a DTD to do it for you.
您不应该使用 InputStream 直接读取文件。打开工作簿并访问工作表后，您可以遍历工作表中的所有行，然后遍历行中的单元格。示例位于http://poi.apache.org/spreadsheet/quick-guide.html#Iterator的 Apache POI 站点上。然后，您可以像正在执行的操作一样手动打印出您的 XML，或者使用众多 XML 库之一和 DTD 来为您完成。
Here's the complete source that works:
这是有效的完整来源：
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import java.text.*;
public class XSLXReader {
static DecimalFormat df = new DecimalFormat("#####0");
public static void main(String[] args) {
FileWriter fostream;
PrintWriter out = null;
String strOutputPath = "D:\Proj\Current_\";
String strFilePrefix = "Master 5.2-B";
try {
InputStream inputStream = new FileInputStream(new File("D:\Proj\Current_\Master A-B.xlsx"));
Workbook wb = WorkbookFactory.create(inputStream);
Sheet sheet = wb.getSheet("Bin-code");
fostream = new FileWriter(strOutputPath + "\" + strFilePrefix+ ".xml");
out = new PrintWriter(new BufferedWriter(fostream));
out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
out.println("<Bin-code>");
boolean firstRow = true;
for (Row row : sheet) {
if (firstRow == true) {
firstRow = false;
continue;
}
out.println("\t<DCT>");
out.println(formatElement("\t\t", "ID", formatCell(row.getCell(0))));
out.println(formatElement("\t\t", "Variable", formatCell(row.getCell(1))));
out.println(formatElement("\t\t", "Desc", formatCell(row.getCell(2))));
out.println(formatElement("\t\t", "Notes", formatCell(row.getCell(3))));
out.println("\t</DCT>");
}
out.write("</Bin-code>");
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static String formatCell(Cell cell)
{
if (cell == null) {
return "";
}
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case Cell.CELL_TYPE_ERROR:
return "*error*";
case Cell.CELL_TYPE_NUMERIC:
return XSLXReader.df.format(cell.getNumericCellValue());
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "<unknown value>";
}
}
private static String formatElement(String prefix, String tag, String value) {
StringBuilder sb = new StringBuilder(prefix);
sb.append("<");
sb.append(tag);
if (value != null && value.length() > 0) {
sb.append(">");
sb.append(value);
sb.append("</");
sb.append(tag);
sb.append(">");
} else {
sb.append("/>");
}
return sb.toString();
}
}
This produces output:
这会产生输出：
<?xml version="1.0" encoding="UTF-8"?>
<Bin-code>
<DCT>
<ID>B0001</ID>
<Variable/>
<Desc>VSI_C</Desc>
<Notes/>
</DCT>
<DCT>
<ID>B0001</ID>
<Variable>1</Variable>
<Desc>VSI_C_R</Desc>
<Notes/>
</DCT>
<DCT>
<ID>B0001</ID>
<Variable>2</Variable>
<Desc>VSI_C_P</Desc>
<Notes/>
</DCT>
<DCT>
<ID>B0002</ID>
<Variable/>
<Desc>VSI_C_L</Desc>
<Notes/>
</DCT>
<DCT>
<ID>B0003</ID>
<Variable/>
<Desc>VSI_C_H</Desc>
<Notes/>
</DCT>
<DCT>
<ID>B0004</ID>
<Variable/>
<Desc>VSI_C_O</Desc>
<Notes/>
</DCT>
</Bin-code>
回答by Piyush Vinchurkar
import java.io.*;
导入 java.io.*;
import org.apache.poi.ss.usermodel.*;
导入 org.apache.poi.ss.usermodel.*;
import java.text.*;
导入 java.text.*;
public class XSLXReader {
公共类 XSLXReader {
static DecimalFormat df = new DecimalFormat("#####0");
public static void main(String[] args) {
FileWriter fostream;
PrintWriter out = null;
String strOutputPath = "C://Users//853053//Downloads//poi-bin-3.17-20170915.tar";
// String strFilePrefix = "Master 5.2-B";
try {
InputStream inputStream = new FileInputStream(new File("C://Users//853053//Downloads//poi-bin-3.17-20170915.tar//Master 5.2-B.xls"));
Workbook wb = WorkbookFactory.create(inputStream);
Sheet sheet = wb.getSheet("Sheet1");
// fostream = new FileWriter(strOutputPath + "\" + strFilePrefix+
// ".xml");
// out = new PrintWriter(new BufferedWriter(fostream));
System.out.println("try");
boolean firstRow = true;
for (Row row : sheet) {
if (firstRow == true) {
firstRow = false;
continue;
}
fostream = new FileWriter(new File(strOutputPath
+ File.separator + formatCell(row.getCell(4)) + ".xml"));
System.out.println("try1" + strOutputPath + File.separator
+ formatCell(row.getCell(4)) + ".xml");
out = new PrintWriter(new BufferedWriter(fostream));
out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
out.println("<Bin-code>");
out.println("\t<DCT>");
out.println(formatElement("\t\t", "ID",
formatCell(row.getCell(0))));
out.println(formatElement("\t\t", "Variable",
formatCell(row.getCell(1))));
out.println(formatElement("\t\t", "Desc",
formatCell(row.getCell(2))));
out.println(formatElement("\t\t", "Notes",
formatCell(row.getCell(3))));
out.println(formatElement("\t\t", "txn_id",
formatCell(row.getCell(4))));
out.println("\t</DCT>");
out.write("</Bin-code>");
out.flush();
out.close();
}
// out.write("</Bin-code>");
// out.flush();
// out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings("deprecation")
private static String formatCell(Cell cell)
{
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case Cell.CELL_TYPE_ERROR:
return "*error*";
case Cell.CELL_TYPE_NUMERIC:
return XSLXReader.df.format(cell.getNumericCellValue());
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "<unknown value>";
}
}
private static String formatElement(String prefix, String tag, String value) {
StringBuilder sb = new StringBuilder(prefix);
sb.append("<");
sb.append(tag);
if (value != null && value.length() > 0) {
sb.append(">");
sb.append(value);
sb.append("</");
sb.append(tag);
sb.append(">");
} else {
sb.append(">");
sb.append("<");
sb.append(tag);
sb.append("/>");
}
return sb.toString();
}
}
}
