导出下载excel功能

球king

已于 2023-10-04 21:15:01 修改

阅读量65

点赞数

分类专栏： java 文章标签： excel java

于 2023-09-27 09:35:21 首次发布

本文链接：https://blog.csdn.net/weixin_67377649/article/details/133339492

版权

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

javaScript 代码

$(function () {
    // 点击导出按钮
    $("#exportBtn").click(function () {
        layer.confirm('是否确定导出查询数据', {
            title: "提示",
            icon: 3,
            offset: '30%'
        }, function (index) {
            let institution = {}
            institution.isPublic = $("#isPublic").val(); // 查询条件
            institution.fileStatus = $("#fileStatus").val();  // 查询条件
            institution.title = $("#title").val();  // 查询条件
            layer.msg(downloadExcel(ctx + 'institution/expInstitutionExcel', institution), {time: 500,offset:'30%'})
        });
    });
})


function downloadExcel(url, institution) {
    let html = [];
    html.push(`<html><head></head><body><h3>开始下载...</h3><form id='expForm' onsuspend="" method='post' action='${url}'>`);
    for (const key in institution) {
        html.push(`<input type='hidden' name='${key}' value='${institution[key]}'/>`);
    }
    html.push(`</form><script type='text/javascript'>document.getElementById("expForm").submit();</script></body></html>`);
    return html.join("");
}

Java后端代码

    /**
     * 导出excel
     * @param response
     * @param request
     * @return
     */
    @RequestMapping("/expInstitutionExcel")
    @ResponseBody
    public String expInstitutionExcel(HttpServletResponse response, HttpServletRequest request){
        ExportUtil<Institution> exportUtil = new ExportUtil<>();
        String fileName = "制度文件" + System.currentTimeMillis() + ".xlsx";
        try {
            // 查看制度数据
            List<Institution> institutionList = institutionService.getInstitutionList(request);
            // 设置头部信息
            String headers = "[{name:'序号',width:8},{name:'文档名称',width:40},{name:'级别',width:10}," +
                    "{name:'部门',width:25},{name:'文档类别',width:20},{name:'文档状态',width:15},{name:'公开状态',width:15}," +
                    "{name:'发布状态',width:15}]";
            List<ExcelHeader> headerList = JSON.parseArray(String.valueOf(JSON.parse(headers)), ExcelHeader.class);
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            // 导出
            exportUtil.expData(institutionList,workbook,headerList);
            // 下载
            ExportUtil.downloadExcel(response,workbook,fileName);
        } catch (Exception e) {
            e.printStackTrace();
            return "<html><body>" +
                    "<a href=\"javascript:window.opener=null;window.open('','_self');window.close();\" style=\"text-decoration: none;color:black;font-size:20px;\">网络异常，请重新导出</a>" +
                    "</body></html>";
        }
        return null;
    }

保存头部信息的实体类

public class ExcelHeader {
    // 宽度
    private int width;
    // 名称
    private String name;

    public int getWidth() {
        return width;
    }

    public void setWidth(int width) {
        this.width = width;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

导出方法

    /**
     * 导出
     * @param list
     * @param workbook
     * @param headerList
     * @throws Exception
     */
    public void expData(List<Institution> list, Workbook workbook, List<ExcelHeader> headerList) throws Exception {
        // 将行索引初始化为0，表示从第一行开始
        int rowIndex = 0;
        Sheet sheet = workbook.createSheet();
        // 设置所有列的默认宽度为20个字符
        sheet.setDefaultColumnWidth(20);
        // 自动调整列的宽度，该方法的参数2表示调整第2列的宽度
        sheet.autoSizeColumn(2);
        // 创建新的行对象
        Row row = sheet.createRow(rowIndex++);
        // 创建新的单元格样式对象
        CellStyle headStyle = workbook.createCellStyle();
        // 创建字体对象
        Font font = workbook.createFont();
        // 设置字体的大小为11磅
        font.setFontHeightInPoints((short)11);
        // 设置字体名称
        font.setFontName("Courier New");
        // 设置粗体
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headStyle.setFont(font);

        // 设置上下左右四个边框的宽度为细线
        headStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        headStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        headStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        headStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        // 设置上下左右四个边框的颜色为黑色
        headStyle.setTopBorderColor(HSSFColor.BLACK.index);
        headStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        headStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        headStyle.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置单元格内容的水平对齐方式为居中对齐
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 遍历headerList，并逐个创建单元格、设置单元格值和样式，并设置列宽
        for (int i = 0; i < headerList.size(); i++) {
            ExcelHeader header = headerList.get(i);
            Cell cell = row.createCell(i);
            cell.setCellValue(header.getName());
            cell.setCellStyle(headStyle);
            sheet.setColumnWidth(i, header.getWidth() * 256);
        }

        CellStyle cellStyle = workbook.createCellStyle();
        Font cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 11);
        cellFont.setFontName("Courier New");
        cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        //设置上下左右四个边框宽度
        cellStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        cellStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        //设置上下左右四个边框颜色
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        for (int i = 0; i < list.size(); i++) {
            Institution institution = list.get(i);
            Row row1 = sheet.createRow(rowIndex++);
            // 序号
            Cell cell0 = row1.createCell(0);
            cell0.setCellValue(i+1);
            cell0.setCellStyle(cellStyle);
            // 文档名称
            Cell cell1 = row1.createCell(1);
            cell1.setCellValue(institution.getTitle());
            cell1.setCellStyle(cellStyle);
            // 级别
            Cell cell2 = row1.createCell(2);
            cell2.setCellValue(institution.getDepLevel());
            cell2.setCellStyle(cellStyle);
            // 部门
            Cell cell3 = row1.createCell(3);
            cell3.setCellValue(institution.getRelationDepName());
            cell3.setCellStyle(cellStyle);
            // 文档类别
            Cell cell4 = row1.createCell(4);
            cell4.setCellValue(institution.getTypeName());
            cell4.setCellStyle(cellStyle);
            // 文档状态
            Cell cell5 = row1.createCell(5);
            Integer status = institution.getFileStatus();
            String fileStatus = "";
            if (status != null) {
                if (status == 1) {
                    fileStatus = "有效";
                }else if (status == 2) {
                    fileStatus = "修改";
                }else {
                    fileStatus = "已废止";
                }
            }
            cell5.setCellValue(fileStatus);
            cell5.setCellStyle(cellStyle);
            // 公开状态
            Cell cell6 = row1.createCell(6);
            cell6.setCellValue(institution.getIsPublic() == 1 ? "公开" : "不公开");
            cell6.setCellStyle(cellStyle);
            // 发布状态
            Cell cell7 = row1.createCell(7);
            Integer ats = institution.getApprovalStatus();
            String approvalStatus = "";
            if (ats == -1) {
                approvalStatus = "退回";
            }else if (ats == 0) {
                approvalStatus = "保存";
            }else if (ats == 101) {
                approvalStatus = "审核中";
            }else {
                approvalStatus = "已发布";
            }
            cell7.setCellValue(approvalStatus);
            cell7.setCellStyle(cellStyle);
        }
    }

下载excel

    /**
     * 下载Excel
     *
     * @param response
     * @param wb
     * @param fileName
     * @throws Exception
     */
    public static void downloadExcel(HttpServletResponse response, SXSSFWorkbook wb, String fileName) throws Exception {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

引入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>

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

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