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

 
 


Umesh
Java code for converting between .xls to .xlsx and vice versa
1 Answer(s)      3 years 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.
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 Pages:
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
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
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
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 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
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
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
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.
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
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() &&
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
temperature converting
converted Celcius to Fahrenheit and vice versa. For this, we have create three
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
To retrieve the IP address from Host Name, vice-versac
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
Upload csv or .xlsx file from JSP form to Database Table using servlet
Upload csv or .xlsx file from JSP form to Database Table using servlet  dear sir, i need the Servlet code that reads the .xlsx or CSV excel file... to Submit My Project i am using following code which is Working for .xls Excel file
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.
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
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
Converting XML to string
Converting XML to string  Hi Friends, I had an requirement in which a java program receives an xml messages from external web application... code so that I can study from it. Thanks in advance. Regards, Lisha Ahuja
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
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
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 file into pdf - Struts
converting html file into pdf  i want to convert html file into pdf file using java code please help me
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 biginteger to String
and String to the BigInteger vice-versa. To Convert BigInteger value to the String...; In our java example code we have created the two BigInteger variables... Java biginteger to String     
RMI-Example-3
to server and vice versa. This application for running requires a Remote Machine.... Well in this application there are seven java files. List of java files and their code are shown below: Code for Server and Client Machine
Hibernate Annotations
and vice versa. Most commonly XML file is used to write the metadata information... the metadata is clubbed into the POJO java file along with the code this helps... and java code. Prerequisite for setting the project :- Make sure you have
Hibernate with Annotation
the transformation of data from POJO to database tables and vice versa. Most commonly XML file... mapping. All the metadata is clubbed into the POJO java file along with the code... for the metadata and java code.   Now, read more information at: http
Converting a Filename to a URL
Converting a Filename to a URL     ... of the URL class, is used to get the file name from the URL. Here is the code... of this program is given below: C:\ConstructFileNamePath>java
Converting Numbers to Strings
Java: Converting Numbers to Strings Vanilla Java: Converting numbers... DecimalFormat, but as soon as the Java 5 format() and printf() methods are better documented, they are a better second choice. Converting Anything to String
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
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?  Hi Friend, We are providing you a code
XLS JDBC Example
.style1 { background-color: #FFFFCC; } XLS JDBC XlS JDBC driver is used to access xls file from java application. It is read only JDBC driver. You can only do a SELECT * FROM xls file statement. The other SQL command
Java Temperature Converter
Java Temperature Converter In this tutorial, you will learn how to convert temperature from Celsius to Fahrenheit and vice versa. Here is an example where we have created three functions celcius(), fahrenheit() and display table
Java Biginteger
can convert the BigInteger to String and String to the BigInteger vice-versa... Java Biginteger       Java biginteger to String As we can convert
String Arrays Java
String Arrays Java          String array cannot hold numbers or vice- versa. Jsp... of the length, its length can't be changed. The code of the program is given below
converting From Hexadecimal to Decimal System - Java Interview Questions
converting From Hexadecimal to Decimal System  Write a program in Java user is required to enter a number system XVI and turn it into the decimal.../java/java-conversion/DecimalToHexadecimal.shtml  Hi Friend, Try
PHP SQL Connection
and MySQL database and vice-versa. Understand with Example The Tutorial illustrate... between PHP and SQL. To understand the example we create a table 'Stu
Quick Hibernate Annotation Tutorial
tables and vice versa. Most commonly XML file is used to write the metadata information in Hibernate. The Java 5 (Tiger) version has introduced a powerful way... files for the metadata and java code. Prerequisite for setting the project
Converting string to Int - JDBC
Converting string to Int  String nrate=request.getParameter("t3"); i want convert this nrate to integer to make calculations on it... so what... Friend, Use the following code to convert string to int. Integer i
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
Converting Anything to String
Java: Converting Anything to String Summary: Converting any data... for converting any object into a string. printf() was added in Java 5 to use a format... control using some of the alternatives. Converting numbers to strings - See
reading the records from a .xlsx file and storing those records in database table
reading the records from a .xlsx file and storing those records in database table  Here is my requirement, I want to read the records from a .xlsx...){ if(saveFile.endsWith(".xlsx")){ byte[] b=strLine.getBytes
XML Books
integrating XML with Java (and vice versa) you can buy. It contains over 1000 pages...; Processing XML with Java Welcome to Processing XML with Java, a complete tutorial about writing Java programs that read
Create Excel(.xlsx) document using Apache POI
Create Excel(.xlsx) document using Apache POI In this section, you will learn how to create a Excel sheet having .xlsx extension using Apache POI library...); fileOut.close(); } } OUTPUT The output of the above code will create
converting one file format to another
converting one file format to another  Hi ser I need a code to export data from data grid to PDF and XL format plz help me out
Read Excel(.xlsx) document using Apache POI
Read Excel(.xlsx) document using Apache POI In this section, you will learn how to read Excel file having .xlsx extension using Apache POI library...;xlsx/workbook.xlsx"; // // Create an ArrayList to store the data read
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