如何使用Java将数据从数据库插入到电子表格中。(How to insert data from a database to a spread sheet using Java.)
问题描述 (Problem Description)
如何使用Java将数据从数据库插入到电子表格中。
解决方案 (Solution)
以下是使用Java将数据从数据库插入电子表格的程序。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/details", "root" , "password");
//Getting data from the table emp_tbl
Statement statement = connect.createStatement();
ResultSet resultSet = statement.executeQuery("select * from student_data");
//Creating a Work Book
XSSFWorkbook workbook = new XSSFWorkbook();
//Creating a Spread Sheet
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("ID"));
cell = row.createCell(2);
cell.setCellValue(resultSet.getString("NAME"));
cell = row.createCell(3);
cell.setCellValue(resultSet.getString("BRANCH"));
cell = row.createCell(4);
cell.setCellValue(resultSet.getString("PERCENTAGE"));
cell = row.createCell(5);
cell.setCellValue(resultSet.getString("EMAIL"));
i++;
}
FileOutputStream out = new FileOutputStream(
new File("C:/poiexcel/exceldatabase.xlsx"));
workbook.write(out);
out.close();
System.out.println("exceldatabase.xlsx written successfully");
}
}
数据库 (Database)mysql> select * from student_data;
+----+--------+--------+------------+---------------------+
| ID | NAME | BRANCH | PERCENTAGE | EMAIL |
+----+--------+--------+------------+---------------------+
| 1 | Ram | IT | 85 | ram123@gmail.com |
| 2 | Rahim | EEE | 95 | rahim123@gmail.com |
| 3 | Robert | ECE | 90 | robert123@gmail.com |
+----+--------+--------+------------+---------------------+
3 rows in set (0.00 sec)
结果 (Result)