在Spring Boot中导出数据库中的数据至EXCEL文件中

最新推荐文章于 2024-02-29 12:00:00 发布

blutjager

最新推荐文章于 2024-02-29 12:00:00 发布

阅读量1.3k

点赞数

分类专栏： spring-boot

本文链接：https://blog.csdn.net/qq_28023121/article/details/78930435

版权

spring-boot 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一个导出押金数据到EXCEL的小栗子

controller

    @ApiOperation(value = "导出押金订单信息", notes = "导出押金订单信息")
    @GetMapping(value = "/exportDeposit")
    public RespResultVO<String> exportDeposit(HttpServletResponse response,@RequestParam(required = true) @ApiParam(value = "押金状态")String status,@RequestParam(required = false) @ApiParam(value = "订单号")String orderNum) throws IOException {
        return excelExportService.exportDeposit(response,status,orderNum);
    }

Service

    public RespResultVO<String> exportDeposit(HttpServletResponse response,String status,String orderNum) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            if (orderNum==null){
                orderNum="";
            }
            List<DepositExportVO> depositExportVOS=new ArrayList<DepositExportVO>();
            if (status.equals("已交押金")){
                depositExportVOS = orderHeaderRepository.findPayDepositFlow("%"+orderNum+"%");
            }else if (status.equals("已退押金")){
                depositExportVOS = orderHeaderRepository.findRefundDepositFlow("%"+orderNum+"%");
            }
            String[] fields = {"订单号","押金应收流水ID", "应收金额", "支付方", "支付方式", "支付状态"};
            ExcelExport excelExport = new ExcelExport();
            HSSFWorkbook wb = excelExport.generateExcel();
            wb = excelExport.generateOrderDepositVOSheet(wb, "押金订单信息", fields, depositExportVOS);
            excelExport.export("押金订单信息", wb, response);
            return RespResultVO.<String>builder().message("success").build();
        } catch (Exception e) {
            return RespResultVO.<String>builder().message(e.getMessage()).build();
        }
    }

ExcelExport util

import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * Created by Jens on 2017/6/5.
 */
public class ExcelExport {
    // 第一步，创建一个webbook，对应一个Excel文件
    public HSSFWorkbook generateExcel() {
        return new HSSFWorkbook();
    }

    public HSSFWorkbook createRow(HSSFWorkbook wb, String sheetName, String[] fields) {
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头
        HSSFCellStyle style = wb.createCellStyle();
        HSSFCell cell;
        int m = 0;
        for (String fieldName : fields) {
            cell = row.createCell(m);
            cell.setCellValue(fieldName);
            cell.setCellStyle(style);
            m++;
        }
        return wb;
    }

    public HSSFWorkbook generateOrderDepositVOSheet(HSSFWorkbook wb, String sheetName, String[] fields,
                                      List<DepositExportVO> list) {
        HSSFWorkbook hssfWorkbook = this.createRow(wb, sheetName, fields);
        HSSFSheet hssfSheet = hssfWorkbook.getSheet(sheetName);
        HSSFRow hssfRow;
        for (int i = 0; i < list.size(); i++) {
            hssfRow = hssfSheet.createRow(i + 1);
            DepositExportVO data = list.get(i);
            // 第五步，创建单元格，并设置值
            hssfRow.createCell(0).setCellValue(data.getNumber());
            hssfRow.createCell(1).setCellValue(data.getPayNumber());
            hssfRow.createCell(2).setCellValue(data.getOrderAmount().toString());
            hssfRow.createCell(3).setCellValue(data.getMemberName());
            hssfRow.createCell(4).setCellValue(data.getSource());
            hssfRow.createCell(5).setCellValue(data.getStatus());
        }
        return wb;
    }


    public void export(String title, HSSFWorkbook wb, HttpServletResponse response) {
        // 第六步，实现文件下载保存
        try {
            response.setHeader("content-disposition", "attachment;filename="
                    + URLEncoder.encode(title, "utf-8") + ".xls");

            OutputStream outputStream = response.getOutputStream();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wb.write(baos);
            byte[] xlsBytes = baos.toByteArray();
            outputStream.write(xlsBytes);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}