Exporting Resultset from Java database to Excel using Apache Poi

java code to export data to excel
code to compare two excel files in java using apache poi
how to read data from excel and store in datatable in java
how to compare excel data with database in java
apache poi tutorial
apache poi example
apache poi excel sql query
how to read a particular row in excel using java

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();
                }

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();
    }

Java code example to export data from database to Excel file, Java code example to export data from database to Excel file. First, make sure that you specify the dependencies for MySQL JDBC driver and Apache POI API for Excel in Maven's pom.xml file: Note that the writeHeaderLine() method writes the column names of the table to the first line in the Excel file. Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically


I suggest another solution, taking advantage of MemPOI lib. Take a look:

public class Plexada2 {

    public static void main(String[] args) {
        try {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           Connection conn = DriverManager.getConnection("jdbc:odbc:Storeway","root", "");
           PreparedStatement prepStmt = conn.prepareStatement("Select * from Storeway.order");

           File fileDest = new File("C:\\Users\\lawix10\\Desktop\\testlno9.xls");

           new MempoiBuilder()
                .setFile(fileDest)
                .addMempoiSheet(new MempoiSheet(prepStmt))
                .build()
                .prepareMempoiReportToFile()
                .get();
         } catch (Exception e) {
            // TODO manage exception
         }
    }
}

Converting ResultSet to Excel table in Java using Apache POI , Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically generate Excel file from a ResultSet. We will construct a class named: ResultSetToExcel. Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically generate Excel file from a ResultSet. We will construct a class named: ResultSetToExcel.


can we have for loop to enter dynamic values into getString()?I tried this, but i couldnt fetch exact result.

My code: public static void excel(String appDB) { List headerValues=new ArrayList();

    XSSFWorkbook workbook = new XSSFWorkbook();
    try {
    setConnection(appDB);

    String queryName="SELECT * FROM ALL_TABLES where table_name='table_name'";

    Reporter.addStepLog("----------------------------------- " + queryName.toUpperCase()
            + "\n - Validation Start" + " -----------------------------------");
    ps = con.prepareStatement(queryName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();

    Statement statement = con.createStatement();
    XSSFSheet spreadsheet = workbook.createSheet("employedb");
      ResultSet resultSet = statement.executeQuery("select * from all_tab_columns where table_name='table_name'"); 
      XSSFRow row = spreadsheet.createRow(0);
      XSSFCell cell;
      int cc=resultSet.getMetaData().getColumnCount();
      for(int i=1;i<=cc;i++)
      {
          String headerVal=resultSet.getMetaData().getColumnName(i);
          headerValues.add(headerVal);
          cell = row.createCell(i-1);
          cell.setCellValue(resultSet.getMetaData().getColumnName(i));
      }
      System.out.println(headerValues);
      int i = 1;
      while (resultSet.next())
      {  
          for(int j=1;j<=cc;j++)
          {  
          System.out.println(resultSet.getString(j));
          XSSFRow row1 = spreadsheet.createRow((short) i);
          row1.createCell((short) i).setCellValue(resultSet.getString(resultSet.getMetaData().getColumnName(j)));
          i++;

      }  
      }

      FileOutputStream out = new FileOutputStream(new File("S:\\Downloads\\excel.xlsx"));
      workbook.write(out);
      out.close();  
      System.out.println("exceldatabase.xlsx written successfully");

}catch(Exception e){}
}

Apache POI - Database, Apache POI - Database - This chapter explains how the POI library interacts with a database. With the help of JDBC, you can retrieve data from a database and insert that data into Write into Excel from Database ResultSet; import java.sql​. That’s a couple of example programs that show you how to export data from database to Excel file. To learn more about writing Excel file, read this tutorial: How to Write Excel Files in Java using Apache POI . Other Java Coding Tutorials: Java code example to import data from Excel to database


Above issue got resolved.

public static void excel(String appDB)
{
    List<String> headerValues=new ArrayList<String>();

    XSSFWorkbook workbook = new XSSFWorkbook();
    try {
    setConnection(appDB);

    String queryName="SELECT * FROM ALL_TABLES where table_name='table_name";

    Reporter.addStepLog("----------------------------------- " + queryName.toUpperCase()
            + "\n - Validation Start" + " -----------------------------------");
    ps = con.prepareStatement(queryName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();

    Statement statement = con.createStatement();
    XSSFSheet spreadsheet = workbook.createSheet("employedb");
      ResultSet resultSet = statement.executeQuery("select * from all_tab_columns where table_name='table_name'");


      XSSFRow row = spreadsheet.createRow(0);
      XSSFCell cell;
      int cc=resultSet.getMetaData().getColumnCount();
      for(int i=1;i<=cc;i++)
      {
          String headerVal=resultSet.getMetaData().getColumnName(i);
          headerValues.add(headerVal);
          cell = row.createCell(i-1);
          cell.setCellValue(resultSet.getMetaData().getColumnName(i));
      }
      System.out.println(headerValues);

      int i = 1;
      while (resultSet.next())
      {  

          XSSFRow row1 = spreadsheet.createRow((short) i);
          for(int p=0;p<headerValues.size();p++)
          {
          row1.createCell((short) p).setCellValue(resultSet.getString(headerValues.get(p)));
          }
          i++;
      } 
      FileOutputStream out = new FileOutputStream(new File("S:\\Downloads\\excel.xlsx"));
      workbook.write(out);
      out.close();  
      System.out.println("exceldatabase.xlsx written successfully");

}catch(Exception e){}
}

Compare Excel.csv to Oracle Database in Java, How do you write to an existing Excel file in Java using poi? Today the reverse is the case, we will focus on Exporting data from mysql to excel using java. To be successful in Exporting Data from MySQL to Excel using Java, you will first of all get the data from the database into a resultset object, then use the data in the resultSet object to export to an Excel (xlsx) file.


Export ResultSet to Excel in Java, How do I read a specific cell value in Excel using Java? This tutorial shows you how simple and easy it is to read Excel files using Apache POI’s API. 1. Getting Apache POI library. Apache POI is the pure Java API for reading and writing Excel files in both formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later). To use Apache POI in your Java project: For non-Maven projects:


Exporting Database Data to Excel Using Java, The data in cells can be formatted using predefined formats or user-defined formats. The below example shows how to export ResultSet to Excel in Java. compile "org.apache.poi:poi:3.17" // For `.xls` files compile "org.apache.poi:poi-ooxml:3.17" // For `.xlsx` files Writing to an excel file using Apache POI. Let’s create a simple Employee class first. We’ll initialize a list of employees and write the list to the excel file that we’ll generate using Apache POI.


Exporting Data from MySQL to Excel using Java (Apache POI , Exporting Database Data to Excel Using Java - Core Java ResultSet; import java.sql.Statement; import org.apache.poi.hssf.usermodel. Apache POI provides libraries for reading and writing excel file in Java. There are other libraries available in market but Apache POI is most popular and reliable among them. In this tutorial I will show you how you can read or write data to excel file in Java using Apache POI Library.