调用部分：
package com.otdrmsys.action;
import com.otdrmsys.util.ResultSetToExcel;
public class ExcelExport {
public static void main(String[] args) {
// String fileName = "otdr";//文件名，不带路径，不带.xls后缀
// String [] coloumItems = {"otdr编号","otdr所在站点id","目标光纤段id"};
// String sql = "select * from _otdr";
// String fileName = "变电站";//文件名，不带路径，不带.xls后缀
// String [] coloumItems = {"站点id", "站点名称", "站点等级", "经纬度","经度值", "纬度值"};
// String sql = "select * from _station";
// String fileName = "光纤段";// 文件名，不带路径，不带.xls后缀
// String[] coloumItems = { "光纤段id", "光纤段名称", "起点", "终点", "负责人","负责人手机号" };
// String sql = "select id, name,( "
// + "select name from _station s where s.id = f.startPoint_id ),( "
// + "select name from _station s where s.id=f.endPoint_id ),( "
// + "select name from _repairer r where r.id=f.repairer_id ), ( "
// + "select phoneNum from _repairer r where r.id=f.repairer_id ) "
// + "from _fiberline f;";
// String fileName = "操作记录";// 文件名，不带路径，不带.xls后缀
// String[] coloumItems = { "操作记录id", "操作时间", "操作对象", "操作类型", "操作内容" };
// String sql = "select id, operation_date, object, type, content from _operation;";
// String fileName = "负责人";// 文件名，不带路径，不带.xls后缀
// String[] coloumItems = { "负责人id", "姓名", "手机号" };
// String sql = "select id, name, phoneNum from _repairer;";
// String fileName = "历史故障信息统计";// 文件名，不带路径，不带.xls后缀
// String[] coloumItems = { "统计id", "光纤段名称", "故障次数","故障总时长" };
// String sql = "select id, fiberLine_name, errorTimes, totalDuration from _statistic;";
// String fileName = "网站用户";// 文件名，不带路径，不带.xls后缀
// String[] coloumItems = { "用户id", "用户名", "用户角色", "真实姓名" };
// String sql = "select id, user_name, role_id, realname from user;";
String fileName = "故障记录";// 文件名，不带路径，不带.xls后缀
String[] coloumItems = { "故障记录id", "光纤段名称", "故障经度", "故障纬度", "故障时间", "修复时间", "故障时长", "是否已修复" };
String sql = "select id, fiberLine_name, error_lon, error_lat, error_date, recover_date, duration, fixStatus from _errorrecord;";
ResultSetToExcel.writeExcel(fileName, coloumItems, sql);
}
}
功能封装部分：
一、导出Excel功能封装
package com.otdrmsys.util;
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;
/***
* 将数据库中的数据导出成Excel 说明：该类是在apache的poi组件来实现的
* 用户只要提供给writeExcel方法文件名，列名,和一个sql查询语句就可以导出数据到excel文件
*
*/
public class ResultSetToExcel {
/**
* 写Excel操作
*
* @param fileName
* 文件名，但不需要后缀名
* @param coloumItems
* 字段名，即表中的每一列的名称
* @param sql
* 数据库查询语句
*/
public static String backupPath = "D:/网站数据备份目录_勿删/";
public static void writeExcel(String fileName, String[] coloumItems,
String sql) {
File backupFolder = new File(backupPath);
if (!backupFolder.exists()) {
backupFolder.mkdir();
}
Connection conn = DB.createConn();
PreparedStatement ps = DB.prepare(conn, sql);
ResultSet rs = null;
try {
rs = ps.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(backupPath + fileName
+ ".xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
createTag(coloumItems, sheet);// 写表格的列名
createValue(rs, 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();
}
}
}
DB.close(ps);
DB.close(conn);
}
/**
* 创建表格表头
*
* @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();
}
}
}
二、数据库操作封装
package com.otdrmsys.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DB {
public static Connection createConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDataBaseName", "root", "123456");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static PreparedStatement prepare(Connection conn, String sql) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
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();
}
}
}