首页 > 代码库 > 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表格中