Java基于注解的Excel导出方式

目录

• Java注解的Excel导出
• java简单的导出excel
• 总结

Java注解的Excel导出

依赖：

<dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>4.1.2</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>4.1.2</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>4.1.2</version>
    </dependency>

注解配合工具类做了个小工具如下:

第一步：自定义注解:（读者请直接复制）

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
* 自定义导出Excel数据注解
*
* @author sunziwen
* @version 1.0
* @date 2018-12-29 15:00
**/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
  /**
  * 导出到Excel中的名字.
  */
  public String name();

  /**
  * 日期格式, 如: yyyy-MM-dd
  */
  public String dateFormat() default "";

  /**
  * 读取内容转表达式 (如: 0=男,1=女,2=未知)
  */
  public String readConverterExp() default "";

  /**
  * 导出时在excel中每个列的高度 单位为字符
  */
  public double height() default 14;

  /**
  * 导出时在excel中每个列的宽 单位为字符
  */
  public double width() default 20;

  /**
  * 文字后缀,如% 90 变成90%
  */
  public String suffix() default "";

  /**
  * 当值为空时,字段的默认值
  */
  public String defaultValue() default "";

  /**
  * 提示信息
  */
  public String prompt() default "";

  /**
  * 设置只能选择不能输入的列内容.
  */
  public String[] combo() default {};

  /**
  * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
  */
  public boolean isExport() default true;
}

第二步：实体类:（为每个需要的字段打上@Excel注解）

import java.time.LocalDateTime;

import com.ciih.authcenter.client.util.excel.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
* (User)表实体类
*
* @author suzniwen
* @since 2021-04-13 16:11:55
*/
@SuppressWarnings("serial")
@EqualsAndHashCode(callSuper = true)
@Data
public class User {
  @Excel(name = "编号")
  @ApiModelProperty(value = "主键")
  private String id;
  @Excel(name = "账号")
  @ApiModelProperty(value = "账号")
  private String loginName;
  @Excel(name = "用户名")
  @ApiModelProperty(value = "用户名")
  private String userName;
  @ApiModelProperty(value = "用户名拼音")
  private String namePinyin;
  @Excel(name = "性别", readConverterExp = "1=男,0=女")
  @ApiModelProperty(value = "性别")
  private String gender;
  @Excel(name = "证件类型",readConverterExp="1=居民身份证,2=香港居民来往内地通行证,3=澳门居民来往内地通行证,4=台湾居民来往大陆通行证,6=护照")
  @ApiModelProperty(value = "证件类型")
  private String credType;
  @Excel(name = "证件号码")
  @ApiModelProperty(value = "证件号码")
  private String credNum;
  @ApiModelProperty(value = "机构id")
  private String orgId;
  @Excel(name = "机构名称")
  @ApiModelProperty(value = "机构名称")
  private String orgName;
  @Excel(name = "电话")
  @ApiModelProperty(value = "电话")
  private String phone;
  @Excel(name = "邮箱")
  @ApiModelProperty(value = "邮箱")
  private String email;
  @Excel(name = "人员类型",readConverterExp = "student=学生,teacher=教师,parent=家长,system=系统人员,developers=开发者,manager=管理员")
  @ApiModelProperty(value = "人员类型")
  private String personType;
  @Excel(name = "应用系统角色编码")
  @ApiModelProperty(value = "应用系统角色编码")
  private String appRoleCode;
  @ApiModelProperty(value = "创建时间")
  private LocalDateTime createTime;
  @ApiModelProperty(value = "更新时间")
  private LocalDateTime updateTime;
}

第三步：解析工具类:（读者请直接复制） 

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
* Excel相关操作
*
* @author sunziwen
* @version 1.0
* @date 2022-03-25 15:20
**/
public class ExcelUtil<T> {
  private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

  private Class<T> clazz;

  public ExcelUtil(Class<T> clazz) {
    this.clazz = clazz;
  }

  /**
  * 对excel表单默认第一个索引名转换成list
  *
  * @param input 输入流
  * @return 转换后集合
  */
  public List<T> importExcel(InputStream input) throws Exception {
    return importExcel(StringUtils.EMPTY, input);
  }

  /**
  * 对excel表单指定表格索引名转换成list
  *
  * @param sheetName 表格索引名
  * @param input   输入流
  * @return 转换后集合
  */
  public List<T> importExcel(String sheetName, InputStream input) throws Exception {
    List<T> list = new ArrayList<T>();

    Workbook workbook = WorkbookFactory.create(input);
    Sheet sheet = null;
    if (StringUtils.isNotEmpty(sheetName)) {
      // 如果指定sheet名,则取指定sheet中的内容.
      sheet = workbook.getSheet(sheetName);
    } else {
      // 如果传入的sheet名不存在则默认指向第1个sheet.
      sheet = workbook.getSheetAt(0);
    }

    if (sheet == null) {
      throw new IOException("文件sheet不存在");
    }

    int rows = sheet.getPhysicalNumberOfRows();

    if (rows > 0) {
      // 默认序号
//      int serialNum = 0;
      // 有数据时才处理 得到类的所有field.
      Field[] allFields = clazz.getDeclaredFields();

      /**
      * 这里是要将实体类的属性与excel表的列序号对应上，有两种方式：
      * 1.按照先后顺序进行一一对应。
      * 2.按照注解的name值与表头对应起来
      */

      // 定义一个map用于存放列的序号和field.
      Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();

      //定义一个name到Excel表的index的映射Map
      HashMap<String, Integer> name2index = new HashMap<>();

      //默认第一行是表头
      Row r = sheet.getRow(0);
      for (int i = 0; i < allFields.length; i++) {
        Cell cell = r.getCell(i);
        if (cell == null) {
          continue;
        } else {
          // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
          r.getCell(i).setCellType(CellType.STRING);
          cell = r.getCell(i);
          String c = cell.getStringCellValue();
          if (StringUtils.isEmpty(c)) {
            continue;
          }
          name2index.put(c, i);
        }
      }

      for (int col = 0; col < allFields.length; col++) {
        Field field = allFields[col];
        // 将有注解的field存放到map中.
        if (field.isAnnotationPresent(Excel.class)) {
          Excel excel = field.getAnnotation(Excel.class);
          String name = excel.name();
          Integer index = name2index.get(name);
          if (index != null) {
            field.setAccessible(true);
            fieldsMap.put(index, field);
          }

        }
      }
      //下面这个是按序号一一对应的。不太友好，还是按照表头名称来对应
//      for (int col = 0; col < allFields.length; col++) {
//        Field field = allFields[col];
//        // 将有注解的field存放到map中.
//        if (field.isAnnotationPresent(Excel.class)) {
//          // 设置类的私有字段属性可访问.
//          field.setAccessible(true);
//          fieldsMap.put(++serialNum, field);
//        }
//      }
      for (int i = 1; i < rows; i++) {
        // 从第2行开始取数据,默认第一行是表头.
        Row row = sheet.getRow(i);
        int cellNum = allFields.length;
        T entity = null;
        for (int j = 0; j < cellNum; j++) {
          Cell cell = row.getCell(j);
          if (cell == null) {
            continue;
          } else {
            // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
            row.getCell(j).setCellType(CellType.STRING);
            cell = row.getCell(j);
          }

          String c = cell.getStringCellValue();
          if (StringUtils.isEmpty(c)) {
            continue;
          }

          // 如果不存在实例则新建.
          entity = (entity == null ? clazz.newInstance() : entity);
          // 从map中得到对应列的field.
          Field field = fieldsMap.get(j);
          // 取得类型,并根据对象类型设置值.
          Class<?> fieldType = field.getType();
          if (String.class == fieldType) {
            field.set(entity, String.valueOf(c));
          } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
            field.set(entity, Integer.parseInt(c));
          } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
            field.set(entity, Long.valueOf(c));
          } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
            field.set(entity, Float.valueOf(c));
          } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
            field.set(entity, Short.valueOf(c));
          } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
            field.set(entity, Double.valueOf(c));
          } else if (Character.TYPE == fieldType) {
            if ((c != null) && (c.length() > 0)) {
              field.set(entity, Character.valueOf(c.charAt(0)));
            }
          } else if (Date.class == fieldType) {
            //对字符串解析成日期

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String s = cell.getStringCellValue()
                .replaceAll("/", "-")
                .replaceAll("上午", "")
                .replaceAll("下午", "")
                .replaceAll(" ", " ");
            System.out.println("----------------------------:" + s);
            Date parse = sdf.parse(s);
            field.set(entity, parse);
//            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
//              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//              cell.setCellValue(sdf.format(cell.getNumericCellValue()));
//              c = sdf.format(cell.getNumericCellValue());
//            } else {
//              c = cell.getStringCellValue();
//            }
          } else if (java.math.BigDecimal.class == fieldType) {
            c = cell.getStringCellValue();
          }
        }
        if (entity != null) {
          list.add(entity);
        }
      }
    }

    return list;
javascript  }

  /**
  * 对list数据源将其里面的数据导入到excel表单
  * 针对List<Map>类型的数据
  *
  * @param list   导出数据集合
  * @param sheetName 工作表的名称
  * @return 结果
  */
  public static void exportMapExcel(List<Map> list, String[] title, String sheetName, HttpServletResponse response) {
    if (null == list || list.size() == 0) {
      return;
    }

    HSSFWorkbook workbook = null;
    try {
      // 产生工作薄对象
      workbook = new HSSFWorkbook();
      // excel2003中每个sheet中最多有65536行
      int sheetSize = 65536;
      // 取出一共有多少个sheet.
      double sheetNo = Math.ceil(list.size() / sheetSize);
      for (int index = 0; index <= sheetNo; index++) {
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        if (sheetNo == 0) {
          workbook.setSheetName(index, sheetName);
        } else {
          // 设置工作表的名称.
          workbook.setSheetName(index, sheetName + index);
        }
        HSSFRow row;
        HSSFCell cell; // 产生单元格

        // 产生第一行，写入标题
        row = sheet.createRow(0);
        if (null == title || title.length == 0) {
          throw new RuntimeException("导出错误");
        }
        for (int i = 0; i < title.length; i++) {
          cell = row.createCell(i);
          cell.setCellType(CellType.STRING);
          cell.setCellValue(title[i]);
        }
        int cell_idx = 0;
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());
        // 写入各条记录,每条记录对应excel表中的一行
        for (int i = startNo; i < endNo; i++) {
          row = sheet.createRow(i + 1 - startNo);
          // 得到导出对象.
          Map map = list.get(i);
          Set keySet = map.keySet();
          Iterator values = keySet.iterator();
          cell_idx = 0;
          while (values.hasNext()) {
            Object value = map.get(values.next());
            cell = row.createCell(cell_idx);
            cell.setCellValue(value.toString());
            cell_idx++;
          }
        }
      }
      //String filename = encodingFilename(sheetName);
      response.setHeader("content-Type", "application/vnd.ms-excel");
      response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(sheetName, "UTF-8"));
//      response.setContentType("application/octet-stream");
//      response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
      response.flushBuffer();
      workbook.write(response.getOutputStream());
      //return filename;
    } catch (Exception e) {
      log.error("导出Excel异常{}", e.getMessage());
      throw new RuntimeException("导出Excel失败，请联系网站管理员！");
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch开发者_开发培训 (IOException e1) {
          e1.printStackTrace();
        }
      }
    }
  }

  /**
  * 对list数据源将其里面的数据导入到excel表单
  *
  * @param list   导出数据集合
  * @param sheetName 工作表的名称
  * @return 结果
  */
  public void exportExcel(List<?> list, String sheetName, HttpServletResponse response) {
    HSSFWorkbook workbook = null;
    try {
      // 得到所有定义字段
      Field[] allFields = clazz.getDeclaredFields();
      List<Field> fields = new ArrayList<Field>();
      // 得到所有field并存放到一个list中.
      for (Field field : allFields) {
        if (field.isAnnotationPresent(Excel.class)) {
          fields.add(field);
        }
      }

      // 产生工作薄对象
      workbook = new HSSFWorkbook();
      // excel2003中每个sheet中最多有65536行
      int sheetSize = 65536;
      // 取出一共有多少个sheet.
      double sheetNo = Math.ceil(list.size() / sheetSize);
      for (int index = 0; index <= sheetNo; index++) {
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        if (sheetNo == 0) {
          workbook.setSheetName(index, sheetName);
        } else {
          // 设置工作表的名称.
          workbook.setSheetName(index, sheetName + index);
        }
        HSSFRow row;
        HSSFCell cell; // 产生单元格

        // 产生一行
        row = sheet.createRow(0);
        // 写入各个字段的列头名称
        for (int i = 0; i < fields.size(); i++) {
          Field field = fields.get(i);
          Excel attr = field.getAnnotation(Excel.class);
          // 创建列
          cell = row.createCell(i);
          // 设置列中写入内容为String类型
          cell.setCellType(CellType.STRING);
          HSSFCellStyle cellStyle = workbook.createCellStyle();
          cellStyle.setAlignment(HorizontalAlignment.CENTER);
          cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
          if (attr.name().indexOf("注：") >= 0) {
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFFont.COLOR_RED);
//            cellStyle.setFont(font);
            //设置颜色
//            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
            sheet.setColumnWidth(i, 6000);
          } else {
            HSSFFont font = workbook.createFont();
            // 粗体显示
            font.setBold(true);
            // 选择需要用到的字体格式
//            cellStyle.setFont(font);
            //设置颜色
//            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
            // 设置列宽
            sheet.setColumnWidth(i, (int) ((attr.width() + 0.72) * 256));
            row.setHeight((short) (attr.height() * 20));
          }
//          cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//          cellStyle.setWrapText(true);
          cell.setCellStyle(cellStyle);

          // 写入列名
          cell.setCellValue(attr.name());

          // 如果设置了提示信息则鼠标放上去提示.
          if (StringUtils.isNotEmpty(attr.prompt())) {
            // 这里默认设了2-101列提示.
            setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);
          }
          // 如果设置了combo属性则本列只能选择不能输入
          if (attr.combo().length > 0) {
            // 这里默认设了2-101列只能选择不能输入.
            setHSSFValidation(sheet, attr.combo(), 1, 100, i, i);
          }
        }

        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());
        // 写入各条记录,每条记录对应excel表中的一行
        HSSFCellStyle cs = workbook.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        for (int i = startNo; i < endNo; i++) {
          row = sheet.createRow(i + 1 - startNo);
          // 得到导出对象.
          T vo = (T) list.get(i);
          for (int j = 0; j < fields.size(); j++) {
            // 获得field.
            Field field = fields.get(j);
            // 设置实体类私有属性可访问
            field.setAccessible(true);
            Excel attr = field.getAnnotation(Excel.class);
            try {
              // 设置行高
              row.setHeight((short) (attr.height() * 20));
              // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
              if (attr.isExport()) {
                // 创建cell
                cell = row.createCell(j);
                cell.setCellStyle(cs);
                if (vo == null) {
                  // 如果数据存在就填入,不存在填入空格.
                  cell.setCellValue("");
                  continue;
                }

                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                if (StringUtils.isNotEmpty(dateFormat)) {
                  cell.setCellValue(new SimpleDateFormat(dateFormat).format((Date) field.get(vo)));
                } else if (StringUtils.isNotEmpty(readConverterExp)) {
                  cell.setCellValue(convertByExp(String.valueOf(field.get(vo)), readConverterExp));
                } else {
                  cell.setCellType(CellType.STRING);
                  // 如果数据存在就填入,不存在填入空格.
                  cell.setCellValue(field.get(vo) == null ? attr.defaultValue() : field.get(vo) + attr.suffix());
                }
              }
            } catch (Exception e) {
              log.error("导出Excel失败{}", e.getMessage());
            }
          }
        }
      }
      //String filename = encodingFilename(sheetName);
      response.setContentType("application/octet-stream");
      response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName, "UTF-8"));
      response.flushBuffer();
      workbook.write(response.getOutputStream());
    } catch (Exception e) {
      log.error("导出Excel异常{}", e.getMessage());
      throw new RuntimeException("导出Excel失败，请联系网站管理员！");
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (IOException e1) {
          e1.printStackTrace();
        }
      }
    }
  }

  /**
  * 设置单元格上提示
  *
  * @param sheet     要设置的sheet.
  * @param promptTitle  标题
  * @param promptContent 内容
  * @param firstRow   开始行
  * @param endRow    结束行
  * @param firstCol   开始列
  * @param endCol    结束列
  * @return 设置好的sheet.
  */
  private static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow,
                     int endRow, int firstCol, int endCol) {
    // 构造constraint对象
    DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
    // 四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
    dataValidationView.createPromptBox(promptTitle, promptContent);
    sheet.addValidationData(dataValidationView);
    return sheet;
  }

  /**
  * 设置某些列的值只能输入预制的数据,显示下拉框.
  *
  * @param sheet  要设置的sheet.
  * @param textlist 下拉框显示的内容
  * @param firstRow 开始行
  * @param endRow  结束行
  * @param firstCol 开始列
  * @param endCol  结束列
  * @return 设置好http://www.devze.com的sheet.
  */
  private static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow,
                       int firstCol, int endCol) {
    // 加载下拉列表内容
    DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
    sheet.addValidationData(dataValidationList);
    return sheet;
  }

  /**
  * 解析导出值 0=男,1=女,2=未知
  *
  * @param propertyValue 参数值
  * @param converterExp 翻译注解
  * @return 解析后值
  * @throws Exception
  */
  private static String convertByExp(String propertyValue, String converterExp) throws Exception {
    try {
      String[] convertSource = converterExp.split(",");
      for (String item : convertSource) {
        String[] itemArray = item.split("=");
        if (itemArray[0].equals(propertyValue)) {
          return itemArray[1];
        }
      }
    } catch (Exception e) {
      throw e;
    }
    return propertyValue;
  }

  /**
  * 编码文件名
  */
  /*private static String encodingFilename(String filename) {
    filename = UUID.randomUUID().toString() + "_" + filename + ".xls";
    return filename;
  }*/
}

第四步：无论导入导出，Excel表格的第一行都是跟实体类上@Excel注解的name属性进行映射对应的。

以下是示例:导出Excel表（该方法最好是返回void或者返回null），否则后台会报错（不影响运行）。   

  @PostMapping("/export")
  @ResponseBody
  public void export()
  {
    ExcelUtil<User> excelUtil = new ExcelUtil<>(User.class);
    //要导出的数据集
    List<User> list = getList();
    excelUtil.exportExcel(list, "userInformation.xlsx", ServletUtils.getResponse());
  }

附件：前端代码：

exportExcel() {
  let link = document.createElement('a');
  link.style.display = 'none'
  axIOS({
    url: 'http://test.cih.net/user/pageExport',
    methods: "get",
    data: {},
    headers: {
      token: 'eyJ0eXB1234bklkIjoie1wiZWnm7jwkJtbvdcnqo'
    }
  }).then((res) => {
    let blob = new Blob([res.data], {
      type: "application/vnd.ms-excel"
    }); // 2.获取请求返回的response对象中的blob 设置文件类型，这里以excel为例
    let url = window.URL.createObjectURL(blob); // 3.创建一个临时的url指向blob对象

    // 4.创建url之后可以模拟对此文件对象的一系列操作，例如：预览、下载
    let a = document.createElement("a");
    a.href = url;
    a.download = "导出表格.xlsx";
    a.click();
    // 5.释放这个临时的对象url
    window.URL.revokeObjectURL(url);
  })
}

java简单的导出excel

1.pom 引入poi

   <!-- poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooXML</artifactId>
    </dependency>

2.创建excel导出工具类

@Slf4j
public class ExportExcelUtils {

  /**
  * 导出Excel
  *
  * @param excelName 要导出的excel名称
  * @param list   要导出的数据集合
  * @param c     中英文字段对应Map，即要导出的excel表头
  * @param response 使用response可以导出到浏览器
  * @param <T>
  */
  public static <T> void export(String excelName, List<T> list, Class<T> c, HttpServletResponse response) {
    // 设置日期格式
    SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    // 设置默认文件名为当前时间：年月日时分秒
    if (excelName == null || excelName == "") {
      excelName = df.format(new Date());
    } else {
      excelName = excelName + df.format(new Date());
    }

    try {
      //创建一个WorkBook,对应一个Excel文件
      HSSFWorkbook wb = new HSSFWorkbook();
      //在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
      HSSFSheet sheet = wb.createSheet(excelName);
      //设置 边距、页眉、页脚
      HSSFPrintSetup printSetup = sheet.getPrintSetup();
      //打印方向，true：横向，false：纵向(默认)
      printSetup.setLandscape(true);
      printSetup.setHeaderMargin(0.2);
      printSetup.setFooterMargin(0.2);
      //设置打印缩放为88%
      ///printSetup.setScale((short) 55);
      printSetup.setFitHeight((short) 0);
      printSetup.setFitWidth((short) 1);
      //列从左向右显示②
      /// printSetup.setLeftToRight(true);
      // 纸张
      printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
      // 页边距（下）
      sheet.setMargin(HSSFSheet.BottomMargin, 0.8);
      // 页边距（左）
      sheet.setMargin(HSSFSheet.LeftMargin, 0);
      // 页边距（右）
      sheet.setMargin(HSSFSheet.RightMargin, 0);
      // 页边距（上）
      sheet.setMargin(HSSFSheet.TopMargin, 0.8);
      //设置打印页面为水平居中
      sheet.setHorizontallyCenter(true);
      sheet.setVerticallyCenter(true);
      sheet.setAutobreaks(false);
      sheet.setFitToPage(false);
      Footer footer = sheet.getFooter();
      //设置页数
      footer.setCenter("第" + HeaderFooter.page() + "页，共 " + HeaderFooter.numPages() + "页");
      Header header = sheet.getHeader();
      //自定义页眉,并设置页眉 左中右显示信息
      //居中
      ///header.setCenter("Center Header");
      //靠左
      header.setLeft(HSSFHeader.font("宋体", "") +
          HSSFHeader.fontSize((short) 16) + excelName + ".xlsx");
      //靠右
      ///header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
      ///HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

      //创建单元格，并设置值表头 设置表头居中
      HSSFCellStyle style = wb.createCellStyle();
      //设置边框
      //下边框
      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      //左边框
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      //上边框
      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      //右边框
      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      //自动换行
      ///style.setWrapText(true);
      //创建一个居中格式
      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      //上下居中
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      //设置字体
      HSSFFont font = wb.createFont();
      font.setFontName("宋体");

      style.setFont(font);
      // 填充工作表
      //获取需要转出的excel表头的map字段
      LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
      //循环注解里面的值 填入Link集合
      Field[] declaredFields = c.getDeclaredFields();
      //获取属性对应的枚举类
      LinkedHashMap<String, Class> converterMap = new LinkedHashMap<>();

      for (Field declaredField : declaredFields) {
        //获取注解对象
        ExportEntityMap declaredAnnotation = declaredField.getDeclaredAnnotation(ExportEntityMap.class);
        ExportProperty converterAnnotation = declaredField.getDeclaredAnnotation(ExportProperty.class);
        if (declaredAnnotation != null) {
          fieldMap.put(declaredAnnotation.EnName(), declaredAnnotation.CnName());
          if (converterAnnotation != null) {
            converterMap.put(declaredAnnotation.EnName(), converterAnnotation.Converter());
          }
        }
      }
      fillSheet(sheet, list, fieldMap, style, converterMap);
      // 设置response头信息
      response.reset();

      try {
        log.info("excelName:{}", excelName);
        String fileName = URLEncoder.encode(excelName + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
      } catch (UnsupportedEncodingException e1) {
        log.error("导出excel失败，异常：", e1);
      }
      //将文件输出
      OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
      response.setContentType("application/octet-stream");
      response.flushBuffer();

      wb.write(outputStream);
      outputStream.flush();
      outputStream.close();
    } catch (Exception e) {
      log.error("导出Excel失败，异常：", e);
    }
  }


  /**
  * 根据字段名获取字段对象
  *
  * @param fieldName 字段名
  * @param clazz   包含该字段的类
  * @return 字段
  */
  public static Field getFieldByName(String fieldName, Class<?> clazz) {
    // 拿到本类的所有字段
    Field[] selfFields = clazz.getDeclaredFields();
    // 如果本类中存在该字段，则返回
    for (Field field : selfFields) {
      //如果本类中存在该字段，则返回
      if (field.getName().equals(fieldName)) {
        return field;
      }
    }
    // 否则，查看父类中是否存在此字段，如果有则返回
    Class<?> superClazz = clazz.getSuperclass();
    if (superClazz != null && superClazz != Object.class) {
      //递归
      return getFieldByName(fieldName, superClazz);
    }
    // 如果本类和父类都没有，则返回空
    return null;
  }

  /**
  * 根据字段名获取字段值
  *
  * @param fieldName 字段名
  * @param o     对象
  * @return 字段值
  * @throws Exception 异常
  */
  public static String getFieldValueByName(String fieldName, Object o)
      throws Exception {
    String fieldValue = null;
    //根据字段名得到字段对象
    Field field = getFieldByName(fieldName, o.getClass());
    //如果该字段存在，则取出该字段的值
    if (field != null) {
      //类中的成员变量为private,在类外边使用属性值，故必须进行此操作
      field.setAccessible(true);
      //获取当前对象中当前Field的value
      fieldValue = field.get(o) == null ? "" : field.get(o).toString();
      String fieldType = field.getGenericType().toString();
      if ("class java.math.BigDecimal".equals(fieldType) || "class java.lang.Double".equals(fieldType)) {
        fieldValue = new BigDecimal(fieldValue).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
      } else if ("class java.time.LocalDateTime".equals(fieldType) || "class java.time.LocalDate".equals(fieldType)) {
        fieldValue = fieldValue.replace("T", " ");
      }
    } else {
      throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 "
          + fieldName);
    }
    return fieldValue;
  }

  /**
  * 根据带路径或不带路径的属性名获取属性值,即接受简单属性名， 如userName等，又接受带路径的属性名，如student.department.name等
  *
  * @param fieldNameSequence 带路径的属性名或简单属性名
  * @param o         对象
  * @return 属性值
  * @throws Exception 异常
  */
  public static String getFieldValueByNameSequence(String fieldNameSequence,
                          Object o) throws Exception {
    String value = null;
    // 将fieldNameSequence进行拆分
    String[] attributes = fieldNameSequence.split("\\.");
    if (attributes.length == 1) {
      value = getFieldValueByName(fieldNameSequence, o);
    } else {
      // 根据数组中第一个连接属性名获取连接属性对象，如student.department.name
      Object fieldObj = getFieldValueByName(attributes[0], o);
      //截取除第一个属性名之后的路径
      String subFieldNameSequence = fieldNameSequence
          .substring(fieldNameSequence.indexOf(".") + 1);
      //递归得到最终的属性对象的值
      value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
    }
    return value;
  }

  /**
  * 向工作表中填充数据
  *
  * @param sheet    excel的工作表名称
  * @param list     数据源
  * @param fieldMap   中英文字段对应关系的Map
  * @param style    表格中的格式
  * @param converterMap 属性对应转化器的Map
  * @throws Exception 异常
  */
  public static <T> void fillSheet(HSSFSheet sheet, List<T> list,
                  LinkedHashMap<String, String> fieldMap, HSSFCellStyle style, LinkedHashMap<String, Class> converterMap) throws Exception {
    // 定义存放英文字段名和中文字段名的数组
    String[] enFields = new String[fieldMap.size()];
    String[] cnFields = new String[fieldMap.size()];
    // 填充数组
    int count = 0;
    for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
      enFields[count] = entry.getKey();
      cnFields[count] = entry.getValue();
      count++;
    }

    //存储最大列宽
    Map<Integer, Integer> maxWidth = new HashMap<>(16);
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = null;
    // 填充表头
    for (int i = 0; i < cnFields.length; i++) {
      cell = row.createCell(i);
      cell.setCellValue(cnFields[i]);
      cell.setCellStyle(style);
      sheet.autoSizeColumn(i);
      //设置自适应宽高
      maxWidth.put(i, cell.getStringCellValue().getBytes().length * 256 + 200);
    }
    // 填充内容
    for (int index = 0; index < list.size(); index++) {
      row = sheet.createRow(index + 1);
      // 获取单个对象
      T item = list.get(index);
      int j = 0;
      for (int i = 0; i < enFields.length; i++) {
        HSSFCell createCell = row.createCell(j);
        String objValue = getFieldValueByNameSequence(enFields[i], item);
        /php/获取属性对应的枚举类
        ClFYaNwEpass<Enum> converterClazz = converterMap.get(enFields[i]);
        objValue = convertFieldValue(objValue, converterClazz);
        String fieldValue = objValue == null ? "" : objValue;
        cell = row.createCell(i);
        createCell.setCellValue(fieldValue);

        int length = createCell.getStringCellValue().getBytes().length * 256 + 200;
        //这里把宽度最大限制到15000
        if (length > 15000) {
          length = 15000;
        }
        maxWidth.put(j, Math.max(length, maxWidth.get(j)));
        j++;
        createCell.setCellStyle(style);
      }
    }

    // 列宽自适应
    for (int i = 0; i < cnFields.length; i++) {
      sheet.setColumnWidth(i, maxWidth.get(i));
    }
  }

  /**
  * 转换属性值
  *
  * @param objValue    :
  * @param converterClazz :
  * @return java.lang.String
  **/
  private static <T> String convertFieldValue(String objValue, Class<Enum> converterClazz) throws Exception {
    if (ObjectUtil.isNotNull(converterClazz)) {
      //获取所有枚举实例
      Enum[] enumConstants = converterClazz.getEnumConstants();
      //根据方法名获取方法
      Method valueMethod = converterClazz.getMethod("getValue");
      if (ObjectUtil.isNull(valueMethod)) {
        throw new Exception(converterClazz.getSimpleName() + "类找不到getValue()方法");
      }
      Method descMethod = converterClazz.getMethod("getDescription");
      if (ObjectUtil.isNull(descMethod)) {
        throw new Exception(converterClazz.getSimpleName() + "类找不到getDescription()方法");
      }
      for (Enum enumFiled : enumConstants) {
        //执行枚举方法获得枚举实例对应的值
        Object value = valueMethod.invoke(enumFiled);
        Object description = descMethod.invoke(enumFiled);
        if (ObjectUtil.equal(objValue, value.toString())) {
          objValue = description.toString();
        }
      }
    }
    return objValue;
  }

}

3.自定义标签Export标签

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportEntityMap {
  String EnName() default "实体属性名称";

  String CnName() default "excel标题名称";
}
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportProperty {

  Class<? extends Enum> Converter();
}

枚举 

public enum SexEnum {

  /**
  * 男
  */
  MALE(1, "MALE", "男"),

  /**
  * 女
  */
  FEMALE(2, "FEMALE", "女");

  private int value;

  private String code;

  private String description;

  SexEnum(int value, String code, String description) {
    this.value = value;
    this.code = code;
    this.description = description;
  }

  public int getValue() {
    return value;
  }

  public String getDescription() {
    return description;
  }

  public String getCode() {
    return code;
  }
}

4. 实体类引入@ExportEntityMap标签

  /**
  * 性别
  */
  @ExportEntityMap(CnName = "性别", EnName = "sex")
  //通过@ExportProperty来转换枚举字段
  @ExportProperty(Converter = SexEnum.class)
  private Integer sex;
  /**
  * 年龄
  */
  @ExportEntityMap(CnName = "年龄", EnName = "age")
  private Integer age;

5.在Controller层中调用ExportExcelUtils方法即可

  @ApiOperation("xxxx")
  @PostMapping(value = "/xxxx")
  public void getExport(HttpServletResponse re编程客栈sponse, @RequestBody ListRo ro) {
    //导出数据
    ExportExcelUtils.export("name", list, EntityClass.class, response);
  }

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。