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;
      	}
      	
      }