ant design pro 上传excel示例

ant design pro 上传 excel 示例

前言
- TS
- java
- model
- 前端展示

前言

大家好！最近在学习使用 ant des i gn pro ,本人是java后端,没有专业的学习过react,都是自己看文档一步一步弄出来的,有什么不对的或者更加高效的方式,可以在评论区留下您宝贵的意见,共同学习进步,谢谢大家。

TS

import React, {useRef, useState} from 'react';
import {Button, message, Modal} from 'antd';
import {ModalForm, ProFormUploadDragger} from '@ant-design/pro-form';
import ProTable, {ActionType, ProColumns} from "@ant-design/pro-table";
import {getCoImportGoodsList, deleteImportGoods} from "@/services/oms/goods";
import {PlusOutlined} from "@ant-design/icons/lib";
import {PageContainer} from "@ant-design/pro-layout";
import {getCoNo} from "@/services/oms/common";


const ImportGoodsView: React.FC = () => {
  const actionRef = useRef<ActionType>();
  const [uploadExcel, handleUploadExcel] = useState<boolean>(false);
  const [coNoList, setCoNoList] = useState<API.Select[]>([]);//所有公司
  const [file, setFile] = useState<any>();
  /**
   * 删除导入商品
   */
  const deleteGoods=async (row: API.ckBase) => {
    const hide = message.loading('删除中...');
    try {
     await deleteImportGoods({
        // @ts-ignore
        id: row?.id
      });
        hide();
        message.success("删除成功");
        // @ts-ignore
        actionRef.current.reload();
        return true;
    } catch (error) {
      hide();
      message.error('删除失败，请重试！');
      return false;
    }
  };
  //按钮的oClick事件调用此函数
  const showDeleteConfirm = async (params?: API.ImportGoodsListItem) => {
    Modal.confirm({
      title: '提示:',
      content: '确认移除当前商品吗?',
      closable: true,
      onOk: () => {
        // @ts-ignore
        deleteGoods(params)
      }
    });
  };

  const columns: ProColumns<API.ImportGoodsListItem>[] = [
    {
      title: 'ID',
      dataIndex: 'id',
      width: 180,
      // fixed: 'left',
      hideInTable: true, //在表格中隐藏
      tooltip: '唯一主键', //提示信息
      hideInSearch: true, //在搜索框中隐藏
      sorter: (a, b) => a.id - b.id,
    },
    {
      title: '所属公司',
      ellipsis: true,
      width: 180,
      dataIndex: 'coNo',
      valueType: 'select',
      editable: false,
      valueEnum: () => {
        const options = {};
        coNoList.forEach((item) => {
          options[item.value] = item.label
        });
        return options;
      },
    },
    {
      title: '商品编号',
      dataIndex: 'code',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '一级品类',
      dataIndex: 'categoryOne',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '二级品类',
      dataIndex: 'categoryTwo',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '商品名称',
      dataIndex: 'goodsName',
      width: 180,
    },
    {
      title: '商品规格',
      dataIndex: 'goodsSpec',
      width: 180,
    },
    {
      title: '销售单位①',
      dataIndex: 'saleUnit',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '销售单位②',
      dataIndex: 'saleUnitTwo',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    }, {
      title: '转换值',
      dataIndex: 'conversionValue',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '销售价',
      dataIndex: 'price',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '税率',
      dataIndex: 'taxRate',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '供应商',
      dataIndex: 'gysName',
      width: 180,
    },
    {
      title: '采购方式',
      dataIndex: 'procurementMethod',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '⑥⑨码',
      dataIndex: 'barCode',
      width: 180,
      hideInSearch: true, //在搜索框中隐藏
    },
    {
      title: '操作',
      width: 180,
      key: 'option',
      valueType: 'option',
      render: (text, record) => [
        <Button
          type="primary"
          key="delete"
          onClick={() => {
            showDeleteConfirm(record)
          }}
        >
          移除
        </Button>,
      ],
    },
  ];
  return (
    <PageContainer>
      <ProTable<API.GoodsListItem, API.PageParams>
        columns={columns}
        actionRef={actionRef}
        rowKey="id"
        search={{
          labelWidth: 120,
        }}
        scroll={{x: 1200, y: 500}}
        options={false}
        toolBarRender={() => [
          <Button type="primary" key="primary" onClick={() => {
            handleUploadExcel(true);
          }}><PlusOutlined/> 导入excel </Button>,
        ]}
        // @ts-ignore
        request={getCoImportGoodsList} //调用接口
        headerTitle="已导入商品列表"
        onLoad={() => {
          getCoNo().then(value => {//获取公司数据
            // @ts-ignore
            setCoNoList(value)
          });
        }}
      />
      <ModalForm
        title="导入Excel"
        visible={uploadExcel}//updateModalVisible为true时打开悬浮窗
        modalProps={{//引用ModalProps里面组件
          destroyOnClose:true,//取消时刷新弹窗值
          onCancel: () => {handleUploadExcel(false)},//点击取消生效
        }}
        submitter={{
          // 配置按钮文本
          searchConfig: {
            resetText: '重置',
            submitText: '导入商品',
          },
          resetButtonProps: {
            style: {
              display: 'none',
            },
          },
          submitButtonProps: {},
        }}
        onFinish={async () => {
          if (file == '') {
            message.error("请选择你要导入的商品档案文件")
          } else {
            const formData = new FormData();
            formData.append('file', file);
            fetch('/v5/coGoodsInfo/importGoods', {
              method: 'POST',
              // @ts-ignore
              headers: {
                'x-xianghaoxi-authtoken-ssl': window.sessionStorage.getItem('TOKEN'),
              },
              body: formData,
            })
              .then(res => res.json())
              .then(data => {
                if (data.result) {
                  message.success(data.msg);
                  handleUploadExcel(false);
                  if (actionRef.current) {
                    actionRef.current.reload();
                  }
                } else {
                  message.error(data.msg);
                }

              })
          }
        }}
      >
        <ProFormUploadDragger
          max={1}
          title="单击或拖动商品档案文件到此区域进行导入"
          description={<div>仅支持excel文件</div>}
          name="file"
          fieldProps={{
            beforeUpload(info: any) {
              setFile(info);
              return false;
            },
            onChange(info: any) {
              if (info.file.status == 'removed') { //移除文件时；
                setFile('');
              }
            },
          }}
        />
      </ModalForm>
    </PageContainer>
  );
};
export default ImportGoodsView;

java

 /**
     * 导入商品
     */
    @AuthTag
    @SSLTag
    @ApiOperation(value = "[SSL]导入商品", notes = "导入商品", response = BooleanResultModel.class)
    @RequestMapping(value = "/importGoods", method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<?> importGoods(
            @RequestParam(value = "file") MultipartFile file
    ) throws IOException {
       BooleanResultModel resultModel=new BooleanResultModel();
        InputStream is = file.getInputStream();
        String coNo = AuthManager.getCurrentAuthData().getCoNo();
        try {
            Workbook wb = new XSSFWorkbook(is);
            if(StringUtils.isEmpty(coNo)){
                resultModel.setResult(false);
                resultModel.setMsg("不能使用平台账号导入商品");
            }else {
                resultModel= coImportGoodsBiz.importGoods(wb, coNo);
            }
        } catch (Exception e) {
            resultModel.setResult(false);
            resultModel.setMsg("文件格式错误,请导入正确格式的文件");
        }
        return  new ResponseEntity<>(resultModel, HttpStatus.OK);
    }

  /**
     * 导入excel到 co_import_goods表
     *
     * @param wb
     * @param coNo
     * @return
     * @throws Exception
     */
    public BooleanResultModel importGoods(Workbook wb, String coNo) throws Exception {
        BooleanResultModel resultModel = new BooleanResultModel();
        int xmlTitleIndex = 0;
        Class<?> clz = Class.forName("com.sunyuki.ec.agms.base.model.co.CoImportGoodsModel");
        // 查询 title 的 fiedlName
        Field[] fields = clz.getDeclaredFields();
        Map map = new HashMap();
        for (Field field : fields) {
            boolean fieldHasAnno = field.isAnnotationPresent(CoImportGoodsModel.ExcelTitle.class);
            if (fieldHasAnno) {
                CoImportGoodsModel.ExcelTitle fieldAnno = field
                        .getAnnotation(CoImportGoodsModel.ExcelTitle.class);
                // 输出注解属性
                String value = fieldAnno.value();
                map.put(value, new CoImportGoodsBiz.ExcelTileIndexTable(-1, value, field.getName()));
            }
        }

        List<CoImportGoodsModel> sheetList = new ArrayList<CoImportGoodsModel>();
        int sheetSize = wb.getNumberOfSheets();
        for (int i = 0; i < sheetSize; i++) {// 遍历sheet页
            Sheet sheet = wb.getSheetAt(i);
            int rowSize = sheet.getLastRowNum() + 1;
            for (int j = 0; j < rowSize; j++) {// 遍历行
                Row row = sheet.getRow(j);
                if (row == null) {// 略过空行
                    continue;
                }
                int cellSize = row.getLastCellNum();// 行中有多少个单元格，也就是有多少列
                if (j == xmlTitleIndex) {// 根据 title 获取 exlce里面的 index
                    List<String> rowList = new ArrayList<String>();// 对应一个数据行
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        String value = null;
                        if (cell != null) {
                            value = cell.toString();
                            CoImportGoodsBiz.ExcelTileIndexTable excelTileIndexTable = (CoImportGoodsBiz.ExcelTileIndexTable) map.get(value);
                            if (excelTileIndexTable != null &amp;&amp; excelTileIndexTable.getIndex() == -1) {
                                excelTileIndexTable.setIndex(k);
                            }
                        }
                        rowList.add(value);
                    }
                    // 根据获取的 title 、fieldName、index
                } else if (j > xmlTitleIndex) {
                    // 获取excel里面对应的数据
                    CoImportGoodsModel templateDtlModel = new CoImportGoodsModel();
                    // 设置共有属性

                    Class model = templateDtlModel.getClass();
                    for (Object k : map.keySet()) {
                        CoImportGoodsBiz.ExcelTileIndexTable et = (CoImportGoodsBiz.ExcelTileIndexTable) map.get(k);
                        Field f = model.getDeclaredField(et.getFileName());
                        f.setAccessible(true);
                        Cell cell = null;
                        cell = row.getCell(et.getIndex());
                        String value = null;
                        if (cell != null) {
                             // 日期特殊处理
                            if (cell.getCellTypeEnum()== CellType.NUMERIC &amp;&amp; HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date d = cell.getDateCellValue();
                                value = "";
                                // 科学计数法特殊处理
                            } else if ("mobile".equals(f.getName())) {
                                DecimalFormat df = new DecimalFormat("0");
                                value = df.format(cell.getNumericCellValue());
                            } else if ("productNumber".equals(f.getName())) {
                                if (cell.getCellTypeEnum()== CellType.NUMERIC) {
                                    DecimalFormat df = new DecimalFormat("0");
                                    value = df.format(cell.getNumericCellValue());
                                } else {
                                    value = cell.getStringCellValue();
                                }
                            } else {
                                value = cell.toString();
                            }
                            String type = f.getType().toString();// 得到此属性的类型
                            if (type.endsWith("int") || type.endsWith("Integer")) {

                                Integer v = new Double(value).intValue();
                                f.set(templateDtlModel, v); // 给属性设值
                            } else if (type.endsWith("BigDecimal")) {
                                if (value == null || value == "") {
                                    String j1 = "";
                                }
                                f.set(templateDtlModel, new BigDecimal(value));
                            } else {
                                f.set(templateDtlModel, value);
                            }
                        }

                    }


                    sheetList.add(templateDtlModel);
                }
            }
        }
        String goodsName = "";
        boolean result = true;
        for (CoImportGoodsModel coImportGoodsModel : sheetList) {
            if (StringUtils.isEmpty(coImportGoodsModel.getCostPrice())) {
                coImportGoodsModel.setCostPrice("0");
            }
            if (StringUtils.isEmpty(coImportGoodsModel.getPrice())) {
                coImportGoodsModel.setPrice("0");
            }
            if(StringUtils.isEmpty(coImportGoodsModel.getGoodsSpec())){
                coImportGoodsModel.setGoodsSpec("无");
            }
            if (StringUtils.isEmpty(coImportGoodsModel.getGoodsName())
                    || StringUtils.isEmpty(coImportGoodsModel.getCategoryOne())
                    || StringUtils.isEmpty(coImportGoodsModel.getSaleUnit())
                    || StringUtils.isEmpty(coImportGoodsModel.getGysName())
                    || StringUtils.isEmpty(coImportGoodsModel.getProcurementMethod())
            ) {
                result = false;
            }

        }

        if (result) {
            resultModel.setResult(true);
            for (CoImportGoodsModel coImportGoodsModel : sheetList) {
                CoImportGoodsModel coImportGoodsModel1 = coImportGoodsDao.getCoImportGoodsModelByCoNoAndGoodsNameAndGoodsSpec(coNo, coImportGoodsModel.getGoodsName(), coImportGoodsModel.getGoodsSpec());
                if (coImportGoodsModel1 != null) {
                    goodsName += coImportGoodsModel.getGoodsName() + ",";
                } else {
                    coImportGoodsModel.setCoNo(coNo);
                    coImportGoodsDao.insert(coImportGoodsModel);
                }
            }
            if (!StringUtils.isEmpty(goodsName)) {
                resultModel.setMsg("导入部分商品," + goodsName + "等商品已经存在无须重复导入");
            } else {
                resultModel.setMsg("导入所有商品成功");
            }
        } else {
            resultModel.setResult(false);
            resultModel.setMsg("导入失败," +
                    "请检查导入的excel中是否存在商品名称、一级品类、销售单位、供应商、采购方式为空");
        }


        return resultModel;
    }

model

 package com.sunyuki.ec.agms.base.model.co;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


/**
 * @author xxx
 * @title: CoImportGoodsModel
 * @projectName public-client-site
 * @description: 导入商品实体
 * @date 2021/11/1214:42
 */
public class CoImportGoodsModel {

    private Integer id;//主键ID
    private String coNo;//所属公司
    private String coName;
    @ExcelTitle("商品编号")
    private String code;//商品编号
    @ExcelTitle("一级品类")
    private String categoryOne;//一级品类
    @ExcelTitle("二级品类")
    private String categoryTwo;//二级品类
    @ExcelTitle("商品名称")
    private String goodsName;//商品名称
    @ExcelTitle("规格")
    private String goodsSpec;//商品规格
    @ExcelTitle("销售单位")
    private String saleUnit;//销售单位
    @ExcelTitle("二级单位")
    private String saleUnitTwo;//二级销售单位
    @ExcelTitle("转换值")
    private String conversionValue;//转换值
    @ExcelTitle("销售价")
    private String price;//价格
    @ExcelTitle("成本价")
    private String costPrice;//成本
    @ExcelTitle("税率")
    private String taxRate;//税率
    @ExcelTitle("供应商")
    private String gysName;//供应商
    @ExcelTitle("采购方式")
    private String procurementMethod;//采购方式
    @ExcelTitle("69码")
    private String barCode;//69码
    /**
     * excel title名称
     */
    @Target({ElementType.METHOD, ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ExcelTitle {
        String value() default "";
    }
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCoNo() {
        return coNo;
    }

    public void setCoNo(String coNo) {
        this.coNo = coNo;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getCategoryOne() {
        return categoryOne;
    }

    public void setCategoryOne(String categoryOne) {
        this.categoryOne = categoryOne;
    }

    public String getCategoryTwo() {
        return categoryTwo;
    }

    public void setCategoryTwo(String categoryTwo) {
        this.categoryTwo = categoryTwo;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public String getGoodsSpec() {
        return goodsSpec;
    }

    public void setGoodsSpec(String goodsSpec) {
        this.goodsSpec = goodsSpec;
    }

    public String getSaleUnit() {
        return saleUnit;
    }

    public void setSaleUnit(String saleUnit) {
        this.saleUnit = saleUnit;
    }

    public String getSaleUnitTwo() {
        return saleUnitTwo;
    }

    public void setSaleUnitTwo(String saleUnitTwo) {
        this.saleUnitTwo = saleUnitTwo;
    }

    public String getConversionValue() {
        return conversionValue;
    }

    public void setConversionValue(String conversionValue) {
        this.conversionValue = conversionValue;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getCostPrice() {
        return costPrice;
    }

    public void setCostPrice(String costPrice) {
        this.costPrice = costPrice;
    }

    public String getTaxRate() {
        return taxRate;
    }

    public void setTaxRate(String taxRate) {
        this.taxRate = taxRate;
    }

    public String getGysName() {
        return gysName;
    }

    public void setGysName(String gysName) {
        this.gysName = gysName;
    }

    public String getProcurementMethod() {
        return procurementMethod;
    }

    public void setProcurementMethod(String procurementMethod) {
        this.procurementMethod = procurementMethod;
    }

    public String getCoName() {
        return coName;
    }

    public void setCoName(String coName) {
        this.coName = coName;
    }

    public String getBarCode() {
        return barCode;
    }

    public void setBarCode(String barCode) {
        this.barCode = barCode;
    }
}