javaweb--数据导出到excel

最新推荐文章于 2024-09-10 21:07:25 发布

only_xu

最新推荐文章于 2024-09-10 21:07:25 发布

阅读量449

点赞数

分类专栏：数据导入文章标签： ibatis excel sping-mvc

本文链接：https://blog.csdn.net/u010722920/article/details/45627647

版权

数据导入专栏收录该内容

0 篇文章 0 订阅

订阅专栏

框架：spring （包括mvc）、ibatis、jquery easyui

前台js代码：

$(function(){
        $('#dg').datagrid({
            url:'book.json',
            pagination:true,
            singleSelect:true,
            toolbar: [{
                text:'导出excel',
            iconCls: 'icon-edit',
            handler: function(){
                var s = $('#dg').datagrid("options").columns[0];
                var title="";
                var field="";
                for(var i=0;i<s.length;i++){    
                    title=title+s[i].title+',';
                    field = field+s[i].field+',';   
                }
                title=title.substring(0,title.length-1);
                field=field.substring(0,field.length-1);
                $('#t1').attr("value",title);
                $('#f1').attr("value",field);
                $('#form2').submit();
                }
            }],
            columns:[[
                {field:'orderid',title:'订单号',width:100},    
                {field:'deskid',title:'桌号',width:100},    
                {field:'cusname',title:'预订人姓名',width:100,align:'right'},   
                {field:'cusnum',title:'预订人数',width:100,align:'right'}, 
                {field:'cusphone',title:'预订手机号码',width:100,align:'right'},
                {field:'ostate',title:'订单状态',width:100,align:'right'}
            ]]
        }); 
    })

jsp代码：

<body>
  <table id="dg"></table> 
   <form id="form2" action="/WfjManage/outerBook.do"style="display:none">
    <input id="t1" type="text" name="t" />
    <input id="f1" type="text" name="f" />
   </form>
  </body>

后台util方法：

/**
     * 设置excel的表头
     * @param str 前台截取的title
     * @param sheet  HSSFSheet的sheet
     */
    public static void outerHeader(String[] str,HSSFSheet sheet){
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        for (int i = 0; i < str.length; i++) {
            //为第一行写入数据
            cell=row.createCell(i);
            cell.setCellValue(str[i]);
        }
    }
    /**
     * 设置excel的数据
     * @param str  获取的field
     * @param sheet  HSSFSheet的sheet
     * @param list   查询数据库获取的list数据
     * @param rowIndex 开始行
     */
    public static void outerColumns(String[] str,HSSFSheet sheet,List<Map<String, Object>> list,int rowIndex){
        HSSFRow row;
        int columnsNum = list.size();
        for (int i = 0; i < columnsNum; i++) {
            row=sheet.createRow(i+rowIndex);
            Map<String, Object> obj = list.get(i);
            for (int j = 0; j < str.length; j++) {
                row.createCell(j).setCellValue(obj.get(str[j].toUpperCase()).toString());
            }
        }

    }

action层代码：

@Autowired
IDishBookService iDishBookService;

@RequestMapping("/outerBook")
    public void outerBook(HttpServletResponse response,HttpServletRequest request){
        String[] str1 = request.getParameter("f").split(",");//前台截取的field
        String s = null;
        try {
            s = new String(request.getParameter("t").getBytes("ISO-8859-1"),
                    "utf-8");//解决乱码
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        String[] str2 = s.split(",");//前台获取的title
        response.setContentType("application/octet-stream");//设置数据类型
        response.setHeader("content-disposition", "attachment;filename=outer.xls");//设置头信息    导出的xls名称
        HSSFWorkbook workbook = new HSSFWorkbook();//创建工作簿
        HSSFSheet sheet = workbook.createSheet("sheet0");//创建excel的sheet
        Excel.outerHeader(str2, sheet);//static method 设置表头
        Excel.outerColumns(str1, sheet, iDishBookService.showBook(), 1);//static method 写入值
        try {
            ServletOutputStream outputStream = response.getOutputStream();//输出流
            workbook.write(outputStream);//写入工作簿
            outputStream.flush();//释放
            outputStream.close();//关闭
        } catch (IOException e) {
            e.printStackTrace();
        }

    }