SpringBoot 导出 Excel

  • 时间:
  • 来源:互联网
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_29323645/article/details/103202565

添加 maven 依赖

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

编写工具类

  • 可以实现 
    • 1.导出Excel 到本地 / 浏览器
    • 2.导出包含多个 Sheet 的 Excel 到本地 / 浏览器
package com.geotmt.billingcenter.common.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Descriptinon: Excel 导出类
 * @Author:       yanghanwei
 * @Mail:         yanghanwei@geotmt.com
 * @CreateDate:   2018/12/3 17:15
 * @Version:      v1
 */
public class ExportExcelUtils {

    private static final String ATTR_ARR_STR = "attrArr";
    private static final String DATA_LIST_STR = "dataList";
    private static final String TITLE_ARR_STR = "titleArr";
    private static final String SHEET_NAME = "Sheet";
    private static final String SHEET_NAME_STR = "sheetName";
    /**
     * 默认单元格宽度
     */
    private static final Integer DEFAULT_CELL_WIDTH = 3000;

    private static FileOutputStream fout = null;
    private static OutputStream out = null;

    private static OutputStream setResponseHeaderFileName(HttpServletResponse response, String filename) throws IOException {
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename+".xls","UTF-8"));
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        return out;
    }

    private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);

    /**
     * 导出 exccel到本地 工具类(多个sheet导出)
     * @param mapList
     * @param filePath 本地路径
     * sheetName:   sheet名字
     * titleArr:    表头中文名数组
     * attrArr:     数据对应的实体类属性数组(与表头一一对应)
     * dataList:    到处的数据list
     */
    public static void buildExcelTemplate( List<Map<String, Object>> mapList,String filePath, Integer cellWidth) {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fout = null;
        try{
            // 将文件存到指定位置
            fout = new FileOutputStream(filePath);
            if (!mapList.isEmpty()) {
                for (int a = 0; a < mapList.size(); a++) {
                    String sheetName = "Sheet1";
                    if(!StringUtils.isEmpty(mapList.get(a).get(SHEET_NAME_STR))){
                        sheetName = String.valueOf(mapList.get(a).get(SHEET_NAME_STR));
                    }
                    String[] titleArr = (String[]) mapList.get(a).get("titleArr");
                    String[] attrArr = (String[]) mapList.get(a).get("attrArr");
                    Object dataList = mapList.get(a).get("dataList");
                    // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
                    HSSFSheet sheetMonitor = wb.createSheet(sheetName);
                    // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                    HSSFRow rowMonitor = sheetMonitor.createRow(0);
                    // 第四步,创建单元格,并设置值表头 设置表头居中
                    HSSFCellStyle style = ExportExcelUtils.getColumnTopStyle(wb);

                    HSSFCell cellMonitor = rowMonitor.createCell(0);
                    for (int i = 0; i < titleArr.length; i++) {
                        cellMonitor.setCellValue(titleArr[i]);
                        cellMonitor.setCellStyle(style);
                        cellMonitor = rowMonitor.createCell((i + 1));
                    }
                    String jsonString = JSONObject.toJSONString(dataList);
                    JSONArray jsonArray = JSONObject.parseArray(jsonString);
                    if (jsonArray != null && !jsonArray.isEmpty()) {
                        for (int i = 0; i < jsonArray.size(); i++) {
                            sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
                            Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                            if(null != map){
                                for (int j = 0; j < titleArr.length; j++) {
                                    rowMonitor = sheetMonitor.createRow(i + 1);
                                    // 第五步,写入实体数据 实际应用中这些数据从数据库得到,
                                    for (int k = 0; k < attrArr.length; k++) {
                                        if (map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                                            // 第四步,创建单元格,并设置值
                                            if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                                String value = String.valueOf(map.get(attrArr[k]));
                                                rowMonitor.createCell(k).setCellValue(value);
                                            }else{
                                                rowMonitor.createCell(k).setCellValue("");
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            wb.write(fout);
        }catch (Exception e){
            logger.error("导出Excel失败,Exception:" + e);
            logger.info("导出Excel失败,Exception:{0}", e);
        }finally {
            try {
                if(null != fout){
                    fout.close();
                }
            }catch (Exception e){
                logger.error("关闭流异常:[{}]",e);
            }
        }
    }

    /**
     * 导出 exccel 到浏览器工具类(1个sheet导出)
     * @param dataListForMap
     * sheetName:       sheet名字
     * titleArr:        表头中文名数组
     * attrArr:         数据对应的实体类属性数组(与表头一一对应)
     * dataListForMap:  导出的数据list
     */
    public static void buildExcelTemplate(Map<String, Object> dataListForMap, HttpServletResponse response, String fileName, Integer cellWidth) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
        Object sheetName = dataListForMap.get("sheetName");
        HSSFSheet sheetMonitor = wb.createSheet(!StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME);
        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow rowMonitor = sheetMonitor.createRow(0);
        try{
            out = setResponseHeaderFileName(response,fileName);
            //创建excel标题,设置列宽
            createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
            //写入数据
            writeData(sheetMonitor,1,dataListForMap);
            wb.write(out);
        }catch (Exception e){
            logger.info("导出Excel失败,Exception:{}",e);
        }finally {
            try {
                out.close();
            }catch (Exception e){
                logger.error("关闭流异常:[{}]",e);
            }
        }
    }


    /**
     * 导出多个 sheet 到浏览器
     * @param mapList
     *      * sheetName:   sheet名字
     *      * titleArr:    表头中文名数组
     *      * attrArr:     数据对应的实体类属性数组(与表头一一对应)
     *      * dataList:    到处的数据list
     * @param response
     * @param fileName      文件名称
     */
    public static void buildExcelTemplate(List<Map<String, Object>> mapList, HttpServletResponse response, String fileName, Integer cellWidth) {
        HSSFWorkbook wb = new HSSFWorkbook();
        if(null != mapList && !mapList.isEmpty()){
            try{
                for (int i=0; i<mapList.size(); i++) {
                    Map<String, Object> dataListForMap = mapList.get(i);
                    // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
                    Object sheetName = dataListForMap.get(SHEET_NAME_STR);
                    String sheetNameRt = !StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME + (i+1);
                    HSSFSheet sheetMonitor = wb.createSheet(sheetNameRt);
                    // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                    HSSFRow rowMonitor = sheetMonitor.createRow(0);
                    out = setResponseHeaderFileName(response,fileName);
                    //创建excel标题
                    createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
                    //写入数据
                    writeData(sheetMonitor,1,dataListForMap);
                }
                wb.write(out);
            }catch (IOException e){
                e.printStackTrace();
                logger.info("导出Excel失败,Exception:{}",e);
            }catch (Exception e) {
                logger.info("导出Excel失败,Exception:{}",e);
            }finally {
                try {
                    out.close();
                }catch (Exception e){
                    logger.error("关闭流异常:[{}]",e);
                }
            }
        }
    }

    /**
     * 创建excel标题
     * @param dataListForMap
     */
    private static void createTitle(HSSFWorkbook wb, HSSFRow rowMonitor, Map<String, Object> dataListForMap, HSSFSheet sheetMonitor, Integer cellWidth){
        String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);

        //居中样式
        HSSFCellStyle centerStyle = ExportExcelUtils.getColumnTopStyle(wb);

        for (int i = 0; i < titleArr.length; i++) {
            // 设置表格宽度(自适应)
            sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
            HSSFCell cellMonitor = rowMonitor.createCell(i);
            cellMonitor.setCellStyle(centerStyle);
            cellMonitor.setCellValue(titleArr[i]);
        }
    }

    /**
     * 写入数据
     * @param sheetMonitor
     * @param startRow
     * @param dataListForMap
     */
    public static void writeData(HSSFSheet sheetMonitor, Integer startRow, Map<String, Object> dataListForMap){
        String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
        String[] attrArr = (String[]) dataListForMap.get(ATTR_ARR_STR);
        Object dataList = dataListForMap.get(DATA_LIST_STR);

        String jsonString = JSONObject.toJSONString(dataList);
        JSONArray jsonArray = JSONObject.parseArray(jsonString);
        if (jsonArray != null && !jsonArray.isEmpty()) {
            for (int i = 0; i < jsonArray.size(); i++) {
                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                for (int j = 0; j < titleArr.length; j++) {
                    HSSFRow rowMonitor = sheetMonitor.createRow( i + startRow);
                    // 第五步,写入实体数据 实际应用中这些数据从数据库得到,
                    for (int k = 0; k < attrArr.length; k++) {
                        if (map != null && attrArr[k] != null && map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                            // 第四步,创建单元格,并设置值
                            if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                String value = String.valueOf(map.get(attrArr[k]));
                                rowMonitor.createCell(k).setCellValue(value);
                            }else{
                                rowMonitor.createCell(k).setCellValue("");
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 列头单元格样式
     * @param workbook
     * @return
     */
    private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)12);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());

        style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;

    }

    /**
     * 列数据信息单元格样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)12);
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //在样式用应用设置的字体;
        style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }

    /**
     * 判断是否是整数
     * @param str
     * @return
     */
    private static Pattern pattern = Pattern.compile("^\\d+$");
    public static boolean isIntNum(String str) {
        Matcher isIntNum = pattern.matcher(str);
        if (!isIntNum.matches()) {
            return false;
        }
        return true;
    }

    /**
     * 判断是否是浮点数
     * @param str
     * @return
     */
    private static Pattern pattern2 = Pattern.compile("\\d+\\.\\d+$|-\\d+\\.\\d+$");
    public static boolean isDoubleNum(String str) {
        Matcher isDoubleNum = pattern2.matcher(str);
        if (!isDoubleNum.matches()) {
            return false;
        }
        return true;
    }
}

简单使用

@ApiOperation(value = "导出成本统计")
    @RequestMapping(value = "/exportList",method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public void ExportConsumeStatis(@RequestBody ParamsCost obj,
                                    HttpServletResponse response){
        logger.info("导出成本统计,ParamsConsume:{}",obj);
        try{
            if(null == obj.getEffectStartTime() || null == obj.getEffectEndTime()){
                throw new MyException("开始时间和结束时间不能为空!", HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            }
            String startStr = GeoDateUtils.getDateStr(obj.getEffectStartTime(), GeoDateUtils.DATE_FORMAT2);
            String endStr = GeoDateUtils.getDateStr(obj.getEffectEndTime(), GeoDateUtils.DATE_FORMAT2);
            String fileName = startStr + "-" + endStr + "(成本统计)";
            List<CostStatisVo> consumeStatisVos = costStatisService.costStatisList(obj);
            if (null != consumeStatisVos) {
                consumeStatisVos.add(costStatisService.costStatisTotal(obj));
            }
            
            // 拼接 Excel 数据
            Map<String,Object> map = new HashMap<>(3);
            String[] titleArr = {"日期","产品","客户","用户","用户账号","用户ID","运营商","内部接口","外部接口","计费数","金额"};
            String[] attrArr = {"dayFlag","productName","companyName","cuserName","account","cuserId","isp","interfaceName","outInterfaceName","countCharging","cost"};
            map.put("titleArr",titleArr);
            map.put("attrArr",attrArr);
            map.put("dataList", consumeStatisVos);
            ExportExcelUtils.buildExcelTemplate(map, response, fileName,4000);
        }catch (Exception e) {
            e.printStackTrace();
            logger.error("导出消耗统计,Exception:",e);
        }
    }

 

本文链接http://element-ui.cn/news/show-175.aspx