Java code for converting between .xls to .xlsx and vice versa

I've a web application which allows clients to upload or download MS Excel sheets. I need a solution in Java from which i can upload in MS Excel 2003 and download in MS Excel 2007/2010 or vice versa.

View Answers

January 3, 2011 at 11:56 AM

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.