excel报表导出功能

最新推荐文章于 2022-09-05 15:01:13 发布

sinat_35007100

最新推荐文章于 2022-09-05 15:01:13 发布

阅读量742

点赞数

本文链接：https://blog.youkuaiyun.com/sinat_35007100/article/details/89378811

版权

1、先在前台定义导出按钮

2、在<script>标签中绑定按钮的eventlistener

               //导出
               $('#daochu').click(function() {
                   //alert("0");
                   window.location.href = "/ygxwdagl/servlet/YGPCServlet?method=dcLISTJF&HDID=" + HDID+"&HDNM="+HDNM;

}); //$('#daochu').click(function() {结束

3、后台接收，导出报表

// 导出家访统计报表
           public void dcLISTJF(HttpServletRequest request,
                   HttpServletResponse response) throws ServletException, IOException {
               System.out.println("dcLISTJF:");

               String HDID = request.getParameter("HDID");
               String HDNM = request.getParameter("HDNM");
               System.out.println("HDID：" + HDID);
               System.out.println("HDNM：" + HDNM);

Gson gson = new Gson();

               // 得到
               List list = null;
               try {
                   String sql = "{call XWDA_JFFHTJINFO(?,?)}";
                   Object param[] = { HDID,HDNM };
                   list = (List) qr.query(sql, new BeanListHandler(YGPCINFO.class),param);
               } catch (SQLException e) {
                   e.printStackTrace();
               }

               // 导出表
               try {
                   // 使用new String(str.getByts("ISO8859-1"), "GBK")，
                   // 先将服务器传来的参数按照ISO8859-1编码，再将编码的结果用gbk解码，形成字符串
                   response.reset();
                   response.setContentType("application/vnd.ms-excel");
                   response.setHeader("Content-disposition",
                           "attachment; filename="
                                   + new String("jfwcqk.xls".getBytes("gb2312"),
                                           "iso8859-1"));//标红处需修改成自定义的excel表名

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

WritableWorkbook book = Workbook.createWorkbook(os);

// create Sheet named "Sheet_1". 0 means this is 1st page.
WritableSheet sheet = book.createSheet("Sheet_1", 0);// 工作表名称

                   // font
                   // 设置Excel字体
                   WritableFont font = new WritableFont(WritableFont.ARIAL, 9,
                           WritableFont.BOLD, false,
                           jxl.format.UnderlineStyle.NO_UNDERLINE,
                           jxl.format.Colour.BLACK);
                   WritableCellFormat titleFormat = new WritableCellFormat(font);
                   // font
                   WritableFont font1 = new WritableFont(WritableFont.ARIAL, 9,
                           WritableFont.NO_BOLD, false,
                           jxl.format.UnderlineStyle.NO_UNDERLINE,
                           jxl.format.Colour.BLACK);
                   WritableCellFormat format1 = new WritableCellFormat(font1);

                   // font
                   // 设置数字列格式，双击这个单元格不会变为数字
                   // 定义一个单元格样式
                   WritableCellFormat szwb = new WritableCellFormat(NumberFormats.TEXT); // NumberFormats
                   // .
                   // TEXT是一个强制显示文本的格式
                   szwb.setFont(font1); // 设置字体
                   szwb.setAlignment(jxl.format.Alignment.RIGHT);
                   CellView cv = new CellView(); // 定义一个列显示样式
                   cv.setFormat(szwb);// 把定义的单元格格式初始化进去
                   cv.setAutosize(true);

                   String[] title = { "活动ID","活动名称", "部门名称", "已完成人数","部门总人数", "完成率" };//title为需要导出的列名
                   // 设置Excel表头
                   Label label = new Label(0, 0, "");
                   for (int i = 0; i < title.length; i++) {
                       label = new Label(i, 0, title[i], titleFormat);
                       sheet.addCell(label);
                   }

java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
nf.setMaximumFractionDigits(2);// 保留两位小数

                   // 设置表内容
                   YGPCINFO jfwcinfo = null;
                   for (int i = 0; i < list.size(); i++) {
                       jfwcinfo = (YGPCINFO) list.get(i);

                       label = new Label(0, (i + 1), jfwcinfo.getHDID() + "");
                       sheet.addCell(label);
                       label = new Label(1, (i + 1), jfwcinfo.getHDNM() + "");
                       sheet.addCell(label);
                       label = new Label(2, (i + 1), jfwcinfo.getDEPNM() + "");
                       sheet.addCell(label);
                       label = new Label(3, (i + 1), jfwcinfo.getCKRS() + "");
                       sheet.addCell(label);
                       label = new Label(4, (i + 1), jfwcinfo.getBMZRS() + "");
                       sheet.addCell(label);
                       label = new Label(5, (i + 1), jfwcinfo.getCKBL()*100 + "%");
                       sheet.addCell(label);

}

                   sheet.setColumnView(0, cv);// 设置工作表中第n列的样式
                   sheet.setColumnView(1, cv);// 设置工作表中第n列的样式
                   sheet.setColumnView(2, cv);// 设置工作表中第n列的样式
                   sheet.setColumnView(3, cv);// 设置工作表中第n列的样式
                   sheet.setColumnView(4, cv);// 设置工作表中第n列的样式
                   sheet.setColumnView(5, cv);// 设置工作表中第n列的样式

                   book.write();
                   book.close();

os.flush();

               } catch (Exception e) {
                   e.printStackTrace();
               }

}