java、Extjs导出数据库里的数据至Excel并下载至本地进行数据备份方法之二——使用Action
来源:互联网 发布:淘宝手机端描述尺寸 编辑:程序博客网 时间:2024/06/06 08:28
辅助类,导出Excel文件功能的封装类:
package com.otdrmsys.util;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;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 org.apache.struts2.ServletActionContext;public class ExportExcel {public static final Logger errorLogger = Logger.getLogger("dailyError");public static final Logger infoLogger = Logger.getLogger("dailyInfo");public static void exportExcel(String fileName, String[] columnItems,String sql) {HttpServletResponse response = ServletActionContext.getResponse();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);}/** * 创建表格表头 * * @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", "123456");} 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();}}}
/* * 备份数据 */public void backup() {String fileName = "光纤段";// 文件名,不带路径,不带.xls后缀String[] columnItems = { "光纤段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;";ExportExcel.exportExcel(fileName, columnItems, sql); }
Extjs代码中的调用语句:
{id: 'fiberLineBackupBtnId',text: '导出',hidden: true,tooltip: '备份光纤段信息',iconCls: 'save',handler: function(){/**使用servlet方式**///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 ;/**使用Action方式**/window.location.href = 'fiberLine_backup.action' ;}}实现效果与上一篇博客完全一样。
0 0
- java、Extjs导出数据库里的数据至Excel并下载至本地进行数据备份方法之二——使用Action
- java、Extjs导出数据库里的数据至Excel并下载至本地进行数据备份方法之一——使用servlet
- Java实现数据库数据导出到Excel中并下载到浏览器本地
- 将数据库里的数据导出到EXCEL中的方法
- java将数据库里的数据导出到excel
- java将数据库里的数据导出到excel
- .net MVC 数据库数据导出到Excel表格并下载到本地
- ExtJS:Grid数据导出至excel实例
- Extjs导出excel数据
- Extjs将GridPanel中的数据导出到Excel的方法
- PHP将Excel导入数据库及数据库数据导出至Excel的方法
- PHP将Excel导入数据库及数据库数据导出至Excel的方法 亲测
- Java表格数据导出EXCEL 并从服务器下载实例
- java查询数据导出excel并返回给浏览器下载
- 将数据库里的数据保存到本地Excel
- java读取数据库数据并导出到EXCEL中
- java将数据库里的数据导出到excel(转)
- PHP使用外部命令导出数据库,备份到服务器并下载到本地
- 相似图片检索实践:surf+bovw+kmeans+lsh
- Channel类详解[1-3]
- O2O模式5种具有代表性的尝试
- JavaScript(10)正则表达式
- 未来研究院“十年预测”:思考科技对未来社会的影响
- java、Extjs导出数据库里的数据至Excel并下载至本地进行数据备份方法之二——使用Action
- hive优化原则
- leetcode: Best Time to Buy and Sell Stock
- 初识contiki(2.7版本)
- C运行时库(C Run-time Library)详解
- 技术人员的提升和自我推销
- SEO从业者必须了解的搜索引擎工作原理
- c++单例实现
- hive优化思路