【项目实战】Java POI之Excel导出经典案例四

Java 同时被 3 个专栏收录
27 篇文章 0 订阅
4 篇文章 0 订阅
3 篇文章 0 订阅

该案例的实现和【项目实战】Java POI之Excel导出经典案例一类似,点击【导出Excel】按钮进行操作。

1、首先创建Excel文档模板:


2、在JSP中添加【导出Excel】按钮,代码如下:

<a href="javascript:void(0);"  target="_blank" οnclick="excelProductProfit()" class="button button-primary button-small">导出到Excel</a>


3、在JS中实现点击事件:

/* 导出到Excel */
function excelProductProfit(){
	window.location = "excelProductProfit.do?startMin="+$("#startMin").val()
		+"&startMax="+$("#startMax").val()
		+"&productName="+$("#productName").val()
		+"&supplierName="+$("#supplierName").val()
		+"&orgIds="+$("#orgIds").val()
		+"&operatorIds="+$("#operatorIds").val()
		+"&operType="+$("#operType").val()
		+"&orderNo="+$("#dicIds").val();
}


4、Java后台Controller实现代码方法如下:

@RequestMapping(value = "/excelProductProfit.do")
    @ResponseBody
    public void excelProductProfit(HttpServletRequest request, HttpServletResponse response, String startMin,String startMax,String productName,String supplierName,Integer operType,
    		String orderNo,String operatorIds,String orgIds) {
        List<DicInfo> typeList = dicService.getListByTypeCode(BasicConstants.SALES_TEAM_TYPE,
                WebUtils.getCurBizId(request));
        PageBean<GroupOrder> pageBean = new PageBean<GroupOrder>();
        Map<String, Object> pm = new HashMap<String, Object>();
        pm.put("startMin", startMin);
        pm.put("startMax", startMax);
        pm.put("productName", productName);
        pm.put("supplierName", supplierName);
        pm.put("orderNo", orderNo);
        pm.put("operatorIds", operatorIds);
        pm.put("operType", operType);
        pm.put("set", WebUtils.getDataUserIdSet(request));
        if (orgIds != null && StringUtils.isNotBlank(orgIds.toString())) {
            Set<Integer> set = new HashSet<Integer>();
            String[] orgIdArr = orgIds.toString().split(",");
            for (String orgIdStr : orgIdArr) {
                set.add(Integer.valueOf(orgIdStr));
            }
            set = platformEmployeeService.getUserIdListByOrgIdList(WebUtils.getCurBizId(request), set);
            String salesOperatorIds = "";
            for (Integer usrId : set) {
                salesOperatorIds += usrId + ",";
            }
            if (!salesOperatorIds.equals("")) {
                pm.put("saleOperatorIds", salesOperatorIds.substring(0, salesOperatorIds.length() - 1));
            }
        }
        pageBean.setParameter(pm);
        pageBean.setPage(1);
        pageBean.setPageSize(10000);
        pageBean = groupOrderService.selectProductProfitStatisticsListPage(pageBean, WebUtils.getCurBizId(request));
        List<GroupOrder> orders=pageBean.getResult();
        String path = "";

        try {
            String url = request.getSession().getServletContext()
                    .getRealPath("/template/excel/ProductProfitStatistics.xlsx");
            FileInputStream input = new FileInputStream(new File(url)); // 读取的文件路径
            XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));
            XSSFFont createFont = wb.createFont();
            createFont.setFontName("微软雅黑");
            createFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            createFont.setFontHeightInPoints((short) 12);

            XSSFFont tableIndex = wb.createFont();
            tableIndex.setFontName("宋体");
            tableIndex.setFontHeightInPoints((short) 11);

            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
            cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
            cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中

            CellStyle styleFontCenter = wb.createCellStyle();
            styleFontCenter.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
            styleFontCenter.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
            styleFontCenter.setBorderTop(CellStyle.BORDER_THIN);// 上边框
            styleFontCenter.setBorderRight(CellStyle.BORDER_THIN);// 右边框
            styleFontCenter.setAlignment(CellStyle.ALIGN_CENTER); // 居中
            styleFontCenter.setFont(createFont);

            CellStyle styleFontTable = wb.createCellStyle();
            styleFontTable.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
            styleFontTable.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
            styleFontTable.setBorderTop(CellStyle.BORDER_THIN);// 上边框
            styleFontTable.setBorderRight(CellStyle.BORDER_THIN);// 右边框
            styleFontTable.setAlignment(CellStyle.ALIGN_CENTER); // 居中
            styleFontTable.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            styleFontTable.setFillPattern(CellStyle.SOLID_FOREGROUND);

            CellStyle styleLeft = wb.createCellStyle();
            styleLeft.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
            styleLeft.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
            styleLeft.setBorderTop(CellStyle.BORDER_THIN);// 上边框
            styleLeft.setBorderRight(CellStyle.BORDER_THIN);// 右边框
            styleLeft.setAlignment(CellStyle.ALIGN_LEFT); // 居左

            CellStyle styleRight = wb.createCellStyle();
            styleRight.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
            styleRight.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
            styleRight.setBorderTop(CellStyle.BORDER_THIN);// 上边框
            styleRight.setBorderRight(CellStyle.BORDER_THIN);// 右边框
            styleRight.setAlignment(CellStyle.ALIGN_RIGHT); // 居右
            Sheet sheet = wb.getSheetAt(0); // 获取到第一个sheet
            Row row = null;
            Cell cc = null;
            // 遍历集合数据,产生数据行
            Iterator<GroupOrder> it = orders.iterator();
            int index = 0;
            while (it.hasNext()) {
                GroupOrder order = it.next();
                String orderMode1 = "";
                for (DicInfo item : typeList) {
                    if (item.getId().equals( order.getOrderMode())) {
                        orderMode1 = item.getValue();
                    }
                }
                row = sheet.createRow(index + 2);
                cc = row.createCell(0);
                cc.setCellValue(index + 1);
                cc.setCellStyle(cellStyle);
                cc = row.createCell(1);
                cc.setCellValue(orderMode1);
                cc.setCellStyle(styleLeft);
                cc = row.createCell(2);
                cc.setCellValue(order.getSupplierName());
                cc.setCellStyle(styleLeft);
                cc = row.createCell(3);
                cc.setCellValue(order.getProductName());
                cc.setCellStyle(styleLeft);
                cc = row.createCell(4);
                cc.setCellValue(order.getNumAdult() == null ? 0 : order.getNumAdult());
                cc.setCellStyle(styleLeft);
                cc = row.createCell(5);
                cc.setCellValue(order.getNumChild() == null ? 0 : order.getNumChild());
                cc.setCellStyle(styleLeft);
                cc = row.createCell(6);
                cc.setCellValue(order.getTotalIncome() == null ? 0 : order.getTotalIncome().doubleValue());
                cc.setCellStyle(styleLeft);
                cc = row.createCell(7);
                cc.setCellValue(order.getOtherTotal() == null ? 0 : order.getOtherTotal().doubleValue());
                cc.setCellStyle(cellStyle);
                cc = row.createCell(8);
                cc.setCellValue(order.getTotalCost() == null ? 0 : order.getTotalCost().doubleValue());
                cc.setCellStyle(cellStyle);
                cc = row.createCell(9);
                cc.setCellValue(((order.getTotalIncome().add(order.getOtherTotal())).subtract(order.getTotalCost())).doubleValue());
                cc.setCellStyle(cellStyle);
                index++;

            }
            List<String> list = getTotal(orders);
            row = sheet.createRow(orders.size() + 2); // 加合计行
            cc = row.createCell(0);
            cc.setCellStyle(styleRight);
            cc = row.createCell(1);
            cc.setCellStyle(styleRight);
            cc = row.createCell(2);
            cc.setCellStyle(styleRight);
            cc = row.createCell(3);
            cc.setCellValue("合计:");
            cc.setCellStyle(styleRight);
            cc = row.createCell(4);
            cc.setCellValue(list.get(0));
            cc.setCellStyle(styleRight);
            cc = row.createCell(5);
            cc.setCellValue(list.get(1));
            cc.setCellStyle(styleRight);
            cc = row.createCell(6);
            cc.setCellValue(list.get(13));
            cc.setCellStyle(styleRight);
            cc = row.createCell(7);
            cc.setCellValue(list.get(10));
            cc.setCellStyle(styleRight);
            cc = row.createCell(8);
            cc.setCellValue(list.get(14));
            cc.setCellStyle(styleRight);
            cc = row.createCell(9);
            cc.setCellValue(list.get(15));
            cc.setCellStyle(cellStyle);
            CellRangeAddress region = new CellRangeAddress(orders.size() + 3, orders.size() + 4, 0, 9);
            sheet.addMergedRegion(region);
            row = sheet.createRow(orders.size() + 3);
            cc = row.createCell(0);
            cc.setCellValue("打印人:" + WebUtils.getCurUser(request).getName() + " 打印时间:"
                    + DateUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
            path = request.getSession().getServletContext().getRealPath("/") + "/download/" + System.currentTimeMillis()
                    + ".xlsx";
            FileOutputStream out = new FileOutputStream(path);
            wb.write(out);
            out.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String fileName = "";
        try {
            fileName = new String("统计表.xlsx".getBytes("UTF-8"), "iso-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        download(path, fileName, request, response);
    }


downLoad方法实现如下:

private void download(String path, String fileName, HttpServletRequest request, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();

            /*
             * //解决IE浏览器下下载文件名乱码问题 if
             * (request.getHeader("USER-AGENT").indexOf("msie") > -1){ fileName
             * = new URLEncoder().encode(fileName) ; }
             */
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            file.delete();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }


5、最终效果如下:


KO,功能实现完成!!!

  • 1
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页

打赏

张思全

实践,方能出真知!打赏博主吧!

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值