Convert / Download Data from Data Base into Excel Formate

Hi!


Commonly facing an issue to convert the data from database into Excel formate.


here i give an example code how to convert it.

i am using apache poi external jar for this process.

You can download from apache site.

simply i am calling the record from the MySQL Server, while if it is true then i pass those result-set through the poi


have to pass the header of your excel column and align your column and the row



try 
   { 
HSSFWorkbook xlsWorkbook = new HSSFWorkbook();                      HSSFSheet xlsSheet = xlsWorkbook.createSheet();                short rowIndex = 0;                Class.forName("com.mysql.jdbc.Driver"); 
Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/readmain", "root", "root");
PreparedStatement stmt = conn.prepareStatement("select EmployeeId,Name,Department,Designation,Branch,DATE_FORMAT(Saldate, '%d/%m/%Y') as Saldate,LeaveTaken,Basic,Additionalpay,Spl,Other,Gross,Netsal,Insurance,Laveded,Loanamt,Festiveladv,Traveladv,Salaryadv,Othersded,Totded from newsal where EmployeeId ='" + ide + "' ORDER BY Saldate desc");// ORDER BY Saldate desc");
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData colInfo = rs.getMetaData();
                List<String> colNames = new ArrayList<String>();
                HSSFRow titleRow = xlsSheet.createRow(rowIndex++);
                for (int i = 1; i <= colInfo.getColumnCount(); i++) {
                    colNames.add(colInfo.getColumnName(i));
                    titleRow.createCell((short) (i - 1)).setCellValue(
                            new HSSFRichTextString(colInfo.getColumnName(i)));
                    xlsSheet.setColumnWidth((short) (i - 1), (short) 4000);
                }
                while (rs.next()) {
                    HSSFRow dataRow = xlsSheet.createRow(rowIndex++);
                    short colIndex = 0;
                    for (String colName : colNames) {
                        dataRow.createCell(colIndex++).setCellValue(
                                new HSSFRichTextString(rs.getString(colName)));
                    }
                }
                String mn = JOptionPane.showInputDialog("Enter File Name To Save On C:/Excell/", "");
                if (!mn.equals("")) {
                    xlsWorkbook.write(new FileOutputStream(new File("C:/Excell/" + mn + ".xls")));
                    JOptionPane.showMessageDialog(null, mn + " Is Save On C:/Excell/");
                    conn.close();
                } else {
                    JOptionPane.showMessageDialog(null, "Enter File Name");
                }
            } catch (Exception r) {
                JOptionPane.showMessageDialog(null, "You Cancell The Process");
            }


        } else {
            JOptionPane.showMessageDialog(null, "Enter Valid ID Please");
        }
    } 

now you can easily download your record's into excel formate.

Thank You!

Have A happy Day..,