维护从 XML 到 Excel 文件的层次结构 - Java [英] Maintaining hierarchy from XML to Excel file - Java
问题描述
我有一个包含嵌套记录的 XML 文件.我必须从文件中获取记录并写入 Excel 文件.现在，我生成的文件是一个平面文件.(我使用 Apache POI 写入 Excel 文件).我需要它来维护层次信息，以便嵌套记录缩进.
I have an XML file containing nested records. I have to get records from the file and write to an Excel file. Right now, the file I have produced is a flat file. (I used Apache POI to write to the Excel file). I need it to maintain the hierarchical information, such that the nested records are indented.
我的 XML 文件如下所示:
My XML file looks like:
<node>
<id>123</id>
<label>ABC</label>
<node>
<id>456</id>
<label>DEF</label>
....... so on
我当前的 Excel 看起来像:
My current Excel looks like :
我需要类似的东西(代表 XML 文件中的层次结构):
I need something like (representing the hierarchy in XML file):
有没有人有过类似的经历?我非常感谢您的帮助.
Does anyone have any experience with something like that? I would really appreciate the help.
推荐答案
如果您愿意测试 Sax Parser，我可能有一个您可以尝试的解决方案.下面是我使用的类，它包含 SAXParser 的默认处理程序以及我用来在将 xml 文件输入到 xlsx 文件之前对其进行格式化的代码.它看起来有点沉重，所以我尝试尽可能添加评论以使其易于理解.
If you are willing to test out Sax Parser I may have a solution you could try. Below is the class I have used, it contains a Default Handler for the SAXParser as well as the code I used to format the xml file before inputting it to the xlsx file. It looks a tad hefty and so I have tried to add comments wherever possible to try and make it understandable.
public class SO2 {
private SO2(File xml){
wb = new XSSFWorkbook(); //Workbook to create
sheet = wb.createSheet(); //Sheet to write to
try {
SO2.retrieveSaxParser().parse(xml, SO2.retrieveHandler()); //Begin parse
Path file = Paths.get(System.getProperty("user.home"), "Desktop", "XMLTest.xlsx"); //Where to write file
wb.write(new FileOutputStream(file.toString()));
} catch (SAXException | IOException | ParserConfigurationException e) {
JOptionPane.showMessageDialog(null, e.getMessage());
}
System.exit(0);
}
protected static void instertUpdate(String data, int columnNum, int rowNum) {//Method to add to spreadsheet
/*The below writes to the file, the row if statements are there to stop the method
* overwriting any rows already created
*/
if(row != null){
if(row.getRowNum() != rowNum){
row = sheet.createRow(rowNum);
}
} else {
row = sheet.createRow(rowNum);
}
cell = row.createCell(columnNum);//Make our cell
cell.setCellValue(data);//Write to it
}
private static SAXParser retrieveSaxParser() throws ParserConfigurationException, SAXException{
return SAXParserFactory.newInstance().newSAXParser();//Get parser
}
private static DefaultHandler retrieveHandler() {
DefaultHandler handler = new DefaultHandler(){//Handler with methods required for parsing xml
@Override
public void startElement(String uri, String localName,String qName, Attributes attributes) throws SAXException {
if(startWasPrevious == true){//Start indenting after the first element tag processed
indent++;
}
rowNumber++;//Move row down for each tag
columnNumber = indent; //Cell number set to current indent level
SO2.instertUpdate("<" + qName + ">", columnNumber, rowNumber);//Insert
startWasPrevious = true; //For formatting
previous = startTag;//For formatting
}
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
if(startWasPrevious == false){//For removal of indentation
indent--;
}
if(!previous.equals("text")){//If text wasn't last part parsed then set column to indent
columnNumber = indent;
} else{//If text was processed last move cell across
columnNumber++;
}
if(previous.equals("end")){//Move to a newline if last parsed element was an ending tag
rowNumber++;
}
if(startWasPrevious == false){ //If there was no text previously
SO2.instertUpdate("</" + qName + ">", columnNumber, rowNumber);
} else { //If there was text then this will be enclosing end tag
SO2.instertUpdate("</" + qName + ">", columnNumber, rowNumber);
}
startWasPrevious = false; //For formatting
previous = endTag;
}
@Override
public void characters(char ch[], int start, int length) throws SAXException {
String s = new String(ch, start, length).trim();//Get text
if(s.length() > 0){
columnNumber++; //Move column number along
SO2.instertUpdate(s, columnNumber, rowNumber);
previous = text;
}
}
};
return handler;
}
//Main
public static void main(String[] args) {
JFrame file = new JFrame("File choice. . .");
file.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
FileDialog dialog = new FileDialog(file, "Choose a file", FileDialog.LOAD);//Get XML File
dialog.setDirectory(Paths.get(System.getProperty("user.home")).resolve("Desktop").toString());
dialog.setFile("*.xml");
dialog.setVisible(true);
if(dialog.getFile() == null){
System.exit(0);
} else {
xmlFile = new File(dialog.getDirectory() + dialog.getFile());
javax.swing.SwingUtilities.invokeLater(new Runnable() {
@Override
public void run() {
new SO2(xmlFile);
}
});
}
}
private static File xmlFile;
private static XSSFWorkbook wb;
private static Sheet sheet;
private static Row row;
private static Cell cell;
private static boolean startWasPrevious = false; //For formatting purposes
private static int rowNumber = -1; //Hold row number
private static int columnNumber = 0;//Hold number of cell to wtite to
private static int indent;//For indenting
private static String previous = "";//To know what was last processed
private static final String endTag = "end";//Values for previous to hold
private static final String text = "text";//Values for previous to hold
private static final String startTag = "start";//Values for previous to hold
}
这是我使用的 xml 文件:
This was the xml file I used:
<?xml version="1.0"?>
<empire>
<darkness>
<sith>
<title>Darth</title>
<name>Vader</name>
<power>Grip</power>
</sith>
<sith>
<title>Darth</title>
<name>Sidious</name>
<power>Lightning</power>
</sith>
</darkness>
希望有帮助，祝你好运
这篇关于维护从 XML 到 Excel 文件的层次结构 - Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
