Write Database Data to an Excel File
【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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/