用Java从数据库生成表格(.xls)
下面展示一些 内联代码片
。
//注意:要导入两个jar包
1:mysql-connector-java-5.1.19-bin.jar
2:poi-3.13.jar
//dname:数据库;tname:数据库指定的表;pan:指定哪个盘;ran:指定哪个文件夹;
也可以使用Scanner用户控制台输出,效果会更好
```javascript
// 代码实现部分
import java.sql.Statement;
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.SQLException;
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;
public class TestRan {
//dname:数据库;tname:数据库指定的表;pan:指定哪个盘;ran:指定哪个文件夹;
public static void writerDBtoExcel(String dname,String tname,String pan,String ran) throws IOException, ClassNotFoundException{
File file = new File(""+pan+":/"+ran+"/"+tname+".xls");
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet("表");//创建xls工作表名
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dname+"?characterEncoding=utf-8", "root", "313428");
Statement st =conn.createStatement();
String sql = "select * from "+tname+"";
//数据库查询到然后返回的集
ResultSet rs = st.executeQuery(sql);
//得到数据库表中的列数
ResultSetMetaData rsmd = rs.getMetaData();
int c = rsmd.getColumnCount();
//创建表头
HSSFRow row0 = sheet.createRow(0);
for(int i=0;i<c;i++){
//找到表头每一列
HSSFCell cel = row0.createCell(i);
//找到后写每一列的值
cel.setCellValue(rsmd.getColumnName(i+1));
}
//开始写其余列
//在写文件时候:行数是从0开始数,列数是从1开始数
int r = 1;
while(rs.next()){
HSSFRow row = sheet.createRow(r++);
for(int i=0;i<c;i++){
HSSFCell cel = row.createCell(i);
cel.setCellValue(rs.getString(rsmd.getColumnName(i+1)));
}
}
//创建文件夹
File file2 = new File(""+pan+":/"+ran);
boolean flag = file2.mkdirs();
System.out.println(flag);
//写进xls
FileOutputStream out = new FileOutputStream(file);
book.write(out);
book.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//测试代码部分
// public static void main(String[] args){
// try {
// writerDBtoExcel("ran", "yingxiong", "D", "ran/jerry");
// } catch (ClassNotFoundException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } catch (IOException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}