import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
*/
/**
- @author 2019-3-5>>上午9:16:29
/
public class writeDbtoExcel {
/*
* 将数据库dname中的表名为tname的数据表写入Excel表格
*
* @param dname
* @param tname
*/
public static void main(String[] args) {
String tname = “tname”;
String dname = “dname”;
Excel(dname, tname);
}
public static Object Excel(String dname, String tname) {
String path = "E:/excel2/excel7.xls";
File file = new File(path);
try {
if (!file.exists()) {//判断文件是否存在
//如果不存在就创建
file.createNewFile();
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
Class.forName("com.mysql.jdbc.Driver");
Connection con;
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"
+ dname + "?useUnicode=true&charterEncoding=utf-8", "root",
"123456");
Statement st = con.createStatement();
String sql = "select name,sex,num from " + tname +" where id=1";
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData rms = rs.getMetaData();// 得到结果集的字段名
int c = rms.getColumnCount();// 得到数据表的结果集的字段显示
// 生成表单的第一行,即表头
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < c; i++) {
HSSFCell cell = row.createCell(i);// 创建第一行的第一列
cell.setCellValue(rms.getColumnName(i + 1));
}
int r = 1;
while (rs.next()) {
HSSFRow row2 = sheet.createRow(r++);// 创建非第一行的其他行
for (int i = 0; i < c; i++) {
HSSFCell cell = row2.createCell(i);
cell.setCellValue(rs.getString(i + 1));
}
}
FileOutputStream stream = new FileOutputStream(file);
workbook.write(stream);
// workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
return file;
}
}
希望可以帮助你。。。。