cn.hutool.poi.excel.ExcelUtil Java Examples

/**
 * 导出excel
 */
public static void downloadExcel(List<Map<String, Object>> list, HttpServletResponse response) throws IOException {
    String tempPath =System.getProperty("java.io.tmpdir") + IdUtil.fastSimpleUUID() + ".xlsx";
    File file = new File(tempPath);
    BigExcelWriter writer= ExcelUtil.getBigWriter(file);
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(list, true);
    //response为HttpServletResponse对象
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
    response.setHeader("Content-Disposition","attachment;filename=file.xlsx");
    ServletOutputStream out=response.getOutputStream();
    // 终止后删除临时文件
    file.deleteOnExit();
    writer.flush(out, true);
    //此处记得关闭输出Servlet流
    IoUtil.close(out);
}
 

/**
 * 导出excel
 */
public static void downloadExcel(List<Map<String, Object>> list, HttpServletResponse response) throws IOException {
    String tempPath =System.getProperty("java.io.tmpdir") + IdUtil.fastSimpleUUID() + ".xlsx";
    File file = new File(tempPath);
    BigExcelWriter writer= ExcelUtil.getBigWriter(file);
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(list, true);
    //response为HttpServletResponse对象
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
    response.setHeader("Content-Disposition","attachment;filename=file.xlsx");
    ServletOutputStream out=response.getOutputStream();
    // 终止后删除临时文件
    file.deleteOnExit();
    writer.flush(out, true);
    //此处记得关闭输出Servlet流
    IoUtil.close(out);
}
 

/**
 * 导出excel
 */
public static void downloadExcel(List<Map<String, Object>> list, HttpServletResponse response) throws IOException {
    String tempPath = SYS_TEM_DIR + IdUtil.fastSimpleUUID() + ".xlsx";
    File file = new File(tempPath);
    BigExcelWriter writer = ExcelUtil.getBigWriter(file);
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(list, true);
    //response为HttpServletResponse对象
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
    response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");
    ServletOutputStream out = response.getOutputStream();
    // 终止后删除临时文件
    file.deleteOnExit();
    writer.flush(out, true);
    //此处记得关闭输出Servlet流
    IoUtil.close(out);
}
 

/**
 * 打印待发货的订单表
 *
 * @param order
 * @param consignmentName   发件人姓名
 * @param consignmentMobile 发货人手机号
 * @param consignmentAddr   发货地址
 */
@GetMapping("/waitingConsignmentExcel")
@PreAuthorize("@pms.hasPermission('order:order:waitingConsignmentExcel')")
public void waitingConsignmentExcel(Order order, @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date startTime,
                                    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date endTime, String consignmentName, String consignmentMobile,
                                    String consignmentAddr, HttpServletResponse response) {
    Long shopId = SecurityUtils.getSysUser().getShopId();
    order.setShopId(shopId);
    order.setStatus(OrderStatus.PADYED.value());
    List<Order> orders = orderService.listOrdersDetialByOrder(order, startTime, endTime);

    //通过工具类创建writer
    ExcelWriter writer = ExcelUtil.getWriter();
    Sheet sheet = writer.getSheet();
    sheet.setColumnWidth(0, 20 * 256);
    sheet.setColumnWidth(1, 20 * 256);
    sheet.setColumnWidth(2, 20 * 256);
    sheet.setColumnWidth(3, 60 * 256);
    sheet.setColumnWidth(4, 60 * 256);
    sheet.setColumnWidth(7, 60 * 256);
    sheet.setColumnWidth(8, 60 * 256);
    sheet.setColumnWidth(9, 60 * 256);
    // 待发货
    String[] hearder = {"订单编号", "收件人", "手机", "收货地址", "商品名称", "数量", "发件人姓名", "发件人手机号", "发货地址", "备注"};
    writer.merge(hearder.length - 1, "发货信息整理");
    writer.writeRow(Arrays.asList(hearder));

    int row = 1;
    for (Order dbOrder : orders) {
        UserAddrOrder addr = dbOrder.getUserAddrOrder();
        String addrInfo = addr.getProvince() + addr.getCity() + addr.getArea() + addr.getAddr();
        List<OrderItem> orderItems = dbOrder.getOrderItems();
        row++;
        for (OrderItem orderItem : orderItems) {
            // 第0列开始
            int col = 0;
            writer.writeCellValue(col++, row, dbOrder.getOrderNumber());
            writer.writeCellValue(col++, row, addr.getReceiver());
            writer.writeCellValue(col++, row, addr.getMobile());
            writer.writeCellValue(col++, row, addrInfo);
            writer.writeCellValue(col++, row, orderItem.getProdName());
            writer.writeCellValue(col++, row, orderItem.getProdCount());
            writer.writeCellValue(col++, row, consignmentName);
            writer.writeCellValue(col++, row, consignmentMobile);
            writer.writeCellValue(col++, row, consignmentAddr);
            writer.writeCellValue(col++, row, dbOrder.getRemarks());
        }
    }
    writeExcel(response, writer);
}
 

/**
 * 已销售订单
 *
 * @param order
 */
@GetMapping("/soldExcel")
@PreAuthorize("@pms.hasPermission('order:order:soldExcel')")
public void soldExcel(Order order, @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date startTime,
                      @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date endTime, HttpServletResponse response) {
    Long shopId = SecurityUtils.getSysUser().getShopId();
    order.setShopId(shopId);
    order.setIsPayed(1);
    List<Order> orders = orderService.listOrdersDetialByOrder(order, startTime, endTime);

    //通过工具类创建writer
    ExcelWriter writer = ExcelUtil.getWriter();
    // 待发货
    String[] hearder = {"订单编号", "下单时间", "收件人", "手机", "收货地址", "商品名称", "数量", "订单应付", "订单运费", "订单实付"};
    Sheet sheet = writer.getSheet();
    sheet.setColumnWidth(0, 20 * 256);
    sheet.setColumnWidth(1, 20 * 256);
    sheet.setColumnWidth(3, 20 * 256);
    sheet.setColumnWidth(4, 60 * 256);
    sheet.setColumnWidth(5, 60 * 256);

    writer.merge(hearder.length - 1, "销售信息整理");
    writer.writeRow(Arrays.asList(hearder));

    int row = 1;
    for (Order dbOrder : orders) {
        UserAddrOrder addr = dbOrder.getUserAddrOrder();
        String addrInfo = addr.getProvince() + addr.getCity() + addr.getArea() + addr.getAddr();
        List<OrderItem> orderItems = dbOrder.getOrderItems();
        int firstRow = row + 1;
        int lastRow = row + orderItems.size();
        int col = -1;
        // 订单编号
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getOrderNumber());
        // 下单时间
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getCreateTime());
        // 收件人
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, addr.getReceiver());
        // "手机"
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, addr.getMobile());
        // "收货地址"
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, addrInfo);
        int prodNameCol = ++col;
        int prodCountCol = ++col;
        for (OrderItem orderItem : orderItems) {
            row++;
            // 商品名称
            writer.writeCellValue(prodNameCol, row, orderItem.getProdName());
            // 数量
            writer.writeCellValue(prodCountCol, row, orderItem.getProdCount());
        }
        // 订单应付
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getTotal());
        // 订单运费
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getFreightAmount());
        // 订单实付
        mergeIfNeed(writer, firstRow, lastRow, ++col, col, dbOrder.getActualTotal());

    }
    writeExcel(response, writer);
}