Exporting Resultset from Java database to Excel using Apache Poi

Question

Help am stucked on this project of exporting from result set to excel..previous solutions here haven't answered my questions but they have helped..here is my code so far it only displays a row in the database. My Code

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;


public class Plexada2 {


        public static void main(String[] args) {
        try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection conn = DriverManager.getConnection("jdbc:odbc:Storeway","root", "");
         Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = st.executeQuery("Select * from Storeway.order");

         HSSFWorkbook workbook = new HSSFWorkbook();
         HSSFSheet sheet = workbook.createSheet("lawix10");


         Row row = sheet.createRow(0);
         int x=0;
         while (rs.next()){
         String crate_id=  rs.getString(2);
         String content=rs.getString(3);
         String Order_type=  rs.getString(4);
         java.sql.Date date= rs.getDate(5);
         String datex= String.valueOf(date);

         row.createCell(0).setCellValue(crate_id);
         row.createCell(1).setCellValue(content);
         row.createCell(2).setCellValue(Order_type);
         row.createCell(3).setCellValue(datex); 

                }
         x+=1;  



        String yemi = "C:\\Users\\lawix10\\Desktop\\testlno9.xls";
        FileOutputStream fileOut;
        try {
             fileOut = new FileOutputStream(yemi);
             workbook.write(fileOut);
             fileOut.close();
                }

Answer 1

Ahh its very difficult to inspect your code trying formatting it before you post it and Regarding your query try something like this.

try {
    Class.forName("driverName");
    Connection con = DriverManager.getConnection("url", "user", "pass");
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("Select * from tablename");
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("lawix10");
    HSSFRow rowhead = sheet.createRow((short) 0);
    rowhead.createCell((short) 0).setCellValue("CellHeadName1");
    rowhead.createCell((short) 1).setCellValue("CellHeadName2");
    rowhead.createCell((short) 2).setCellValue("CellHeadName3");
    int i = 1;
    while (rs.next()){
        HSSFRow row = sheet.createRow((short) i);
        row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("column1")));
        row.createCell((short) 1).setCellValue(rs.getString("column2"));
        row.createCell((short) 2).setCellValue(rs.getString("column3"));
        i++;
    }
    String yemi = "g:/test.xls";
    FileOutputStream fileOut = new FileOutputStream(yemi);
    workbook.write(fileOut);
    fileOut.close();
    } catch (ClassNotFoundException e1) {
       e1.printStackTrace();
    } catch (SQLException e1) {
        e1.printStackTrace();
    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e1) {
        e1.printStackTrace();
    }