简易MySQL数据库下载成为excel文档

简易MySQL数据库下载成为excel文档

package cn.excel;

import java.io.FileOutputStream;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ResultToExcel {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/insertexcel";
String filename = "D:\\test.xlsx";
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, "root", "1234");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from courses");
ResultSetMetaData colName = rs.getMetaData();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("stuDB");
Row row = sheet.createRow((short) 0);
Cell cell = null;
cell = row.createCell((short) 0);
cell.setCellValue(colName.getColumnName(1));
cell = row.createCell((short) 1);
cell.setCellValue(colName.getColumnName(2));
cell = row.createCell((short) 2);
cell.setCellValue(colName.getColumnName(3));
cell = row.createCell((short) 3);
cell.setCellValue(colName.getColumnName(4));
cell = row.createCell((short) 4);
cell.setCellValue(colName.getColumnName(5));
cell = row.createCell((short) 5);
cell.setCellValue(colName.getColumnName(6));
cell = row.createCell((short) 6);
cell.setCellValue(colName.getColumnName(7));
int i = 1;
while (rs.next()) {
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(rs.getInt("id"));
cell = row.createCell(1);
cell.setCellValue(rs.getString("dept"));
cell = row.createCell(2);
cell.setCellValue(rs.getString("course"));
cell = row.createCell(3);
cell.setCellValue(rs.getString("book_num"));
cell = row.createCell(4);
cell.setCellValue(rs.getDouble("credit"));
cell = row.createCell(5);
cell.setCellValue(rs.getDate("publish_date"));
cell = row.createCell(6);
cell.setCellValue(rs.getString("book_name"));
i++;
}
FileOutputStream FOut = new FileOutputStream(filename);
workbook.write(FOut);
FOut.flush();
FOut.close();
rs.close();
stmt.close();
conn.close();
System.out.println("Success");
} catch (Exception e) {
e.printStackTrace();
}
}

}