精华内容
下载资源
问答
  • 2020-12-22 11:06:46

    @OverridepublicResult importSkuListAsync(MultipartFile file) {//1.参数校验

    if (file == null ||file.isEmpty()) {throw new GoodsException("导入文件为空");

    }//判断文件格式

    String filename =file.getOriginalFilename();

    String suffixName= filename.substring(filename.indexOf("."));if (!".xlsx".equalsIgnoreCase(suffixName) && !".xls".equalsIgnoreCase(suffixName)) {throw new GoodsException("文件格式要求:.xlsx/.xls");

    }

    dealDataAsync(file, suffixName);returnResult.success();

    }/*** 异步处理excel数据校验、落库、上传文件服务器等

    *

    *@paramfile*/@Asyncpublic voiddealDataAsync(MultipartFile file, String suffixName) {

    InputStream inputStream= null;try{

    inputStream=file.getInputStream();

    }catch(Exception e) {

    e.printStackTrace();

    }//1.注册任务

    String dataFlag = System.currentTimeMillis() + "";//任务注册

    Long id = reg("{\"service\":\"goods-web\"}", getUserName(), dataFlag, getRealName(), DataFileTaskTypeEnum.IMPORT.getType());if (id == null) {

    log.error("【导入商品】 uploadSkuFile 数据版本号:" + dataFlag + ", 注册导入任务失败");return;

    }//2.excel数据校验

    SkuUploadListener listener = newSkuUploadListener();

    dealExcel(listener, suffixName, id, dataFlag, inputStream);//3.把错误数据分装集合中,正确数据封装集合中//验证失败的数据

    List uploadFailList =listener.getUploadFailList();//验证通过的数据

    List skuModeList =listener.getSkuModelList();//总数量

    int totalSize =listener.getTotalSize();//4.把成功集合插入数据库,错误数据上传文件服务器

    try{for(SkuModel skuModel : skuModeList) {

    Sku sku= newSku();

    BeanUtils.copyProperties(skuModel, sku);

    saveOrUpdateSku(sku);

    }

    }catch(Exception e){

    log.error("【导入商品】 数据版本号:{},保存采购单报错:{},错误详情:{}", dataFlag, e.getMessage(), e);

    finish(id, DataFileTaskStatusEnum.FAIL.getType(),"", totalSize, 0, "保存到数据库报错", dataFlag);return;

    }//5.完成任务(上传失败数据到文件服务器)//导入状态

    int status =DataFileTaskStatusEnum.SUCCESS.getType();

    String fileUrl= "";//导入失败的数据,生成异常文件

    if (!CollectionUtils.isEmpty(uploadFailList)) {if(CollectionUtils.isEmpty(skuModeList)) {

    status=DataFileTaskStatusEnum.FAIL.getType();

    }else{

    status=DataFileTaskStatusEnum.PART_SUCCESS.getType();

    }

    fileUrl=createErrFile(uploadFailList, dataFlag);

    }

    finish(id, status, fileUrl, totalSize, skuModeList.size(),"导入结束", dataFlag);

    }private voiddealExcel(SkuUploadListener listener, String suffixName, Long id, String dataFlag, InputStream file) {

    ExcelTypeEnum excelTypeEnum;if(ExcelTypeEnum.XLSX.getValue().equalsIgnoreCase(suffixName)) {

    excelTypeEnum=ExcelTypeEnum.XLSX;

    }else if(ExcelTypeEnum.XLS.getValue().equalsIgnoreCase(suffixName)) {

    excelTypeEnum=ExcelTypeEnum.XLS;

    }else{

    log.error("【导入采购单】 uploadPurchaseFile 数据版本号:" + dataFlag + ",上传文件格式不是 " + ExcelTypeEnum.XLSX.getValue() + "/" +ExcelTypeEnum.XLS.getValue());

    finish(id, DataFileTaskStatusEnum.FAIL.getType(),"", 0, 0, "文件格式不正确", dataFlag);return;

    }//解析文件

    try{

    ExcelReader excelReader= new ExcelReader(file, excelTypeEnum, null, listener);

    excelReader.read(new Sheet(1, 1, SkuModel.class));

    }catch(Exception e) {

    log.error("【导入采购单】 uploadPurchaseFile 数据版本号:{},解析文件报错:{},错误详情:{}", dataFlag, e.getMessage(), e);

    finish(id, DataFileTaskStatusEnum.FAIL.getType(),"", 0, 0, "解析文件报错", dataFlag);return;

    }

    }/*** 导入商品,异常文件生成

    *

    *@parammodelList

    *@paramdataFlag*/

    private String createErrFile(ListmodelList, String dataFlag) {//生成文件类型

    ByteArrayOutputStream out = null;

    String fileUrl= "";try{

    out= newByteArrayOutputStream();

    ExcelWriter writer= newExcelWriter(out, ExcelTypeEnum.XLSX);if (modelList.size() >FILE_SIZE) {

    List> splitList =Lists.partition(modelList, FILE_SIZE);for (int i = 0; i < splitList.size(); i++) {//写一个sheet,

    Sheet sheet = new Sheet(i + 1, 0, SkuUploadFailModel.class);

    writer.write(splitList.get(i), sheet);

    }

    }else{//写一个sheet,

    Sheet sheet = new Sheet(1, 0, SkuUploadFailModel.class);

    writer.write(modelList, sheet);

    }

    writer.finish();

    CloudStorageService oss=oSSFactory.build();

    String path= oss.getDefaultPath("/导入商品异常反馈.xlsx");

    fileUrl=oss.upload((out).toByteArray(), path);

    log.info("【导入商品】 createErrFile 数据版本号:" + dataFlag + ",生成文件url:" +fileUrl);

    }catch(Exception e) {

    log.error("【导入商品】 createErrFile 数据版本号:" + dataFlag + ",导出报错: " +e.getMessage(), e);

    }finally{try{if (out != null) {

    out.close();

    }

    }catch(Exception e) {

    e.printStackTrace();

    }

    }returnfileUrl;

    }privateLong reg(String param, String userName, String dataFlag, String realName, Integer type) {

    DataFileTaskDTO dataFileTaskDTO= newDataFileTaskDTO();

    dataFileTaskDTO.setParams(param);

    dataFileTaskDTO.setDataType(SystemModuleEnum.DOWNLOAD_GOODS.getType());

    dataFileTaskDTO.setType(type);

    dataFileTaskDTO.setRemark(SystemModuleEnum.DOWNLOAD_GOODS.getTypeName()+ ",数据版本号:" +dataFlag);

    dataFileTaskDTO.setSource(SystemModuleEnum.DOWNLOAD_GOODS.getSystem());

    dataFileTaskDTO.setCreateUser(realName);

    dataFileTaskDTO.setCreateUserJobNumber(userName);

    log.info("【导入商品】推送到磐石注册任务参数,dataFileTaskDTO:{}", JSONObject.toJSONString(dataFileTaskDTO));

    Result ret=remote.reg(dataFileTaskDTO);

    log.info("【导入商品】推送到磐石注册任务返回结果,ret:{}", JSONObject.toJSONString(ret));if (!CheckUtils.isNull(ret) && ret.isSuccess() && ret.getData() != null) {returnLong.parseLong( ret.getData().toString());

    }return null;

    }privateLong finish(Long id, Integer taskStatus, String fileUrl, Integer total, Integer successTotal, String remark, String dataFlag) {

    DataFileTaskDTO taskDTO= newDataFileTaskDTO();

    taskDTO.setId(id);

    taskDTO.setTaskStatus(taskStatus);

    taskDTO.setFileUrl(fileUrl);

    taskDTO.setTotal(total== null ? 0: total);

    taskDTO.setSuccessTotal(successTotal== null ? 0: successTotal);

    taskDTO.setRemark(remark+ ",数据版本号:" +dataFlag);

    log.info("【导入商品】推送到磐石完成任务参数,dataFileTaskDTO:{}", JSONObject.toJSONString(taskDTO));

    Result ret=remote.finsh(taskDTO);

    log.info("【导入商品】推送到磐石完成任务返回结果,ret:{}", JSONObject.toJSONString(ret));if (!CheckUtils.isNull(ret) &&ret.isSuccess()) {return(Long) ret.getData();

    }return null;

    }/*** 获取用户名

    *

    *@return

    */

    privateString getUserName() {//获取当前用户

    String userName = "";

    LoginUserVo loginUserVo=UserVoThreadLocal.get();if(Objects.nonNull(loginUserVo)) {

    userName=loginUserVo.getUserName();

    }returnuserName;

    }/*** 获取真实姓名

    *

    *@return

    */

    privateString getRealName() {//获取当前用户

    String realName = "";

    LoginUserVo loginUserVo=UserVoThreadLocal.get();if(Objects.nonNull(loginUserVo)) {

    realName=loginUserVo.getRealName();

    }returnrealName;

    }

    更多相关内容
  • 通过easyExcel导出(简洁版)

    千次阅读 2021-10-20 10:12:33
    第一步: 导入依赖 <!-- 导处Excel --> <dependency>...easyexcel</artifactId> <version>3.0.0-beta3</version> </dependency> 第二步: 新建方法类 package c

    第一步:
    导入依赖

        <!-- 导处Excel -->
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>3.0.0-beta3</version>
        </dependency>
    

    第二步:
    新建方法类

    package com.lc.yangzi.module.marketing.sell;
    
    import lombok.extern.slf4j.Slf4j;
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import org.apache.poi.util.IOUtils;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.List;
    
    /**
     * Excel工具类
     */
    @Slf4j
    public class ExcelUtils {
    
        /**
         * 导出Excel(07版.xlsx)到指定路径下
         *
         * @param path      路径
         * @param excelName Excel名称
         * @param sheetName sheet页名称
         * @param clazz     Excel要转换的类型
         * @param data      要导出的数据
         */
        public static void export2File(String path, String excelName, String sheetName, Class clazz, List data) {
            String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
            EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
        }
    
        /**
         * 导出Excel(07版.xlsx)到web
         *
         * @param response  响应
         * @param excelName Excel名称
         * @param sheetName sheet页名称
         * @param clazz     Excel要转换的类型
         * @param data      要导出的数据
         * @throws Exception
         */
        public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            excelName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
            EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
        }
    
        /**
         * 将指定位置指定名称的Excel导出到web
         *
         * @param response  响应
         * @param path      文件路径
         * @param excelName 文件名称
         * @return
         * @throws UnsupportedEncodingException
         */
        public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
            File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
            if (!file.exists()) {
                return "文件不存在!";
            }
    
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            excelName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
    
            try (
                    FileInputStream in = new FileInputStream(file);
                    ServletOutputStream out = response.getOutputStream();
            ) {
                IOUtils.copy(in, out);
                return "导出成功!";
            } catch (Exception e) {
                log.error("导出文件异常:", e);
            }
    
            return "导出失败!";
        }
    
    }
    
    

    第二步:
    新建要导出的列表类

    package com.lc.yangzi.module.marketing.sell.customerinfo.domain;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import lombok.Data;
    
    @Data
    public class CustomerInfoExcel {
    
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"客户名称"}, index = 0)
        private String customerName;//客户名称
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"客户等级"}, index = 1)
        private Integer customerLevel;//客户等级:A,B,C,D
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"地址"}, index = 2)
        private String address;//地址
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"联系电话"}, index = 3)
        private String contactPhone;//联系电话
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"审核状态"}, index = 4)
        private String auditStatusName;//状态
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"提货状态"}, index = 5)
        private String pickingStatusName;//提货状态
        @ColumnWidth(20) // 定义列宽
        @ExcelProperty(value = {"管理人"}, index = 6)
        private String managerUserName;//管理人姓名(多个逗号分隔)
    
    
        @ExcelIgnore
        private Integer auditStatus;//审核状态审核状态:0草稿1审核中2已完结
        @ExcelIgnore
        private Long id;//主键ID
        @ExcelIgnore
        private Long orgId;//和组织管联 yz_sys_org where category = 3、
        @ExcelIgnore
        private Integer pickingStatus;//提货状态
        @ExcelIgnore
        private Long createUserId;//创建人
    }
    
    

    第四步:
    调用方法

        //导出Excel
        @RequestMapping(value="/exportExcel", method = RequestMethod.GET)
        public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
            try {
                String customerId = request.getParameter("customerId");
                String auditStatus = request.getParameter("auditStatus");
                String pickingStatus = request.getParameter("pickingStatus");
    
                if(StringUtils.isBlank(customerId)){
                    customerId="0";
                }if(StringUtils.isBlank(auditStatus)){
                    auditStatus="-1";
                }if(StringUtils.isBlank(pickingStatus)){
                    pickingStatus="0";
                }
                List<CustomerInfoExcel> list = customerInfoService.selectAllCustomerInfoExcel(Long.parseLong(customerId),Integer.parseInt(auditStatus),Integer.parseInt(pickingStatus));
                ExcelUtils.export2Web(response, "客户信息", "客户信息", CustomerInfoExcel.class, list);
            } catch (Exception e) {
                log.error("报表导出异常:", e);
            }
        }
    
    展开全文
  • easyexcel导入导出

    2022-05-15 17:23:58
    { // 生成EXCEL并指定输出路径 OutputStream out = null; ExcelWriter excelWriter = null; try { out = new FileOutputStream(fileName); excelWriter = EasyExcel.write(out, clazz).build(); WriteSheet ...
    <template>
      <div>
        <div class="elian-box">
          <div class="elian-title">
            <i></i>
            <div>查询</div>
          </div>
          <el-form
            ref="form"
            :model="form"
            label-width="120px"
            @keyup.enter.native.prevent="formSearch"
          >
            <el-row :gutter="10">
    
              <el-col :span="6" class="form-button-group">
                <el-form-item label-width="0">
                  <el-button @click="collapsed = !collapsed"
                    >{{ collapsed ? '收起' : '展开' }}
                    <i
                      :class="{
                        'el-icon-arrow-down': !collapsed,
                        'el-icon-arrow-up': collapsed
                      }"
                    />
                  </el-button>
                  <el-button
                    type="primary"
                    :loading="gridLoading"
                    @click="formSearch()"
                    >查询</el-button
                  >
                  <el-button @click="clearForm('form'), formSearch()"
                    >重置</el-button
                  >
                </el-form-item>
              </el-col>
            </el-row>
          </el-form>
        </div>
        <elian-tool>
          <template #left>
            <div class="elian-title">
              <i></i>
              <div>列表</div>
            </div></template
          >
          <template #right>
            <el-button size="small" @click="downloadTemplateExcel" style="float: left;margin-top: 10px;margin-right: 10px">导入模板下载</el-button>
            <el-upload
              :action="importExcel()"
              :file-list="fileList"
              :limit="9"
              :data="routerInfo"
              :show-file-list="false"
              :on-success="handleSuccess"
              :filetype="['xlxs']"
              :headers="$download.addHeadToken()"
              style="float: left"
            >
            <el-button type="primary" style="margin-right: 10px">导入</el-button>
            </el-upload>
            <el-button type="primary" @click="exportExcel" >导出</el-button>
          </template>
        </elian-tool>
        <div class="elian-box">
          <elian-grid
            ref="table"
            :data="tableData"
            :loading="gridLoading"
            :page-size="pageSize"
            :page-sizes="pageSizes"
            :current-page="currentPage"
            :total="total"
            height="200"
            resize
            @pagination-current-change="paginationCurrentChange"
            @pagination-size-change="paginationSizeChange"
            @selection-change="selectionChange"
          >
            <el-table-column
              label="序号"
              type="index"
              
              fixed
              :index="handleIndex"
              width="50"
            >
            </el-table-column>
            <el-table-column
              prop="tenditmId"
              label="招标项目代码"
              min-width="240"
              
              show-overflow-tooltip
            >
            </el-table-column>
          </elian-grid>
        </div>
      </div>
    </template>
    <script>
    import gridMixins from '@/components/ElianGrid/grid-mixins' //列表方法引入
    import formMixins from '@/mixins/form' //form方法引入
    import download from "downloadjs";
    import { getToken } from "@/utils/auth";
    import { queryDrugDclaFilterPage, deleteDrugDclaFilterById, downloadTemplateExcel, importExcel, exportExcel } from '@/api/std/drugDclaFilter' //列表接口引入
    export default {
      props: {
        routerInfo: {
          type: Object
        }
      },
      components: {},
      mixins: [gridMixins, formMixins],
      data() {
        return {
          fileList:[],
          form: {},
          tableData: []
        }
      },
      created() {},
      methods: {
        getToken,
        importExcel,
        //多选的事件
        selectionChange() {},
        // 列表 数据获取
        async getList() {
          let response
          this.gridLoading = true
          try {
            this.queryData.confirmStas = 0
            response = await queryDrugDclaFilterPage(this.queryData)
          } catch (error) {
            this.gridLoading = false
            return
          }
          const { current, total, records } = response.data
          this.tableData = records
          this.currentPage = current
          this.total = total
          this.gridLoading = false
        },
    
        deletedetel(drugInfoId) {
          this.$confirm('是否确认删除?', '提示', {
            confirmButtonText: '确定',
            cancelButtonText: '取消',
            type: 'warning'
          })
            .then(() => {
              deleteDrugDclaFilterById(drugInfoId).then(res => {
                if (res.success === true) {
                  this.$message({
                    type: 'success',
                    message: '删除成功!'
                  })
                  this.getList()
                }
              })
            })
            .catch(() => {
              this.$message({
                type: 'info',
                message: '已取消删除'
              })
            })
        },
        downloadTemplateExcel(){
          this.$confirm('是否下载导入模板?', '提示', {
            confirmButtonText: '确定',
            cancelButtonText: '取消',
            type: 'warning'
          }).then(() => {
            downloadTemplateExcel().then(resp => {
              this.$message.success("导入下载模板成功!")
              let mimetypes =
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
              let blob = new Blob([resp.data], {type: mimetypes})
              let filename = decodeURI(resp.headers.filename)
              download(blob, filename, mimetypes)
            })
          })
        },
        exportExcel(){
          this.$confirm('是否导出?', '提示', {
            confirmButtonText: '确定',
            cancelButtonText: '取消',
            type: 'warning'
          }).then(() => {
            exportExcel(this.queryData).then(resp => {
              this.$message.success("导出成功!")
              let mimetypes =
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' // 此mimetype根据文档类型切换
              let blob = new Blob([resp.data], {type: mimetypes})
              let filename = decodeURI(resp.headers.filename)
              download(blob, filename, mimetypes)
            })
          })
        },
        handleSuccess(res){
          if (res.success==true){
            this.$message.success("导入成功")
          }else{
            this.$message.warning("导入失败")
          }
          this.getList();
        },
      }
    }
    </script>
    
    import request from '@/utils/request'
    import dowrequest from "@/utils/downloadrequest";
    
    /**
     * 下载导入模板
     * @param {*} id
     */
    export function downloadTemplateExcel() {
      return dowrequest.get(
        '/tps-local-bd/web/std/dlcg/drugDclaFilter/downloadTemplateExcel'
      )
    }
    /**
     * 导入
     * @returns {string}
     */
    export function importExcel() {
      return '/tps-local-bd/web/std/dlcg/drugDclaFilter/import';
    }
    
    /**
     * 导出
     * @returns {string}
     */
    export function exportExcel(params) {
      return dowrequest.post(
        '/tps-local-bd/web/std/dlcg/drugDclaFilter/export',params
      )
    }
    
    
    controller
    /**
    	 * 下载模板文件
    	 *
    	 * @param response
    	 */
    	@SneakyThrows
    	@ApiOperation(value = "下载导入模板", notes = "下载导入模板")
    	@GetMapping("/downloadTemplateExcel")
    	public void downloadTemplateExcel(HttpServletResponse response) {
    		List<ExDrugDclaFilter> data = new ArrayList<>();
    		Map<Integer,String[]> mapDropDown = new HashMap<>();
    		//0未确认,1已确认,2已生成
    		String[] confirmStas = {"未确认","已确认","已生成"};
    		mapDropDown.put(4,confirmStas);
    		ExcelUtil.writeExcel(response, data,mapDropDown,"中选申报药品信息导入模板","sheet", ExDrugDclaFilter.class);
    	}
    
    	@PostMapping("/export")
    	@ApiOperation(value = "excel导出", notes = "Excel导出")
    	public void excelDownload(HttpServletResponse response, @RequestBody DrugDclaFilterDTO drugDclaFilterDTO) {
    		drugDclaFilterService.excelDownload(response,drugDclaFilterDTO);
    	}
    
    	@SneakyThrows
    	@PostMapping("/import")
    	@ApiOperation(value = "excel导入", notes = "excel导入")
    	public Result<String> excelImport(@RequestParam("file") MultipartFile file, @CurrentUserContext UserContext userContext) {
    		if (ObjectUtil.isEmpty(file)){
    			return Results.failure("file 为空!");
    		}
    		return drugDclaFilterService.excelImport(file.getInputStream(), userContext);
    	}
    service
        
    @Override
        @SneakyThrows
        public Result<String> excelImport(@NotNull InputStream inputStream, UserContext userContext) {
            String redisKey = "drugDclaFilterExcel"+ IdWorker.getIdStr();
            EasyExcel.read(inputStream, ExDrugDclaFilter.class, new DrugDclaFilterExcelDataListener(
                    drugDclaFilterBO, userContext, redisKey, cacheUtil)).sheet().doRead();
            String errStr = cacheUtil.get(redisKey,String.class);
            // 如果有异常信息返回失败
            if (StringUtils.isNotBlank(errStr)){
                log.debug(errStr);
                // 清空
                cacheUtil.del(redisKey);
                return Results.failure(errStr);
            }else {
                return Results.success("导入成功!");
            }
        }
    
        @SneakyThrows
        @Override
        public void excelDownload(HttpServletResponse response, DrugDclaFilterDTO drugDclaFilterDTO) {
            // 查询数据 最好根据查询日期查询 临时写死5001 测试 可以切sheet
            drugDclaFilterDTO.setSize(5001);
            List<DrugDclaFilterDTO> list = this.queryList(drugDclaFilterDTO);
            if (CollectionUtil.isNotEmpty(list)){
                ExcelUtil.writeExcel(response,DozerUtil.convertList(list, ExDrugDclaFilter.class),
                        "中选申报药品信息导出", ExDrugDclaFilter.class);
            }
        }
    dto
    
    @Data
    public class ExDrugDclaFilter {
    
        @ColumnWidth(15)
        private String tenditmId;
    }
    
    package cn.hsa.spp.std.listener;
    
    import cn.hsa.spp.comm.cache.CacheUtil;
    import cn.hsa.spp.comm.common.context.UserContext;
    import cn.hsa.spp.comm.common.exception.Result;
    import cn.hsa.spp.comm.util.DozerUtil;
    import cn.hsa.spp.comm.util.ValidationUtil;
    import cn.hsa.spp.std.bo.DrugDclaFilterBO;
    import cn.hsa.spp.std.dao.model.DrugDclaFilterDO;
    import cn.hsa.spp.std.dto.ExDrugDclaFilter;
    import cn.hutool.core.collection.CollectionUtil;
    import cn.hutool.core.util.ObjectUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.baomidou.mybatisplus.core.toolkit.IdWorker;
    import lombok.extern.slf4j.Slf4j;
    
    import java.time.LocalDateTime;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Optional;
    import java.util.concurrent.TimeUnit;
    
    /**
     * EXCEL导入的监听器
     *
     * @author songlc
     * @version V1.0
     * @since 2020-9-02 19:45
     */
    @Slf4j
    public class DrugDclaFilterExcelDataListener extends AnalysisEventListener<ExDrugDclaFilter> {
        /**
         * 每隔3000条,然后清理list ,方便内存回收
         */
        private static final int BATCH_COUNT = 3000;
    
        private DrugDclaFilterBO drugDclaFilterBO;
    
        /**
         * 用户信息
         */
        private UserContext userContext;
    
        /**
         * 缓存工具
         */
        private CacheUtil cacheUtil;
    
        /**
         * 用来存储异常信息
         */
        private String redisKey;
    
        /**
         * 异常信息
         */
        private StringBuffer errorStr = new StringBuffer();
    
        List<DrugDclaFilterDO> list = new ArrayList<>();
    
    
        List<DrugDclaFilterDO> listCheck = new ArrayList<>();
    
    
        public DrugDclaFilterExcelDataListener(DrugDclaFilterBO drugDclaFilterBO,UserContext userContext, String redisKey, CacheUtil cacheUtil) {
            this.drugDclaFilterBO = drugDclaFilterBO;
            this.userContext = userContext;
            this.redisKey = redisKey;
            this.cacheUtil = cacheUtil;
        }
    
        @Override
        public void invoke(ExDrugDclaFilter exDrugDclaFilter, AnalysisContext analysisContext) {
            // 逻辑判断 并添加到List容器中
            Result<String> result = ValidationUtil.fastFailValidate(exDrugDclaFilter);
            if (ObjectUtil.isNotEmpty(result) && result.getCode() == 0){
                DrugDclaFilterDO drugDclaFilterDO = DozerUtil.convert(exDrugDclaFilter, DrugDclaFilterDO.class);
                //Optional.ofNullable(drugDclaFilterDO).map(o -> list.add(o));
                Optional.ofNullable(drugDclaFilterDO).map(o -> listCheck.add(o));
            }else {
                errorStr.append("第")
                        .append(analysisContext.readRowHolder().getRowIndex())
                        .append("行:")
                        .append(result.getMessage())
                        .append(" ");
            }
            // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
    /*        if (list.size() >= BATCH_COUNT) {
                drugDclaFilterBO.saveBatch(list);
                // 存储完成清理 list
                list.clear();
            }*/
        }
    
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            // 收尾
    /*        if (CollectionUtil.isNotEmpty(list)){
                drugDclaFilterBO.saveBatch(list);
            }*/
            // 存缓存 调用方获取展示
            cacheUtil.set(redisKey,errorStr.toString(),60, TimeUnit.SECONDS);
            // 没有异常信息 调用导入功能
            if (StrUtil.isBlank(errorStr.toString())){
                if (CollectionUtil.isNotEmpty(listCheck)){
                    // 填充数据
                    listCheck.forEach(o -> this.convertBaseDo(o,userContext));
                    drugDclaFilterBO.saveOrUpdateAll(listCheck);
                }
            }
    
           /* Map<String, List<DrugDclaFilterDO>> collect = listCheck.parallelStream().collect(Collectors.groupingBy(o -> o.getDrugCode() + "_" +
                    o.getTenditmId() + "_0"));
    
            List<DrugDclaFilterDO> addList = new ArrayList<>();
            List<DrugDclaFilterDO> updateList = new ArrayList<>();
            for (Map.Entry<String,List<DrugDclaFilterDO>> entry : collect.entrySet()){
                if (entry.getValue().size() > 1){
                    updateList.addAll(entry.getValue());
                }else {
                    addList.addAll(entry.getValue());
                }
            }
            if (CollectionUtil.isNotEmpty(addList)){
                drugDclaFilterBO.saveBatch(list);
            }
            if (CollectionUtil.isNotEmpty(updateList)){
                drugDclaFilterBO.updateAll(updateList);
            }*/
        }
    
        /**
         * 转换
         * @param userContext
         */
        public DrugDclaFilterDO convertBaseDo(DrugDclaFilterDO t, UserContext userContext){
            t.setDrugInfoId(IdWorker.getIdStr());
            t.setConfirmStas("0");
            t.setRid(IdWorker.getIdStr());
            t.setCrterId(userContext.getUserId());
            t.setCrterName(userContext.getUserName());
            t.setCrteOptinsNo(userContext.getOrgId());
            t.setCrteTime(LocalDateTime.now());
            t.setOptTime(LocalDateTime.now());
            t.setOpterId(userContext.getUserId());
            t.setOpterName(userContext.getUserName());
            t.setOptinsNo(userContext.getOrgId());
            t.setUpdtTime(LocalDateTime.now());
            t.setInvdFlag("0");
            return t;
        }
    }
    

    工具类

    package cn.hsa.spp.comm.util;
    
    
    import cn.hsa.spp.comm.common.exception.ServiceException;
    import cn.hutool.core.collection.CollectionUtil;
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.write.builder.ExcelWriterBuilder;
    import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import lombok.SneakyThrows;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    /**
     * EXCEL的工具类
     *
     * @author liwenjun
     * @version V1.0
     * @since 2019-12-02 18:03
     */
    @Slf4j
    public class ExcelUtil {
    
        private static final int MAXROWS = 5000;
    
        /**
         * 生成EXCEL工具类
         *
         * @param fileName
         * @param sheetName
         * @param data
         * @param clazz
         * @throws FileNotFoundException
         */
        public static void expExcel(String fileName, String sheetName, List data, Class clazz) {
            // 生成EXCEL并指定输出路径
            OutputStream out = null;
            ExcelWriter excelWriter = null;
            try {
                out = new FileOutputStream(fileName);
                excelWriter = EasyExcel.write(out, clazz).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                excelWriter.write(data, writeSheet);
            } catch (Exception e) {
                throw new ServiceException("导出EXCEL异常");
            } finally {
                if (excelWriter != null) {
                    excelWriter.finish();
                }
                try {
                    if (out != null) {
                        out.close();
                    }
                } catch (IOException e) {
                    log.info(LogUtil.getBaseLog() + "生成EXCEL异常" + e.getMessage());
                }
            }
        }
    
        private ExcelUtil() {
        }
    
        /**
         * excel下载
         *
         * @param response  返回对象
         * @param fileName  文件名
         * @param sheetName 页签名称
         * @param head      对象
         * @param data      数据
         */
        public static void writeExcel(HttpServletResponse response, String fileName, String sheetName, Class head, List data) {
            try (OutputStream outputStream = response.getOutputStream()) {
                setRespHeader(response, fileName);
    
                EasyExcel.write(outputStream, head).sheet(sheetName).doWrite(data);
            } catch (IOException e) {
                log.error("excel下载失败", e);
                throw new ServiceException("excel下载失败");
            }
        }
    
        /**
         * 设置响应头
         *
         * @param response
         * @param fileName
         * @throws UnsupportedEncodingException
         */
        public static void setRespHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileNameUrl = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + fileNameUrl + ".xlsx");
            response.setHeader("filename", fileNameUrl + ".xlsx");
        }
    
        /**
         * 获取默认表头内容的样式
         * @return
         */
        private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
            /** 表头样式 **/
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色(浅灰色)
            // 可以参考:https://www.cnblogs.com/vofill/p/11230387.html
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            // 字体大小
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 10);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            /** 内容样式 **/
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 内容字体样式(名称、大小)
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontName("宋体");
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            //设置内容垂直居中对齐
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置内容水平居中对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            // 头样式与内容样式合并
            return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        }
    
        /**
         * 导出
         * @param response
         * @param data
         * @param fileName
         * @param sheetName
         * @param clazz
         * @throws Exception
         */
        public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: "+data.size()+"条。");
            setRespHeader(response,fileName);
            EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);
            log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
        }
    
        /**
         * 导出 下拉框
         * @param response
         * @param data
         * @param mapDropDown Map<Integer,String[]> key是列的index 从0开始  value 是数据集
         * @param fileName
         * @param sheetName
         * @param clazz
         */
        @SneakyThrows
        public static void writeExcel(HttpServletResponse response, List<?> data, Map<Integer,String[]> mapDropDown, String fileName, String sheetName, Class<?> clazz){
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: "+data.size()+"条。");
            setRespHeader(response,fileName);
            EasyExcel.write(response.getOutputStream(), clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(sheetName)
                    .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                    .registerWriteHandler(new TemplateCellWriteHandlerDate(mapDropDown))
                    .doWrite(data);
            log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
        }
    
        /**
         * @author QiuYu
         * @createDate 2020-11-16
         * @param response
         * @param data  查询结果
         * @param fileName 导出文件名称
         * @param clazz 映射实体class类
         * @param <T>  查询结果类型
         * @throws Exception
         */
        public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class<?> clazz) throws Exception {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: "+data.size()+"条。");
    
            //List<List<T>> lists = SplitListUtil.splitList(data,MAXROWS); // 分割的集合
            List<List<T>> lists = CollectionUtil.split(data, MAXROWS); // 分割的集合
            setRespHeader(response,fileName);
            OutputStream out = response.getOutputStream();
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
            ExcelWriter excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            if (CollectionUtil.isNotEmpty(lists)){
                for (int i =1;i<=lists.size();i++){
                    excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                    excelWriterSheetBuilder.sheetNo(i);
                    excelWriterSheetBuilder.sheetName("sheet"+i);
                    writeSheet = excelWriterSheetBuilder.build();
                    excelWriter.write(lists.get(i-1),writeSheet);
                }
            }
            out.flush();
            excelWriter.finish();
            out.close();
            log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
        }
    }
    

    展开全文
  • JAVA使用easyexcel导出excel

    万次阅读 2018-11-28 14:32:03
    JAVA使用easyexcel导出excel 导出excel的工具有很多,如poi,jxl等但是他们导出excel会比较的消耗内存,一旦数据大起来可能造成内存溢出,而easyexcel很好的避免了内存溢出的问题。 首先下载easyexcel的依赖包: ...

    导出excel的工具有很多,如poi,jxl等但是他们导出excel会比较的消耗内存,一旦数据大起来可能造成内存溢出,而easyexcel很好的避免了内存溢出的问题。

    首先下载easyexcel的依赖包:

    <dependency>
        	<groupId>com.alibaba</groupId>
        	<artifactId>easyexcel</artifactId>
        	<version>1.0.4</version>
    </dependency>
    

    java模型映射(省略了get和set方法):

    public class ClsModal extends BaseRowModel{
    	
    	@ExcelProperty(value="班级编号",index=0)
    	private Integer cid;
    	
    	@ExcelProperty(value="班级名称",index=1)
    	private String cname;
    

    导出类:

    package exportExcel;
    
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.util.List;
    
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.support.ExcelTypeEnum;
    
    public class ExportExcel {
    
    	public void export(List<ClsModal> clsList) {
    		try(OutputStream out=new FileOutputStream("C:\\Users\\hc\\Desktop\\exportCls.xlsx")) {
    			ExcelWriter writer=new ExcelWriter(out,ExcelTypeEnum.XLSX);
    			
    			if(!clsList.isEmpty()) {
    				/**
    				  * 1表示第1个工作表,
    				  * 0表示从第几行开始读取数据,最小值为0
    				  * clsList.get(0).getClass()表示要导出的实体类 
    				  * /
    				Sheet sheet=new Sheet(1,0,clsList.get(0).getClass());
    				
    				sheet.setSheetName("导出班级测试");
    				writer.write(clsList, sheet);
    			}
    			writer.finish();
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    }
    
    

    测试类:

    package exportExcel;
    
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExportTest {
    	public static void main(String[] args) {
    		//准备数据
    		List<ClsModal> clsList=new ArrayList<ClsModal>();
    		for(int i=0;i<3;i++) {
    			ClsModal cls=new ClsModal();
    			cls.setCid(i);
    			cls.setCname("java"+i);
    			clsList.add(cls);
    		}
    		
    		new ExportExcel().export(clsList);
    	}
    }
    
    

    测试结果:
    在这里插入图片描述
    如果是前后端分离,则只需要使用HttpServletResponse获得OutputStream流,然后再形成excel表,将其写入到输出流中,然后响应给前端即可。但注意要将响应的头设置成“Content-Disposition”。

    展开全文
  • 使用EasyExcel导出数据模板

    千次阅读 2021-04-09 17:34:16
    ​ 最近在做项目的时候用到了阿里的EasyExcel,用它来将数据导出到一个固定excel模板之中,主要是为了写一个送货单,其中有list的数据导出。 ​ 本人目前在实习阶段,有错误请指正,如有侵权,联系删除。 一、...
  • 最详细入门EasyExcelEasyExcel如此简单
  • 利用EasyExcel导出Excel的两种方式

    千次阅读 2020-10-25 16:05:44
    } 第一种是直接输出到指定的文件路径下: @RequestMapping("/test1") @LogMsg("输出文件路径下") public void test1() { String fileName = "D:/RESOURCE_ROOT/CoreResrcFile/" + "simpleWrite" + System....
  • EasyExcel导出Excel本地

    千次阅读 2021-03-22 22:29:07
    @ApiOperation(value = "导出Excel") public ResponseData exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { applyRecordService.exportExce..
  • 史上最全的Excel导入导出easyexcel

    万次阅读 多人点赞 2019-04-04 17:56:55
    文章目录环境搭建读取excel文件小于1000行数据默认读取指定读取大于1000行数据默认读取指定读取导出excle单个Sheet导出无模型映射导出模型映射导出多个Sheet导出 环境搭建 easyexcel 依赖(必须) springboot ...
  • Easyexcel导出并下载excel本地

    千次阅读 2020-09-22 18:48:28
    } 难点是读取和保存路径,在打包后不能正常,不要放项目中。 前端 function easyExcel() { var formatter = $("#formId").serialize(); console.log(formatter) if ($("#startTime").val() == "") { $.modal....
  • java easyexcel导出zip

    2021-01-03 18:41:57
    } /** * @Title: writeExcel * @Description: 写入excel本地路径 */ private void writeExcel(File newFile, Class<?> clazz, List<?> datalist,ExcelTypeEnum excelType) { EasyExcel.write(newFile, clazz)....
  • 用到easyexcel通过模板导出物料数据并在excel中具体位置插入尺寸图,研究了官方语雀文档,发现导出图片使用的是ImageData类封装一些图像位置信息和图像信息,但是搞来搞去发现老是会在最后一行第一格插入或者报什么...
  • 使用EasyExcel导出数据

    2021-07-29 17:09:49
    使用EasyExcel导出 下载模板 导入excel功能,都需要对应的模板导入,也有动态的表头数据导入,可以看excel行数据不规则解析匹配。这里一般都是固定excel文件,存放在项目路径下面,下载模板的时候都是找对应的文件。...
  • EasyExcel导出数据多个sheet并实现页面下载

    万次阅读 多人点赞 2020-01-17 16:28:02
    第一、Java解析Excel工具EasyExcel Java 程序员在项目上一般会经常遇到解析数据、生成Excel的需求,比较流行的就是Apache poi框架了,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些...
  • easyExcel导出多个sheet的excel

    千次阅读 2021-08-05 10:49:19
    easyExcel导出依赖新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个...
  • 第一步:添加easyExcel依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.2</version> </dependency> ...
  • easyExcel下载或导出

    2021-10-29 11:05:38
    easyexcel</artifactId> <version>3.0.1</version> 下载或导出Excel文件:只需要Excel对应的实体类 和 数据即可 Excel对应的实体类 @ContentRowHeight(20) // 内容的行高 @HeadRowHeight(20) /...
  • EasyExcel组件快速入门:导出Excel文件
  • 一、导入pom ...easyexcel</artifactId> <version>3.0.5</version> </dependency> 相关版本信息可去maven库查询,链接如下:https://mvnrepository.com/artifact/com.alibaba
  • easyexcel导出的默认样式是这样的,我不喜欢表头的灰色背景,感觉比较丑,而且有时候我们需要修改表头的背景颜色、字号、单元格格式等等。当默认的样式不能满足我们的需求时,就需要我们自行处理对应样式。
  • 作者对easyExcel的描述:Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,...
  • 在在多数的项目中都有对excel 文件进行数据导入,这里我们采用阿里开源框架进行excel 表格导入和导出 在使用easyexcel 时可以借鉴官方网站 前官网:Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 读...
  • 使用easyExcel导出百万级数据

    千次阅读 2019-07-17 18:55:32
    1:测试数据量两百二十多万,Excel2007,一个Excel多个sheet 2:导入maven依赖包 <dependency> <groupId>com.alibaba<...easyexcel</artifactId> <version>1.1.2-bet...
  • 1.demo 1.引入依赖 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba<...easyexcel</artifactId> <version>2
  • 用esayExcel做的导出在本地跑无问题,服务器上就找不文件。这个问题可以用下面这行代码解决 InputStream templateFileName = this.getClass().getClassLoader().getResourceAsStream("excel/" + templateName +...
  • //打开指定路径的excel,如果注释掉本行,相当于新建excel workbook.loadFromFile(fileName); //获取第一个sheet表格 Worksheet sheet = workbook.getWorksheets().get(0); //随机起一个名称 Random random = new ...
  • EasyExcel 问题合集:Excel 填充文件导出二进制流,资源文件夹模板文件读取
  • 首先为文件创建相对路径 ​ // 文件名 String fileName = "测试表格_".concat(DateUtil.nowDateTime(Constants.DATE_TIME_FORMAT_NUM)) .concat(BaixiangUtil.randomCount(111111111, 999999999).toString())....
  • easyexcel 多个sheet 导出,并且可以把批量下载附件,并打包压缩包。
  • easyExcel 非常方便解决数据量大的导入导出操作,解决性能的问题。并且其有丰富的应用功能,比如导入、导出多sheet。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 493
精华内容 197
关键字:

easyexcel导出到指定路径