java、Extjs匯出資料庫裡的資料至Excel並下載至本地進行資料備份方法之一——使用servlet__Oracle

推薦使用這種方式，因為封裝性更好，當需要匯出的表列需要變動時，只需要在js檔案中改動就行了，不像使用Action方法時需要變動Action中的Java代碼。

每次點擊匯出按鈕後，調用ExportExcel函數並傳入fileName、columnItemsStr、sql三個參數，該函數完成Excel檔案匯出至用戶端的功能。傳入參數時需要注意Extjs傳遞中文參數至後台時亂碼的處理。本文中已經做了處理，具體是：在js中對中文參數作兩次編碼處理，如：var fileName = "光纖段"; var url = 'ExportExcel' + '?fileName=' + encodeURI(encodeURI(fileName)) + '&columnItemsStr=' + encodeURI(encodeURI(columnItemsStr)) +'&sql=' + sql;，後台接收參數時，要進行解碼：String fileName = URLDecoder.decode(request.getParameter("fileName"),"UTF-8");，這樣在後台就能擷取到中文參數。

servlet檔案內容（匯出Excel檔案功能的封裝）：

package com.otdrmsys.servlet;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.net.URLDecoder;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.log4j.Logger;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;import com.otdrmsys.util.DB;/** * Servlet implementation class ExportExcel */public class ExportExcel extends HttpServlet {private static final long serialVersionUID = 1L;public static final Logger errorLogger = Logger.getLogger("dailyError");public static final Logger infoLogger = Logger.getLogger("dailyInfo");     /**     * @see HttpServlet#HttpServlet()     */    public ExportExcel() {        super();    }/** */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {String fileName = URLDecoder.decode(request.getParameter("fileName"),"UTF-8");String columnItemsStr = URLDecoder.decode(request.getParameter("columnItemsStr"),"UTF-8");String sql = request.getParameter("sql");String[] columnItems = columnItemsStr.split(",");//從字串形式的列名中擷取列名數組Connection conn = DB.createConn();PreparedStatement ps = DB.prepare(conn, sql);ResultSet rs = null;try {rs = ps.executeQuery(sql);} catch (SQLException e) {e.printStackTrace();}HSSFWorkbook workbook = new HSSFWorkbook();try {HSSFSheet sheet = workbook.createSheet();createTag(columnItems, sheet);// 寫表格的列名createValue(rs, sheet);// 擷取資料集，然後獲得資料，寫檔案//通過Response把資料以Excel格式儲存          response.reset();         response.setContentType("application/msexcel;charset=UTF-8");        response.setHeader("Content-Disposition", "attachment;filename=\""          + new String((fileName + ".xls").getBytes("GBK"),"ISO8859_1") + "\"");        OutputStream outputStream = response.getOutputStream();              workbook.write(outputStream); //輸出檔案到用戶端            outputStream.flush();              outputStream.close();  } catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} DB.close(ps);DB.close(conn);}/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}/** * 建立表格表頭 *  * @param tags * @param sheet */private static void createTag(String[] tags, HSSFSheet sheet) {HSSFRow row = sheet.createRow(0);HSSFCell cell = null;// 定義儲存格為字串類型   for (int i = 0; i < tags.length; i++) {cell = row.createCell(i);cell.setCellValue(tags[i]);//cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文處理 }}/** * 設定表格內容 *  * @param res * @param sheet */private static void createValue(java.sql.ResultSet res, HSSFSheet sheet) {try {int flag = 1;int count = res.getMetaData().getColumnCount();HSSFRow row = null;HSSFCell cell = null;while (res.next()) {row = sheet.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();}}}

DB封裝：

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/wenling_map_db", "root", "hel610");} 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();}}}

Extjs代碼中的調用語句：

{id: 'fiberLineBackupBtnId',text: '匯出',hidden: true,tooltip: '備份光纖段資訊',iconCls: 'save',handler: function(){var fileName = "光纖段";// 檔案名稱，不帶路徑，不帶.xls尾碼var columnItemsStr = "光纖段id,光纖段名稱,起點,終點,負責人,負責人手機號";var 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;";var url = 'ExportExcel' + '?fileName=' + encodeURI(encodeURI(fileName)) + '&columnItemsStr=' + encodeURI(encodeURI(columnItemsStr)) +'&sql=' + sql;window.location.href = url ;}}

實現效果如下：

下載後的檔案內容：