把MySQL数据转到Excel里面(POI)

package com.marvel.readmysql;


import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;


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 ReadData {


public static void main(String[] args) throws Exception {
String dburl = "jdbc:mysql://127.0.0.1:3306/jyjf2_1_22_2016";
String dbuser = "root";
String dbpwd = "root";
Class.forName("com.mysql.jdbc.Driver");


OutputStream os;
// os=new FileOutputStream("D:\\315\\jyjf2_1_22_2016.xls");
File file = new File("D:\\315\\prefix_j.xls");
HSSFWorkbook wwb = new HSSFWorkbook();// 创建xls文件
HSSFSheet sheet = null;// 设置sheet名


List<String> tableName = new ArrayList<String>();


Map<String, List<Entry<String, String>>> tables = new HashMap<String, List<Entry<String, String>>>();
Connection con = DriverManager.getConnection(dburl, dbuser, dbpwd);
DatabaseMetaData metaDb = con.getMetaData();


// 获取所有的表
ResultSet rsTableName = metaDb.getTables(null, null, null, new String[] { "table" });
int i = 0;


// 循环遍历每一个表结构
while (rsTableName.next()) {


// 获取表名
String tmpTableName = rsTableName.getString("table_name");


if (tmpTableName.substring(0, 1).equals("j")) {

// 根据表名创建一个sheet
sheet = wwb.createSheet(tmpTableName);// 设置sheet名


tableName.add(tmpTableName);
// 查询表结构
String sql = "SHOW FULL COLUMNS FROM " + tmpTableName;
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);


int j = 3;
HSSFRow row = sheet.createRow(1);

HSSFCell cell = row.createCell(0);

cell.setCellValue(tmpTableName);//设置表名

row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellValue("字段");
cell = row.createCell(1);
cell.setCellValue("说明");

// 把数据都读到 excel 里面
while (rs.next()) {


row = sheet.createRow(j);

cell = row.createCell(0);
cell.setCellValue(rs.getString(1));

cell = row.createCell(1);
cell.setCellValue(rs.getString(9));

读了全部
cell = row.createCell(2);
cell.setCellValue(rs.getString(3));
cell = row.createCell(3);
cell.setCellValue(rs.getString(4));
cell = row.createCell(4);
cell.setCellValue(rs.getString(5));
cell = row.createCell(5);
cell.setCellValue(rs.getString(6));
cell = row.createCell(6);
cell.setCellValue(rs.getString(7));
cell = row.createCell(7);
cell.setCellValue(rs.getString(8));

cell = row.createCell(8);
cell.setCellValue(rs.getString(9));
j++;


}


} else {
continue;
}
i++;
}


os = new FileOutputStream(file);
wwb.write(os);
wwb.close();
}
}