Excel根据模板导出+Spring读取配置文件+动态读取sql文件

1. 用到技术
1. poi 3.11
2. Spring 4.1.4
2. 代码如下

1. package com.fh.server.util;
   
   import java.io.BufferedInputStream;
   import java.io.BufferedReader;
   import java.io.File;
   import java.io.FileInputStream;
   import java.io.FileNotFoundException;
   import java.io.FileOutputStream;
   import java.io.IOException;
   import java.io.InputStream;
   import java.io.InputStreamReader;
   import java.net.URL;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.text.SimpleDateFormat;
   import java.util.Date;
   import java.util.HashMap;
   import java.util.Map;
   import java.util.Properties;
   
   import org.apache.poi.hssf.usermodel.HSSFCell;
   import org.apache.poi.hssf.usermodel.HSSFCellStyle;
   import org.apache.poi.hssf.usermodel.HSSFFont;
   import org.apache.poi.hssf.usermodel.HSSFRow;
   import org.apache.poi.hssf.usermodel.HSSFSheet;
   import org.apache.poi.hssf.usermodel.HSSFWorkbook;
   import org.apache.poi.poifs.filesystem.POIFSFileSystem;
   
   import com.mysql.jdbc.Connection;
   
   public class SqlManager {
   
   	private static SqlManager sqlManager = null;
   
   	private static Connection con;
   	private static Map<String, String> sqls = new HashMap<String, String>();
   	private static File fi = null;
   	static String fileName;
   	private static POIFSFileSystem fs;
   	private static HSSFWorkbook wb = null;
   	private static FileOutputStream out;
   	static String[] sql;
   	private static String url = "";
   
   	private SqlManager() {
   		try {
   			// excel模板路径
   			URL excelUrl = SqlManager.class.getClassLoader().getResource("template.xls");
   			String path = excelUrl.getPath();
   			fi = new File(path);
   			fs = new POIFSFileSystem(new FileInputStream(fi));
   			// // 读取excel模板
   			wb = new HSSFWorkbook(fs);
   			SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHMM");
   			fileName = path + "数据分析（信息化问卷调查系统）_" + format.format(new Date()) + ".xls";
   			Class.forName("com.mysql.jdbc.Driver");
   			Class.forName("com.mysql.jdbc.Driver").newInstance();
   			url = GetJDBCUrl();
   			con = (Connection) DriverManager.getConnection(url);
   			sql = getSql(SqlManager.class, "votereports").split(";");
   		} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException
   				| IOException e) {
   			e.printStackTrace();
   		}
   	}
   
   	public static SqlManager getInstance() {
   		if (sqlManager == null) {
   			sqlManager = new SqlManager();
   		}
   		return sqlManager;
   	}
   
   
   	public static <T> String getSql(Class<T> resourceClass, String id) {
   		String key = resourceClass.getName() + "." + id;
   		String sql = (String) sqls.get(key);
   		if (sql == null) {
   			sql = readFromFile(resourceClass, id);
   		}
   		return sql;
   	}
   
   	private static <T> String readFromFile(Class<T> resourceClass, String id) {
   		// System.out.println(resourceClass.getName());
   		String content = "";
   		InputStream inputstream = null;
   		InputStreamReader reader = null;
   		BufferedReader br = null;
   		try {
   			String sqlfile = id + ".sql";
   			inputstream = resourceClass.getResourceAsStream(sqlfile);
   			if (inputstream == null) {
   				return null;
   			}
   			reader = new InputStreamReader(inputstream, "utf-8");
   
   			br = new BufferedReader(reader);
   			String line = null;
   			while ((line = br.readLine()) != null) {
   				content += line + "\n";
   			}
   			System.out.println(content);
   		} catch (IOException ex2) {
   			ex2.printStackTrace();
   		} finally {
   			if (inputstream != null) {
   				try {
   					inputstream.close();
   				} catch (IOException ex) {
   				}
   			}
   
   		}
   
   		return content;
   	}
   
   	public static void main(String[] args) throws FileNotFoundException {
   
   		SqlManager sqlmgr = new SqlManager();
   		try {
   			fs = new POIFSFileSystem(new FileInputStream(sqlmgr.fi));
   			// 读取excel模板
   			wb = new HSSFWorkbook(fs);
   		} catch (IOException e1) {
   			e1.printStackTrace();
   			System.out.println();
   		}
   
   		if (con != null) {
   			setExcel(sql, wb);
   			try {
   				out = new FileOutputStream(fileName);
   				wb.write(out);
   				out.close();
   			} catch (IOException e) {
   				e.printStackTrace();
   			}
   		}
   
   	}
   
   	// 读取Properties的全部信息
   	private static String GetJDBCUrl() throws IOException {
   		String filePath = SqlManager.class.getClassLoader().getResource("config.properties").getPath();
   		Properties pps = new Properties();
   		InputStream in = new BufferedInputStream(new FileInputStream(filePath));
   		pps.load(in);
   		String baseUrl = pps.getProperty("jdbc.url");
   		String username = pps.getProperty("jdbc.username");
   		String pwd = pps.getProperty("jdbc.password");
   		return baseUrl + "&user=" + username + "&password=" + pwd;
   	}
   
   	private static void setExcel(String[] sql, HSSFWorkbook wb) {
   		HSSFSheet sheet = null;
   		HSSFCellStyle cellStyle = null;
   		HSSFFont font = null;
   
   		for (int i = 0; i < sql.length; i++) {
   			// 读取了模板内所有sheet内容
   			sheet = wb.getSheetAt(i);
   			cellStyle = wb.createCellStyle();
   			font = wb.createFont();
   			font.setFontName("微软雅黑");
   			font.setFontHeightInPoints((short) 10);// 设置字体大小
   			cellStyle.setFont(font);
   			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   
   			// 修改模板内容导出新模板
   			try {
   				ResultSet set = con.createStatement().executeQuery(sql[i]);
   				int numberofrows = set.getMetaData().getColumnCount();
   				// List setData = resultSetToList(set);
   				int rowNm = 1;
   				while (set.next()) {
   					HSSFRow row = sheet.createRow(rowNm);
   					// row.setRowStyle(cellStyle);
   					for (int j = 1; j <= numberofrows; j++) {
   						HSSFCell cell = row.createCell(j - 1);
   						cell.setCellStyle(cellStyle);
   						cell.setCellValue(set.getString(j));
   					}
   					rowNm++;
   				}
   			} catch (SQLException e) {
   				e.printStackTrace();
   			}
   		}
   
   	}
   
   	public static String getSql(Object obj, String id) {
   		return getSql(obj.getClass(), id);
   	}
   
   	public static HSSFWorkbook getWorkBook() {
   		SqlManager sqlmgr = new SqlManager();
   		if (con == null) {
   			try {
   				con = (Connection) DriverManager.getConnection(url);
   			} catch (SQLException e) {
   				e.printStackTrace();
   			}
   		}
   		setExcel(sql, wb);
   //		try {
   //			out = new FileOutputStream(fileName);
   //			con.close();
   //			return out;
   //		} catch (FileNotFoundException | SQLException e) {
   //			e.printStackTrace();
   //			return null;
   //		}
   		return wb;
   	}
   	
   }