java从excel表中取出数据再向mysql中插入数据_Java如何将数据从数据库插入到电子表格中？...

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

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

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

参考示例：

在数据库：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文件内容，如下所示 –

¥ 我要打赏 纠错/补充 收藏