

HI iwant to change .xls java code to suport new version .xlsx
and my .xls code is
public class ExcellGprs {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
String strXMLFile = "ExcellGprs.xml";
if (args != null && args.length > 1) {
strXMLFile = args[1];
}
ArrayList alSheets = XMLUtil.getXMLFromFile(strXMLFile, "StartCol");
for (int i = 0; i < alSheets.size(); i++) {
try {
HashMap hmSheet = (HashMap) alSheets.get(i);
Class.forName(hmSheet.get("DatabaseDrive").toString());
con = DriverManager.getConnection(hmSheet.get("DatabaseURL").toString());
Calendar c = GregorianCalendar.getInstance();
int nDays = 1;
if (hmSheet.get("Days") != null) {
nDays = Integer.parseInt(hmSheet.get("Days").toString());
}
c.add(Calendar.DAY_OF_MONTH, -nDays);
String[] saMonth = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
String strFilePath = hmSheet.get("FileName").toString();
String strDD = c.get(Calendar.DAY_OF_MONTH) > 9 ? String.valueOf(c.get(Calendar.DAY_OF_MONTH)) : "0" + String.valueOf(c.get(Calendar.DAY_OF_MONTH));
String strMM = c.get(Calendar.MONTH) > 8 ? String.valueOf(c.get(Calendar.MONTH) + 1) : "0" + String.valueOf(c.get(Calendar.MONTH) + 1);
String strYY = String.valueOf(c.get(Calendar.YEAR)).substring(2, 4);
strFilePath = strFilePath.replaceAll("Ã?Â¥DDÃ?Â¥", strDD);
strFilePath = strFilePath.replaceAll("Ã?Â¥MMÃ?Â¥", strMM);
strFilePath = strFilePath.replaceAll("Ã?Â¥MMMÃ?Â¥", saMonth[c.get(Calendar.MONTH)]);
strFilePath = strFilePath.replaceAll("Ã?Â¥YYÃ?Â¥", strYY);
strFilePath = strFilePath.replaceAll("Ã?Â¥YYYYÃ?Â¥", String.valueOf(c.get(Calendar.YEAR)));
File f = new File(strFilePath);
HSSFWorkbook objWB = null;
HSSFSheet objShe = null;
HSSFRow objRow = null;
HSSFCell objCell = null;
if (f.exists()) {
objWB = new HSSFWorkbook(new java.io.FileInputStream(f));
} else {
objWB = new HSSFWorkbook();
}
HSSFCellStyle objNumberStyle = CreateExcel.dataStyle1(objWB);
String strSheetName = hmSheet.get("Name").toString();
if (f.exists()) {
objShe = objWB.getSheet(strSheetName);
} else {
objShe = objWB.createSheet(strSheetName);
}
int nRow = Integer.parseInt(hmSheet.get("StartRow").toString());
String strQuery = hmSheet.get("Query").toString();
strQuery = strQuery.replaceAll("Ã?Â¥DDÃ?Â¥", strDD);
strQuery = strQuery.replaceAll("Ã?Â¥MMÃ?Â¥", strMM);
strQuery = strQuery.replaceAll("Ã?Â¥MMMÃ?Â¥", saMonth[c.get(Calendar.MONTH)]);
strQuery = strQuery.replaceAll("Ã?Â¥YYÃ?Â¥", strYY);
strQuery = strQuery.replaceAll("Ã?Â¥YYYYÃ?Â¥", String.valueOf(c.get(Calendar.YEAR)));
System.out.println(strQuery);
String strCategory = hmSheet.get("Categorys").toString();
String[] saCategory = strCategory.split(",");
st = con.prepareStatement(strQuery);
int nTemp = objShe.getLastRowNum();
int nTempCol = Integer.parseInt(hmSheet.get("StartCol").toString());
for (int j = 0; j < saCategory.length; j++) {
nRow = nTemp;
st.setString(1, saCategory[j]);
rs = st.executeQuery();
nRow++;
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
int nCol = nTempCol;
objRow = objShe.getRow(nRow);
if (objRow == null) {
objRow = objShe.createRow(nRow);
}
for (int k = 0; k < rsmd.getColumnCount(); k++) {
nCol++;
while (objRow.getCell(nCol) != null && objRow.getCell(nCol).getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
nCol++;
}
objCell = objRow.createCell(nCol);
objCell.setCellValue(rs.getDouble(k + 1));
objCell.setCellStyle(objNumberStyle);
}
++nRow;
}
nTempCol += rsmd.getColumnCount();
st.clearParameters();
}
// objShe.createFreezePane(2, 3);
objShe.getAlternateFormula();
HSSFFormulaEvaluator.evaluateAllFormulaCells(objWB);
//objWB.setActiveSheet(objWB.getSheetIndex(objShe));
// objWB.setFirstVisibleTab(objWB.getSheetIndex(objShe));
//objShe.setRowSumsBelow(true);
FileOutputStream fileOut3 = new FileOutputStream(new File(strFilePath));
objWB.write(fileOut3);
fileOut3.close();
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception e) {
}
}
if (st != null) {
try {
st.close();
st = null;
} catch (Exception e) {
}
}
if (con != null) {
try {
con.close();
con = null;
} catch (Exception e) {
}
}
}
}
} catch (Exception e) {
e.printStackTrace(System.out);
}
}
}
and i changed this code into this
public class NewExcell {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
String strXMLFile = "NewExcell.xml";
if (args != null && args.length > 1) {
strXMLFile = args[1];
}
ArrayList alSheets = XMLUtil.getXMLFromFile(strXMLFile, "StartCol");
for (int i = 0; i < alSheets.size(); i++) {
try {
HashMap hmSheet = (HashMap) alSheets.get(i);
Class.forName(hmSheet.get("DatabaseDrive").toString());
con = DriverManager.getConnection(hmSheet.get("DatabaseURL").toString());
Calendar c = GregorianCalendar.getInstance();
int nDays = 1;
if (hmSheet.get("Days") != null) {
nDays = Integer.parseInt(hmSheet.get("Days").toString());
}
c.add(Calendar.DAY_OF_MONTH, -nDays);
String[] saMonth = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
String strFilePath = hmSheet.get("FileName").toString();
String strDD = c.get(Calendar.DAY_OF_MONTH) > 9 ? String.valueOf(c.get(Calendar.DAY_OF_MONTH)) : "0" + String.valueOf(c.get(Calendar.DAY_OF_MONTH));
String strMM = c.get(Calendar.MONTH) > 8 ? String.valueOf(c.get(Calendar.MONTH) + 1) : "0" + String.valueOf(c.get(Calendar.MONTH) + 1);
String strYY = String.valueOf(c.get(Calendar.YEAR)).substring(2, 4);
strFilePath = strFilePath.replaceAll("Ã?Â¥DDÃ?Â¥", strDD);
strFilePath = strFilePath.replaceAll("Ã?Â¥MMÃ?Â¥", strMM);
strFilePath = strFilePath.replaceAll("Ã?Â¥MMMÃ?Â¥", saMonth[c.get(Calendar.MONTH)]);
strFilePath = strFilePath.replaceAll("Ã?Â¥YYÃ?Â¥", strYY);
strFilePath = strFilePath.replaceAll("Ã?Â¥YYYYÃ?Â¥", String.valueOf(c.get(Calendar.YEAR)));
File f = new File(strFilePath);
Workbook[] objWB = null;
Sheet objShe = null;
Row objRow = null;
Cell objCell = null;
if (f.exists()) {
objWB = new Workbook[]{new HSSFWorkbook(new java.io.FileInputStream(f)), new XSSFWorkbook(new java.io.FileInputStream(f))};
// objWB = new XSSFWorkbook(new java.io.FileInputStream(f)); CreationHelper createHelper = objWB[1].getCreationHelper();
} else {
objWB = new Workbook[]{new HSSFWorkbook(), new XSSFWorkbook()};
CreationHelper createHelper = objWB[1].getCreationHelper();
}
// CellStyle objNumberStyle = CreateExcel.dataStyle1(objWB);
String strSheetName = hmSheet.get("Name").toString();
if (f.exists()) {
objShe = objWB[1].getSheet(strSheetName);
} else {
objShe = objWB[1].createSheet(strSheetName);
}
int nRow = Integer.parseInt(hmSheet.get("StartRow").toString());
String strQuery = hmSheet.get("Query").toString();
strQuery = strQuery.replaceAll("Ã?Â¥DDÃ?Â¥", strDD);
strQuery = strQuery.replaceAll("Ã?Â¥MMÃ?Â¥", strMM);
strQuery = strQuery.replaceAll("Ã?Â¥MMMÃ?Â¥", saMonth[c.get(Calendar.MONTH)]);
strQuery = strQuery.replaceAll("Ã?Â¥YYÃ?Â¥", strYY);
strQuery = strQuery.replaceAll("Ã?Â¥YYYYÃ?Â¥", String.valueOf(c.get(Calendar.YEAR)));
System.out.println(strQuery);
String strCategory = hmSheet.get("Categorys").toString();
String[] saCategory = strCategory.split(",");
st = con.prepareStatement(strQuery);
int nTemp = objShe.getLastRowNum();
int nTempCol = Integer.parseInt(hmSheet.get("StartCol").toString());
for (int j = 0; j < saCategory.length; j++) {
nRow = nTemp;
st.setString(1, saCategory[j]);
rs = st.executeQuery();
nRow++;
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
int nCol = nTempCol;
objRow = objShe.getRow(nRow);
if (objRow == null) {
objRow = objShe.createRow(nRow);
}
for (int k = 0; k < rsmd.getColumnCount(); k++) {
nCol++;
while (objRow.getCell(nCol) != null && objRow.getCell(nCol).getCellType() == Cell.CELL_TYPE_FORMULA) {
nCol++;
}
objCell = objRow.createCell(nCol);
objCell.setCellValue(rs.getDouble(k + 1));
// objCell.setCellStyle(objNumberStyle);
}
++nRow;
}
nTempCol += rsmd.getColumnCount();
st.clearParameters();
}
// objShe.createFreezePane(2, 3);
// objShe.getAlternateFormula();
//HSSFFormulaEvaluator.evaluateAllFormulaCells(objWB);
//objWB.setActiveSheet(objWB.getSheetIndex(objShe));
// objWB.setFirstVisibleTab(objWB.getSheetIndex(objShe));
//objShe.setRowSumsBelow(true);
FileOutputStream fileOut3 = new FileOutputStream(new File(strFilePath));
objWB[1].write(fileOut3);
fileOut3.close();
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (Exception e) {
}
}
if (st != null) {
try {
st.close();
st = null;
} catch (Exception e) {
}
}
if (con != null) {
try {
con.close();
con = null;
} catch (Exception e) {
}
}
}
}
} catch (Exception e) {
e.printStackTrace(System.out);
}
}
} to convert to .xlsx but i am getting exception this exception error in output
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException at NewExcell.main(NewExcell.java:75) Java Result: 1
can u tell me the problem.why i getting this error and what changes i have to do in my code in clear way.