package com.javen.excel;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.javen.db.JDBCConn;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
主要是用来创建excel工作薄
*/
public class MysqlToExcelUp {
public void WriteExcel(File fileName,ResultSet rs) throws Exception{
//创建一个新的工作薄,fileName 包含了文件名以及路径。
WritableWorkbook wwb = null;
WritableSheet ws = null;
try {
wwb = Workbook.createWorkbook(fileName);
ws = wwb.createSheet("sheettest", 0);//给工作薄添加一个工作表,命名为 sheettest.
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
int i = 0;
int j = 0;
//下面for循环里面的rs.getMetaData().GetColumnCount() 获取数据库中某个表的列总数
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++)
{
//rs.getMetaData().getColumnName()获取表的列名。并添加到 excel表Label里,Label(i,j,s)表示i列j行添加s,s必须是String
ws.addCell(new Label(k, 0, rs.getMetaData().getColumnName(k + 1)));
}
while (rs.next()) {
//算法,依次添加数据库中所有符合的数据到excel中
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k, j + i + 1, rs.getString(k + 1)));
}
i++;
}
wwb.write();//写入工作薄
wwb.close();//关闭工作薄
rs.close();//关闭结果集
}
public static void main(String[]args){
MysqlToExcelUp mexel = new MysqlToExcelUp();
JDBCConn myjdbc = new JDBCConn();
myjdbc.getConnection();
String sql = "select * from stu";
Statement stm = null;
ResultSet rs = null;
File newFile = new File("D:\\excel.xls");
try {
stm = myjdbc.getConnection().createStatement();
rs = stm.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
mexel.WriteExcel(newFile , rs);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
myjdbc.getConnection().close();
//关闭数据库连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
java.io.FileNotFoundException: D:\excel.xls (拒绝访问。)报这样的错
D:\excel.xls 右击看看文件时不是只读。
http://bbs.csdn.net/topics/390156968
//判断文件不存在,先创建
File newFile = new File("D:\excel.xls");
try{
if(!newFile.exists()){
newFile.getParentFile().mkdirs();
newFile.createNewFile();
}
}catch(Exception e){
e.printStackTrace();
}