从数据库读数据写到 xls

【问题】

I need your help in storing the fetched values from a SQL statement to an excel file. I wrote the below 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. So kindly assist. 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();

      }

   }

}

}

【回答】

数据库到Excel的问题,并非一定要用java编程去处理,推荐SPL去解决,只需建立数据源并输出数据。如下代码:


A

1

$select id, name, amount FROM Employee

2

=file("C:\\Desktop\\poi-test.xls ").xlsexport@t(A1)

 

A1:建立数据源连接

A2:将数据输出到Excel文件

 

函数xlsexport支持导出为xlsxlsx@t表示在首行写入字段名。上述代码不仅可以在命令行执行,也很容易集成到JAVA,参考【Java 如何调用 SPL 脚本