Apache Poi 简明教程

Apache POI - Database

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

Write into Excel from Database

让我们假设要从 MySQL 数据库 test 中检索以下名为 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 文件如下所示。

exceldatabase