jeecgbootExecl导出模版、导入execl记录

之前模块发时也曾想交给度娘结果还是自己实现吧这里记录一下方便日后copy，希望也能帮到路过的小伙伴～

后台代码展示：

一：controller层代码展示

@Service
public class MajorServiceImpl implements MajorService {
    /**
	  * 导出模版
	  * */
	 @RequestMapping(value = "/exportXlsDownload")
	 public void download(HttpServletResponse response) throws IOException {
		 try {
			 response.setCharacterEncoding("UTF-8");
			 response.setHeader("content-Type", "application/vnd.ms-excel");
			 HSSFWorkbook workbook = new HSSFWorkbook();
			 HSSFSheet sheet1 = workbook.createSheet();
			 String fileName = "专业名录";
			 HSSFRow rowBt = sheet1.createRow(0);
			 String[] bt = new String[]{"专业大类", "专业名称", "专业介绍"};
			 for (int i = 0; i < bt.length; i++) {
				 sheet1.setColumnWidth(i,5000);
				 HSSFCell cell = rowBt.createCell(i);
				 cell.setCellValue(bt[i]);
			 }
			 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
			 workbook.write(response.getOutputStream());
			 workbook.close();
		 }catch (Exception e){
			 throw new IOException("导出失败,请联系管理员");
		 }

	 }
      /*
	  * 导入execl-专业名录
	  */
	 @RequestMapping(value = "/importExcels", method = RequestMethod.POST)
	 public Result<?> importExecl(HttpServletRequest request, HttpServletResponse response, OrgFile orgFile){
		 return hzkMajorRmService.importSchoolNewStuList(orgFile);
	 }
}

二：Dao层代码

@Data
public class MajorPo implements Serializable {
    private Integer orgType;
    private String parentCode;
    private String parentName;
    private String majorName;
    private String majorCode;
    private String orderCode;
    private String majorData;
    private String majorJs;
    private String newOprUserId;

    /*mac command+n 生成 set、get方法 以下省略*/
}

三、service层代码展示

public interface IHzkMajorRmService extends IService<HzkMajorRm> {
    /*
     * 热门专业介绍execl-导入
     */
    Result<Object> importSchoolNewStuList(OrgFile orgFile);
}

四、service-impl逻辑处理实现

//导入
    @Override
    public Result<Object> importSchoolNewStuList(OrgFile orgFile) {
        MultipartFile file = orgFile.getFile(); //获得文件
        String newOprUserId =  orgFile.getUserId();//用户id
        if (file.isEmpty()) {
            return Result.error("文件为空！");
        }
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0); //获取shell下标 默认从0开始
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row=sheet.getRow(i);
                MajorPo po = new MajorPo();
                Cell cell2 = row.getCell(2);
                cell2.setCellType(CellType.STRING); //设置单元格类型 setCellType已过期
                po.setNewOprUserId(newOprUserId);
                po.setParentName(row.getCell(0).getStringCellValue());
                po.setMajorName(row.getCell(1).getStringCellValue());
                po.setMajorJs(cell2.getStringCellValue());
                this.baseMapper.insertRmMajor(po);
            }
        }catch (IOException e){
            return Result.error(e.getMessage());
        }
        return Result.OK("导入成功");
    }

五、mapper层代码

public interface HzkMajorRmMapper extends BaseMapper<HzkMajorRm> {
    //execl插入操作
    void insertRmMajor(MajorPo po);
}

六、xml数据库操作代码

<?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="org.jeecg.modules.demo.hzkMajorRm.mapper.HzkMajorRmMapper">
    <!--  execl导入. -->
    <insert id="insertRmMajor">
        INSERT INTO hzk_major_rm(parent_name,major_name,major_js,new_opr_user_id)
        VALUES(#{parentName},#{majorName},#{majorJs},#{newOprUserId})
    </insert>
</mapper>

------------------------------------------------------前台代码实现-----------------------------------------------------

<template>
  <div>
    <!--引用表格-->
    <BasicTable @register="registerTable">
      <!--插槽:table标题-->
      <template #tableTitle>
        <a-button type="primary" @click="handleAdd" preIcon="ant-design:plus-outlined"> 新增</a-button>
        <a-button type="primary" preIcon="ant-design:export-outlined" @click="onExportXlsAll"> 批量导入</a-button>
        <a-dropdown v-if="selectedRowKeys.length > 0">
          <template #overlay>
            <a-menu>
              <a-menu-item key="1" @click="batchHandleDelete">
                <Icon icon="ant-design:delete-outlined"></Icon>
                删除
              </a-menu-item>
            </a-menu>
          </template>
          <a-button
            >批量操作
            <Icon icon="mdi:chevron-down"></Icon>
          </a-button>
        </a-dropdown>
      </template>
      <!--操作栏-->
      <template #action="{ record }">
        <TableAction :actions="getTableAction(record)" :dropDownActions="getDropDownAction(record)" />
      </template>
      <!--字段回显插槽-->
      <template #htmlSlot="{ text }">
        <div v-html="text"></div>
      </template>
      <!--省市区字段回显插槽-->
      <template #pcaSlot="{ text }">
        {{ getAreaTextByCode(text) }}
      </template>
      <template #fileSlot="{ text }">
        <span v-if="!text" style="font-size: 12px; font-style: italic">无文件</span>
        <a-button v-else :ghost="true" type="primary" preIcon="ant-design:download-outlined" size="small" @click="downloadFile(text)">下载</a-button>
      </template>
    </BasicTable>
    <!-- 表单区域 -->
    <HzkMajorRmModal @register="registerModal" @success="handleSuccess"></HzkMajorRmModal>
    <!-- 
      批量导入（个人封装的组件）
     @customChange:成功回调
     visible：展开关闭弹出层
     orgType：tab切换值
     excelUrl：导入接口
    -->
    <ExcelMoadl @customChange="successHandle" :excelUrl="importExcels" :key="upkey" v-model:visible="Turnswitch"  :orgType="mode"  :isYearSelect="false" @downLoadTmp="downLoadTmp"/>
  </div>
</template>

<script lang="ts" name="HzkMajorRm-hzkMajorRm" setup>
  import { ref, computed, unref } from 'vue';
  import { BasicTable, useTable, TableAction } from '/@/components/Table';
  import { useModal } from '/@/components/Modal';
  import { useListPage } from '/@/hooks/system/useListPage';
  import HzkMajorRmModal from './components/HzkMajorRmModal.vue';
  import { columns, searchFormSchema } from './HzkMajorRm.data';
  import { list, deleteOne, batchDelete, getImportUrl, getExportUrl,exportXlsDownload,importExcels } from './HzkMajorRm.api';
  import { downloadFile } from '/@/utils/common/renderUtils';
  const checkedKeys = ref<Array<string | number>>([]);
    // 导入导出组件
  import ExcelMoadl from '../../components/upLoadExel.vue';
  import {downloadByData} from "/@/utils/file/download";
  //注册model
  const [registerModal, { openModal }] = useModal();
  //注册table数据
  const { prefixCls, tableContext, onExportXls, onImportXls } = useListPage({
    tableProps: {
      title: 'hzk_major_rm',
      api: list,
      columns,
      canResize: false,
      formConfig: {
        //labelWidth: 120,
        schemas: searchFormSchema,
        autoSubmitOnEnter: true,
        showAdvancedButton: true,
        fieldMapToNumber: [],
        fieldMapToTime: [],
      },
      actionColumn: {
        width: 120,
        fixed: 'right',
      },
      beforeFetch: (params) => {
        params.column = 'oprDate';
      },
    },
    exportConfig: {
      name: 'hzk_major_rm',
      url: getExportUrl,
    },
    importConfig: {
      url: getImportUrl,
      success: handleSuccess,
    },
  });

  const [registerTable, { reload }, { rowSelection, selectedRowKeys }] = tableContext;
 /***
   * 批量导入
   */
   const Turnswitch = ref(false);
   const upkey = ref(13);
   function onExportXlsAll() {
    Turnswitch.value = true;
    upkey.value = new Date().getTime();
  }
  /**
   * execl导入成功回调
   */
   const successHandle = (e) =>{
      if(e)reload();
  }
  /*
   * 下载模块
   */
   function  downLoadTmp(){
    let orgTypeStr ='热门专业介绍';
    /*
     * 下载文件
     */
     exportXlsDownload({}).then((response) => {
      downloadByData(response,orgTypeStr,'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    });
  }

</script>

<style scoped></style>

--------------------------------导出execl模版&导入组件代码如下------------------------------------

<template>
  <div>
    <a-modal :width="1000" :footer="null" v-model:visible="visible" title="批量导入" @ok="handleOk">
      <!-- 下载部分 -->
      <div class="flex xycenter bg" style="height: 60px" @click="onExportXls">
        <div class="icons-list">
          <FileMarkdownOutlined />
        </div>
        <div>下载导入模版</div>
      </div>
      <!-- 入学年份（个性） -->
      <div class="flex" v-show="isYearSelect">
        <div style="text-align: right; margin-left: 35px; margin: 30px" class="flex xycenter">
          <div style="color: red">*</div>
          <div>入学年份</div>
        </div>
        <div class="xycenter">
          <a-space>
            <a-select ref="select" :options="options1" v-model:value="value1" style="width: 220px" @focus="focus"
              @change="handleChange"></a-select>
          </a-space>
        </div>
      </div>
      <!-- 导入文件 -->
      <div class="flex" style="margin-top: 15px">
        <div style="margin: 10px">请选择导入文件</div>
        <div class="flex xycenter">
          <!-- :beforeUpload="beforeUpload" -->
          <a-upload-dragger style="padding: 0 50px; width: 500px" v-model:fileList="fileList" name="file"
            :multiple="false" 
            :data="paramData"
            :action="excelUrl"
            :headers="headers"
             @change="handleChangeup"
             @drop="handleDrop">
            
            <p class="ant-upload-drag-icon">
              <inbox-outlined></inbox-outlined>
            </p>
            <p class="ant-upload-text">请选择要导入的文件</p>
          </a-upload-dragger>
          <div>
            <!-- <a-button :disabled="fileList.length <= 0" type="primary" :loading="loading" @click="onImportXls">上传文件</a-button> -->
          </div>
        </div>
      </div>
      <div style="height: 50px"></div>
    </a-modal>
  </div>
</template>
<script setup lang="ts">
import {ref, onMounted, defineEmits, reactive} from 'vue';
  import {useListPage} from '/@/hooks/system/useListPage';
  // 图标
  import {FileMarkdownOutlined,InboxOutlined} from '@ant-design/icons-vue';
  import {SelectTypes} from 'ant-design-vue/es/select';
  import { message} from 'ant-design-vue';
  import { HTzkYearList, exportXlnews,importExcels} from '../enterSchool/enrollmentPlan/HzkSchool.api';
  import {downloadByData} from "/@/utils/file/download";
  import {useUserStore} from '/@/store/modules/user';
  const { userInfo } = useUserStore();
  import { getToken } from '/@/utils/auth';
  import type { UploadChangeParam } from 'ant-design-vue';
  import {useGlobSetting} from "/@/hooks/setting";
  import {ConfigEnum} from "/@/enums/httpEnum";

  const loading = ref(false);
  const value1 = ref('2016');
  const glob = useGlobSetting();
  
  // 接收
  const props = defineProps({
    visible: {
      type: Boolean,
      default: false,
    },
    createYear: {
      type: String,
      default: ''
    },
    //上传地址
    excelUrl: {
      type: String,
      default: '',
    },
    orgType: {
      type: String,
      default: '',
    },
    isYearSelect:{
      type:Boolean,
      default:false
    }
  });
  const   token = getToken();
  const visible = ref(props.visible);
  const orgType = ref(props.orgType);
  const createYear = ref(props.createYear);
  const excelUrl  = ref(glob.uploadUrl + props.excelUrl);
  const headers   = reactive({});
  headers[ConfigEnum.TOKEN] = token;
  const files = ref('');
  const resourceData = ref({});
  console.log(props, 'props');

  const fileList = ref([]);
  const focus = () => {
    console.log('focus');
  };

  const handleChange = (value: string) => {
    console.log(`selected ${value}`);
     value1.value = value;
     paramData.value.createYear = value;
  };
  const options1 = ref < SelectTypes['options'] > ([]);
  const getUserList = async () => {
    const res = await HTzkYearList({});
    options1.value = res.data;
    value1.value = res.data[0].label;
  }

  const handleOk = () => {
    loading.value = true;
    setTimeout(() => {
      loading.value = false;
      visible.value = false;
    }, 2000);
  };

  const onExportXls = () => {
    emit("downLoadTmp");
  }

  /*
    导入实现
  */
  const emit = defineEmits(['customChange','downLoadTmp'])
  const paramData = ref({
      orgType: Number(orgType.value),
      createYear:value1.value,
      userId: userInfo.id
  })

  const handleChangeup = (info: UploadChangeParam) => {
      const status = info.file.status;
      console.log(info,'infoinfoinfoinfo');

      if (status !== 'uploading') {
        console.log(info.file, info.fileList);
      }
      if(status === 'done'){
        if(info.file.response.code == 200 ){
          message.success(`${info.file.response.message}`);
          emit('customChange', new Date().getTime());
        }
      }else if(status === 'error'){
        message.error(`${info.file.response.message}`);
        fileList.value = [];
        info.fileList = [];
      }
    };
    const handleDrop = (e: DragEvent) => {
        console.log(e);
    };

  onMounted(() => {

    if(props.isYearSelect){
      getUserList();
    }
  })
</script>
<style scoped>
  .icons-list :deep(.anticon) {
    margin-right: 6px;
    font-size: 24px;
  }

  .flex {
    display: flex;
  }

  .xycenter {
    display: flex;
    display: -webkit-flex;
    align-items: center;
    justify-content: center;
  }

  .padding10 {
    padding: 20px;
  }

  .bg {
    background: #fffafa;
  }

  .bg:hover {
    background: #ffe8e8;
    cursor: pointer;
  }
</style>