VUE3+elementui-plus实现简单的CURD上传以及下载工能

前端代码

Vue

<template>
   <div style="width: 88vh;display: flex;justify-content: space-between;margin: 10px auto;">
      <el-input v-model="name" placeholder="请输入商品名称" style="width: 200px;height: 32px;"  class="left_jianju"/>
      <el-button type="primary"  @click="goods()"  class="left_jianju">查询</el-button>
      <el-button type="warning"  @click="reset()"  class="left_jianju">重置</el-button>
      <el-button type="success" @click="handleAddUpdate()"  class="left_jianju">新增</el-button>
      <el-button type="info"    @click="batchDelete()"  class="left_jianju">批量删除</el-button>
      <el-button type="success"   @click="visibleUpload = true" class="left_jianju">导入</el-button>  

      <!--文件上传弹窗-->
    <el-dialog v-model="visibleUpload" :show-close="false" style="width: 60vh;margin: 15% auto;">
    <template #header="{ close, titleId, titleClass }">
      <div class="my-header">
        <h4 :id="titleId" :class="titleClass">文件上传</h4>
        <el-button type="danger" @click="close">
          <el-icon class="el-icon--left"><CircleCloseFilled /></el-icon>
          关闭
        </el-button>
      </div>
    </template>
    <el-upload
     class="left_jianju"
    :action="uploadUrl" 
    :before-upload="handleBeforeUpload"
    :on-success="handleSuccess"
    :on-error="handleError"
    accept=".xlsx, .xls">
      <template #trigger>
        <el-button type="primary">选择文件</el-button>
      </template>
      <template #default>
        <el-button type="success" style="margin-left: 10px;margin-top: -5px;" @click="submitUpload()">上传到服务器</el-button>
      </template>
   </el-upload>
  </el-dialog>

   <el-button  type="danger" @click="exportData()" class="left_jianju">导出</el-button>
   </div>
   <div>
    <el-table :data="state.tableData" style="width: 100%" @selection-change="handleSelectionChange">
      <el-table-column type="selection" width="55" />
    <el-table-column  prop="goodSid" label="编号" width="180" />  
     <el-table-column prop="goodType" label="品牌" />  
     <el-table-column prop="goodsSpname" label="类型" />
     <el-table-column prop="goodsName" label="商品名称" width="180" />
     <el-table-column prop="goodsPrice" label="价格" />
     <el-table-column label="状态">
       <template #default="scope">
         {{ scope.row.status === 1 ? '上架' : '下架' }}
       </template>
     </el-table-column>
     <el-table-column prop="creationDate" label="创建时间" />
     <el-table-column prop="modifyDate" label="修改时间" />                              
    <el-table-column label="操作">
      <template #default="scope">
        <el-button size="small" @click="handleEdit(scope.$index, scope.row)">
          修改
        </el-button>
        <el-button
          size="small"
          type="danger"
          @click="deleteConfirm(scope.row.goodSid)"
        >
          删除
        </el-button>
      </template>
    </el-table-column>
  </el-table>
  <div style="margin: 10px auto">
        <el-pagination
             @current-change="goods" 
              @size-change="goods" 
              v-model:current-page="pageNum"
            v-model:page-size="pageSize"
             background :page-sizes="[2, 5, 10, 20]"
            layout="total, sizes, prev, pager, next, jumper" 
            :total="total" 
           prev-text="上一页"
          next-text="下一页"
            style="text-align: center"
            small="mini"/>
    </div>
   </div>

   <!-- 新增弹窗 -->
   <el-dialog v-model="visible" :show-close="false">
    <template #header="{ close, titleId, titleClass }">
      <div class="my-header">
        <h4 :id="titleId" :class="titleClass">新增商品</h4>
        <el-button type="danger" @click="close">
          <el-icon class="el-icon--left"><CircleCloseFilled /></el-icon>
          关闭
        </el-button>
      </div>
    </template>
    <el-form
    style="max-width: 600px"
    :model="state.form"
    ref="ruleFormRef"
    status-icon
    :rules="rules"
    label-width="auto"
  >
   <el-form-item label="品牌" prop="goodType">
      <el-input  v-model="state.form.goodType" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="类型" prop="goodsSpname">
      <el-input  v-model="state.form.goodsSpname" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="商品名称" prop="goodsName">
      <el-input v-model="state.form.goodsName" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="价格" prop="goodsPrice">
      <el-input v-model="state.form.goodsPrice"  type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="状态" prop="status">
      <el-input  v-model="state.form.status" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item>
      <el-button type="primary" @click="submitForm(ruleFormRef)">提交</el-button>
      <el-button @click="resetForm(ruleFormRef)">重置</el-button>
    </el-form-item>
  </el-form>
  </el-dialog>




   <!-- 修改弹窗 -->
   <el-dialog v-model="dialogFormVisibleUpdate" :show-close="false">
    <template #header="{ close, titleId, titleClass }">
      <div class="my-header">
        <h4 :id="titleId" :class="titleClass">修改商品</h4>
        <el-button type="danger" @click="close">
          <el-icon class="el-icon--left"><CircleCloseFilled /></el-icon>关闭</el-button>
      </div>
    </template>
    <el-form
    style="max-width: 600px"
    :model="state.form"
    ref="ruleFormRef"
    status-icon
    :rules="rules"
    label-width="auto"
  >
   <el-form-item label="品牌" prop="goodType">
      <el-input  v-model="state.form.goodType" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="类型" prop="goodsSpname">
      <el-input  v-model="state.form.goodsSpname" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="商品名称" prop="goodsName">
      <el-input v-model="state.form.goodsName" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="价格" prop="goodsPrice">
      <el-input v-model="state.form.goodsPrice"  type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item label="状态" prop="status">
      <el-input  v-model="state.form.status" type="text" autocomplete="off" />
    </el-form-item>
    <el-form-item>
      <el-button type="primary" @click="saveUpdate(ruleFormRef)">提交</el-button>
      <el-button @click="resetForm(ruleFormRef)">重置</el-button>
    </el-form-item>
  </el-form>
  </el-dialog>
</template>

<script setup lang="ts">
import { nextTick, reactive, ref } from "vue";
import { CircleCloseFilled } from '@element-plus/icons-vue'
import type { FormInstance } from 'element-plus'
import { ElMessageBox, ElMessage, ElNotification } from 'element-plus'
import http from "@/http";
const name = ref('')
const pageNum = ref(1)
const pageSize = ref(10)
const total = ref(0)


//--------------------------------------------------查询部分--------------------------------------------------
// 定义表格数据，请求后台方法
const state = reactive({
    tableData: [],
    form: {
            goodsName: '',
            goodsPrice: '',
            goodType: '',
            status: '',
            goodsSpname: ''
    }
})
const goods = ()=>{//请求后台数据进行查询
    return http.get("/goods/listGoods", {
      params: {
        goodsName: name.value,
        pageNum: pageNum.value,
        pageSize: pageSize.value
      }
    }).then(res=>{
      // console.log(res.data.total)
      total.value = res.data.data.total
      state.tableData = res.data.data.records
    })
}
//调用查询方法
goods();



// 重置搜索框方法
const reset = () => {
    name.value = ''
    goods()
}

//--------------------------------------------------新增部分--------------------------------------------------
const visible = ref(false)//定义弹窗是否显示
//定义表单ref,用于提交表单,获取表单数据,相当于就是一个接口
const ruleFormRef = ref<FormInstance>()
// 新增弹窗方法
const handleAddUpdate = () => {
     visible.value = true
     //新增之前清空表单
        state.form = {
            goodsName: '',
            goodsPrice: '',
            goodType: '',
            status: '',
            goodsSpname: ''
        }
}
// 添加效验规则
const rules = reactive({
    goodsName: [
        { required: true, message: '请输入商品名称', trigger: 'blur' },
     ],
    goodsPrice: [
        { required: true, message: '请输入价格', trigger: 'blur' },
    ],
    goodType: [
        { required: true, message: '请输入品牌', trigger: 'blur' },
    ],
    status: [
        { required: true, message: '请输入状态', trigger: 'blur' },
    ],
    goodsSpname: [
        { required: true, message: '请输入名称', trigger: 'blur' },
    ],
})
// 提交表单方法
const submitForm = (formEl: FormInstance | undefined) => {//表单提交
  if (!formEl) return
  formEl.validate((valid) => {//表单校验
    if (valid) {
      // 表单校验成功，提交表单数据
      http.post("/goods/insertGoods", state.form).then(res => {
        if(res.data.code === 200){
          ElNotification.success({
            title: 'Success',
            message: '数据新增成功',
            offset: 100,
          })
          visible.value = false
          goods()
        } else {
          ElNotification.error({
            title: 'Error',
            message: '数据新增失败',
            offset: 100,
          })
        }
      })
    } else {
      console.log('error submit!')
    }
  })
}
// 重置表单方法
const resetForm = (formEl: FormInstance | undefined) => {
  if (!formEl) return
  formEl.resetFields()
}
//--------------------------------------------------修改部分--------------------------------------------------
// 修改弹窗
const dialogFormVisibleUpdate = ref(false)
// 修改触发方法
const handleEdit = (index: number,raw ) => {
    dialogFormVisibleUpdate.value = true
    nextTick(() => {//异步操作
        ruleFormRef.value.resetFields()//清空表单
        state.form = JSON.parse(JSON.stringify(raw))//深拷贝
    })
}
// 修改提交方法
const saveUpdate = (formEl: FormInstance | undefined ) => {  
    if (!formEl) return
       formEl.validate(valid => {    
        if (valid) {
          http.post('/goods/updateById', state.form).then(res => {
        if (res.data.code === 200) {
          ElNotification.success({
            title: 'Success',
            message: '数据修改成功',
            offset: 100,
          })
          dialogFormVisibleUpdate.value = false
          goods()
        } else {
          ElNotification.error({
            title: 'Error',
            message: '数据修改失败',
            offset: 100,
          })
        }
     })
   }
})

}
//--------------------------------------------------删除部分--------------------------------------------------
// 在删除前弹窗提示
const deleteConfirm = (id) => {
  ElMessageBox.confirm(
    '确定要删除吗？',
    '提示',
    {
      confirmButtonText: '确定',
      cancelButtonText: '取消',
      type: 'warning',
    }
  )
    .then(() => {
      //开始删除数据，请求后台
      http.get('/goods/deleteById', {params: {goodSid: id}}).then(res => {
        if (res.data.code === 200) {
          ElMessage({
          type: 'success',
          message: '删除成功',
         })
          goods()
        } else {
          ElMessage({
          type: 'error',
          message: '删除失败',
          })
        }
    })
    })
    .catch(() => {
      ElMessage({
        type: 'info',
        message: '取消删除',
      })
    })
}
//--------------------------------------------------批量删除部分--------------------------------------------------
// 批量删除
const multipleSelection = ref([])
const handleSelectionChange = (val) => {
    multipleSelection.value = val
}
//批量删除方法
const batchDelete = () => {
  if (!multipleSelection.value || !multipleSelection.value.length) {
        ElMessage.warning("请选择数据")
        return
    }
   console.log(multipleSelection.value.map(item => item.goodSid))
    http.post('/goods/deleteByIds', multipleSelection.value.map(item => item.goodSid)).then(res => {
      if (res.data.code === 200) {
        ElMessage.success('批量删除成功')
        goods()
      } else {
        ElMessage.error('批量删除失败')
      }
    })
}

//--------------------------------------------------文件导出--------------------------------------------------
// 导出接口
const exportData = () => {
  http.get('/goods/exportGoods', 
  { responseType: 'blob' ,
    params: {
      date: '2023-12-20'
    }
  }//设置响应类型为blob
) .then(res => {
      const url = window.URL.createObjectURL(new Blob([res.data]));
      const link = document.createElement('a');
      link.href = url;
      link.setAttribute('download', '数据下载.xlsx');
      document.body.appendChild(link);
      link.click();
    })
    .catch(error => {
      ElMessage.error('导出失败'+error);
    });
}


//--------------------------------------------------文件导入--------------------------------------------------


const uploadUrl = 'http://127.0.0.1/goods/upLoadExcel'//文件上传地址,由于这里使用的是axios上传，所以该地址暂时用不到，已经改成axios上传方式
const visibleUpload = ref(false)//文件上传弹窗
//上次的excel文件
const excelFile = reactive({
    fileList: null,
})

// 阻止默认上传行为，改成axios上传
const handleBeforeUpload = (file) => {
  excelFile.fileList = file
  // console.log("aaaaaaa", excelFile.fileList)
  return false
}

//开始上传文件
const submitUpload = () => {
  //文件上传前校验文件是否为空
  if (!excelFile.fileList) {
    ElMessage.error('请选择文件')
    return
  }
  //如果文件不为空，则开始上传文件
  const formData = new FormData();//创建formData对象
  formData.append('file', excelFile.fileList);//将文件添加到formData对象中
  //formData.append('name', 'customValue')//添加自定义字段
  http.post('/goods/upLoadExcel', formData).then(res => {
    if (res.data.code === 200) {
      ElMessage({
          type: 'success',
          message: res.data.data,
         })
         visibleUpload.value = false;
         goods();
    } else {
          ElMessage({
          type: 'error',
          message: res.data.data,
         })
     }
  })
}

// 成功回调
const handleSuccess = (response, file, fileList) => {
         ElMessage({
          type: 'success',
          message: '导入成功'+response.data.data,
         })
}
// 失败回调
const handleError = (error, file, fileList) => {
        ElMessage({
          type: 'error',
          message: '导入失败'+error.data.data,
         })
}

</script>
<style scoped>
.left_jianju{
    margin-right: 5px;
}

.my-header {
  display: flex;
  flex-direction: row;
  justify-content: space-between;
}
</style

后端代码

controller

package com.yuan.controller.goods;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.yuan.dto.R;
import com.yuan.enums.SuccessErrorEnum;
import com.yuan.interfaces.GooodsInterfaces;
import com.yuan.pojo.Goods;
import com.yuan.service.GoodsService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.Parameters;
import io.swagger.v3.oas.annotations.enums.ParameterIn;

import org.apache.commons.lang3.time.DateUtils;
import io.swagger.v3.oas.annotations.tags.Tag;

import io.swagger.v3.oas.annotations.media.Schema;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.ibatis.executor.BatchResult;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 查询的增删改查
 */
@RestController
@RequestMapping("/goods")
@Tag(name = "返回的goods类")
@CrossOrigin
public class GoodsController {
    private static final Logger log = LoggerFactory.getLogger(GoodsController.class);
    @Autowired
    private GoodsService goodsService;


    @GetMapping("/list")
    @Operation(summary = "测试")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    @Parameters({
            @Parameter(name = "id",description = "文件id",in = ParameterIn.PATH),
            @Parameter(name = "token",description = "请求token",required = true,in = ParameterIn.HEADER),
            @Parameter(name = "name",description = "文件名称",required = true,in=ParameterIn.QUERY)
    })
    public Object getName() {
        List<Goods> list = goodsService.list();
        System.out.println("返回的数据"+list);
        return list;
    }


    /**
     * 查询操作
     * @return
     */
    @GetMapping("/listGoods")
    @Operation(summary = " 查询数据")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    @Parameters({
            @Parameter(name = "goodsName",description = "商品名称",required = true,in=ParameterIn.QUERY),
            @Parameter(name = "pageNum",description = "起始页",required = true,in=ParameterIn.QUERY),
            @Parameter(name = "pageSize",description = "分页条数",required = true,in=ParameterIn.QUERY)
    })
    public Object getList(
                         @RequestParam(value = "goodsName",defaultValue = "",required = false) String goodsName,
                         @RequestParam(value = "pageNum",defaultValue = "1",required = false) Integer pageNum,
                        @RequestParam(value = "pageSize", defaultValue = "3", required = false) Integer pageSize) {

        IPage<Goods> goods = goodsService.getGoods(goodsName, pageNum, pageSize);
        System.out.println("返回的数据"+goods);
        return   R.ok(goods);
    }



    /**
     * 新增操作
     * @return
     */
    @RequestMapping("/insertGoods")
    @Operation(summary = "新增数据")
    @ApiOperationSupport(ignoreParameters = {"goods.goodSid"})
    public Object insert(@RequestBody Goods goods) {
        System.out.println("接收前端提交的数据"+goods);
        boolean save = goodsService.save(goods);
        if (save){
            return R.ok("成功");
        }else {
            return R.error("失败");
        }
    }

    /**
     * 删除操作
     * 这里使用的逻辑删除，并不是直接将数据从数据库中进行抹除，而是增加一个字段来表示删除
     * @param goodSid
     * @return
     */
    @RequestMapping("/deleteById")
    @Operation(summary = "根据id删除数据")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    @Parameters({
            @Parameter(name = "goodSid",description = "删除的id",required = true,in=ParameterIn.DEFAULT),
    })
    public Object deleteById(@RequestParam("goodSid") Integer goodSid) {
        //传统的QueryWrapper会导致驼峰命名失效问题，不建议使用
//        QueryWrapper<Goods> queryWrapper = new QueryWrapper<Goods>();
//        QueryWrapper<Goods> queryWrapper = new QueryWrapper<Goods>();
//        queryWrapper.eq("good_sid",goods.getGoodSid());
        log.info("删除的id"+goodSid);

        //这种Lambda表达式的条件构造器，可以有效避免无法自动转换驼峰命名的问题
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<Goods> getGoodsName = queryWrapper.eq(Goods::getGoodSid, goodSid);

        boolean remove = goodsService.remove(getGoodsName);
        if (remove){
            return R.ok(SuccessErrorEnum.SUCCESS);
        }else {
            return R.error(SuccessErrorEnum.ERROR);
        }
    }


    /**
     * 修改操作
     * @param goods
     * @return
     */
    @RequestMapping("/updateById")
    @Operation(summary = "根据id修改数据")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    @Parameters({
            @Parameter(name = "goods",description = "修改的实体类",required = true,in=ParameterIn.QUERY),
    })
    public Object update(@RequestBody Goods goods) {
//        QueryWrapper<Goods> queryWrapper = new QueryWrapper<Goods>();
//        queryWrapper.eq("good_sid",goods.getGoodSid());
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<Goods> getGoodsName = queryWrapper.eq(Goods::getGoodSid, goods.getGoodSid());
        boolean update = goodsService.update(goods, getGoodsName);
        if (update){
            return R.ok(SuccessErrorEnum.SUCCESS);
        }else {
            return R.error(SuccessErrorEnum.ERROR);
        }
    }




    /**
     * 批量删除操作
     * 这里使用的逻辑删除，并不是直接将数据从数据库中进行抹除，而是增加一个字段来表示删除
     * @param goodSids
     * @return
     */
    @RequestMapping(value = "/deleteByIds",method = RequestMethod.POST)
    @Operation(summary = "根据id删除数据")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    @Parameters({
            @Parameter(name = "goodSids",description = "删除的id",required = true,in=ParameterIn.DEFAULT),
    })
    public Object deleteByIds(@RequestBody Integer[] goodSids) {
        for (Integer goodSid : goodSids) {
            log.info("删除的id"+goodSid);
        }
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<Goods> getGoodsName = queryWrapper.in(Goods::getGoodSid, goodSids);//由于是数组类型这里就用IN表示
        boolean remove = goodsService.remove(getGoodsName);
        if (remove){
            return R.ok(SuccessErrorEnum.SUCCESS);
        }else {
            return R.error(SuccessErrorEnum.ERROR);
        }
    }


    /**
     * 文件上传
     */
    @PostMapping(value = "/upLoadExcel")
    @Operation(summary = "文件上传，这里只能只是POST请求方式其他请求方式无效")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    public Object upLoadExcel(@RequestBody MultipartFile file) throws Exception {//这里使用的单文件上传,多文件改成数组即可
        log.info("接收前端上传的文件"+file);
        String name = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();//获取的表格转成文件流的形式
        //1、创建一个工作簿
        Workbook workbook =null;
        if(name.endsWith(".xlsx")) {
            workbook= new XSSFWorkbook(inputStream);
        }else if (name.endsWith(".xls")){
            workbook= new HSSFWorkbook(inputStream);
        }else {
         return  R.error("文件上传格式不正确，只能是.xlsx或者是.xls格式");
        }
        List<BatchResult> goodsUpLoad = goodsService.getGoodsUpLoad(workbook);
        if (!goodsUpLoad.isEmpty()) {
            return R.ok("数据上传成功");
        } else {
            return R.error("上传失败，请检查表格格式是否正确");
        }
    }


    /**
     * 文件导出
     */
    @RequestMapping(value = "/exportGoods")
    @Operation(summary = "文件上传，这里只能只是POST请求方式其他请求方式无效")
    @ApiOperationSupport(author = "yuan")//添加接口作者
    public Object exportGoods(@RequestParam("date") String date,HttpServletResponse response) throws Exception {//这里使用的单文件上传,多文件改成数组即可
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dateTime = sdf.parse(date);
        log.info("时间"+dateTime);

        /**
         *    SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，
         *    SXSSFWorkbook专门处理大数据，对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。
         *    它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming
         *     版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到
         *     硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不
         *     可访问的。只有还保存在内存里的才可以被访问到。
         *     注:HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)
         *     是1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据，
         *     甚至上千条数据就可以考虑使用SXSSFWorkbook了。
         */
        try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
            //创建一个SXSSFSheet 这里使用SXSSFSheet可以调整列宽
            SXSSFSheet sheet = workbook.createSheet();
            //1.获取表头字段
            List<String> listTitle = new ArrayList<>();
//            listTitle.add("年月");
//            listTitle.add("厂部类型");
//            listTitle.add("环保设施名称");
//            listTitle.add("环保设施类型");
//            listTitle.add("介质名称");
//            listTitle.add("数值");
//            String goodSid = getChineseNameByFieldName(Goods.class, "goodSid");
//            System.out.println("字段goodSid的中文名为：" + goodSid);
                Goods goodsPojo = new Goods();
                // 使用反射获取所有字段
                Field[] fields = goodsPojo.getClass().getDeclaredFields();
                // 循环字段并打印中文名称
                for (Field field : fields) {
                    String chineseName = getChineseNameByField( field.getName());
                    log.info("获取实体类的字段名称{}", chineseName);
                    if (null!=chineseName){
                        listTitle.add(chineseName);//将通过反射获取的表头字段存储在List集合中
                    }
                  }
              //2.写入表头
              Row rowTitle = sheet.createRow(0);
            //3.写入表头
            sheet.trackAllColumnsForAutoSizing();//自动调整列宽设置
            for (int i = 0; i < listTitle.size(); i++) {
                sheet.autoSizeColumn(i);//自动调整列宽
                sheet.setColumnWidth(i, sheet.getColumnWidth(0) * 13 / 10);//设置宽度
                Cell cell = rowTitle.createCell(i);
                cell.setCellValue(listTitle.get(i));
             }

           //4.开始获取数据
            List<Goods> goods = goodsService.exportGoods(dateTime);
            List<Map<String, Object>> resultGoods=new ArrayList<>();
            Map<String, Object> map1=  new LinkedHashMap<>();//这里使用LinkedHashMap用于保存数据，他是一个有序集合
            map1.put("goodSid","");//由于第一例是空的，所以给第一列随便添加一个空的即可，或者是跳过第一列进行新增
            resultGoods.add(map1);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //重新对返回的数据进行封装

            List<String> goodsKey = new ArrayList<>();
            goodsKey.add("goodsPrice");//这个是设置成数值类型的字段

            for (Goods goods1 : goods) {
                Map<String, Object> maps=  new LinkedHashMap<>();
                maps.put("goodSid",goods1.getGoodSid());
                maps.put("goodsName",goods1.getGoodsName());
                maps.put("goodsPrice",goods1.getGoodsPrice());
                maps.put("goodsType",goods1.getGoodType());
                maps.put("status",goods1.getStatus().toString().equals("1")?"上架":"下架");
                maps.put("goodsSpname",goods1.getGoodsSpname());
                maps.put("creationDate",simpleDateFormat.format(goods1.getCreationDate()));
                maps.put("modifyDate",simpleDateFormat.format(goods1.getModifyDate()));
                resultGoods.add(maps);
            }
            //开始下载数据
            downloadFile("数据下载", response, workbook, sheet, resultGoods,goodsKey);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }


    /**
     * 通过反射获取自定义的名称
     * @param clazz
     * @param fieldName
     * @return
     * @throws NoSuchFieldException
     */
    public static String getChineseNameByFieldName(Class<?> clazz, String fieldName) throws NoSuchFieldException {
        Field field = clazz.getDeclaredField(fieldName);
        GooodsInterfaces annotation = field.getAnnotation(GooodsInterfaces.class);
        if (annotation != null) {
            return annotation.value();
        }
        return null;
    }
    // 根据实体类字段名称获取中文名称
    public static String getChineseNameByField(String PojoName) throws NoSuchFieldException {
            return getChineseNameByFieldName(Goods.class, PojoName);
    }
    /**
     * 文件下载公共方法
     * @param type
     * @param response
     * @param workbook
     * @param sheet
     * @param resultEp
     * @throws UnsupportedEncodingException
     */
    private void downloadFile(@RequestParam String type, HttpServletResponse response, SXSSFWorkbook workbook, Sheet sheet, List<Map<String, Object>> data,List<String>  key) throws UnsupportedEncodingException {
        // 创建样式和格式化
        CreationHelper createHelper = workbook.getCreationHelper();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        for (int rowNum = 1; rowNum < data.size(); rowNum++) {
            //创建一个行
            Row row = sheet.createRow(rowNum);
            int count = 0;
            for (Map.Entry<String, Object> entry : data.get(rowNum).entrySet()) {
                SXSSFCell cell = (SXSSFCell) row.createCell(count);
                cell.setCellValue(entry.getValue() == null ? "" : entry.getValue().toString());
                //判断是是否和传的参数一致
                int  keys= key.indexOf(entry.getKey());//判断数据中是否包含传入的字段0代表存在1代表不存在
                if (keys!=-1){//如果不等于-1则是要转换的数据
                    setNumberFormat(workbook, cellStyle, cell, entry.getValue());
                }else {  //否则就是String类型
                    cell.setCellValue(entry.getValue()==null?"":entry.getValue().toString());
                }
                count++;
            }
        }
        response.setContentType("APPLICATION/OCTET-STREAM");
        String fileNameTo = URLEncoder.encode(type + ".xlsx", "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileNameTo);
        try {
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     *  设置Excel单元格为数值类型便于公式运算
     *
     * @param workbook
     * @param contextstyle
     * @param cell
     * @param data
     */
    public static void setNumberFormat(SXSSFWorkbook workbook, CellStyle contextstyle, SXSSFCell cell, Object data){
        boolean isNum = false;//data是否为数值型
        boolean isInteger=false;//data是否为整数
        boolean isPercent=false;//data是否为百分数
        if (data != null || "".equals(data)) {
            //判断data是否为数值型
            isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
            //判断data是否为整数（小数部分是否为0）
            isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
            //判断data是否为百分数（是否包含“%”）
            isPercent=data.toString().contains("%");
        }
        //如果单元格内容是数值类型，设置data的类型为数值类型
        if (isNum && !isPercent) {
            // 此处设置数据格式
            DataFormat df = workbook.createDataFormat();
            if (isInteger) {
                contextstyle.setDataFormat(df.getFormat("0_ "));//数据格式只显示整数
            }else{
                contextstyle.setDataFormat(df.getFormat("0.00_ "));//保留两位小数点
            }
            // 设置单元格格式
            cell.setCellStyle(contextstyle);
            // 设置单元格内容为double类型
            cell.setCellValue(Double.parseDouble(data.toString()));
        } else {
            if (data != null && !"".equals(data)) {
                cell.setCellStyle(contextstyle);
                // 设置单元格内容为字符型
                cell.setCellValue(data.toString());
            }
        }
    }
}

service

package com.yuan.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.yuan.pojo.Goods;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.executor.BatchResult;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.Date;
import java.util.List;


public interface GoodsService extends IService<Goods> {


    /**
     *
     * @param name 名称
     * @param pageNum 起始页
     * @param pageSize 分页条数
     * @return
     */
    IPage<Goods> getGoods(@Param("name") String name , @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);


    /**
     * 文件上传接口
     *
     * @param inputStream 文件流
     * @param name        文件名称
     * @return
     */
    List<BatchResult> getGoodsUpLoad(Workbook workbook) throws Exception;



    /**
     * 文件下载接口
     *
     * @param inputStream 文件流
     * @param name        文件名称
     * @return
     */
    List<Goods> exportGoods(Date date) throws Exception;


}

package com.yuan.service.impl;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.ibatis.executor.BatchResult;
import org.apache.poi.ss.usermodel.*;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuan.mapper.GoodsMapper;
import com.yuan.pojo.Goods;
import com.yuan.service.GoodsService;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestParam;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;

@Service
@Transactional
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements GoodsService {

    @Autowired
    private GoodsMapper goodsMapper;


    @Override
    public IPage<Goods> getGoods(String name, Integer pageNum, Integer pageSize) {
        //放弃传统的xml写法使用最纯代码写sql
     //  return goodsMapper.getGoods(name, pageNum, pageSize);
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<Goods> getGoodsName = queryWrapper.like(Goods::getGoodsName, name).orderByDesc(Goods::getCreationDate);
        Page<Goods> page = new Page<>(pageNum,pageSize, true);//传分页参数
        IPage<Goods> goodsIPage = goodsMapper.selectPage(page, getGoodsName);//进行分页
        return goodsIPage;
    }

    /**
     * 文件上传方法
     *
     * @return
     */
    @Override
    public List<BatchResult> getGoodsUpLoad(Workbook workbook) throws Exception{
        //获取第一个Sheet
        Sheet sheet = workbook.getSheetAt(0);
        //获取表格的标题
        Row rowTitle = sheet.getRow(0);//获取最上面一行也就是表头/标题的内容
        if (rowTitle!=null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();//获取表格中所有的列
            for (int countNum = 0; countNum <cellCount ; countNum++) {
                Cell cell = rowTitle.getCell(countNum);//获取表头的数据
                if (cell != null) {
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + " | ");
                }
            }
            System.out.println();
        }
        //方式 1：
        //1.开始获取表格数据
        List<Goods> goodsList = new ArrayList<>();//定义一个集合类存储数据
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) { // 跳过表头
                continue;
            }
            Goods goods = new Goods();//对实体类进行赋值
            //DataFormatter用于格式化单元格数据
            goods.setGoodsName(new DataFormatter().formatCellValue(row.getCell(0)));
            goods.setGoodsPrice(Integer.valueOf(new DataFormatter().formatCellValue(row.getCell(1))));
            goods.setGoodType(new DataFormatter().formatCellValue(row.getCell(2)));
            goods.setStatus(Integer.valueOf(new DataFormatter().formatCellValue(row.getCell(3))));
            goods.setGoodsSpname(new DataFormatter().formatCellValue(row.getCell(4)));
            goodsList.add(goods);
        }
        //关闭资源
        workbook.close();
        //打印输出
        goodsList.forEach(System.out::println);
        System.out.println("条件一"+goodsList.stream().map(Goods::getGoodsName).toList());
        // 2.表格资源获取完毕，在新增之前判断之前的数据是否存在如果存在则删除数据，这里使用 Lambda表达式的条件构造器 LambdaQueryWrapper
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
                 queryWrapper.in(Goods::getGoodsName, goodsList.stream().map(Goods::getGoodsName).toList()) // 第一个条件
                .and(wrapper->wrapper.in(Goods::getGoodType, goodsList.stream().map(Goods::getGoodType).toList()))//第二个条件
                .and(wrapper->wrapper.in(Goods::getStatus,goodsList.stream().map(Goods::getStatus).toList()))//第三个条件
                .and(wrapper->wrapper.in(Goods::getGoodsSpname,goodsList.stream().map(Goods::getGoodsSpname).toList()));//第四个条件
        // 设置查询条件，在新增之前判断之前的数据是否已经存在
        //上传之前根据表格存在的数据删除一遍，或者是查询出存在的数据在进行删除,这里也是使用逻辑删除并没有使用物理删除，
        goodsMapper.delete(queryWrapper);

        //方式 2：
        //获取表格的数据
//        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); //获取所有的行数据
//        for (int rowNum = 1; rowNum <physicalNumberOfRows ; rowNum++) {//跳过第一行的数据从第二行开始
//            Row rowData = sheet.getRow(rowNum);
//            if (rowData!=null){
//                //读取列的数据
//                int cellCount=  rowTitle.getPhysicalNumberOfCells();
//                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//                    Cell cell = rowData.getCell(cellNum);
//                    //匹配列的数据类型
//                    if (cell!=null){
//                        CellType cellType = cell.getCellType();
//                        //判断单元格类型
//                        String cellValue="";
//                        switch (cellType){
//                            case CellType.STRING://字符串
//                                cellValue=String.valueOf(cell.getStringCellValue());
//                                break;
//                            case CellType.BOOLEAN://布尔
//                                cellValue = String.valueOf(cell.getBooleanCellValue());
//                                break;
//                            case CellType.BLANK://空
//                                break;
//                            case CellType.NUMERIC://数字
//                                    cell.setCellType(CellType.STRING);//转换为字符串输出
//                                    cellValue = cell.toString();
//                                break;
//                            case CellType.ERROR:
//                                System.out.print("【数据类型错误】");
//                                break;
//                        }
//                        System.out.print(cellValue+" | ");
//                    }
//                }
//                System.out.println();
//            }
//        }
        //3.数据删除后开始新增数据
            return goodsMapper.insert(goodsList);
    }

    /**
     * 文件下载接口
     * @param date 导出的时间
     * @return
     * @throws Exception
     */
    @Override
    public List<Goods> exportGoods(Date date) throws Exception {
        //根据时间查询要导出的字段,导出的时间大于某一个区间
        LambdaQueryWrapper<Goods> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.ge(Goods::getCreationDate, date);
        List<Goods> goods = goodsMapper.selectList(queryWrapper);
        return goods;
    }
}