需求:将数据库中的某些表格导出成excl表格。
流程:
1.在本地环境中创建excl表格;
package test.cxg.demo1;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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;
import test.cxg.demo2.DataBase;
/***
* 将数据库中的数据导出成Excel 说明:该类是在apache的poi组件来实现的
* 用户只要提供给writeExcel方法文件名,列名,和一个sql查询语句就可以导出数据到excel文件
*
*/
public class ResultSetToExcel {
/**
* 写Excel操作
* @param fileName文件名,但不需要后缀名
* @param coloumItems字段名,即表中的每一列的名称
* @param sql数据库查询语句
*/
public static String path="D:/data_copy/";
public static void writeExcel(String fileName, String[] coloumItems,String sql) {
//写文件
File folder=new File(path);
if (!folder.exists()) {
folder.mkdir();
}
//连接数据库
Connection connection=DataBase.createConn();
PreparedStatement preparedStatement=DataBase.prepare(connection, sql);
ResultSet resultSet=null;
try {
resultSet=preparedStatement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
//文件输出流
FileOutputStream fileOutputStream=null;
try {
fileOutputStream = new FileOutputStream(path + fileName+".xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
createTag(coloumItems, sheet);// 写表格的列名
createValue(resultSet, sheet);// 获取数据集,然后获得数据,写文件
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
DataBase.close(preparedStatement);
DataBase.close(connection);
}
/**
* 创建表格表头
*
* @param tags
* @param s
*/
private static void createTag(String[] tags, HSSFSheet s) {
HSSFRow row = s.createRow(0);
HSSFCell cell = null;
for (int i = 0; i < tags.length; i++) {
cell = row.createCell(i);
cell.setCellValue(tags[i]);
}
}
/**
* 设置表格内容
*
* @param res
* @param s
*/
private static void createValue(java.sql.ResultSet res, HSSFSheet s) {
try {
int flag = 1;
int count = res.getMetaData().getColumnCount();
HSSFRow row = null;
HSSFCell cell = null;
while (res.next()) {
row = s.createRow(flag);
for (int i = 1; i <= count; i++) {
cell = row.createCell(i - 1);
Object obj = res.getObject(i);
cell.setCellValue(obj + "");
}
flag++;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.连接数据库,读取数据库;
package test.cxg.demo2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 封装数据库的相关的操作
* @author Administrator
*
*/
public class DataBase {
/**
* 连接数据库
* @return
*/
public static Connection createConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/so", "root", "");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 数据库的读取
* @param conn
* @param sql
* @return
*/
public static PreparedStatement prepare(Connection conn, String sql) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
/**
* 关闭连接
* @param conn
*/
public static void close(Connection conn) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement stmt) {
try {
stmt.close();
stmt = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.输出处理的结果集。
package test.cxg.demo3;
import test.cxg.demo1.ResultSetToExcel;
public class test {
public static void main(String[] args) {
//创建相关参数
String fileName = "网站用户";// 文件名,不带路径,不带.xls后缀
String[] coloumItems = { "用户id", "创建时间", "会员ID", "昵称" ,"类型","卖商","微信邀请函会员情况",
"代理级别","父ID","孩子数量","总销量", "本月销量", "账户", "会员微信", "所有上级"};
String sql = "select id,gmt_create,member_id,nickname,itype,soshop_id,"
+ "wxinvit_member_id,soagent,parent,childrennum,total,months,"
+ "account,openid,family from soshopmember;";
//开始写表格
ResultSetToExcel.writeExcel(fileName, coloumItems, sql);
System.out.println("数据导出成功!");
}
}