使用JXL导出excel

 

前一段时间做了一个将列表导出excel的功能，在此记录一下。

本例子采用了jxl API实现：

(以下方法是基于当时开发的框架实现，其他框架类似，各参数的获取方式可能会有所不同)

public void exportListAction(@RequestParam Map<String, String> reqs,HttpServletResponse response){

              //reqs是查询列表所需要的条件；

              List<Map<String, Object>>  txnList = txnReportService.exportList(reqs);//需要导出的数据列表

              //excel各列的标题

              String titles [] = {"交易名称","告警总数","交易总数","交易告警比例(%)","告警成功数","告警成功率(%)",

                                                 "平均计算时间(毫秒)","最大计算时间(毫秒)","最小计算时间(毫秒)","阻断率(%)","挂起率(%)","强认证率(%)","告警率(%)","漏报率(%)","误报率(%)"};

              //对应列名，需要获取列表中各条记录的列名

              String colum [] = {"TXNNAME","ALERTNUMBER","TXNNUMBER","TXNALERTRATE","ALERTSUSNUMBER","SUSRATE",

"AVGTIME","MAXTIME","MINTIME","BLOCKRATE","HANGRATE","AUTHRATE","ALERTRATE","DEFRATE","ERRORRATE"};

             

              try{

                     //当前时间戳

                     String dateStr = CalendarUtil.getCalendarByFormat(CalendarUtil.FORMAT9);

                     //文件名

                     String name = "交易告警信息"+dateStr;

                     OutputStream os = response.getOutputStream();// 取得输出流  

                     response.reset();// 清空输出流  

                     //设置响应头

                     response.setHeader("Content-disposition", "attachment; filename="

                                                         + new String(name.getBytes("GB2312"),

                                                             "iso8859_1") + ".xls");// 设定输出文件头  

                     response.setContentType("application/msexcel");// 定义输出类型

                    

                     WritableWorkbook workbook = Workbook.createWorkbook(os);

                     if (workbook != null) {

                            //设置第一张工作表的名称。工作表索引从0开始

                            WritableSheet sheet = workbook.createSheet("交易告警信息", 0);

                           

                            // 设置单元格标题 sheet.addCell(newLabel(列(从0开始), 行(从0开始), 内容.));

                            try {

                                   //设置字体

                                   WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD);    

                                   WritableCellFormat titleFormat = new WritableCellFormat(titleFont);   

                              for(int i=0;i<titles.length;i++){

                                     // 设置标题。此循环是为第一行即标题行设置各列的值

                                     sheet.addCell(new Label(i, 0, titles[i],titleFormat));

                                     // 设置单元格的宽度

                                     sheet.setColumnView(i, 20);

                              }

                              for(int i=0;i<txnList.size();i++){

                                     Map<String,Object> txn = txnList.get(i);

                                     for(int j=0;j<colum.length;j++){

                                            // 此循环是从第二行开始设置每行每列的值

                                            sheet.addCell(new Label(j, i+1, txn.get(colum[j]).toString()));

                                     }

                              }

                              //从内存中写入文件中  

                              workbook.write();

                              //关闭资源，释放内存   

                              workbook.close();

                              os.flush();

                            }catch (RowsExceededException e) {

                                   e.printStackTrace();

                            } catch (WriteException e) {

                                   e.printStackTrace();

                            }

                     }

             

              } catch (IOException e) {

                     e.printStackTrace();

              }

       }

 

/********************如有引用请标明转载地址*********************/