java导出数据库里的数据至Excel进行数据备份 - JAVA

调用部分：

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();
		}
		F

java导出数据库里的数据至Excel进行数据备份(一)