前后端分离下EasyExcel的使用(前后端分离的项目怎么运行)

  本篇文章为你整理了前后端分离下EasyExcel的使用(前后端分离的项目怎么运行)的详细内容,包含有前后端分离 remember me 前后端分离的项目怎么运行 前后端分离如何提交表单 前后端分离数据格式 前后端分离下EasyExcel的使用,希望能帮助你了解 前后端分离下EasyExcel的使用。

  @ExcelProperty:value属性可用来设置表头名称
 

  @ExcelPropertyvalue属性可用来设置表头名称

  点击查看代码

  

@TableName(value = "five_insurances")

 

  @Data

  public class FiveInsurances implements Serializable {

   @ExcelProperty("编号")

   @ColumnWidth(10)

   @TableId(value = "id", type = IdType.AUTO)

   private Integer id;

   * 工号

   @ExcelProperty("工号")

   @ColumnWidth(15)

   @TableField(value = "number")

   private String number;

   * 姓名

   @ExcelProperty("姓名")

   @ColumnWidth(20)

   @TableField(value = "name")

   private String name;

   * 部门id

   @ExcelProperty("部门")

   @ColumnWidth(20)

   @TableField(value = "dept_id")

   private Integer deptId;

   * 电话

   @ExcelProperty("电话")

   @ColumnWidth(20)

   @TableField(value = "phone")

   private String phone;

   * 缴纳基数

   @ExcelProperty({"社保", "缴纳基数"})

   @ColumnWidth(20)

   @TableField(value = "base_payment")

   private String basePayment;

   * 个人缴纳

   @ExcelProperty({"社保", "个人", "缴纳费用"})

   @ColumnWidth(20)

   @TableField(value = "self_payment")

   private String selfPayment;

   * 工伤保险缴纳比例

   @ExcelProperty({"社保", "企业", "工伤保险缴纳比例"})

   @ColumnWidth(20)

   @TableField(value = "ratio")

   private String ratio;

   * 企业缴纳

   @ExcelProperty({"社保", "企业", "缴纳费用"})

   @ColumnWidth(20)

   @TableField(value = "com_payment")

   private String comPayment;

   * 备注

   @ExcelProperty({"社保", "备注"})

   @ColumnWidth(30)

   @TableField(value = "remarks")

   private String remarks;

   @ExcelIgnore

   @TableField(exist = false)

   private Dept dept;

   @ExcelIgnore

   @TableField(exist = false)

   private static final long serialVersionUID = 1L;

  

 

  导出Excel

  Controller

  

@GetMapping("/fihf")

 

  @ApiOperation(value = "导出五险一金列表Excel")

  @ApiImplicitParams(

   @ApiImplicitParam(dataType = "Interger",name = "page",value = "page==-1:查询所有;page==-2,返回空模板",required = false)

  public void exportList(HttpServletResponse response, @RequestParam(value = "page", defaultValue = "1") Integer page) throws Exception {

   PageBean List FiveInsurances pageBean = fiveInsurancesService.selectFiveInsurancesList(page);

   ExcelUtils.exportToWeb(response,"sheet1",FiveInsurances.class,pageBean.getData());

  

 

  前端Axios请求

  

exportFile(page = this.pageBean.current) {

 

   //复选框选中则设置page为-1,表示导出全部

   if (this.checked) {

   page = -1;

   //关闭对话框

   this.dialogVisible = false;

   this.axios({

   method: get,

   url: baseURL + "fileExport/fihf",

   params: {

   page: page,

   responseType: blob //响应类型须设置为二进制文件流

   }).then((res) = {

   if (!res) {

   return

   const link = document.createElement("a");//创建a标签

   let blob = new Blob([res.data], { type: "multipart/form-data" }); //设置文件类型

   link.style.display = "none";

   let url = URL.createObjectURL(blob);

   link.href = url; //给a标签href属性赋值

   link.setAttribute("download", decodeURI(Date.now() + .xlsx));

   document.body.appendChild(link);//挂载a标签

   link.click();//a标签click事件

   document.body.removeChild(link); //移除a标签

   window.URL.revokeObjectURL(url); //销毁下载链接

   console.log(res);

   this.checked = false;

   return this.$message.success("导出报表数据成功!")

  

 

  导入Excel

  Controller

  

@PostMapping("/fihf")

 

  @ApiOperation(value = "导入五险一金列表Excel")

  public ResultVO FiveInsurances FiveInsurances(@RequestPart("file") MultipartFile file) throws IOException {

   if (file.getSize() 0) {

   return ResultVO.createFail(404, "导入数据失败");

   List FiveInsurances list = ExcelUtils.importFile(file, FiveInsurances.class);

   //持久化到数据库

   int i = fiveInsurancesService.insertBatch(list);

   if (i 0) {

   return ResultVO.createSuccess("读取excel成功", null);

   } else {

   return ResultVO.createFail();

  

 

  前端Axios请求

  

handleBeforUpload(file) {

 

   console.log("beforeUpload", file);

   //创建文件附件

   let formData = new FormData();

   //添加到formdata

   formData.append("file", file);

   this.axios({

   method: post,

   url: baseURL + "fileImport/fihf",

   data: formData,

   Headers: {

   "Content-Type": "multipart/form-data",

   }).then((res) = {

   console.log("读取excel", res);

   if (res.data.data == 10000) {

   this.$message.success(res.data.msg)

  

 

  Excel导入导出工具类

  点击查看代码

  

package com.self.salarymanagement.utils;

 

  import com.alibaba.excel.EasyExcel;

  import com.alibaba.excel.support.ExcelTypeEnum;

  import lombok.extern.slf4j.Slf4j;

  import org.apache.poi.util.IOUtils;

  import org.springframework.web.multipart.MultipartFile;

  import javax.servlet.ServletOutputStream;

  import javax.servlet.http.HttpServletResponse;

  import java.io.File;

  import java.io.FileInputStream;

  import java.io.IOException;

  import java.io.UnsupportedEncodingException;

  import java.net.URLEncoder;

  import java.util.List;

   * Excel工具类

  @Slf4j

  public class ExcelUtils {

   * 导出Excel到指定路径下

   * @param path 路径

   * @param excelName Excel名称

   * @param sheetName sheet页名称

   * @param clazz Excel要转换的类型

   * @param data 要导出的数据

   public static void exportFileToLocal(String path, String excelName, String sheetName, Class clazz, List data) {

   String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());

   EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);

   * 导出Excel到web

   * @param response 响应

   * @param sheetName sheet页名称

   * @param clazz Excel要转换的类型

   * @param data 要导出的数据

   * @throws Exception

   public static void exportToWeb(HttpServletResponse response, String sheetName, Class clazz, List data) throws Exception {

  // response.setContentType("application/vnd.ms-excel");

   response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

   response.setCharacterEncoding("utf-8");

   // 这里URLEncoder .encode可以防止中文乱码

   String excelName = URLEncoder.encode(String.valueOf(System.currentTimeMillis()), "UTF-8");

   response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

   EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(data);

   * 导出Excel到web

   * @param response 响应

   * @param excelName Excel名称

   * @param sheetName sheet页名称

   * @param clazz Excel要转换的类型

   * @param data 要导出的数据

   * @throws Exception

   public static void exportToWeb(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {

  // response.setContentType("application/vnd.ms-excel");

   response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

   response.setCharacterEncoding("utf-8");

   // 这里URLEncoder.encode可以防止中文乱码

   excelName = URLEncoder.encode(excelName, "UTF-8");

   response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

   EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);

   * 将指定位置指定名称的Excel导出到web

   * @param response 响应

   * @param path 文件路径

   * @param excelName 文件名称

   * @throws UnsupportedEncodingException

   public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {

   File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));

   if (!file.exists()) {

   return "文件不存在!";

   response.setContentType("application/vnd.ms-excel");

   response.setCharacterEncoding("utf-8");

   // 这里URLEncoder.encode可以防止中文乱码

   excelName = URLEncoder.encode(excelName, "UTF-8");

   response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

   try (

   FileInputStream in = new FileInputStream(file);

   ServletOutputStream out = response.getOutputStream();

   IOUtils.copy(in, out);

   return "导出成功!";

   } catch (Exception e) {

   log.error("导出文件异常:", e);

   return "导出失败!";

   public static T List T importFile(MultipartFile file, Class T clazz) throws IOException {

   return EasyExcel.read(file.getInputStream())

   .head(clazz)

   .registerReadListener(new DefaultExcelListener T ())

   .sheet()

   .doReadSync();

  

 

  Excel导入监听器

  

import com.alibaba.excel.context.AnalysisContext;

 

  import com.alibaba.excel.event.AnalysisEventListener;

  import com.alibaba.excel.exception.ExcelDataConvertException;

  import lombok.extern.slf4j.Slf4j;

  import java.util.ArrayList;

  import java.util.List;

   * @author Liu

   * @create 2023-03-08-11:44

   * @description:

  @Slf4j

  public class DefaultExcelListener T extends AnalysisEventListener T {

   private final List T rows = new ArrayList ();

   * 读取excel操作

   * @param obj 数据

   * @param analysisContext 上下文

   // 每读一样,会调用该invoke方法一次

   @Override

   public void invoke(T obj, AnalysisContext analysisContext) {

   //添加到list

   rows.add(obj);

   log.info("list容量" + rows.size() + obj);

   /** 数据量不是特别大,可以不需要打开

   // 实际数据量比较大时,rows里的数据可以存到一定量之后进行批量处理(比如存到数据库),

   // 然后清空列表,以防止内存占用过多造成OOM

   if(rows.size() = 500){

   log.info("存入数据库ing");

   try {

   Thread.sleep(3000);

   } catch (InterruptedException e) {

   e.printStackTrace();

   rows.clear();

   * 读取玩excel后的操作

   @Override

   public void doAfterAllAnalysed(AnalysisContext analysisContext) {

   log.info("成功读取【" + rows.size() + "】条数据");

   System.out.println("================================");

   rows.forEach(System.out::println);

   System.out.println("================================");

   * 在读取excel异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。

   @Override

   public void onException(Exception exception, AnalysisContext context) {

   log.error("解析失败,但是继续解析下一行:{}", exception.getMessage());

   if (exception instanceof ExcelDataConvertException) {

   ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;

   log.error("第{}行,第{}列解析异常,数据为:{}", excelDataConvertException.getRowIndex(),

   excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());

   * @return 返回读取的总数据

   public List T getRows() {

   return rows;

  

 

  多级表头设置

  嵌套el-table-column即可

  

 el-table-column label="社保" width="180" 

 

   el-table-column label="缴纳基数" width="120"

   template slot-scope="scope"

   span {{ scope.row.basePayment }} /span

   /template

   /el-table-column

   el-table-column label="个人" width="120"

   el-table-column label="缴纳费用" width="120"

   template slot-scope="scope"

   span {{ scope.row.selfPayment }} /span

   /template

   /el-table-column

   /el-table-column

   el-table-column label="企业" width="180"

   el-table-column label="工伤保险缴纳比例" width="180"

   template slot-scope="scope"

   span {{ scope.row.ratio }} /span

   /template

   /el-table-column

   el-table-column label="缴纳费用" width="180"

   template slot-scope="scope"

   span {{ scope.row.comPayment }} /span

   /template

   /el-table-column

   /el-table-column

   el-table-column label="备注" width="120"

   template slot-scope="scope"

   span {{ scope.row.remarks }} /span

   /template

   /el-table-column

   /el-table-column

  

 

  以上就是前后端分离下EasyExcel的使用(前后端分离的项目怎么运行)的详细内容,想要了解更多 前后端分离下EasyExcel的使用的内容,请持续关注盛行IT软件开发工作室。

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: