Apache POI数据库

本章介绍POI库如何与数据库交互。在JDBC的帮助下，您可以从数据库中检索数据，并使用POI库将该数据插入电子表格中。让我们考虑MySQL数据库进行SQL操作。

从数据库写入Excel

让我们假设从MySQL数据库 测试中 检索名为 emp_tbl 的以下员工数据表。

EMP ID	EMP NAME	DEG	SALARY	DEPT
1201	Gopal	Technical Manager	45000	IT
1202	Manisha	Proof reader	45000	Testing
1203	Masthanvali	Technical Writer	45000	IT
1204	Kiran	Hr Admin	40000	HR
1205	Kranthi	Op Admin	30000	Admin

使用以下代码从数据库中检索数据并将其插入电子表格中。

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 ExcelDatabase {
   public static void main(String[] args) throws Exception {
      Class.forName("com.mysql.jdbc.Driver");
      Connection connect = DriverManager.getConnection(
         "jdbc:mysql://localhost:3306/test" ,
         "root" ,
         "root"
      );

      Statement statement = connect.createStatement();
      ResultSet resultSet = statement.executeQuery("select * from emp_tbl");
      XSSFWorkbook workbook = new XSSFWorkbook();
      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("eid"));
         cell = row.createCell(2);
         cell.setCellValue(resultSet.getString("ename"));
         cell = row.createCell(3);
         cell.setCellValue(resultSet.getString("deg"));
         cell = row.createCell(4);
         cell.setCellValue(resultSet.getString("salary"));
         cell = row.createCell(5);
         cell.setCellValue(resultSet.getString("dept"));
         i++;
      }

      FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("exceldatabase.xlsx written successfully");
   }
}

让我们将上面的代码保存为 ExcelDatabase.java 。从命令提示符编译并执行它，如下所示。

$javac ExcelDatabase.java
$java ExcelDatabase

它将在当前目录中生成名为 exceldatabase.xlsx 的Excel文件，并在命令提示符下显示以下输出。

exceldatabase.xlsx written successfully

该 exceldatabase.xlsx 文件如下所示。

Excel数据库