excel导出小demo

Controller:

    public void exportMemberOrderInfo(HttpServletResponse response,
                 @RequestParam(value = "storeName") String storeName,
                 @RequestParam(value = "startTime") String startTime,
                 @RequestParam(value = "endTime") String endTime){
        doexport(response,(workbook, out, resp) -> {
            String fileName = "会员维修记录表" + new String(("-" + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(),"UTF-8");
            String encodedFileName = java.net.URLEncoder.encode(fileName,"UTF-8");
            response.setHeader("Content-disposition","attachment;fileName=" + encodedFileName + ".xls");
            baseService.exportMemberOrderInfo(out, workbook,storeName,startTime,endTime);
        });
    }

    public void doexport(HttpServletResponse response,Exporter exporter){
        ServletOutputStream out;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            response.setCharacterEncoding("utf-8");
            //输出Excel
            response.setContentType("application/vnd.ms-excel");
            out = response.getOutputStream();
            exporter.export(workbook, out, response);
            //将文件输出到客户端浏览器
            workbook.write(out);
//            释放资源
//            Workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error while export", e);
        }
    }

 

service:

/**
 * 导出会员信息
 */
public void exportMemberOrderInfo(ServletOutputStream out, HSSFWorkbook workbook,
                                  String storeName,String startTime,String endTime) {


    try {
        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setWrapText(true);

        HSSFCell cell;
        //会员列表页
        String[] titles = new String[]{"会员ID","会员姓名", "电话号码","会员积分","车牌号", "隶属门店","订单编号","产品名称", "基础服务名称", "产品金额", "工时费", "下单日期", };
        //在workbook中添加一个sheet，对应Excel文件中的会员列表页
        HSSFSheet hssfSheet = workbook.createSheet("会员维修信息页");

        //设置行宽
        for (int i = 0; i < titles.length; i++) {
            //会员维修信息页的行宽
            hssfSheet.setColumnWidth(i, 13 * 256);
        }

        //在会员维修信息页中添加表头第0行，注意老版本poi对Excel的行数列数有限制short
        HSSFRow hssfRow = hssfSheet.createRow(0);
        //创建单元格，并设置值表头，设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        //居中样式
        style.setAlignment(HorizontalAlignment.CENTER);
        //会员订单
        HSSFCell hssfCell = null;
        for (int i = 0; i < titles.length; i++) {
            //会员维修信息页列索引从0开始
            hssfCell = hssfRow.createCell(i);
            //会员维修信息页列名
            hssfCell.setCellValue(titles[i]);
            //会员维修信息页列居中显示
            hssfCell.setCellStyle(style);

        }
        //获取会员维修记录数
        List<OrderAllInfoVO> list = orderService.MemberOrderInfoByStoreNameAndOrderTime(storeName,startTime,endTime);
        List<OrderAllInfoVO> listNew=new ArrayList<>();
        //获取会员积分
        for(OrderAllInfoVO orderAllInfoVO:list){
            BaseInfo baseInfo=baseInfoService.selectById(orderAllInfoVO.getMemberId());
            if(baseInfo!=null){
                orderAllInfoVO.setMemberPoints(baseInfo.getMemberPoints());
            }
            listNew.add(orderAllInfoVO);
        }


        //写入实体数据
        //大于0表示有订单信息
        if (listNew.size() > 0) {
            int index = 1;
            int col = 0;
            //Excel行（+1表示下一行）
            //循环每个会员信息
            int i = 0;
            for (OrderAllInfoVO orderAllInfoVO : listNew) {
                //每次每个会员基础信息写入数据完毕则加一行
                hssfRow = hssfSheet.createRow(index);
                //创建单元格，并设置值
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                //会员ID
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getId() == null ? "" : orderAllInfoVO.getId().toString());
                //获取会员姓名并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberName() == null ? "" : orderAllInfoVO.getMemberName());
                //获取电话号码并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberPhone() == null ? "" : orderAllInfoVO.getMemberPhone());
                //获取会员积分并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getMemberPoints() == null ? "" : orderAllInfoVO.getMemberPoints().toString());
                //获取车牌号并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getCarNum() == null ? "" : orderAllInfoVO.getCarNum());
                //获取隶属门店并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getStoreName() == null ? "" : orderAllInfoVO.getStoreName());
                //获取订单编号并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getOrderNum() == null ? "" : orderAllInfoVO.getOrderNum());
                //获取产品名称并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getProductName() == null ? "" : orderAllInfoVO.getProductName());
                //获取服务名称并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getServicesName() == null ? "" : orderAllInfoVO.getServicesName());
                //获取产品金额并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getProductPrice() == null ? "" : orderAllInfoVO.getProductPrice().toString());
                //获取工时费并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getHourlyWage() == null ? "" : orderAllInfoVO.getHourlyWage().toString());
                //获取下单日期并赋值
                hssfRow.createCell(col++).setCellValue(orderAllInfoVO.getOrderTime() == null ? "" : sdf.format(orderAllInfoVO.getOrderTime()));
                col = 0;
                index++;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

js

open(url)