hi,
I have a requirement to read all the xml files from a parent folder and write in a spreadsheet(separate spreadsheet for each xml file)
I have written the below code in which xml and excel file name are hardcoded.
The format of xml file is same.
package xmlexcel;
import org.apache.poi.hssf.usermodel.*;
import java.util.ArrayList;
import java.awt.List;
import java.io.*;
import java.util.ArrayList;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.xml.sax.SAXException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import javax.xml.xpath.XPathConstants;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
public class XMLconvertExcel {
private static File xmlDocument;
private static NodeList e;
int a = 3;
int b = 3;
public void generateExcel(File xmlDocument) throws XPathExpressionException {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet spreadSheet = wb.createSheet("spreadSheet");
/* Get access to HSSFCellStyle */
HSSFCellStyle my_style = wb.createCellStyle();
spreadSheet.setColumnWidth((short)0,(short) (256*25));
spreadSheet.setColumnWidth((short)1,(short) (256*25));
spreadSheet.setColumnWidth((short)2,(short) (256*25));
spreadSheet.setColumnWidth((short)3,(short) (256*25));
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document document = builder.parse(xmlDocument);
document.getDocumentElement().normalize();
XPath xPath = XPathFactory.newInstance().newXPath();
String expression1 = "/entity-mappings/entity/@class";
String expression2 = "/entity-mappings/entity/table/@name";
String expression3 = ".//attributes/embedded-id/@attribute-type";
String expression4 = ".//attributes/embedded-id/attribute-override/@name";
String expression5 = ".//attributes/embedded-id/attribute-override/column/@name";
String expression6 = ".//attributes/embedded/attribute-override/@name";
String expression7 = ".//attributes/embedded/attribute-override/column/@name";
String expression8 = ".//attributes/version/@name";
String expression9 = "//attributes/version/column/@name";
String expression10 = "//attributes/basic/@name";
String expression11 = "//attributes/basic/column/@name";
NodeList nodeList1 = (NodeList) xPath.compile(expression1).evaluate(document,XPathConstants.NODESET);
NodeList nodeList2 = (NodeList) xPath.compile(expression2).evaluate(document,XPathConstants.NODESET);
NodeList nodeList4 = (NodeList) xPath.compile(expression4).evaluate(document,XPathConstants.NODESET);
NodeList nodeList5 = (NodeList) xPath.compile(expression5).evaluate(document,XPathConstants.NODESET);
NodeList nodeList6 = (NodeList) xPath.compile(expression6).evaluate(document,XPathConstants.NODESET);
NodeList nodeList7 = (NodeList) xPath.compile(expression7).evaluate(document,XPathConstants.NODESET);
NodeList nodeList8 = (NodeList) xPath.compile(expression8).evaluate(document,XPathConstants.NODESET);
NodeList nodeList9 = (NodeList) xPath.compile(expression9).evaluate(document,XPathConstants.NODESET);
NodeList nodeList10 = (NodeList) xPath.compile(expression10).evaluate(document,XPathConstants.NODESET);
NodeList nodeList11 = (NodeList) xPath.compile(expression11).evaluate(document,XPathConstants.NODESET);
//a=nodelist.getLength();
//e = printStackTrace();
//System.out.println("I am here " +e);
System.out.println("Root element :" + document.getDocumentElement().getNodeName() + " nlist length " +nodeList4.getLength());
System.out.println("Node Type :" + document.getDocumentElement().getNodeType());
/*HSSFRow row = spreadSheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
cell.setCellValue("Entity");
cell = row.createCell((short)1);
cell.setCellValue("Table");
cell = row.createCell((short)2);
cell.setCellValue("Attribute");
cell = row.createCell((short)3);
cell.setCellValue("Column");*/
/*create HSSFFont object from the workbook */
/* Create HSSFFont object from the workbook */
HSSFFont my_font=wb.createFont();
/* set the weight of the font */
my_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
/* attach the font to the style created earlier */
my_style.setFont(my_font);
/* At this stage, we have a bold style created which we can attach to a cell */
HSSFRow row = spreadSheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
cell.setCellValue("Entity");
cell.setCellStyle(my_style);
for (int i = 0; i < nodeList1.getLength(); i++) {
//System.out.println("Class :- " +nodeList1.item(i).getFirstChild().getNodeValue());
cell = row.createCell((short) 1);
cell.setCellValue(nodeList1.item(i).getFirstChild().getNodeValue());
}
HSSFRow row1 = spreadSheet.createRow(1);
HSSFCell cell1 = row1.createCell((short)0);
cell1.setCellValue("Table");
cell1.setCellStyle(my_style);
for (int i = 0; i < nodeList2.getLength(); i++) {
//System.out.println("Class :- " +nodeList2.item(i).getFirstChild().getNodeValue());
cell = row1.createCell((short) 1);
cell.setCellValue(nodeList2.item(i).getFirstChild().getNodeValue());
}
HSSFRow row2 = spreadSheet.createRow(2);
HSSFCell cell2 = row2.createCell((short)0);
cell2.setCellValue("Attribute");
cell2.setCellStyle(my_style);
cell2 = row2.createCell((short)1);
cell2.setCellValue("Column");
row2.getCell((short)1).setCellStyle(my_style);
for (int i = 0; i < nodeList4.getLength(); i++) {
row2 = spreadSheet.createRow(a);
cell = row2.createCell((short) 0);
cell.setCellValue(nodeList4.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override :- " +nodeList4.item(i).getFirstChild().getNodeValue());
a++;
}
for (int i = 0; i < nodeList6.getLength(); i++) {
row2 = spreadSheet.createRow(a);
cell = row2.createCell((short) 0);
cell.setCellValue(nodeList6.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override :- " +nodeList6.item(i).getFirstChild().getNodeValue());
a++;
}
for (int i = 0; i < nodeList8.getLength(); i++) {
row2 = spreadSheet.createRow(a);
cell = row2.createCell((short) 0);
cell.setCellValue(nodeList8.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override :- " +nodeList8.item(i).getFirstChild().getNodeValue());
a++;
}
for (int i = 0; i < nodeList10.getLength(); i++) {
row2 = spreadSheet.createRow(a);
cell = row2.createCell((short) 0);
cell.setCellValue(nodeList10.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override :- " +nodeList10.item(i).getFirstChild().getNodeValue());
a++;
}
for (int i = 0; i < nodeList5.getLength(); i++) {
row2 = spreadSheet.createRow(b);
cell = row2.createCell((short)1);
cell.setCellValue(nodeList5.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override column :- " +nodeList5.item(i).getFirstChild().getNodeValue());
b++;
}
for (int i = 0; i < nodeList7.getLength(); i++) {
row2 = spreadSheet.createRow(b);
cell = row2.createCell((short) 1);
cell.setCellValue(nodeList7.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override column :- " +nodeList7.item(i).getFirstChild().getNodeValue());
b++;
}
for (int i = 0; i < nodeList9.getLength(); i++) {
row2 = spreadSheet.createRow(b);
cell = row2.createCell((short) 1);
cell.setCellValue(nodeList9.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override column :- " +nodeList9.item(i).getFirstChild().getNodeValue());
b++;
}
for (int i = 0; i < nodeList11.getLength(); i++) {
row2 = spreadSheet.createRow(b);
cell = row2.createCell((short) 1);
cell.setCellValue(nodeList11.item(i).getFirstChild().getNodeValue());
System.out.println("attribute-override column :- " +nodeList11.item(i).getFirstChild().getNodeValue());
b++;
}
//wb.write(arg1.getOutputPayload().getOutputStream());
//Outputting to Excel spreadsheet
FileOutputStream output = new FileOutputStream(new File("C:\\java_training\\com\\XMLtoExcel\\orm\\AccountBenefitActionSet.xls"));
wb.write(output);
output.flush();
output.close();
} catch (IOException e) {
System.out.println("IOException " + e.getMessage());
} catch (ParserConfigurationException e) {
System.out.println("ParserConfigurationException " +e.getMessage());
}catch (SAXException e) {
System.out.println("SAXException " +e.getMessage());
}
}
/*private String printStackTrace() {
// TODO Auto-generated method stub
return null;
}*/
/**
* @param args
*/
public static void main(String[] args) {
File xmlDocument = new File("C:\\java_training\\com\\XMLtoExcel\\orm\\AccountBenefitActionSet.orm.xml");
//FileInputStream xmlDocument = new FileInputStream(new File("C:\\java_training\\com\\XMLtoExcel\\AccountCreditMatrixDetails.orm.xml"));
XMLconvertExcel excel = new XMLconvertExcel();
try {
excel.generateExcel(xmlDocument);
} catch (XPathExpressionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
