springboot项目集成POI导出Excel表

最新推荐文章于 2024-08-17 03:14:27 发布

安蒙1278

最新推荐文章于 2024-08-17 03:14:27 发布

阅读量505

点赞数

分类专栏： Springboot

本文链接：https://blog.csdn.net/an1278/article/details/84589731

版权

Springboot 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

POI是Excel2003，POI-ooxml是2007+以上的。

项目pom中导入依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

编写导出Excel的工具类。这是依据我的项目编写的工具类，文章尾部会贴出公共方法的工具类

/**
 * 导出Excel表
 * @param response
 * @param headers
 * @param row
 * @param workbook
 * @param sheet
 * @throws IOException
 */
public static void export(HttpServletResponse response, String[] headers, HSSFRow row,HSSFWorkbook workbook , HSSFSheet sheet) throws IOException {

    SimpleDateFormat fdate = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
    String fileName = fdate.format(new Date()) + ".xls";
    //headers表示excel表中第一行的表头
     row = sheet.createRow(0);

    HSSFCellStyle style=workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);

    //在excel表中添加表头
    for(int i=0;i<headers.length;i++){
        HSSFCell cell = row.createCell(i);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }
    response.setContentType("application/octet-stream");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    response.flushBuffer();
    workbook.write(response.getOutputStream());
}

编写控制器ExportExcelController，本来想导入前端页面中的数据，但是发现，有些时候需要导入一个经过筛选的和一些前端页面没有的数据。所有干脆就直接从数据库中导入。

@RequestMapping("/exportOrder")
public void exportOrder(@RequestParam(value = "typeId", defaultValue = "-1") Integer typeId,
                        @RequestParam(value = "status", defaultValue = "-1") Integer status,
                        @RequestParam(value = "startDate", defaultValue = "") String startDate,
                        @RequestParam(value = "endDate", defaultValue = "") String endDate,
                        @RequestParam(value = "keys", defaultValue = "") String keys,
                        HttpServletResponse response) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(); 
    HSSFSheet sheet = workbook.createSheet("订单信息表");
    String[] headers = {"订单编号", "类型", "用户昵称", "openId", "数量", "金额", "状态", "创建时间", "商品名称", "规格", "数量"};

    EntityWrapper<Order> wrapper = new EntityWrapper<>();

    if (typeId > -1) {
        wrapper.where("type_id={0}", typeId);
    }
    if (status > -1) {
        wrapper.where("status={0}", status);
    }
    wrapper.where(!StringUtils.isBlank(keys), "(open_id like concat(concat('%',{0}),'%') or order_id like concat(concat('%',{0}),'%')  )", keys);

    if (!StringUtils.isBlank(startDate) && !StringUtils.isBlank(endDate)) {
        int startUnix = DateUtils.getUnixTimeByDate(DateUtils.dateFormat(startDate, "yyyy-MM-dd"));
        int endUnix = DateUtils.getUnixTimeByDate(DateUtils.dateFormat(endDate, "yyyy-MM-dd"));
        endUnix += 60 * 60 * 24;
        wrapper.between("create_time", startUnix, endUnix);
    } else if (!StringUtils.isBlank(startDate)) {
        int startUnix = DateUtils.getUnixTimeByDate(DateUtils.dateFormat(startDate, "yyyy-MM-dd"));
        wrapper.gt("create_time", startUnix);
    } else if (!StringUtils.isBlank(endDate)) {
        int endUnix = DateUtils.getUnixTimeByDate(DateUtils.dateFormat(endDate, "yyyy-MM-dd"));
        endUnix += 60 * 60 * 24;
        wrapper.lt("create_time", endUnix);
    }
    wrapper.orderBy("create_time desc");
    List<Order> orders = orderService.selectList(wrapper);

    int rowNum = 1;
    HSSFRow row = null;

    for (Order order : orders) {
        row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue(order.getOrderId());
        row.createCell(1).setCellValue(ActivityType.valueOf(order.getTypeId()).getMessage());
        row.createCell(2).setCellValue(WechatLogic.GetWechat(order.getOpenId()).getNickname());
        row.createCell(3).setCellValue(order.getOpenId());
        row.createCell(4).setCellValue(String.valueOf(OrderLogic.GetOrderProducts(order.getOrderId()).size()));
        row.createCell(5).setCellValue(order.getAmount().doubleValue());
        row.createCell(6).setCellValue(OrderStatus.valueOf(order.getStatus()).getMessage());
        row.createCell(7).setCellValue(DateUtils.formatDateByUnixTime(Long.valueOf(order.getCreateTime()), "yyyy-MM-dd HH:mm:ss"));
        List<OrderProductDto> orderProductDtos = OrderLogic.GetOrderProducts(order.getOrderId());
        for (OrderProductDto orderProductDto : orderProductDtos) {
            row.createCell(8).setCellValue(ProductLogic.GetProduct(orderProductDto.getProductId()).getProductName());
            row.createCell(9).setCellValue(ProductLogic.GetProductSpec(orderProductDto.getProductSpecId()).getSpecName());
            row.createCell(10).setCellValue(orderProductDto.getNumber());
            rowNum++;
            row = sheet.createRow(rowNum);
        }
        rowNum++;
        sheet.autoSizeColumn(rowNum);
    }
    ExcelUtil.export(response, headers, row, workbook, sheet);
}

前端页面中的js

$("button[data-method='export']").click(function () {

    var typeId = $("select[name='typeId']").val();
    var status = $("select[name='status']").val();
    var keys = $("input[name='keys']").val();
    var startDate = $("input[name='startDate']").val();
    var endDate = $("input[name='endDate']").val();
    window.location.href="/excel/exportOrder?typeId="+typeId+"&status="+status+"&keys="+keys+"&startDate="+startDate+"&endDate="+endDate;
});

下面是一个公共的导出Excel的工具类，可以根据自己传入数据类型进行修改，list，map..

/**
 * 创建excel并填入数据
 * @param head 数据头
 * @param body 主体数据
 * @return HSSFWorkbook
 */
public static HSSFWorkbook expExcel(HttpServletResponse response,JSONArray head, JSONArray body) {
    //创建一个excel工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建一个sheet工作表
    HSSFSheet sheet = workbook.createSheet("学生信息");

    //创建第0行表头，再在这行里在创建单元格，并赋值
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = null;
    for (int i = 0; i < head.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(head.getString(i));//设置值
    }

    //将主体数据填入Excel中
    for (int i = 0, isize = body.size(); i < isize; i++) {
        row = sheet.createRow(i + 1);
        JSONArray stuInfo = body.getJSONArray(i);
        for (int j = 0, jsize = stuInfo.size(); j < jsize; j++) {
            cell = row.createCell(j);
            cell.setCellValue(stuInfo.getString(j));//设置值
        }
    }
    response.setContentType("application/octet-stream");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    response.flushBuffer();
    workbook.write(response.getOutputStream());
}

网上还有很多设置单元格的格式。