如何使用Java将数据从数据库插入到电子表格中。（How to insert data from a database to a spread sheet using Java.）

问题描述 (Problem Description)

如何使用Java将数据从数据库插入到电子表格中。

解决方案 (Solution)

以下是使用Java将数据从数据库插入电子表格的程序。

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class InsertDataFromDataBaseToSpreadSheet {
   public static void main(String[] args) throws Exception {
      //Connecting to the database
      Class.forName("com.mysql.jdbc.Driver");
      Connection connect = DriverManager.getConnection(
         "jdbc:mysql://localhost:3306/details", "root" , "password");
      //Getting data from the table emp_tbl
      Statement statement = connect.createStatement();
      ResultSet resultSet = statement.executeQuery("select * from student_data");
      //Creating a Work Book
      XSSFWorkbook workbook = new XSSFWorkbook();
      //Creating a Spread Sheet
      XSSFSheet spreadsheet = workbook.createSheet("employe db");
      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell;
      cell = row.createCell(1);
      cell.setCellValue("EMP ID");
      cell = row.createCell(2);
      cell.setCellValue("EMP NAME");
      cell = row.createCell(3);
      cell.setCellValue("DEG");
      cell = row.createCell(4);
      cell.setCellValue("SALARY");
      cell = row.createCell(5);
      cell.setCellValue("DEPT");
      int i = 2;
      while(resultSet.next()) {
         row = spreadsheet.createRow(i);
         cell = row.createCell(1);
         cell.setCellValue(resultSet.getInt("ID"));
         cell = row.createCell(2);
         cell.setCellValue(resultSet.getString("NAME"));
         cell = row.createCell(3);
         cell.setCellValue(resultSet.getString("BRANCH"));
         cell = row.createCell(4);
         cell.setCellValue(resultSet.getString("PERCENTAGE"));
         cell = row.createCell(5);
         cell.setCellValue(resultSet.getString("EMAIL"));
         i++;
      }
      FileOutputStream out = new FileOutputStream(
         new File("C:/poiexcel/exceldatabase.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("exceldatabase.xlsx written successfully");
   }
}

数据库 (Database)

mysql> select * from student_data; 
+----+--------+--------+------------+---------------------+ 
| ID | NAME   | BRANCH | PERCENTAGE | EMAIL               | 
+----+--------+--------+------------+---------------------+ 
|  1 | Ram    | IT     |         85 | ram123@gmail.com    | 
|  2 | Rahim  | EEE    |         95 | rahim123@gmail.com  | 
|  3 | Robert | ECE    |         90 | robert123@gmail.com | 
+----+--------+--------+------------+---------------------+ 
3 rows in set (0.00 sec)

结果 (Result)