- 用到技术
- poi 3.11
- Spring 4.1.4
- 代码如下
-
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; } }
-
Excel根据模板导出+Spring读取配置文件+动态读取sql文件
最新推荐文章于 2022-05-02 18:45:05 发布