Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa Java-Beginners Java code for converting between .xls to .xlsx and vice versa
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Umesh
Java code for converting between .xls to .xlsx and vice versa
1 Answer(s)      4 years and 7 months ago
Posted in : Java Beginners


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.

Advertisement
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.



Related Tutorials/Questions & Answers:
Java code for converting between .xls to .xlsx and vice versa - Java Beginners
Java code for converting between .xls to .xlsx and vice versa  I've... need a solution in Java from which i can upload in MS Excel 2003 and download in MS Excel 2007/2010 or vice versa
Java code for conversion between .xls and .xlsx or vice versa - Development process
or tool in Java for converting between .xls to .xlsx or Vice versa...Java code for conversion between .xls and .xlsx or vice versa  I've... Excel sheets. The problem is I'm using MS Excel 2003 (.xls format
Advertisements
I converting binary to decimal and vice versa
I converting binary to decimal and vice versa  can i ask for the codes in converting binary to decimal and decimal to binary to be run in one program with repeat.. pls. help me! i badly need the codes by monday!! pls
how to convert a text file thats is in russian to english and vice versa using java ?
how to convert a text file thats is in russian to english and vice versa using java ?   Hello, I am a beginner to java, I want to convert a text file which is in english to russian and vice versa Kindly advice me Thanks
how to convert a .xlsx to .xls fil - JSP-Servlet
how to convert a .xlsx to .xls fil  Dear sir, How to convert a .xlsx file to .xls using poi .Please give me some clue.... Thanks in advance
Convert a file name path to url and vice versa in java
with the toURL() method of the File class. Here is the code of Convert the Filename
How can i extract email ids from xls/xlsx file in java?
How can i extract email ids from xls/xlsx file in java?  that's the code..... i tried to extract email id;s from xls file........... but it doesn;t gives the proper output? table is like this: bhavik
conversion from decimal to hexadecimal and vice versa
conversion from decimal to hexadecimal and vice versa  can i get the code for converting decimal to hexadecimal   Here is a code...("Hexa decimal: " + hex1); } } Here is a code that converts hexadecimal
how to open jdialog form in jinternalframe and transfer data from jdialog to jinternalform and vice-versa - Java Beginners
how to open jdialog form in jinternalframe and transfer data from jdialog to jinternalform and vice-versa  hi....... i am developing simple... dialog frame from jinternalframe any transfering record between jinternalframe
Java xlsx - JSP-Servlet
Java xlsx  Dear Sir's / Madam's i want to read/write XLSX file (of office 2007), please help me. although i can read/write xls file but not xlsx file.   Hi Friend, 1)Write .xlsx file: import java.io.
How to delete .xlsx file in java?
How to delete .xlsx file in java?  Hello Sir, I am unable to delete .xlsx file using below code, What is wrong here? if(myFile != null && myFile.exists()    && myFile.canRead() &&
Overview of Networking through JAVA,To retrieve the IP address from Host Name, vice-versa
To retrieve the IP address from Host Name, vice-versa... to explain the method to find out the IP address from host name and to vice verse. Here... the program then he/she should need to write "exit". The complete code
source code program - Java Beginners
source code program  I need the source code for a program that converts temperatures from celsius to fahrenheit and vice versa, as well as converting kilometers to miles and vice versa using Java "classes".  Hi
converting string to double in java
converting string to double in java  Please post an example to converting string to double in java. Thanks!   Convert String to Double Tutorial
Excel conversion tool. - Java Beginners
Excel conversion tool.  Hi, I need a conversion tool which can convert .xls(Excel 2003) to .xlsx (Excel 2007) and vice-versa. Please suggest any links ro tools. Thank You
Converting HTML to image - Java Beginners
Converting HTML to image   Is it possible to convert html to image with java code ?   Hi friend, Please give the problem in details...://www.roseindia.net/java/ Thanks
Using HSSF 3.5 to READ XLS - Java Beginners
to do is to iterate through an XLS file and print out every colum and every row to the command prompt. Can someone help me with code for this plz?  Hi Friend, Try the following code: import java.io.*; import java.util.
Converting HTML to XML - Java Beginners
Converting HTML to XML  Hi, I am generating an HTML file (JSP from a Java Struts App.) I need to figure out a way to create an XML file from that HTML document. If you can refer me to some Java Code, that would
Converting jsp variable to java variable
Converting jsp variable to java variable  Hi how to convert java script variable to java variable on same jsp page
converting image to byte[] - Java Beginners
converting image to byte[]  Dear Sir Can you please tell me a solution for converting a java.awt.Image to byte[] eg: java.awt.Image... = convertImageToByte(image); public byte[] convertImageToByte(image){ // Code
losing precision converting from java BigDecimal to double
losing precision converting from java BigDecimal to double  losing precision converting from java BigDecimal to double
java programming code - Java Beginners
java programming code  I want to write a program that converts temperatures from celsius to fahrenheit and vice versa. It also converts kilometers to miles and vice versa using classes
Transferring values between javascript and java code in jsp - JSP-Servlet
Transferring values between javascript and java code in jsp  Is there a way to transfer values between the javascripts and the java code(scriptlet, expressions etc) in a jsp page?   Hi Friend, 1)Pass value from jsp
xml Converting to java using JDOM
xml Converting to java using JDOM  Hello , I am new to java and JDom so i make a Xml file and i need help to read it from java using objects , my... should be inside a student object i will include the java code and xml code and i
java image converting to byte codes - Java Beginners
java image converting to byte codes  i want to convert an image to human unreadable format which is uploaded by client to server.How can i do
JSP Excel report download for .xlsx format
; The above code works fine because i am trying to save/open excel file in .xls format but my requirement is to download excel with format .xlsx (m.S 2007),so i changed...JSP Excel report download for .xlsx format  <%@ page contentType
converting java class to java fx class
converting java class to java fx class   i am working on a java... a java file that would work for text to speech. it is working fine as a .java file. but when i m converting the same file to javafx .. it is giving an error " speech
diff between encapsulation and abstraction code
diff between encapsulation and abstraction code  diff between encapsulation and abstraction code
converting html to ppt,pptx - Java Beginners
converting html to ppt,pptx   Hi, i convert html to .doc format.But i want to convert the html to ppt,xml,pptx,docx,pdf.. Is there any possibility to solve this problem
converting html to ppt,pptx - Java Beginners
converting html to ppt,pptx   Hi, i convert html to .doc format.But i want to convert the html to ppt,xml,pptx,docx,pdf.. Is there any possibility to solve this problem
converting Json data into rows of DTO in java
converting Json data into rows of DTO in java  I am using jqxgrid, and I am able to access the json data in JAVA controller. But now my requirement... have downloaded & used the following JAR file 'java-json.jar'. Thanks
Advertisements
 

 

 

DMCA.com