Springboot apache poi 浏览器下载 excel

qiaobing1226

已于 2022-06-30 10:50:48 修改

阅读量655

点赞数

分类专栏： springboot 文章标签： spring boot java apache

于 2022-06-14 15:42:20 首次发布

本文链接：https://blog.csdn.net/qiaobing1226/article/details/125279090

版权

springboot 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用SpringBoot通过POST接口支持下载Excel文件，涉及pom.xml依赖配置、Controller接口、Service业务逻辑、实体类以及实用工具类ExcelUtil的实现过程。

摘要由CSDN通过智能技术生成

在日常干活中，经常会遇到后端实现下载文件的需求，将自己实现的步骤记录下来，以便以后查看方便或者帮助更多的同学。

springboot POST 接口，支持浏览器下载excel文件，具体实现实现：

1.pom 加入依赖jar

        <!-- excel导出工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

2.controller 层添加接口

    @RequestMapping(value = "/exportAddataReport", method = RequestMethod.POST)
    public void exportAddataReport(@RequestBody ExportAddataBatchIdDto exportAddataBatchIdDto,
                                   HttpServletResponse response) throws Exception {
        log.info("exportAddataReport 入参: {}", exportAddataBatchIdDto.toString());
        if (StringUtils.isNotEmpty(exportAddataBatchIdDto.getBatchId()) && StringUtils.isNotEmpty(exportAddataBatchIdDto.getTaskId())) {
            materialServiceImpl.exportAddataReport(response, exportAddataBatchIdDto);
        }
    }

3.service层加入逻辑代码

@Override
    public void exportAddataReport(HttpServletResponse response, ExportAddataBatchIdDto exportAddataBatchIdDto) {
        String suffix = ".xls";
        try {
            CsvFileUtils csvFileUtils = new CsvFileUtils();
            List<Dict> dictList = new ArrayList<>();
            String fName = exportAddataBatchIdDto.getTaskId() + "_" + exportAddataBatchIdDto.getBatchId();
            //表头赋值
            String[] head = {"名称", "编号", "描述", "图层", "要素ID", "tileID", "误报编号"};
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode(fName, "UTF-8");
            List<IcsCheckReport> icsCheckReports = icsCheckReportMapper.selectAddataReportByBatchId(Integer.valueOf(exportAddataBatchIdDto.getBatchId()), "", null, "", "", null);
            List<String[]> list = new ArrayList<>();
            for (IcsCheckReport report : icsCheckReports) {
                String[] arrs = new String[head.length];
                arrs[0] = String.valueOf(report.getCheckName() == null ? "" : report.getCheckName());
                arrs[1] = String.valueOf(report.getChkId() == null ? "" : report.getChkId());
                arrs[2] = String.valueOf(report.getErrorDesc() == null ? "" : report.getErrorDesc());
                arrs[3] = String.valueOf(report.getLayerName() == null ? "" : report.getLayerName());
                arrs[4] = String.valueOf(report.getFeatureId() == null ? "" : report.getFeatureId());
                arrs[5] = String.valueOf(report.getTileId() == null ? "" : report.getTileId());
                arrs[6] = String.valueOf(report.getMisrepId() == null ? "" : report.getMisrepId());
                list.add(arrs);
            }
            if (!list.isEmpty()) {
                ExcelData data = new ExcelData();
                data.setHead(head);
                data.setData(list);
                data.setFileName(fileName + suffix);
                //实现导出
                ExcelUtil excelUtil = new ExcelUtil();
                excelUtil.exportExcel(response, data);
                log.info("export list size:" + list.size());
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e.toString());
            e.printStackTrace();
        }
    }

4.实体类

@Data
public class ExportAddataBatchIdDto {
    @NotNull(message = "batchId不可为空")
    String batchId;
    @NotNull(message = "taskId不可为空")
    String taskId;
}

5.工具类ExcelUtil

package com.xxx.check.util;

import com.xxx.check.model.IcsCheckReport;
import com.xxx.check.model.vo.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;


public class ExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    public void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("导出解析开始，fileName:{}", data.getFileName());
        try {
            //实例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个Excel表单，参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //设置表头
            setTitle(workbook, sheet, data.getHead());
            //设置单元格并赋值
            setData(sheet, data.getData());
            //设置浏览器下载
            setBrowser(response, workbook, data.getFileName());
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }


    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            //居中
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //创建表头名称
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }


    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try {
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }


    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        OutputStream outStream = null;
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            outStream = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gbk");
            response.setCharacterEncoding("GBK");
            // 这里URLEncoder.encode可以防止中文乱码
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            //将excel写入到输出流中
            workbook.write(outStream);
            outStream.flush();
            outStream.close();
            response.flushBuffer();
            log.info("浏览器下载成功:{}", fileName);
        } catch (Exception e) {
            log.info("浏览器下载失败:{}", fileName);
            e.printStackTrace();
        } finally {
            if (outStream != null) {
                try {
                    outStream.close();
                } catch (Exception e) {
                    log.error("浏览器下载失败 ::: " + e.getMessage(), e);
                }
            }
        }

    }


    public static List<Object[]> importExcel(String fileName) {
        log.info("导入解析开始，fileName:{}", fileName);
        try {
            List<Object[]> list = new ArrayList<>();
            InputStream inputStream = new FileInputStream(fileName);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            //获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                //过滤表头行
                if (i == 0) {
                    continue;
                }
                //获取当前行的数据
                Row row = sheet.getRow(i);
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        objects[index] = (int) cell.getNumericCellValue();
                    }
                    if (cell.getCellType().equals(STRING)) {
                        objects[index] = cell.getStringCellValue();
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                    }
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                    }
                    index++;
                }
                list.add(objects);
            }
            log.info("导入文件解析成功！");
            return list;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }


}