vue+ts+java用excel文件导入数据

vue+ts+java用excel文件导入数据

<template>
  <el-dialog v-model="dialogVisible" :title="`${parameter.title}`" :destroy-on-close="true" width="580px" draggable>
    <el-form class="drawer-multiColumn-form" label-width="100px">
      <el-form-item label="模板下载 :">
        <el-button type="primary" :icon="Download" @click="downloadFile">点击下载</el-button>
      </el-form-item>
      <el-form-item label="文件上传 :">
        <el-upload
          ref="uploadFile"
          drag
          :auto-upload="false"
          :limit="excelLimit"
          :on-change="handleChange"
          accept=".xls,.xlsx"
          :data="uploadForm.data"
          :multiple="false"
          :show-file-list="true"
          :before-upload="beforeExcelUpload"
          :on-exceed="handleExceed"
          :on-success="excelUploadSuccess"
          :on-error="excelUploadError"
        >
          <slot name="empty">
            <el-icon class="el-icon--upload"><upload-filled /></el-icon>
            <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
          </slot>
          <template #tip>
            <slot name="tip">
              <div class="el-upload__tip">请上传 .xls , .xlsx 标准格式文件，文件最大为 {{ parameter.fileSize }}MB</div>
            </slot>
          </template>
        </el-upload>
      </el-form-item>
    </el-form>
    <template #footer>
      <span class="dialog-footer">
        <el-button @click="dialogVisible = false">取消</el-button>
        <el-button type="primary" @click="uploadExcel">确认导入</el-button>
      </span>
    </template>
  </el-dialog>
</template>

<script setup lang="ts" name="ImportExcel">
import { ref, reactive } from "vue";
import { ElNotification, ElMessage, UploadFile, UploadFiles, UploadRawFile } from "element-plus";
import { importExcl, exportUserInfo } from "./connect";
import { Download } from "@element-plus/icons-vue";
import axios from "axios";
import { useUserStore } from "@/stores/modules/user";
import { saveAsPbtxt } from "@/utils";
export interface ExcelParameterProps {
  title: string; // 标题
  fileSize?: number; // 上传文件的大小
  fileType?: File.ExcelMimeType[]; // 上传文件的类型
  getTableList?: () => void; // 获取表格数据的Api
}

// 上传文件数组
const fileList = ref();
// 最大文件上传数
const excelLimit = ref(1);
// dialog状态
const dialogVisible = ref(false);
// 父组件传过来的参数
const parameter = ref<ExcelParameterProps>({
  title: "",
  fileSize: 20,
  fileType: ["application/vnd.ms-excel", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]
});

// 接收父组件参数
const acceptParams = (params: ExcelParameterProps) => {
  parameter.value = params;
  dialogVisible.value = true;
  fileList.value = "";
};

// 文件改变时回调钩子
const handleChange = (uploadFile: UploadFile, uploadFiles: UploadFiles) => {
  // file是当前上传的文件，files是当前所有的文件，
  // 不懂得话可以打印一下这两个数据 就能明白
  console.log(uploadFiles);
  fileList.value = uploadFile;
};

const uploadForm = reactive({
  data: {
    name: "",
    type: ""
  }
});
//模板导出
const downloadFile = async () => {
  exportUserInfo({ fileUrl: "花木险种承保管理导入模版.xls" }).then((res: any) => {
    console.log(res);
    const fileName = "花木险种承保管理导入模版.xls";
    const blob = res;
    let dom = document.createElement("a");
    let url = window.URL.createObjectURL(blob);
    dom.href = url;
    dom.download = decodeURI(fileName);
    dom.style.display = "none";
    document.body.appendChild(dom);
    dom.click();
    dom.parentNode?.removeChild(dom);
    window.URL.revokeObjectURL(url);
  });
};

// 文件上传
const uploadExcel = async () => {
  if (fileList.value == "" || fileList.value == undefined || fileList.value == null) {
    return ElMessage.error("请上传文件！");
  }
  const jsonStr = JSON.stringify(uploadForm.data);
  const blob = new Blob([jsonStr], {
    type: "application/json"
  });
  let formData = new FormData();
  formData.append("obj", blob);
  // 这里很重要 file.value.raw才是真实的file文件，file.value只是一个Proxy代理对象
  formData.append("file", fileList.value.raw);
  // 这里是发送请求，注意 headers: {'Content-Type': 'multipart/form-data'}
  const userStore = useUserStore();
  let url = importExcl;
  let method = "post";
  let headers = {
    Accept: "application/json",
    "Content-Type": "multipart/form-data",
    Authorization: "Bearer " + userStore.token
  };
  axios({
    method,
    url,
    data: formData,
    headers
  }).then(res => {
    console.log(res);
    if (res.data.resp_code == 0) {
      ElMessage.success("导入成功！");
      dialogVisible.value = false;
    } else {
      ElMessage.error("导入失败");
      saveAsPbtxt("导入失败错误信息.txt", res.data.resp_msg);
    }
    parameter.value.getTableList!();
  });
};
/**
 * @description 文件上传之前判断
 * @param file 上传的文件
 * */
const beforeExcelUpload = (file: UploadRawFile) => {
  const isExcel = parameter.value.fileType!.includes(file.type as File.ExcelMimeType);
  const fileSize = file.size / 1024 / 1024 < parameter.value.fileSize!;
  if (!isExcel)
    ElNotification({
      title: "温馨提示",
      message: "上传文件只能是 xls / xlsx 格式！",
      type: "warning"
    });
  if (!fileSize)
    setTimeout(() => {
      ElNotification({
        title: "温馨提示",
        message: `上传文件大小不能超过 ${parameter.value.fileSize}MB！`,
        type: "warning"
      });
    }, 0);
  return isExcel && fileSize;
};

// 文件数超出提示
const handleExceed = () => {
  ElNotification({
    title: "温馨提示",
    message: "最多只能上传一个文件！",
    type: "warning"
  });
};

// 上传错误提示
const excelUploadError = () => {
  ElNotification({
    title: "温馨提示",
    message: `批量添加${parameter.value.title}失败，请您重新上传！`,
    type: "error"
  });
};

// 上传成功提示
const excelUploadSuccess = () => {
  ElNotification({
    title: "温馨提示",
    message: `批量添加${parameter.value.title}成功！`,
    type: "success"
  });
};

defineExpose({
  acceptParams
});
</script>
<style>
.upload {
  width: 80%;
}
.el-dialog__footer {
  text-align: center;
}
.el-icon-upload {
  font-size: 2px; /*图标的尺寸大小*/
  margin: 4px 0 2px; /*图标与四周元素的间距*/
}
</style>

/**
 * @name 分户清单文件上传
 */
export const importExcl2 = LOGIN_URL + `pdwy-zsnybx/InsureSplitdetail/import`;

3.controller，model，service，impl

 /**
     * 数据导入
     */
    @Log(name = "承保管理_花木险种承保管理",type = "数据导入")
    @PostMapping(value = "/import")
    public Result importExcl(@RequestPart("file") MultipartFile file){
        return flowersProductService.importExcl(file);
    }


======================
Result importExcl(MultipartFile file);
======================
 @Override
    public Result importExcl(MultipartFile file) {
        try {


            SysUser user = LoginUserContextHolder.getUser();

            List<FlowersProductImport> flowersProductImportList = new ArrayList<>();
            Date date = new Date();
            String username = LoginUserContextHolder.getUser().getUsername();//当前系统用户名id

            ExcekImportUtils<FlowersProductImport> ExcekImportUtils = new ExcekImportUtils<FlowersProductImport>();
            Map<String, Object> map = ExcekImportUtils.importExcel(file, FlowersProductImport.class);

            flowersProductImportList = (List<FlowersProductImport>) map.get("dist");//excel导入的承保数据集合
            StringBuilder errmsg = (StringBuilder) map.get("errmsg");//错误信息

            for (FlowersProductImport i : flowersProductImportList) {
                if (StringUtils.isEmpty(i.getBdh())) {
                    errmsg.append("保单号不可为空,\n");
                } else {
                    Integer num = flowersProductMapper.bdhSelectflowers(i.getBdh());//根据保单号查询承保信息管理
                    if (num > 0) {
                        errmsg.append("保单号为" + i.getBdh() + "的承保信息已存在,\n");
                    } else {
                        String zhen = i.getZhen();
                        if (!StringUtils.isEmpty(zhen)) {
                            String qhdm = jcQhdmsMapper.qhdmSelectName(zhen);//根据镇名查询区划代码
                            if (qhdm != null) {
                                i.setQhdm(qhdm);
                            } else {
                                errmsg.append("保单号为" + i.getBdh() + "的镇区名称有误,\n");
                            }
                        } else {
                            errmsg.append("保单号为" + i.getBdh() + "的镇区名称不可为空,\n");
                        }

//                        String insurancetypeName = i.getInsurancetypeName();//险种名称
//                        if (!StringUtils.isEmpty(insurancetypeName)) {
//                            String xzcode = jcsjInsurancetypeMapper.codeSelectName(insurancetypeName);//根据险种名称查询险种代码
//                            if (xzcode != null) {
//                                i.setInsurancetypeCode(xzcode);
//                            } else {
//                                errmsg.append("保单号为" + i.getBdh() + "的险种名称有误,");
//                            }
//                        } else {
//                            errmsg.append("保单号为" + i.getBdh() + "的险种名称不可为空,\n");
//                        }

                        if (StringUtils.isEmpty(i.getTbr())) {
                            errmsg.append("保单号为" + i.getBdh() + "的投保人不可为空,\n");
                        }
                     /*   if (StringUtils.isEmpty(i.getTbpz())) {
                            errmsg.append("保单号为" + i.getBdh() + "的投保品种不可为空,\n");
                        }*/

                       /* if (i.getZbf() == null) {
                            errmsg.append("保单号为" + i.getBdh() + "的总保费不可为空,\n");
                        }*/

                        if (i.getBxqq() == null) {
                            errmsg.append("保单号为" + i.getBdh() + "的起保日期不可为空,\n");
                        }
                        if (i.getBxzq() == null) {
                            errmsg.append("保单号为" + i.getBdh() + "的终保日期不可为空,\n");
                        }


                        if (StringUtils.isEmpty(i.getCp())) {
                            errmsg.append("保单号为" + i.getBdh() + "的产品不可为空,\n");
                        }
                        if (i.getHszbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的含税总保费不可为空,\n");
                        }
                        if (i.getZjbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的总净保费不可为空,\n");
                        }
                        if (i.getDzbtbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的地市补贴保费不可为空,\n");
                        }
                        if (i.getXjbtbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的县级补贴保费不可为空,\n");
                        }
                        if (i.getNhzjbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的农户自交保费不可为空,\n");
                        }
                        if (StringUtils.isEmpty(i.getTbhs())) {
                            errmsg.append("保单号为" + i.getBdh() + "的投保户数不可为空,\n");
                        }
                        if (StringUtils.isEmpty(i.getZbxsl())) {
                            errmsg.append("保单号为" + i.getBdh() + "的总保险数量不可为空,\n");
                        }
                        if (StringUtils.isEmpty(i.getLmzldd())) {
                            errmsg.append("保单号为" + i.getBdh() + "的林木坐落地点不可为空,\n");
                        }
                        if (StringUtils.isEmpty(i.getCdyf())) {
                            errmsg.append("保单号为" + i.getBdh() + "的出单月份不可为空,\n");
                        }
                        if (i.getRbbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的人保保费不可为空,\n");
                        }
                        if (i.getTbbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的太保保费不可为空,\n");
                        }
                        if (i.getGscbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的国寿财保费不可为空,\n");
                        }
                        if (i.getPabf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的平安保费不可为空,\n");
                        }
                        if (i.getDdbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的大地保费不可为空,\n");
                        }
                        if (i.getZyczbtje()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的中央财政不可为空,\n");
                        }
                        if (i.getSjbtbf()==null) {
                            errmsg.append("保单号为" + i.getBdh() + "的省级财政保费不可为空,\n");
                        }
                        i.setSfzf(0);
                        i.setSftj("0");
                        i.setCreatedTime(date);
                        i.setCreatedBy(username);
                        i.setUpdatedTime(date);
                        i.setUpdatedBy(username);
                        i.setOrganCode(user.getOrganCode());
                    }
                }
            }

            if (StringUtils.isEmpty(errmsg)) {
                for(int i=0; i< flowersProductImportList.size(); i+= 500) {
                    int endSize = Math.min(i + 500, flowersProductImportList.size());
                    flowersProductMapper.batchSaveAquaticProduct(flowersProductImportList.subList(i, endSize));
                }
                return Result.succeed("导入成功");
            } else {
                return Result.failed("导入失败原因：\n" + errmsg);
            }
        } catch (Exception e) {
            return Result.failed(e.getMessage());
        }
    }

4.ExcekImportUtils工具类

package com.pdwy.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导入EXCEL导入工具, 产出指定pojo 列表
 */
public class ExcekImportUtils<T> {

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

    public ExcekImportUtils() {
    }

    /**
     * 解析excel文档
     */
    public List<T> importExcel(File file, Class<T> clazz) {
        List<T> dist = new ArrayList<T>();
        try {
            Field filed[] = clazz.getDeclaredFields();
            // 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
            Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
            // 循环读取所有字段
            for (int i = 0; i < filed.length; i++) {
                Field f = filed[i];
                ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
                if (exa != null) {
                    // 构造设置了Annotation的字段的Setter方法
                    String fieldname = f.getName();
                    String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
                    // 构造调用的method，
                    Method setMethod = clazz.getMethod(setMethodName, new Class[]{f.getType()});
                    String pattern = exa.pattern();
                    // 将这个method以Annotaion的名字为key来存入。
                    fieldmap.put(exa.exportName(), new Object[]{setMethod, pattern});
                }
            }

            FileInputStream in = new FileInputStream(file);
            Workbook workbook = WorkbookFactory.create(in);
            Sheet sheet = workbook.getSheetAt(0);  //示意访问sheet
//			XSSFWorkbook xwb = new XSSFWorkbook(in);
//			XSSFSheet sheet = xwb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();
            Row title = rows.next(); // 取得标题头行
            Iterator<Cell> cellTitle = title.cellIterator(); // 得到第一行的所有列
            Map<Integer, String> titlemap = new HashMap<Integer, String>();// 将标题的文字内容放入到一个map中。
            // 循环标题所有的列
            for (int i = 0; cellTitle.hasNext(); i++) {
                Cell cell = cellTitle.next();
                String value = cell.getStringCellValue();
                titlemap.put(i, value);
            }
            // 解析内容行
            while (rows.hasNext()) {
                Row rown = rows.next();
                T tObject = clazz.newInstance(); // 行的所有列
                // 遍历一行的列
                for (int j = 0; j < rown.getLastCellNum(); j++) {
                    Cell cell = rown.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String titleString = (String) titlemap.get(j);// 这里得到此列的对应的标题
                    // 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
                    if (fieldmap.containsKey(titleString)) {
                        Method setMethod = (Method) fieldmap.get(titleString)[0];
                        Type[] ts = setMethod.getGenericParameterTypes();// 得到setter方法的参数
                        String xclass = ts[0].toString(); // 只要一个参数
                        // 判断参数类型
                        if (xclass.equals("class java.lang.String")) {
                            setMethod.invoke(tObject, this.getCellValue(cell));
                        } else if (xclass.equals("class java.util.Date")) {
                            setMethod.invoke(tObject, formatter.parse(this.getCellValue(cell)));
                        } else if (xclass.equals("class java.lang.Boolean")) {
                            Boolean boolname = true;
                            if (this.getCellValue(cell).equals("否")) {
                                boolname = false;
                            }
                            setMethod.invoke(tObject, boolname);
                        } else if (xclass.equals("class java.lang.Integer")) {
                            setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
                        } else if (xclass.equals("class java.lang.Long")) {
                            setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
                        } else if (xclass.equals("double")) {
                            setMethod.invoke(tObject, new Double(this.getCellValue(cell)));
                        }
                    }
                }
                dist.add(tObject);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return null;
        }
        return dist;
    }

    SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd");

    public boolean isDateValid(String date) {

        formatter2.setLenient(false);
        try {
            formatter2.parse(date);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    /**
     * 解析excel文档
     */
    public Map<String,Object> importExcel(MultipartFile multipartFile, Class<T> clazz) {
        List<T> dist = new ArrayList<T>();
        StringBuilder errmsg=new StringBuilder();
        try {
            Field filed[] = clazz.getDeclaredFields();
            // 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
            Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
            // 循环读取所有字段
            for (int i = 0; i < filed.length; i++) {
                Field f = filed[i];
                ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
                if (exa != null) {
                    // 构造设置了Annotation的字段的Setter方法
                    String fieldname = f.getName();
                    String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
                    // 构造调用的method，
                    Method setMethod = clazz.getMethod(setMethodName, new Class[]{f.getType()});
                    String pattern = exa.pattern();
                    // 将这个method以Annotaion的名字为key来存入。
                    fieldmap.put(exa.exportName(), new Object[]{setMethod, pattern});
                }
            }

            Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);  //示意访问sheet
            sheet.getSheetName();
//			XSSFWorkbook xwb = new XSSFWorkbook(in);
//			XSSFSheet sheet = xwb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();
            Row title = rows.next(); // 取得标题头行
            Iterator<Cell> cellTitle = title.cellIterator(); // 得到第一行的所有列
            Map<Integer, String> titlemap = new HashMap<Integer, String>();// 将标题的文字内容放入到一个map中。
            // 循环标题所有的列
            for (int i = 0; cellTitle.hasNext(); i++) {
                Cell cell = cellTitle.next();
                String value = cell.getStringCellValue().trim();
                titlemap.put(i, value);
            }
            // 解析内容行

            int index = 0;
            while (rows.hasNext()) {
                if(index == 123)
                    logger.info("找到你了");
                Row rown = rows.next();
                int rowNum = rown.getRowNum()+1;
                T tObject = clazz.newInstance(); // 行的所有列
                // 遍历一行的列
                for (int j = 0; j < rown.getLastCellNum(); j++) {
                    Cell cell = rown.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String titleString = (String) titlemap.get(j);// 这里得到此列的对应的标题
                    int column = j + 1;//当前列数
                    // 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
                    if (fieldmap.containsKey(titleString)) {
                        Method setMethod = (Method) fieldmap.get(titleString)[0];
                        Type[] ts = setMethod.getGenericParameterTypes();// 得到setter方法的参数
                        String xclass = ts[0].toString(); // 只要一个参数
                        // 判断参数类型
                        if (xclass.equals("class java.lang.String")) {
                            try {
                                setMethod.invoke(tObject, this.getCellValue(cell));
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        } else if (xclass.equals("class java.util.Date")) {
                            String dataString = cell.toString();
                            try {
                                boolean dateValid = isDateValid(dataString);//正规日期格式
                                Date date;
                                if (dateValid) {
                                    date = formatter2.parse(dataString);
                                } else {
                                    date = formatter.parse(dataString);
                                }
                                setMethod.invoke(tObject, date);
                            } catch (ParseException e) {
                                e.printStackTrace();
                                errmsg.append("第" + rowNum + "行第" + column + "列值为" + "（" + dataString + "）的数据格式错误,\n");
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        } else if (xclass.equals("class java.lang.Boolean")) {
                            Boolean boolname = true;
                            if (this.getCellValue(cell).equals("否")) {
                                boolname = false;
                            }
                            try {
                                setMethod.invoke(tObject, boolname);
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        } else if (xclass.equals("class java.lang.Integer")) {
                            try {
                                setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
                            } catch (NumberFormatException e) {
                                e.printStackTrace();
                                errmsg.append("第" + rowNum + "行第" + column + "列值为" + "（" + this.getCellValue(cell) + "）的数据格式错误,\n");
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        } else if (xclass.equals("class java.lang.Long")) {
                            try {
                                setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
                            } catch (NumberFormatException e) {
                                e.printStackTrace();
                                errmsg.append("第" + rowNum + "行第" + column + "列值为" + "（" + this.getCellValue(cell) + "）的数据格式错误,\n");
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        } else if (xclass.equals("class java.math.BigDecimal")) {
                            try {
                                setMethod.invoke(tObject, new BigDecimal(this.getCellValue(cell)));
                            } catch (NumberFormatException e) {
                                e.printStackTrace();
                                errmsg.append("第" + rowNum + "行第" + column + "列值为" + "（" + this.getCellValue(cell) + "）的数据格式错误,\n");
                            } catch (NullPointerException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                dist.add(tObject);
                index++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            return null;
        }
        Map<String,Object> map=new HashMap<>();
        map.put("dist",dist);
        map.put("errmsg",errmsg);
        return map;
    }

    public String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }
}

5.需要的方法

void batchSaveAquaticProduct(@Param("list") List<FlowersProductImport> flowersProductImportList);
================
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.pdwy.insureAccept.mapper.FlowersProductMapper">
    <insert id="batchSaveAquaticProduct">
        INSERT INTO flowers_product
        (bdh, pdh, zhen, cun, qhdm, tbr, bbxr, sfzh, sjh, insurancetype_code, insurancetype_name, tbpz, tbpzdm, tbmj,
        be,zbf, bxqq, bxzq, organ_code, sfzf, zfsj,created_by, created_time, updated_by, updated_time,bdfyj, fjcl, sftj,
        xl, cp, hszbf, zjbf, dzbtbf, xjbtbf, nhzjbf, tbhs, zbxsl, lmzldd, cdyf, rbbf, tbbf, gscbf,pabf,ddbf, zyczbtje)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.bdh},#{item.pdh},#{item.zhen},#{item.cun},
            #{item.qhdm},#{item.tbr},#{item.bbxr},
            #{item.sfzh},#{item.sjh},
            #{item.insurancetypeCode},#{item.insurancetypeName},#{item.tbpz},
            #{item.tbpzdm},#{item.tbmj},#{item.be},
            #{item.zbf},#{item.bxqq},#{item.bxzq},
            #{item.organCode},#{item.sfzf}, #{item.zfsj},
            #{item.createdBy}, #{item.createdTime},#{item.updatedBy},#{item.updatedTime},
            #{item.bdfyj},#{item.fjcl},#{item.sftj},
            #{item.xl},#{item.cp},#{item.hszbf},
            #{item.zjbf},#{item.dzbtbf},#{item.xjbtbf},
            #{item.nhzjbf},#{item.tbhs},#{item.zbxsl},
            #{item.lmzldd},#{item.cdyf},#{item.rbbf},
            #{item.tbbf},#{item.gscbf},
            #{item.pabf},#{item.ddbf},#{item.zyczbtje}
            )
        </foreach>

    </insert>

   
</mapper>

实体加注解@ExcelAnnotation(exportName = “保单号”, order = 1)

package com.pdwy.utils;

import java.lang.annotation.*;

@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {

    /** 与excel标题头对应 */
    public String exportName();

    /** 转换格式, 如时间类型 yyyy-MM-dd HH:mm:ss */
    public String pattern() default "";

    /** 在excel中位置 */
    public int order() default 0;

    /** 对应字典名称,wutiancai **/
    public String dictName() default "";

    /** 列宽,wutiancai **/
    public int columnWidth() default 0;

}