Apache POI數據庫

本章介紹了POI庫與數據庫的交互方式。有了JDBC幫助，可以從數據庫中檢索數據並插入數據來使用POI庫電子表格。讓我們考慮SQL操作MySQL數據庫。

寫入數據庫

讓我們假設數據表是 emp_tbl 存有僱員信息是從MySQL數據庫 test 中檢索。

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

使用下面的代碼從數據庫中檢索數據，並插入到同一個電子表格。

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文件如下所示。