Need to fetch data from database and store it in excel using java

Question

I have to export data from database to excel sheet by using java.I have around 500+ column in DB, so need help in code where i need not to put the column name. I got some code from internet, but its specific to particular column name, if i implement in that way there will be lot of manual work.Any suggestion on code?

      import  java.io.*;  
import  java.sql.*;
import  org.apache.poi.hssf.usermodel.HSSFSheet;  
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import  org.apache.poi.hssf.usermodel.HSSFRow;
import  org.apache.poi.hssf.usermodel.HSSFCell;  

public class DatabaseTesting{
    public static void main(String[]args){
try{
String filename="c:/data.xls" ;
HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet =  hwb.createSheet("new sheet");

//HSSFRow rowhead=   sheet.createRow((short)0);


Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from employee");

XSSFRow row = spreadsheet.createRow(1);
XSSFCell cell;
// use ResultSetMetaData to fetch the column names
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int c=1; c <= columnCount; ++c) {
    String name = rsmd.getColumnName(c);
    cell = row.createCell(c);
    cell.setCellValue(name);
}

int i = 2;

while (resultSet.next()) {
    row = spreadsheet.createRow(i);
    for (int c=1; c <= columnCount; ++c) {
        cell = row.createCell(c);
        cell.setCellValue(resultSet.getObject(C));
    }
}

System.out.println("Your excel file has been generated!");

} catch ( Exception ex ) {
    System.out.println(ex);

}
    }
}

Assuming the above code actually did work for the table and spreadsheet for which it was designed, then all you would need to change would be the JDBC connection details, along with the column names to find the data. Have you tried adjusting this code yet? — Tim Biegeleisen, Jun 26 '19 at 13:45
that is my question, i have 500 column in DB, if i change this code manually there will be lot of manual work. How i can import the all the 500 column in excel from DB without specifying all column name. — Piyush, Jun 26 '19 at 13:53
Use the resultset metadata to iterate the columns; see [`ResultSetMetaData`](https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html — Stephen C, Jun 26 '19 at 13:59
@StephenC I learn something new on this site everyday. Had no idea that `ResultSet` was this powerful. I wonder if every underlying database would implement `ResultSetMetaData` though. — Tim Biegeleisen, Jun 26 '19 at 14:03
@Tim - The JDBC specification says: "A driver that is compliant with the JDBC specification must ... Fully implement the following interfaces: ... java.sql.ResultSetMetaData." Granted, that's no guarantee that every driver will implement the interface completely (or properly), but it *is* a mandatory part of the JDBC spec. — Gord Thompson, Jul 08 '19 at 15:15

Tim Biegeleisen · Answer 1 · 2019-07-08T14:57:13.623

0

The ResultSet class exposes getters which accept a column index (starting at position 1), in addition to the named getters which we are familiar with seeing. You may take advantage of that here:

XSSFRow row = spreadsheet.createRow(1);
XSSFCell cell;
// use ResultSetMetaData to fetch the column names
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int c=1; c <= columnCount; ++c) {
    String name = rsmd.getColumnName(c);
    cell = row.createCell(c);
    cell.setCellValue(name);
}

int i = 2;

while (resultSet.next()) {
    row = spreadsheet.createRow(i);
    for (int c=1; c <= columnCount; ++c) {
        cell = row.createCell(c);
        cell.setCellValue(resultSet.getObject(c));
    }
}

Notes:

If you look at the call to XSSFCell#setCellValue(), you will see that I am using ResultSet#getObject to fetch the value. The reason for this is that we don't know, a priori, what column type the database column would be. I am assuming here that setCellValue can accept an Object as input. If not, then you would have to use some alternative, e.g. using ResultSet#getString() everywhere.

edited Jul 08 '19 at 14:57

answered Jun 26 '19 at 14:00

Tim Biegeleisen

502,043
27
286
360

Thanks Tim for ur help. This will be fix for 500 column if i am not aware how many column, it might be more than 500.How to handle such scenarios. Incase my query wold be select *from tabname; so i am expecting what ever the column would be there all should save in excel. Any help on this. – Piyush Jun 27 '19 at 17:03
@Piyush Use `ResultSetMetaData#getColumnCount()`. – Tim Biegeleisen Jun 27 '19 at 23:38
what is spreadsheet and resultSet, i am getting error and ask for create variable but which is not.I have updated the above code, can you please have a look thanks. – Piyush Jul 08 '19 at 14:42
@Piyush I took these variable names from your own code. I cannot explain it any better than you can. – Tim Biegeleisen Jul 08 '19 at 14:43
in cell.setCellValue(resultSet.getObject(C)); what C indicates? – Piyush Jul 08 '19 at 14:56
Sorry, that should have been a lowercase `c`. – Tim Biegeleisen Jul 08 '19 at 14:57
if i change it to small c, i am getting error as "The method setCellValue(boolean) in the type XSSFCell is not applicable for the arguments (Object)" – Piyush Jul 08 '19 at 15:00

Need to fetch data from database and store it in excel using java

1 Answers1