在Java编程中，如何将数据从数据库插入到电子表格中？

注意：需要访问网址：http://poi.apache.org/download.html ， 下载一个Apache POI软件包。这里下载最新版本：poi-bin-3.17-20170915.tar.gz解压并将全部.jar文件导入 。

需要导入全部包(在原来的基础上增加连接到数据库的jar包)，如下图所示 -

参考示例：

http://poi.apache.org/spreadsheet/quick-guide.html

在数据库：testdb中，创建一个emp表，其表结构和数据记录如下 -

CREATE TABLE `emp` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `age` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `salary` float(10,2) unsigned DEFAULT '0.00',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('100', '王大大', '25', '8100.00', '技术');
INSERT INTO `emp` VALUES ('101', ' 李小伟', '38', '1200.00', '市场');
INSERT INTO `emp` VALUES ('102', ' 张方择', '29', '13500.00', '综合');

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

package com.yiibai;

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/testdb?useSSL=false", "root" , "123456");

      //Getting data from the table emp_tbl
      Statement statement = connect.createStatement();
      ResultSet resultSet = statement.executeQuery("SELECT * FROM emp");

      //Creating a Work Book
      XSSFWorkbook workbook = new XSSFWorkbook();

      //Creating a Spread Sheet
      XSSFSheet spreadsheet = workbook.createSheet("员工数据信息");
      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell;

      cell = row.createCell(1);
      cell.setCellValue("编号");

      cell = row.createCell(2);
      cell.setCellValue("姓名");

      cell = row.createCell(3);
      cell.setCellValue("年龄");

      cell = row.createCell(4);
      cell.setCellValue("薪水");

      cell = row.createCell(5);
      cell.setCellValue("部门");
      int i = 2;

      while(resultSet.next()) {
         row = spreadsheet.createRow(i);
         cell = row.createCell(1);
         cell.setCellValue(resultSet.getInt("emp_id"));

         cell = row.createCell(2);
         cell.setCellValue(resultSet.getString("name"));

         cell = row.createCell(3);
         cell.setCellValue(resultSet.getString("age"));

         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("excel_from_database.xlsx"));

      workbook.write(out);
      out.close();

      System.out.println("excel_from_database.xlsx written successfully");
   }
}

执行上面示例代码，得到以下结果 -

excel_from_database.xlsx written successfully

创建的Excel文件内容，如下所示 -

纠错/补充 收藏