【Question】

I need your help in storing the fetched values from a SQL statement to an Excel file. I wrote the following code, but I am facing difficulties on how to write the fetched values under the appropriate columns. For example at the beginning, I created 3 headers (ID - Name - Salary). Now, I need to write the fetched values from the SQL statement under each appropriate header, but I do not know how to write them. The code is:

 

public void GenerateExcel() {

Connection conn = null;

Statement stmt = null;

FileOutputStream fileOut = new FileOutputStream("C:\\Desktop\\poi-test.xls");

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet worksheet = workbook.createSheet("Employee Details");

HSSFRow row1 = worksheet.createRow((short) 0);

HSSFCell cellA1 = row1.createCell((short) 0);

cellA1.setCellValue("ID");

HSSFCell cellB1 = row1.createCell((short) 1);

cellB1.setCellValue("Name");

HSSFCell cellC1 = row1.createCell((short) 1);

cellC1.setCellValue("Salary");

try{

Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

stmt = conn.createStatement();

 

String sql = "SELECT id, name, amount FROM Employee";

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()){

int id = rs.getInt("id");

int age = rs.getString("name");

String first = rs.getInt("amount");

}

rs.close();

workbook.write(fileOut);

fileOut.flush();

fileOut.close();

}catch(SQLException se){

se.printStackTrace();

}catch(Exception e){

e.printStackTrace();

}finally{

try{

if(stmt!=null)

conn.close();

}catch(SQLException se){

}

try{

if(conn!=null)

conn.close();

}catch(SQLException se){

se.printStackTrace();

}

 }

}

}

 

【Answer】

Java isn’t the only way of writing database data to an Excel file. There’s an easier route: create the data source and export data to the target file in SPL (Structured Process Language). Here’s the script:

	A
1	$select id, name, amount   FROM Employee
2	=file("C:\\Desktop\\poi-test.xls  ").xlsexport@t(A1)

 

A1: Establish a data source connection.

A2: Output data to an Excel file.

 

xlsexport() function writes data to an xls or xlsx file; @t option enables writing column headers to the first row. You can execute the above SPL script from the command line and integrate it into a Java program. More explanations can be found in How to Call an SPL Script in Java.