首页 > 代码库 > Java 从数据库中查找信息导入Excel表格中
Java 从数据库中查找信息导入Excel表格中
前端js
function Excel (){ //ajax请求 $.ajax({ url : "outPutAboutShopInfo", type : "post", dataType : "json", data:{ "basicShop.shopId" : shopId, "basicShop.shopMemo" : stringType //不方便增加字段所以使用门店的一个“备注”字段来接收‘类型‘ }, success : function(data) { window.location.href = data.communal.data; } }); }
后端 Java
该方法是将数据插入excel中，将excel保存到服务器中，然后访问服务器下载文件，方法不怎么好
需要导入的jar包
第一：获得数据的方法
使用存储过程获得数据
举例：
// 这个存储过程的核心就是一条简单的sql语句
DELIMITER $$ DROP PROCEDURE IF EXISTS `pahung82`.`ht_out_put_mem_phone`$$ CREATE DEFINER=`root`@`%` PROCEDURE `ht_out_put_mem_phone`( in shopId varchar(40) -- 门店Id ) BEGIN SELECT VIP_NAME ,VIP_TELEPHONE from mem_vip where SHOP_ID = shopId; END$$ DELIMITER ;
结果类似如下：
第二：调用存储过程，将数据写入excel表格
1.调用存储过程
Session session = null; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; // 存储过程 条件 门店Id String shopId = basicShop.getShopId(); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection();
// 存储过程调用 cs = conn.prepareCall("{call ht_out_put_mem_phone(?)}"); cs.setString(1, shopId);// 填充参数 boolean hadResults = false;
// 运行存储过程 hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值
// 这里就是循环结果集的值
} } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); }
2.将数据写入excel表格
Session session = null; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; // 存储过程 条件 门店Id String shopId = basicShop.getShopId(); // 1.创建一个workbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 2.在workbook中添加一个sheet，对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet("手机号"); // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 4.创建单元格，设置值表头，设置表头居中 HSSFCellStyle style = wb.createCellStyle(); // 居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("手机号"); cell.setCellStyle(style); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection(); cs = conn.prepareCall("{call ht_out_put_mem_phone(?)}"); cs.setString(1, shopId); boolean hadResults = false; hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值 row.createCell(0).setCellValue(rs.getString(1)); row.createCell(1).setCellValue(rs.getString(2)); } } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); }
注意：当你要插入对个sheet时 则只需再来一遍 HSSFSheet sheet_qq = wb.createSheet("QQ号"); 后面即重复
3.保存文件
FileOutputStream out = new FileOutputStream(request.getSession().getServletContext().getRealPath("") + "/" + "xxx.xls"); //服务器的绝对路径 request.getSession().getServletContext().getRealPath("") // 例如：D:\Tomcat\apache-tomcat-8.0.37\me-webapps\Test // Test是项目名 wb.write(out); out.close();
4.访问下载
HttpServletRequest request = ServletActionContext.getRequest(); // request.getRequestURL() 是StringBuffer类型，所以要转换一下 String url = request.getRequestURL().toString(); //文件路径地址 request.getRequestURL() + "xxx.xls";
文件路径地址 url = request.getRequestURL() + "xxx.xls";
只需要将路径地址传给js,success 函数中使用 window.location.href = http://www.mamicode.com/url ; 可得到下载框
附上一个老长的代码，仅空参考
public Communal outPutAboutShopInfo(BasicShop basicShop) throws Exception { //存储escel文件名 String excel_shop_name = "shop_info.xls"; HttpServletRequest request = ServletActionContext.getRequest(); HttpServletResponse response = ServletActionContext.getResponse(); Communal communal = new Communal(); String [] out_type = new String []{}; // 接收导出数据类型 （1 手机号，2 会员信息，3 会员基卡，4记次卡次数信息） if(null != basicShop.getShopMemo() && !"".equals(basicShop.getShopMemo())){ out_type = basicShop.getShopMemo().toString().split(","); } Session session = null; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; // 存储过程 条件 门店Id String shopId = basicShop.getShopId(); // 1.创建一个workbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); for(int i=0;i<out_type.length;i++) { // 导出会员手机号码 if ( out_type[i] .equals("1")) { System.out.println(); // 2.在workbook中添加一个sheet，对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet("手机号"); // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 4.创建单元格，设置值表头，设置表头居中 HSSFCellStyle style = wb.createCellStyle(); // 居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("手机号"); cell.setCellStyle(style); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection(); cs = conn.prepareCall("{call ht_out_put_mem_phone(?)}"); cs.setString(1, shopId); boolean hadResults = false; hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值 row.createCell(0).setCellValue(rs.getString(1)); row.createCell(1).setCellValue(rs.getString(2)); } } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); } } //导出会员信息 if (out_type[i] .equals("2")) { // 2.在workbook中添加一个sheet，对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet("会员信息"); // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 4.创建单元格，设置值表头，设置表头居中 HSSFCellStyle style = wb.createCellStyle(); // 居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("会员编号"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("会员号"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("状态(01挂失2注销)"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("会员名称"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("大写首字母"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("出生日期"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("入会时间"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("是否开通短信(1是0否)"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("每月短信费用"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("短信指向"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("电话号码"); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue("QQ"); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("性别"); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue("证件类型(0身份证 1学生证 2工作证 3军官证)"); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue("证件号码"); cell.setCellStyle(style); cell = row.createCell(15); cell.setCellValue("邮箱"); cell.setCellStyle(style); cell = row.createCell(16); cell.setCellValue("职业"); cell.setCellStyle(style); cell = row.createCell(17); cell.setCellValue("邮编"); cell.setCellStyle(style); cell = row.createCell(18); cell.setCellValue("地址"); cell.setCellStyle(style); cell = row.createCell(19); cell.setCellValue("头像"); cell.setCellStyle(style); cell = row.createCell(20); cell.setCellValue("密码"); cell.setCellStyle(style); cell = row.createCell(21); cell.setCellValue("介绍人"); cell.setCellStyle(style); cell = row.createCell(22); cell.setCellValue("备注"); cell.setCellStyle(style); cell = row.createCell(23); cell.setCellValue("会员积分"); cell.setCellStyle(style); cell = row.createCell(24); cell.setCellValue("消费总额"); cell.setCellStyle(style); cell = row.createCell(25); cell.setCellValue("最后消费时间"); cell.setCellStyle(style); cell = row.createCell(26); cell.setCellValue("卡名称"); cell.setCellStyle(style); cell = row.createCell(27); cell.setCellValue("会员卡编号"); cell.setCellStyle(style); cell = row.createCell(28); cell.setCellValue("卡状态(0挂失1启用)"); cell.setCellStyle(style); cell = row.createCell(29); cell.setCellValue("卡类型0是储值 1是折扣积分 2是计次"); cell.setCellStyle(style); cell = row.createCell(30); cell.setCellValue("卡售价"); cell.setCellStyle(style); cell = row.createCell(31); cell.setCellValue("卡有效期限"); cell.setCellStyle(style); cell = row.createCell(32); cell.setCellValue("积分"); cell.setCellStyle(style); cell = row.createCell(33); cell.setCellValue("卡金"); cell.setCellStyle(style); cell = row.createCell(34); cell.setCellValue("卡抵用金"); cell.setCellStyle(style); cell = row.createCell(35); cell.setCellValue("会员卡消费总额"); cell.setCellStyle(style); cell = row.createCell(36); cell.setCellValue("最后消费日期"); cell.setCellStyle(style); cell = row.createCell(37); cell.setCellValue("计次卡次数"); cell.setCellStyle(style); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection(); cs = conn.prepareCall("{call ht_out_put_mem_info(?)}"); cs.setString(1, shopId); boolean hadResults = false; hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值 row.createCell(0).setCellValue(rs.getString(1)); row.createCell(1).setCellValue(rs.getString(2)); row.createCell(2).setCellValue(rs.getString(3)); row.createCell(3).setCellValue(rs.getString(4)); row.createCell(4).setCellValue(rs.getString(5)); row.createCell(5).setCellValue(rs.getString(6)); row.createCell(6).setCellValue(rs.getString(7)); row.createCell(7).setCellValue(rs.getString(8)); row.createCell(8).setCellValue(rs.getString(9)); row.createCell(9).setCellValue(rs.getString(10)); row.createCell(10).setCellValue(rs.getString(11)); row.createCell(11).setCellValue(rs.getString(12)); row.createCell(12).setCellValue(rs.getString(13)); row.createCell(13).setCellValue(rs.getString(14)); row.createCell(14).setCellValue(rs.getString(15)); row.createCell(15).setCellValue(rs.getString(16)); row.createCell(16).setCellValue(rs.getString(17)); row.createCell(17).setCellValue(rs.getString(18)); row.createCell(18).setCellValue(rs.getString(19)); row.createCell(19).setCellValue(rs.getString(20)); row.createCell(20).setCellValue(rs.getString(21)); row.createCell(21).setCellValue(rs.getString(22)); row.createCell(22).setCellValue(rs.getString(23)); row.createCell(23).setCellValue(rs.getString(24)); row.createCell(24).setCellValue(rs.getString(25)); row.createCell(25).setCellValue(rs.getString(26)); row.createCell(26).setCellValue(rs.getString(27)); row.createCell(27).setCellValue(rs.getString(28)); row.createCell(28).setCellValue(rs.getString(29)); row.createCell(29).setCellValue(rs.getString(30)); row.createCell(30).setCellValue(rs.getString(31)); row.createCell(31).setCellValue(rs.getString(32)); row.createCell(32).setCellValue(rs.getString(33)); row.createCell(33).setCellValue(rs.getString(34)); row.createCell(34).setCellValue(rs.getString(35)); row.createCell(35).setCellValue(rs.getString(36)); row.createCell(36).setCellValue(rs.getString(37)); row.createCell(37).setCellValue(rs.getString(38)); } } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); } } if (out_type[i] .equals("3")) { // 2.在workbook中添加一个sheet，对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet("会员基卡"); // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 4.创建单元格，设置值表头，设置表头居中 HSSFCellStyle style = wb.createCellStyle(); // 居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("卡名称"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("卡类型0是储值；1是折扣积分比例；2是计次"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("有效期)"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("初始面值"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("卡赠送金"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("服务折扣"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("产品折扣"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("服务积分比率"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("产品积分比率"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("初始积分"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("充值积分比例"); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue("状态（1启用，0不启用）"); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("是否储值（0否 1是）"); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue("办卡提成"); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue("办卡提成方式(0是比例1是固定)"); cell.setCellStyle(style); cell = row.createCell(15); cell.setCellValue("办卡业绩比例"); cell.setCellStyle(style); cell = row.createCell(16); cell.setCellValue("充值提成"); cell.setCellStyle(style); cell = row.createCell(17); cell.setCellValue("充值提成方式(0是比例1是固定)"); cell.setCellStyle(style); cell = row.createCell(18); cell.setCellValue("充值业绩比例"); cell.setCellStyle(style); cell = row.createCell(19); cell.setCellValue("还款提成"); cell.setCellStyle(style); cell = row.createCell(20); cell.setCellValue("还款提成方式(0是比例1是固定)"); cell.setCellStyle(style); cell = row.createCell(21); cell.setCellValue("还款业绩比例"); cell.setCellStyle(style); cell = row.createCell(22); cell.setCellValue("备注"); cell.setCellStyle(style); cell = row.createCell(23); cell.setCellValue("卡售价"); cell.setCellStyle(style); cell = row.createCell(24); cell.setCellValue("抵用金状态 1是启用；0是不启用"); cell.setCellStyle(style); cell = row.createCell(25); cell.setCellValue("初始抵用金"); cell.setCellStyle(style); cell = row.createCell(26); cell.setCellValue("充值满多少"); cell.setCellStyle(style); cell = row.createCell(27); cell.setCellValue("送多少"); cell.setCellStyle(style); cell = row.createCell(28); cell.setCellValue("消费满多少"); cell.setCellStyle(style); cell = row.createCell(29); cell.setCellValue("消费满送多少"); cell.setCellStyle(style); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection(); cs = conn.prepareCall("{call ht_out_put_mem_card(?)}"); cs.setString(1, shopId); boolean hadResults = false; hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值 row.createCell(0).setCellValue(rs.getString(1)); row.createCell(1).setCellValue(rs.getString(2)); row.createCell(2).setCellValue(rs.getString(3)); row.createCell(3).setCellValue(rs.getString(4)); row.createCell(4).setCellValue(rs.getString(5)); row.createCell(5).setCellValue(rs.getString(6)); row.createCell(6).setCellValue(rs.getString(7)); row.createCell(7).setCellValue(rs.getString(8)); row.createCell(8).setCellValue(rs.getString(9)); row.createCell(9).setCellValue(rs.getString(10)); row.createCell(10).setCellValue(rs.getString(11)); row.createCell(11).setCellValue(rs.getString(12)); row.createCell(12).setCellValue(rs.getString(13)); row.createCell(13).setCellValue(rs.getString(14)); row.createCell(14).setCellValue(rs.getString(15)); row.createCell(15).setCellValue(rs.getString(16)); row.createCell(16).setCellValue(rs.getString(17)); row.createCell(17).setCellValue(rs.getString(18)); row.createCell(18).setCellValue(rs.getString(19)); row.createCell(19).setCellValue(rs.getString(20)); row.createCell(20).setCellValue(rs.getString(21)); row.createCell(21).setCellValue(rs.getString(22)); row.createCell(22).setCellValue(rs.getString(23)); row.createCell(23).setCellValue(rs.getString(24)); row.createCell(24).setCellValue(rs.getString(25)); row.createCell(25).setCellValue(rs.getString(26)); row.createCell(26).setCellValue(rs.getString(27)); row.createCell(27).setCellValue(rs.getString(28)); row.createCell(28).setCellValue(rs.getString(29)); row.createCell(29).setCellValue(rs.getString(30)); } } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); } } if (out_type[i] .equals("4")) { // 2.在workbook中添加一个sheet，对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet("记次卡信息"); // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 4.创建单元格，设置值表头，设置表头居中 HSSFCellStyle style = wb.createCellStyle(); // 居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("卡名称"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("服务名称"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("剩余次数)"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("会员号"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("原单价"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("提成价"); cell.setCellStyle(style); try { session = this.hibernateTemplate.getSessionFactory().openSession(); session.beginTransaction(); conn = session.connection(); cs = conn.prepareCall("{call ht_out_put_mem_card_times_info(?)}"); cs.setString(1, shopId); boolean hadResults = false; hadResults = cs.execute(); int index = 0, no = 0; while (hadResults) { rs = (ResultSet) cs.getResultSet(); if (index == 0) { while (rs.next()) { no++; row = sheet.createRow((int) no); // 创建单元格，设置值 row.createCell(0).setCellValue(rs.getString(1)); row.createCell(1).setCellValue(rs.getString(2)); row.createCell(2).setCellValue(rs.getString(3)); row.createCell(3).setCellValue(rs.getString(4)); row.createCell(4).setCellValue(rs.getString(5)); row.createCell(5).setCellValue(rs.getString(6)); } } hadResults = cs.getMoreResults(); // 检查是否存在更多结果集 index++; } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); cs.close(); session.clear(); conn.close(); session.close(); } } } System.out.println(System.getProperty("user.dir") + "\\XXX.xls"); System.out.println(request.getRequestURL() + "/XXX.xls"); System.out.println(request.getSession().getServletContext().getRealPath("") + "/XXX.xls"); //response.setHeader("Content-Disposition" ,"attachment;filename="+new String((excel_shop_name).getBytes(),"UTF-8")); //response.setContentType("application/msexcel;charset=UTF-8"); FileOutputStream out = new FileOutputStream(request.getSession().getServletContext().getRealPath("") + "/" + excel_shop_name); wb.write(out); out.close(); // request.getRequestURL() 是StringBuffer类型，所以要转换一下 String url = request.getRequestURL().toString(); String [] root_path = url.split("outPutAboutShopInfo"); //路径地址 request.getRequestURL() + "xxx.xls"; communal.setData(root_path[0] + excel_shop_name); return communal; }
Java 从数据库中查找信息导入Excel表格中
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。