【JAVA】Excel导入

这里是我写的excel导入，前端是基于vue2，发布主要是用于参考!
注意：后端里包含了一些项目业务

后端
RechargeController.java

    public void excelImportRecharge(){
        UploadFile file = getFile("file", "/excel/");
        renderJson(crmReceivablesRechargeService.excelImportRecharge(file));
    }

    /**
     * @author  dzc
     * 获取认款充值导入模板
     */
    public void downloadExcel(){
        List<String> recordList=new ArrayList<>();
        recordList.add("流水号(*)");
        recordList.add("总账户(*)");
        recordList.add("客户账户(*)");
        recordList.add("转账金额(*)");
        recordList.add("支付方式(*)");
        recordList.add("备注(*)");
        recordList.add("加款时间(*)");
        recordList.add("台账类型(*)");
        recordList.add("客户id(*)");
        recordList.add("客户名(*)");

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("认款充值导入表");
        sheet.setDefaultRowHeight((short)400);
        CellStyle textStyle = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        for (int i = 0; i < recordList.size(); i++) {
            sheet.setDefaultColumnStyle(i,textStyle);
            sheet.setColumnWidth(i,20*256);
        }
        HSSFRow titleRow = sheet.createRow(0);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short)16);
        cellStyle.setFont(font);
        titleRow.createCell(0).setCellValue("认款充值导入模板(*)为必填项");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleRow.getCell(0).setCellStyle(cellStyle);
        CellRangeAddress region = new CellRangeAddress(0,0 , 0, recordList.size()-1);
        sheet.addMergedRegion(region);
        List<String> roleList= Db.query("select role_name from 72crm_admin_role where role_type!=5 and is_hidden=1");
        try {
            HSSFRow row = sheet.createRow(1);
            for (int i = 0; i < recordList.size(); i++) {
                String[] setting = null;
                String titleName=recordList.get(i);
                HSSFCell cell = row.createCell(i);

                if ("角色".equals(titleName)) {
                    setting =roleList.toArray(new String[roleList.size()]);
                    cell.setCellValue(titleName + "(*)");
                }else{
                    cell.setCellValue(titleName);
                }
                if (setting!=null && setting.length != 0) {
                    String fieldName = "_" + titleName;
                    HSSFSheet hidden = wb.createSheet(fieldName);
                    HSSFCell sheetCell = null;
                    for (int j = 0, length = setting.length; j < length; j++)
                    {
                        String name = setting[j];
                        HSSFRow sheetRow = hidden.createRow(j);
                        sheetCell = sheetRow.createCell(0);
                        sheetCell.setCellValue(name);
                    }
                    Name namedCell = wb.createName();
                    namedCell.setNameName(fieldName);
                    namedCell.setRefersToFormula(fieldName+"!$A$1:$A$"+setting.length);
                    CellRangeAddressList regions = new CellRangeAddressList(2, Integer.MAX_VALUE, i, i);
                    DVConstraint constraint = DVConstraint.createFormulaListConstraint(fieldName);
                    HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                    wb.setSheetHidden(wb.getSheetIndex(hidden),true);
                    sheet.addValidationData(dataValidation);
                }
            }

            HttpServletResponse response = getResponse();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=recharge_import.xls");
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            Log.getLog(getClass()).error("error:", e);
        } finally {
            try {
                wb.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
        renderNull();
    }

    /**
     * 下载认款充值错误模板
     * @author dzc
     */
    public void downExcel(){
        String token=get("token");
        File file=new File(BaseConstant.UPLOAD_PATH+"excel/"+token);
        if(file.exists()){
            renderFile(file,"import_recharge_error.xlsx");
        }else {
            renderNull();
        }
    }

RechargeService.java

    /**
     * 导入充值
     * @param file
     * @return
     */
    @Before(Tx.class)
    public R excelImportRecharge(UploadFile file){
        List<List<Object>> errList = new ArrayList<>();
        String[] errorMessages = {
                "流水号不能为空！",
                "总账户不能为空！",
                "客户账户不能为空！",
                "转账金额不能为空！",
                "支付方式不能为空！",
                "备注不能为空！",
                "加款时间不能为空！",
                "台账类型不能为空！",
                "客户id不能为空!",
                "客户名不能为空！"
        };
        if(file.getFile()==null||!file.getFile().exists()){
            return R.ok();
        }
        AtomicReference<Integer> num= new AtomicReference<>(0);
        ExcelUtil.readBySax(file.getFile().getAbsolutePath(),0,(int sheetIndex, int rowIndex, List<Object> rowList)->{
            if(rowIndex>1){
                if(rowList.isEmpty()){
                    return;
                }else {
                    num.getAndSet(num.get() + 1);
                }

                for (int i = 0; i < rowList.size(); i++) {
                    if (StrUtil.isEmptyIfStr(rowList.get(i))) {
                        rowList.add(0, errorMessages[i]);
                        errList.add(rowList);
                        return;
                    }
                }
                String transactionFlow = rowList.get(0).toString().trim();
                String totalAccount = rowList.get(1).toString().trim();
                String customerAccount= rowList.get(2).toString().trim();
                BigDecimal transferAmount;
                try {
                    BigDecimal ta = new BigDecimal(rowList.get(3).toString().trim());
                    transferAmount= ta;
                    // 其他处理
                } catch (NumberFormatException e) {
                    // 处理转换失败的情况，例如添加到错误列表
                    rowList.add(0, "转账金额格式错误");
                    errList.add(rowList);
                    return;
                }

                String paymentMethod=rowList.get(4).toString().trim();
                String remarks=rowList.get(5).toString().trim();

                Date additionalPaymentTime = null;
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                dateFormat.setLenient(false); // 禁用宽松模式，严格验证日期格式
                try {
                    java.util.Date dateValue = dateFormat.parse(rowList.get(6).toString());
                    additionalPaymentTime = dateValue;
                } catch (ParseException e) {
                    rowList.add(0, "日期格式错误，yyyy-MM-dd HH:mm:ss 为正确格式");
                    errList.add(rowList);
                    return;
                }

                String ledgerType=rowList.get(7).toString().trim();
                Integer customerId= Integer.parseInt(rowList.get(8).toString());
                String customerName = (rowList.size() > 9 && rowList.get(9).toString() != "") ? rowList.get(9).toString().trim() : "";

                System.out.println(rowList);

                List<Record> recordList = Db.find("select transaction_flow from 72crm_crm_receivables_recharge where is_delete=1");
                CrmReceivablesRecharge recharge = new CrmReceivablesRecharge();

                for (int i = 0; i < recordList.size(); i++) {
                    if (recordList.get(i).get("transaction_flow").equals(transactionFlow)){
                        rowList.add(0,"重复的流水号！");
                        errList.add(rowList);
                        return;
                    }
                }
                recharge.setTransactionFlow(transactionFlow);
                recharge.setTotalAccount(totalAccount);
                recharge.setCustomerAccount(customerAccount);
                recharge.setTransferAmount(transferAmount);
                recharge.setPaymentMethod(paymentMethod);
                recharge.setRemarks(remarks);
                recharge.setAdditionalPaymentTime(additionalPaymentTime);
                recharge.setLedgerType(ledgerType);
                recharge.setCustomerId(customerId);
                recharge.setCustomerName(customerName);
                recharge.save();
            }
        });
        R result = R.ok().put("totalSize",num.get()).put("errSize",0);
        if(errList.size()>0){
            BigExcelWriter writer=null;
            try {
                String token= IdUtil.simpleUUID();
                writer= ExcelUtil.getBigWriter(file.getUploadPath()+"/"+token);
                writer.merge(10,"认款充值导入模板(*)为必填项");
                writer.setColumnWidth(0,50);
                for (int i = 1; i < 11; i++) {
                    writer.setColumnWidth(i,20);
                }
                writer.setDefaultRowHeight(20);
                Cell cell = writer.getCell(0, 0);
                CellStyle cellStyle = cell.getCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                Font font = writer.createFont();
                font.setBold(true);
                font.setFontHeightInPoints((short) 16);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                writer.writeHeadRow(Arrays.asList("错误信息","流水号(*)","总账户(*)","客户账户(*)", "转账金额(*)","支付方式(*)",
                        "备注(*)","加款时间(*)","台账类型(*)","客户id(*)","客户名(*)"));
                writer.write(errList);
                result.put("errSize",errList.size()).put("token",token);
            }finally {
                if(writer!=null){
                    writer.close();
                }
            }
        }
        return result;
    }

前端vue2
CustomerImportRecharge.vue 组件

<template>
  <el-dialog
    :visible.sync="showDialog"
    :append-to-body="true"
    :show-close="showCancel"
    :close-on-click-modal="false"
    title="批量导入"
    width="750px"
    @close="closeView">
    <div class="dialog-body">
      <el-steps
        :active="stepsActive"
        simple>
        <el-step
          v-for="(item, index) in stepList"
          :key="index"
          :title="item.title"
          :icon="item.icon"
          :status="item.status" />
      </el-steps>
      <div
        v-if="stepsActive == 1"
        class="sections">
        <div>请选择需要导入的文件</div>
        <div class="content">
          <flexbox class="file-select">
            <el-input
              v-model="file.name"
              :disabled="true" />
            <el-button
              type="primary"
              @click="selectFile">选择文件</el-button>
          </flexbox>
        </div>
        <div
          class="download"
          @click="download">
          点击下载《认款充值导入模板》</div>
        <div class="content content-tips">
          <div>操作步骤：</div>
          <div>1、下载《认款充值导入模板》</div>
          <div>2、打开《认款充值导入模板》将对应字段信息输入或粘贴进本表。为保障粘贴信息被有效导入，请使用纯文本或数字</div>
          <div>3、信息输入完毕，点击“选择文件”按钮，选择excel文件上传</div>
          <div>4、点击“确定”开始进行认款充值导入</div>
        </div>
      </div>

      <div
        v-loading="loading"
        v-else-if="stepsActive == 2"
        element-loading-text="数据导入中"
        element-loading-spinner="el-icon-loading"
        class="sections" />

      <div
        v-loading="loading"
        v-else-if="stepsActive == 3"
        class="sections">
        <div class="result-info">
          <i class="wk wk-success result-info__icon" />
          <p class="result-info__des">数据导入完成</p>
          <p class="result-info__detail">导入总数据<span class="result-info__detail--all">{{ resultData.totalSize }}</span>条，导入成功<span class="result-info__detail--suc">{{ resultData.totalSize - (resultData.errSize || 0) }}</span>条，导入失败<span class="result-info__detail--err">{{ resultData.errSize || 0 }}</span>条</p>
          <el-button
            v-if="resultData && resultData.errSize > 0"
            class="result-info__btn--err"
            type="text"
            @click="downloadErrData">下载错误数据</el-button>
        </div>
      </div>
      <input
        id="importInputFile"
        type="file"
        accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
        @change="uploadFile">
    </div>
    <span
      slot="footer"
      class="dialog-footer">
      <el-button
        :class="{ 'is-hidden': !showCancel }"
        @click="closeView">取消</el-button>
      <el-button
        v-if="sureTitle"
        type="primary"
        @click="sureClick">{{ sureTitle }}</el-button>
    </span>
  </el-dialog>
</template>

<script>
import {
  rechargeExcelImportAPI,
  rechargeDownLoadAPI,
  rechargeErrorExcelDownAPI,
} from '@/api/customermanagement/recharge'
import { downloadExcelWithResData } from '@/utils'
export default {
  // 批量导入充值
  name: 'CustomerImportRecharge',
  components: {},
  props: {
    show: {
      type: Boolean,
      default: false
    },
    // CRM类型
    crmType: {
      type: String,
      default: ''
    },
    search: {
      type: String,
      default: ''
    }
  },
  data() {
    return {
      loading: false,
      showDialog: false,
      file: { name: '' },
      stepsActive: 1,
      stepList: [
        {
          icon: 'wk wk-upload',
          title: '上传文件',
          status: 'wait'
        },
        {
          icon: 'wk wk-data-import',
          title: '导入数据',
          status: 'wait'
        },
        {
          icon: 'wk wk-success',
          title: '导入完成',
          status: 'wait'
        }
      ],
      resultData: null
    }
  },
  computed: {
    sureTitle() {
      return {
        1: '立即导入',
        2: '',
        3: '确定'
      }[this.stepsActive]
    },

    showCancel() {
      return this.stepsActive != 2
    }
  },
  watch: {
    show: function(val) {
      this.showDialog = val
      this.resetData()
    }
  },
  mounted() {},
  methods: {
    sureClick() {
      if (this.stepsActive == 1) {
        if (this.stepList[0].status == 'finish') {
          this.stepList[1].status = 'process'
          this.stepsActive = 2
          this.updateFile(res => {
            this.stepList[1].status = 'finish'
            this.stepsActive = 3
            if (res) {
              this.resultData = res
              if (res.errSize > 0) {
                this.stepList[2].status = 'error'
              } else {
                this.stepList[2].status = 'finish'
              }
            }
          })
        } else {
          this.$message.error('请选择导入文件')
        }
      } else if (this.stepsActive == 3) {
        this.closeView()
      }
    },

    updateFile(result) {
      console.log("res",result)
      if (!this.file.name) {
        this.$message.error('请选择导入文件')
      } else {
        this.loading = true
        console.log("this.file",this.file)
        rechargeExcelImportAPI({
          file: this.file
        })
          .then(res => {
            this.loading = false
            if (result) {
              result(res)
            }
            this.$emit('success')
          })
          .catch(() => {
            if (result) {
              result(false)
            }
            this.loading = false
          })
      }
    },

    /**
     * 下载错误
     */
    downloadErrData() {
      this.getImportError(this.resultData.token)
    },

    /**
     * 导入错误下载
     */
    getImportError(token) {
      this.loading = true
      rechargeErrorExcelDownAPI({
        token
      })
        .then(res => {
          downloadExcelWithResData(res)
          this.loading = false
        })
        .catch(() => {
          this.loading = false
        })
    },

    /**
     * 下载模板操作
     */
    download() {
      rechargeDownLoadAPI()
        .then(res => {
          var blob = new Blob([res.data], {
            type: 'application/vnd.ms-excel;charset=utf-8'
          })
          var downloadElement = document.createElement('a')
          var href = window.URL.createObjectURL(blob) // 创建下载的链接
          downloadElement.href = href
          downloadElement.download =
            decodeURI(
              res.headers['content-disposition'].split('filename=')[1]
            ) || '' // 下载后文件名
          document.body.appendChild(downloadElement)
          downloadElement.click() // 点击下载
          document.body.removeChild(downloadElement) // 下载完成移除元素
          window.URL.revokeObjectURL(href) // 释放掉blob对象
        })
        .catch(() => {})
      // window.open(userImportTemplateURL)
    },

    /**
     * 选择文件选择文件
     */
    selectFile() {
      document.getElementById('importInputFile').click()
    },

    /**
     * 选择触发
     */
    uploadFile(event) {
      var files = event.target.files
      const file = files[0]
      this.file = file
      event.target.value = ''
      this.stepList[0].status = 'finish'
    },

    /**
     * 关闭
     */
    closeView() {
      this.$emit('close')
    },

    /**
     * 重置数据
     */
    resetData() {
      this.file = { name: '' }
      this.stepList = [
        {
          icon: 'wk wk-upload',
          title: '上传文件',
          status: 'wait'
        },
        {
          icon: 'wk wk-data-import',
          title: '导入数据',
          status: 'wait'
        },
        {
          icon: 'wk wk-success',
          title: '导入完成',
          status: 'wait'
        }
      ]
      this.stepsActive = 1
      this.resultData = null
    }
  }
}
</script>

<style scoped lang="scss">
.el-steps {
  margin-bottom: 15px;

  /deep/ .el-step__title {
    font-size: 14px;
  }

  /deep/ .el-step.is-simple .el-step__arrow::before,
  /deep/ .el-step.is-simple .el-step__arrow::after {
    height: 10px;
    width: 2px;
  }

  /deep/ .el-step.is-simple .el-step__arrow::after {
    transform: rotate(45deg) translateY(3px);
  }
  /deep/ .el-step.is-simple .el-step__arrow::before {
    transform: rotate(-45deg) translateY(-2px);
  }
}

.sections {
  font-size: 14px;
  min-height: 215px;
  .download {
    cursor: pointer;
    color: #2362fb;
    margin-bottom: 15px;
  }

  /deep/ .el-loading-spinner {
    top: 45%;
    .el-icon-loading {
      font-size: 40px;
      color: #999;
    }

    .el-loading-text {
      color: #333;
    }
  }
}

.content {
  padding: 10px 0;
}

.content-tips {
  font-size: 12px;
  color: #999;
  line-height: 15px;
}

#importInputFile {
  display: none;
}

.file-select {
  .el-input {
    width: 400px;
  }
  button {
    margin-left: 20px;
  }
}

.is-hidden {
  visibility: hidden;
}

// 结果信息
.result-info {
  text-align: center;
  padding-top: 30px;

  &__icon {
    font-size: 40px;
    color: $xr-color-primary;
  }

  &__des {
    margin-top: 15px;
    color: #333;
    font-size: 14px;
  }

  &__detail {
    margin-top: 15px;
    font-size: 12px;
    color: #666;
    &--all {
      color: #333;
      font-weight: 600;
    }

    &--suc {
      color: $xr-color-primary;
      font-weight: 600;
    }

    &--err {
      color: #f94e4e;
      font-weight: 600;
    }
  }

  &__btn--err {
    margin-top: 10px;
  }
}
</style>

api接口（举一例，其他都差不多）

//recharge.js
//充值模板下载

export function rechargeDownLoadAPI(data) {
  return request({
    url: 'Recharge/downloadExcel',
    method: 'post',
    data: data,
    responseType: 'blob'
  })
}