下载EXCEL统一接口——记录开发篇2

下载EXCEL统一接口

前言：公司对系统各个页面导入和导出需求太大，抽出导出录入模板，做成统一接口，供所有项目调用，

1. vo类

templateParameters为下拉值参数，key为需要设置下拉的列，第一列为0，value为下拉的list值

@Data
public class ExcelVo {

    @ApiModelProperty("EXCEL文件名称")
    @NotNull(message = "EXCEL文件名称为空")
    private String fileName;

    @ApiModelProperty("sheet名称")
    @NotNull(message = "sheet名称为空")
    private String sheetName;

    @ApiModelProperty("表头信息")
    @NotNull(message = "表头信息为空")
    private List<String> title;

    @ApiModelProperty("下拉值模板参数")
    private Map<String,List<String>> templateParameters;
}

2. 逻辑层

@Override
    public void download(ExcelVo excelVo, HttpServletRequest request, HttpServletResponse response) {
        HttpDownloadUtils.download(request, response, excelVo.getFileName()+".xlsx",
                this.getWorkbook(excelVo.getTitle(),excelVo.getTemplateParameters(),excelVo.getSheetName()));
    }



    private Workbook getWorkbook(List<String> title, Map<String,List<String>> templateParameters,String sheetName) {
        // 第一步，创建一个webBook，对应一个Excel文件
        Workbook wb = new XSSFWorkbook();
        // 第二步，在webBook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        // 创建第0行 也就是标题
        Row header = sheet.createRow(0);
        // 第三步创建标题的单元格样式style2以及字体样式headerFont1
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 创建绘图对象
        Drawing remark = sheet.createDrawingPatriarch();
        // 创建字体样式
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        // 设置字体类型
        headerFont.setFontName("黑体");
        // 为标题样式设置字体样式
        headerStyle.setFont(headerFont);
        // 创建表头第一列
        for (int i = 0; i < title.size(); i++) {
            Cell materialRow = header.createCell(i);
            materialRow.setCellValue(title.get(i));
            materialRow.setCellStyle(headerStyle);
        }
        // 设置列宽
        for (int i = 0; i < title.size(); i++) {
            sheet.setColumnWidth(i, 35 * 256);
        }

        //遍历下拉值
        if(templateParameters.size() > 0){
            for (Map.Entry<String, List<String>> entry : templateParameters.entrySet()) {
                //设置下拉值
                List<String> downValues = entry.getValue();
                //下拉值sheet
                Sheet categorySheet = wb.createSheet(String.valueOf(entry.getKey()));
                for (int i = 0; i < downValues.size(); i++) {
                    Row orgRow = categorySheet.createRow(i);
                    Cell orgCell = orgRow.createCell(0);
                    orgCell.setCellValue(downValues.get(i));
                }
                // 设置数据有效性
                DataValidationHelper help = sheet.getDataValidationHelper();
                String categorysFormula = entry.getKey() + "!$A$1:$A$" + downValues.size();
                CellRangeAddressList categorysRegions = new CellRangeAddressList(1, ComlibConstant.EXCEL_DATA_VALIDATION_ROW, Integer.parseInt(entry.getKey()), Integer.parseInt(entry.getKey()));
                DataValidationConstraint categorysConstraint = help.createFormulaListConstraint(categorysFormula);
                DataValidation categorysDataValidation = help.createValidation(categorysConstraint, categorysRegions);
                categorysDataValidation.setSuppressDropDownArrow(true);
                sheet.addValidationData(categorysDataValidation);
                // 设置产地下拉值sheet隐藏
                wb.setSheetHidden(wb.getSheetIndex(String.valueOf(entry.getKey())), true);
            }
        }
        return wb;
    }

3. excel工具类

import com.google.common.collect.Sets;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Iterator;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HttpDownloadUtils {
    private static final Logger log = LoggerFactory.getLogger(HttpDownloadUtils.class);
    private static final Set<String> SPECIAL_IE_HEADERS = Sets.newHashSet(new String[]{"MSIE", "TRIDENT", "EDGE"});

    public HttpDownloadUtils() {
    }

    public static void download(HttpServletRequest request, HttpServletResponse response, String filename, Long contentLength, String contentType, byte[] buffer) {
        BufferedOutputStream out = null;

        try {
            String name = downloadFilename(request, filename);
            response.addHeader("Content-Disposition", "attachment;filename=\"" + name + "\"");
            if (contentLength != null) {
                response.addHeader("Content-Length", "" + contentLength);
            }

            if (StringUtils.isNotBlank(contentType)) {
                response.setContentType(contentType);
            }

            out = new BufferedOutputStream(response.getOutputStream());
            out.write(buffer);
            out.flush();
        } catch (Exception var16) {
            log.error("download error:", var16);
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException var15) {
                    log.error("OutputStream close error:", var15);
                }
            }

        }

    }

    public static void download(HttpServletRequest request, HttpServletResponse response, String filename, Workbook workbook) {
        OutputStream out = null;
        if (workbook == null) {
            log.warn("Workbook is null");
        } else {
            try {
                String name = downloadFilename(request, filename);
                response.addHeader("Content-Disposition", "attachment;filename=\"" + name + "\"");
                response.setContentType("application/octet-stream");
                out = response.getOutputStream();
                workbook.write(out);
                out.flush();
            } catch (Exception var13) {
                log.error("download error:", var13);
            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException var12) {
                        log.error("OutputStream close error:", var12);
                    }
                }

            }

        }
    }

    public static String downloadFilename(HttpServletRequest request, String filename) {
        if (StringUtils.isBlank(filename)) {
            return "";
        } else {
            String chrome = "CHROME";
            String header = request.getHeader("User-Agent").toUpperCase();

            try {
                if (containIeHeader(header)) {
                    filename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());
                    filename = filename.replace("+", "%20");
                } else if (header.contains(chrome)) {
                    filename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());
                } else {
                    filename = new String(filename.getBytes(), StandardCharsets.ISO_8859_1);
                }
            } catch (UnsupportedEncodingException var5) {
                log.error("下载文件名编码异常", var5);
            }

            return filename;
        }
    }

    private static boolean containIeHeader(String header) {
        Iterator var1 = SPECIAL_IE_HEADERS.iterator();

        String ieHeader;
        do {
            if (!var1.hasNext()) {
                return false;
            }

            ieHeader = (String)var1.next();
        } while(!header.contains(ieHeader));

        return true;
    }
}

4. 测试数据

{
    "fileName":"文件名称",
    "sheetName":"测试dev",
    "title":["1","2","3"],
    "templateParameters":{
        "0":["1","2","3"],
        "2":["4","5","6"]
    }
}

5. 导出效果

今天工作完成，开始摸鱼~~~