This tool can
generate one or more xls report based on the input provided inside a
xml file.
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;
import org.w3c.dom.Element;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* This java class will read the xml file and generate the excel sheet based on the input parameter
* provided to it.
*
*
*/
public class ReadXmlFile {
public static void main(String argv[]) {
Connection con = null;
try {
String __runId = argv[0];
String __runIdArr[] = __runId.split(",");
if (__runIdArr.length > 0) {
for (int i = 0; i < __runIdArr.length; i++) {
String __filePath = "C:\excel\test1.xml";
File fXmlFile = new File(__filePath);
DocumentBuilderFactory dbFactory = DocumentBuilderFactory
.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(fXmlFile);
doc.getDocumentElement().normalize();
System.out.println("Root element :"
+ doc.getDocumentElement().getNodeName());
NodeList nList = doc.getElementsByTagName("data");
System.out.println("-----------------------");
File f = new File("C:\excel\data_import_"+__runIdArr[i]+".xls");
if (f.createNewFile()) {
System.out
.println("File has been created successfully");
}
FileOutputStream fileOut = new FileOutputStream(f);
HSSFWorkbook hwb = new HSSFWorkbook();
for (int temp = 0; temp < nList.getLength(); temp++) {
Node nNode = nList.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement = (Element) nNode;
String __sheetName = getTagValue("sheetname",
eElement);
String __columns = getTagValue("column", eElement);
String __query = getTagValue("query", eElement);
// String __runId = getTagValue("runid", eElement);
con = DBConnection.getConnection();
if (createSheet(hwb, __sheetName, con, __query,
__columns, __runIdArr[i])) {
System.out
.println(__sheetName
+ " sheet has been created successfully.");
}
}
}
hwb.write(fileOut);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
System.out.println("File has been generated successfully.");
System.exit(1);
}
/**
* @depricated.
* @param sTag String Object.
* @param eElement Element object
* @return returns string object.
*/
private static String getTagValue(String sTag, Element eElement) {
NodeList nlList = eElement.getElementsByTagName(sTag).item(0)
.getChildNodes();
Node nValue = (Node) nlList.item(0);
return nValue.getNodeValue();
}
/**
* This method will create the excel sheet based on queries has been written inside the xml file.
* @param hwb Inatance of HSSFWorkbook
* @param sheetName Instance of
* @param con String
* @return returns boolean values.
* @throws Exception
*/
private static boolean createSheet(HSSFWorkbook hwb, String sheetName,
Connection con, String query, String columns, String runId)
throws Exception {
boolean __sheetstatus = false;
HSSFSheet sheet = hwb.createSheet(sheetName);
HSSFRow rowhead = sheet.createRow((int) 1);
int count = 0;
HSSFFont font = hwb.createFont();
font.setFontHeightInPoints((short) 10);
font.setFontName(HSSFFont.FONT_ARIAL);
HSSFCellStyle headerCell = setHeaderCell(hwb, font);
String columnsArr[] = columns.split(";");
for (int i = 0; i < columnsArr.length; i++) {
setHeadColumnValue(sheet, rowhead, headerCell, columnsArr[i],
count, 75);
count++;
}
PreparedStatement psEventCount = null;
ResultSet rsEventType = null;
try {
psEventCount = con.prepareStatement(query.replaceAll("#", runId));
rsEventType = psEventCount.executeQuery();
int rowCount = 2;
HSSFCellStyle setStringCell = setStringCell(hwb);
while (rsEventType.next()) {
int __count = 0;
HSSFRow currentRowhead = sheet.createRow((short) rowCount);
for (int i = 0; i < columnsArr.length; i++) {
String __equipmentselNumber = rsEventType
.getString(columnsArr[i]);
setHeadColumnValue(sheet, currentRowhead, setStringCell,
__equipmentselNumber, __count, 75);
__count++;
}
rowCount++;
}
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
rsEventType.close();
psEventCount.close();
}
__sheetstatus = true;
return __sheetstatus;
}
/**
* @This method will organize the xls cell values and style of that.
* @param wb Instance of HSSFWorkbook
* @param font Inatance of HSSFFont
* @return Instance of String.
*/
private static HSSFCellStyle setHeaderCell(HSSFWorkbook wb, HSSFFont font) {
HSSFCellStyle headerCell = wb.createCellStyle();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// headerCell.setAlignment(HSSFCellStyle.ALIGN_LEFT);
headerCell.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
headerCell.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerCell.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
headerCell.setFont(font);
headerCell.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCell.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerCell.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCell.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCell.setWrapText(true);
return headerCell;
}
/**
* @This method will organize the xls cell values and style of that.
* @param wb Instance of HSSFWorkbook
* @return returns String instance.
*/
private static HSSFCellStyle setStringCell(HSSFWorkbook wb) {
HSSFCellStyle stringCell = wb.createCellStyle();
stringCell.setAlignment(HSSFCellStyle.ALIGN_LEFT);// "#,##0"
stringCell.setBorderBottom(HSSFCellStyle.BORDER_THIN);
stringCell.setBorderLeft(HSSFCellStyle.BORDER_THIN);
stringCell.setBorderTop(HSSFCellStyle.BORDER_THIN);
stringCell.setBorderRight(HSSFCellStyle.BORDER_THIN);
stringCell.setLocked(false);
stringCell.setWrapText(true);
return stringCell;
}
/**
* This method will set the head columns values.
* @param sheet Instance of HSSFSheet
* @param row Instance of HSSFRow
* @param style Instance of HSSFCellStyle
* @param value Instance of String
* @param colNo Instance of int.
* @param width Instance of of int.
*/
public static void setHeadColumnValue(HSSFSheet sheet, HSSFRow row,
HSSFCellStyle style, String value, int colNo, int width) {
HSSFCell cellt = row.createCell((short) colNo);
sheet.addMergedRegion(new Region(0, (short) colNo, 0,
(short) (colNo + 2)));
cellt.setCellStyle(style);
cellt.setCellValue(value);
sheet.setColumnWidth((short) colNo, (short) (100 * width));
}
}
import java.sql.*;
public class DBConnection {
public static Connection getConnection(){
Connection con=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
//QA
//String url = "jdbc:oracle:thin:@etpcamq1.energy.ge.com:1521:etpcamq1";
//con = DriverManager.getConnection(url,"TBCAM","cam34etpq1");
String url =""
con = DriverManager.getConnection(url,"","");
}catch(Exception e){
e.printStackTrace();
}
return con;
}
}