Generate_XML_From_Excel And Generate XLS_From_Xml

Make two functions using WsApp i.e getXMLFrmExcel(String xlsfile) and generateXLSFile(String fileName).
getXMLFrmExcel(String xlsfile) will be of integer type and set return type as NOMxml in Wsapps method.
generateXLSFile(String fileName) will be of String type.
Then copy paste the code mentioned below and make changes according to your header of XLS file and table names

public static int getXMLFrmExcel(String xlsfile) throws Exception
{
String parentTagName = "GetImportResponse";
String iteratingTagName = "IMPORTDATA";
// String headersString
// ="Inward_Date/Inward_Time/Inward_Branch/Inwarded_by/Application_No/Customer_Name/PFA_CODE/SM_Code/Branch_Outward_Date/Image_No_Count/Image_QC_Image /Inward_Date";
String headersString = "Sr_No/Application_No/Applicant_Name/Receipt_No_/No_Of_Documents/Outward_Pages/Document_Type/Schedule_NO/Outward_Date/Prepared_By/Image_Inward_date/Scanned_pages";
String result = "";
String[] innerTags = iteratingTagName.split("/");
String[] outerTags = parentTagName.split("/");
String[] headers = headersString.split("/");
int errorObject;
try {
InputStream input = new FileInputStream(xlsfile);

POIFSFileSystem fs = new POIFSFileSystem(input);

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

Iterator rows = sheet.rowIterator();

List sheetData = new ArrayList();
int node;
result += "<" + parentTagName + ">";
int syncID = 22;
int i = 0;
while (rows.hasNext()) {
i++;
result += "<" + iteratingTagName + ">";
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
SimpleDateFormat formatter1 = new SimpleDateFormat("hh:mm:ss");

for (int c = 0; cells.hasNext() && c < headers.length; c++) {
if (i == 1) {
result = "<" + parentTagName + ">";
continue;

}
result += " <" + headers[c] + ">";
HSSFCell cell = (HSSFCell) cells.next();

switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
result += formatter.format(cell.getDateCellValue());
} else {
result += (long) cell.getNumericCellValue();
}
break;

case HSSFCell.CELL_TYPE_STRING:
result += (cell.getStringCellValue().trim());
break;

case HSSFCell.CELL_TYPE_BLANK:
break;

case HSSFCell.CELL_TYPE_BOOLEAN:
break;

case HSSFCell.CELL_TYPE_ERROR:
break;

default:
break;
}

result += "";
}
if (i == 1) {
result = "<" + parentTagName + ">";
} else {
result += "";
}
}
result += "";
System.out.println("Result : " + result);
node = new Document().parseString(result);
return node;
} catch (Exception e) {
errorObject = new Document()
.parseString("");
return errorObject;
}
}
@SuppressWarnings("deprecation")
public static String generateXLSFile(String fileName) {


try {
fileName = fileName + FILE_EXTENSION_XLS;
String filePath = EIBProperties.getInstallDir() + REPORTS_FOLDER
+ "\\" + fileName;
FileOutputStream fFile = new FileOutputStream(filePath);
HSSFWorkbook excelWorkbook = new HSSFWorkbook();
HSSFSheet excelWorksheet = excelWorkbook
.createSheet("Branch Outward Sheet");
short rownum = 0;
HSSFCellStyle cs = excelWorkbook.createCellStyle();
HSSFRow rij = excelWorksheet.createRow(rownum);
setOutwardHeader(cs, rij);

String qry = "SELECT * FROM Emp_Details";
QueryObject queryObject = new QueryObject(qry);
BusObjectIterator oBusObjectIterator = queryObject.getObjects();
BusObject oBusObject = null;
while (oBusObjectIterator.hasMoreElements())
{
oBusObject = (BusObject)oBusObjectIterator.nextElement();
rownum++;
short cellnum2 = 0;
rij = excelWorksheet.createRow(rownum);
HSSFCell cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_ID"));
cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_Name"));

cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_Salary"));

cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_DOB"));
cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_DOJ"));
cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_DOL"));
cellnum2++;
cel = rij.createCell(cellnum2);
cel.setCellValue(oBusObject.getStringProperty("Emp_Contact_No"));

}
excelWorkbook.write(fFile);
fFile.close();
return "";

} catch (FileNotFoundException e) {
e.printStackTrace();
throw new BsfRuntimeException("File Not Found" + e.getMessage());

} catch (Exception e) {
logger.log(Severity.ERROR, e);
throw new BsfRuntimeException("Error in reading File"
+ e.getMessage());

}

}

public static void setOutwardHeader(HSSFCellStyle cs, HSSFRow rij) {
int cellType = getDataType("VARCHAR");
int cellnum = 0;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType,
"Application No");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType,
"Proposer Name");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType, "Branch Name");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType, "Zone Name");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType, "PFA Code");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType, "SMCA Name");
cellnum++;
cellnum = (short) fillHeader(cellnum, cs, rij, cellType, "Channel Type");
cellnum++;
}

private static int getDataType(String dType) {

if (dType.equalsIgnoreCase("DATE")) {

return 10;
} else {
return HSSFCell.CELL_TYPE_STRING;
}
}

private static int fillHeader(int cellnum, HSSFCellStyle cs, HSSFRow rij,
int cellType, String header) {
HSSFCell cel = rij.createCell((short) cellnum);
cel.setCellValue(header);
return (short) cellnum++;
}


}
Include poi-2.5.1.jar in the classpath of java archives.
And import all these packages.

import com.cordys.cpc.bsf.busobject.BusObject;
import com.cordys.cpc.bsf.busobject.BusObjectConfig;
import com.cordys.cpc.bsf.busobject.BusObjectIterator;
import com.cordys.cpc.bsf.busobject.QueryObject;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.cordys.cpc.bsf.busobject.exception.BsfRuntimeException;
import com.eibus.util.logger.CordysLogger;
import com.eibus.util.logger.Severity;
import com.eibus.util.system.EIBProperties;
import com.eibus.xml.nom.Document;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
and declare :
private static String FILE_EXTENSION_XLS = ".xls";
private static final String REPORTS_FOLDER = "\\Web\\Sumit_Excelfile";
private static final CordysLogger logger = CordysLogger.getCordysLogger(Emp_Details.class);
in the table .java class where you will paste the above code .