java导出数据到Excel文件

    @GetMapping(value = "/exportData")
    public String export(@RequestParam("excel") String ex, HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        Excel excel = obj.readValue(ex, Excel.class);
        List<Column> columns = new ArrayList<>();
        //new Column("excel表头的名称", "与表头对应的实体类字段")
        columns.add(new Column("设备名称", "name"));
        columns.add(new Column("设备编号", "code"));
        columns.add(new Column("指标名称", "zbName"));
        columns.add(new Column("指标值（mg/L）", "zb"));
        columns.add(new Column("测量时间", "captureTime"));
        excel.setCols(columns);
        excel.setExcelName("尾水历史数据");
        excel.setApi("/api/exportExcel");
        excel.setMethod("GET");
        // excel.getQueryMap()  前端传来的搜索条件，
        excel.setQueryMap(excel.getQueryMap());
        excelService.Excel2007AboveOperate(excel, request, response);
        return ValueUtil.toJson();
    }

package com.admin.service.excel.impl;

import com.admin.service.excel.ExcelService;
import com.admin.service.excel.ExcelUtils;
import com.admin.service.excel.ForwardRequestUtil;
import com.admin.service.excel.StringUtil;
import com.basic.common.bean.RestJson;
import com.basic.common.bean.excel.Excel;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

@Service
public class ExcelServiceImpl implements ExcelService {

    @Override
    public void Excel2007AboveOperate(Excel excel, HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 声明一个工作薄
        HttpHeaders requestHeaders = new HttpHeaders();
        requestHeaders.add("Content-Type", "application/json;charset=utf-8");
        requestHeaders.add("Authorization", excel.getAuthorization());
        RestJson json = ForwardRequestUtil.forwardLocal(excel);
        if (json != null && json.getData() != null && json.getData() != "") {
            List data = (List) json.getData();
            response.setContentType("application/msexcel");// 定义输出类型
            if (StringUtil.isEmpty(request.getHeader("Access-Control-Allow-Origin"))) {
                response.setHeader("Access-Control-Allow-Origin", "*");
            }
            String userAgent = request.getHeader("User-Agent").toLowerCase();
            if (userAgent.contains("mise")) {
                response.setHeader("Content-disposition",
                        "attachment;filename=" + URLEncoder.encode(excel.getExcelName() + ".xlsx", "utf-8"));
            } else {
                response.setHeader("Content-disposition",
                        "attachment;filename=" + new String(excel.getExcelName().getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
            }
            ExcelUtils.Excel2007AboveOperate(excel, data, response);
        }

    }


}

 excel工具类

package com.admin.service.excel;

import com.basic.common.bean.excel.Column;
import com.basic.common.bean.excel.Excel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    /**
     * 读取excel文件
     *
     * @param in
     * @param
     * @return
     * @throws Exception
     */
    public static List<Map> getBankListByExcel(InputStream in, Excel excel) throws Exception {

        List<Map> maps = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = getWorkbook(in);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        List<String> labels = new ArrayList<>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            Row ones = sheet.getRow(0);
            //获得总列数
            int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
            for (int j = 1; j < coloumNum; j++) {
                labels.add(getName(ones.getCell(j), excel));
            }

            //遍历当前sheet中的所有行
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                Map map = new HashMap<>();
                for (int y = 1; y < coloumNum; y++) {
                    cell = row.getCell(y);
//                    //把每个单元格的值付给对象的对应属性
                    if (cell != null) {
                        String name = labels.get(y - 1);
                        String value = getValue(cell);
                        if (StringUtil.isNotEmptyObjects(value, name)) {
                            map.put(labels.get(y - 1), value);
                        }

                    }


                }
                //遍历所有的列(把每一行的内容存放到对象中)
                maps.add(map);
            }
        }
        return maps;
    }


    public static String getName(Cell cell, Excel excel) {
        //行的值
        String rowValue = String.valueOf(getCellValue(cell));
        String name = "";
        List<Column> cols = excel.getCols();
        for (Column col : cols) {
            if (col.getLabel().equals(rowValue)) {
                name = col.getName();
                break;
            }
        }
        return name;
    }

    public static String getValue(Cell cell) {
        //行的值
        String rowValue = "";
        rowValue = String.valueOf(getCellValue(cell));

        return rowValue;
    }

    /**
     * @param inStr
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr) throws Exception {
        Workbook wb = null;
        wb = WorkbookFactory.create(inStr);
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    public static void Excel2007AboveOperate(Excel excel, List data, HttpServletResponse response) throws Exception {

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        List<String> labels = new ArrayList<>();
        List<String> columns = new ArrayList<>();
        for (int i = 0; i < excel.getCols().size(); i++) {
            Column column = excel.getCols().get(i);
            labels.add(column.getLabel());
            columns.add(column.getName());
        }

        Sheet sh = wb.createSheet();
        CellStyle cellstyle = wb.createCellStyle();// 设置表头样式
        cellstyle.setAlignment(HorizontalAlignment.CENTER);// 设置居中
        cellstyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直居中
        cellstyle.setAlignment(HorizontalAlignment.CENTER); //设置水平居中
        Font createFont = wb.createFont(); //创建字体样式

        createFont.setBold(true); // 字体加粗
        createFont.setFontName("Times New Roman"); //设置字体类型
        createFont.setFontHeightInPoints((short) 11); //设置字体大小
        cellstyle.setFont(createFont); //为标题样式设置字体样式
        Row row = sh.createRow(0);
        sh.setColumnWidth(0, 100 * 50);
        CellUtil.createCell(row, 0, "序号", cellstyle);
        for (int i = 0; i < labels.size(); i++) {
            sh.setColumnWidth(i + 1, 100 * 50);
            CellUtil.createCell(row, i + 1, labels.get(i), cellstyle);
        }


        for (int i = 0; i < data.size(); i++) {
            Row row1 = sh.createRow(i + 1);
            Object obj = DataDictionaryUtil.getValue(data.get(i));
            CellUtil.createCell(row1, 0, i + 1 + "");
            if (obj instanceof Map) {
                for (int j = 0; j < columns.size(); j++) {
                    String aa = columns.get(j).trim();
                    Object[] colArr = ((Map) obj).keySet().toArray();
                    for (int k = 0; k < colArr.length; k++) {
                        if (colArr[k].toString().equals(aa)) {
                            if (((Map) obj).get(colArr[k]) != null) {
                                String colstr = ((Map) obj).get(colArr[k]).toString();
                                CellUtil.createCell(row1, j + 1, colstr);

                            } else {
                                CellUtil.createCell(row1, j + 1, "");
                            }
                        }
                    }
                }
            } else {
                Field[] fields = obj.getClass().getDeclaredFields();
                for (int j = 0; j < columns.size(); j++) {
                    for (int k = 0; k < fields.length; k++) {
                        Field field = fields[k];
                        field.setAccessible(true);
                        if (field.getName().equals(columns.get(j).trim())) {
                            if (field.get(obj) != null) {
                                CellUtil.createCell(row1, j + 1, field.get(obj).toString());
                            } else {
                                CellUtil.createCell(row1, j + 1, "");
                            }
                        }

                    }
                }
            }

        }
        try (ServletOutputStream out = response.getOutputStream()) {
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            data = null;
            excel = null;
        }


    }
}

 查询数据的接口

    /**
     * 导出excel
     *
     * @return 响应类
     */

    @GetMapping(value = "/exportExcel")
    public String exportData(WaterQualityHisDTO requestDTO) {
        return ValueUtil.toJson(tailWaterDetectionService.selectTwHisAll(requestDTO));
    }

前端导出代码

 <el-button icon="el-icon-upload2" type="primary" @click="Export">导出</el-button>

 Export() {
      let excel = {
        authorization: getToken(),
        //搜索参数
        queryMap: this.params,
      };
      let downExcelUrl =
        this.http_url +
        `/api/exportData?excel=${encodeURI(JSON.stringify(excel))}`;
      const a = document.createElement("a");
      a.style.display = "none";
      a.href = downExcelUrl;
      a.click();
      a.remove();
    },