怎么样制作一个公众号_兴化网页定制_域名ip查询查网址_网页设计收费标准

EasyExcel读操作

在这里插入图片描述

①、添加依赖(com.alibaba)

easyexcel

②、封装实体类

@Data
public class CategoryExcelVo{@ExcleProperty(value="id",index=0)private Long id;@ExcelProperty(value="名称",index=1)private String name;@ExcelProperty(value="图片url"，index=2)private String imageUrl;@ExcelProperty(value="上级id"，index=3)private Long parentId;@ExcelProperty(value="状态",index=4)private Integer status;@ExcelProperty(value="排序",index=5)private Integer orderNum;
}

③、定义一个监听器

public class ExcelListener<T> extends AnalysisEventListener<T>{//可以通过实例获取该值private List<T> datas = new ArrayList<>();//每解析一行数据，就会调用一次该方法//从第二行开始读取，将内容封装到t对象中@Overridepublic void invoke(T t,AnalysisContext anlaysisContext){dtas.add(t);//将每行数据存储到list，供批量处理，或后续业务逻辑处理}public List<T> getDatas(){return datas;}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext){//excel解析完毕以后需要执行的代码}
}

④、测试

public class EashExcelTest{@Testpublic void read(){String fileName = "C://01.xlsx";ExcelListener<CategoryExcelVo> excelListener = new ExcelListener();EasyExcel.read(fileName,CategoryExcelVo.class,excelListener).sheet().doRead();List<CategoryExcelVo> data = excelListener.getData();System.out.println(data);}
}

EasyExcel写操作

@Test
public void write(){List<CategoryExcelVo> list = new ArrayList<>();list.add(1L,"数码办公","",0L,1,1);list.add(11L,"华为手机","",1L,1,1);EasyExcel.write("C://02.xlsx",CategoryExcelVo.class).sheet("分类数据").doWrite(list);
}

项目中的应用

导出功能

将数据库的数据，写入到Excel中

在这里插入图片描述

前端整合

src/api/category.js

export const ExportCategoryData = ()=>{return request({url:'${api_name}/exportData',method:'get',responseType:'blob' //响应类型为blob，二进制数据类型})
}

category.vue

<div class="tools-div"><el-button type="success" size="small" @click="exportData">导出</el-button>
</div><script setup>import {FindCategoryByParentId,ExportCategoryData} from '@/api/category.js'const exportData = ()=>{ExportCategoryData().then(res=>{const blob = new Blob([res]);const link = document.createElement('a');link.href = window.URL.createObjectURL(blob);//设置下载文件的名称link.download = '分类数据.xlsx';link.click();})}
</script>

代码开发需求
在这里插入图片描述

@GetMapping("/exportData")
public void exportData(HttpServletResponse response){categoryService.exportData(response);
}

@Override
public void exportData(HttpServletResposne response){try{//1.设置响应头信息response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("分类数据","UTF-8");//防止中文乱码response.setHeader("Content-disposition","attachment;filename"+fileName+".xlsx");//2.查询数据库的数据List<Category> categoryList = categoryMapper.findAll();//list<Category> -> List<CategoryExcelVo>for(Category category:categoryList){CategoryExcelVo categoryExcelVo = new CategoryExcelVo();BeanUtils.copyProperties(category,categoryExcelVo);categoryExcelVoList.add(categoryExcelVo);}//3.调用EasyExcel的write方法完成写操作EasyExcel.write(response.getOutputStream(),CategoryExcelVo.class).sheet("分类数据").doWrite(categoryList);}catch(Exception e){e.printStackTrace();throw new GuiguException(ResultCodeEnum.DATA_ERROR);}
}

导入功能

将Excel表中的数据导入到数据库中

@PostMapping("/importData")
public ResultimportData(MultipartFile file){categoryService.importData(file);return Result.build(null,ResultCodeEnum);
}

@Override
public void importData(MultipartFile file){try{ExcelListener<CategoryExcelVo> excelListener = new ExcelListener(categoryMapper);EasyExcel.read(file.getInputStream(),CategoryExcelVo.class,excelListener).sheet().doRead();}catch(IOException e){e.printStackTrace()throw new GuiguException(ResultCodeEnum.DATA_ERROR);}
}

监听器，不能让spring管理

public class ExcelListener implements ReadListener<T>{//每隔5条存储数据库，实际使用者可以100条，然后清理list，方便内存回收private static final int BATCH_COUNT = 100;private List<T> cacheDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);//构造传递mapper，操作数据库private CategoryMapper categoryMapper;public ExcelListener(CategoryMapper categoryMapper){this.categoryMapper = categoryMapper;}//从第二行开始读取，把每行读取的内容封装到t对象@Overridepublic void invoke(T t,AnalysisContext analysisContext){cacheDataList.add(t);//把每行数对象t放到cacheDataList集合里面if(cachedDataList.size()>BATCH_COUNT){//达到BATCH_COUNT需要一次存储数据库，防止数据OOMsaveData();//调用方法一次性批量添加数据库里面cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);//重新创建对象，相当于清理list}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext){//如果没达到BATCH_COUNTsaveData();}private void saveData(){categoryMapper.batchInsert((List<CategoryExcelVo>) cacheDataList);}}

前端整合

<el-button type="primary" size="small" @click="importData">导入</el-button><el-dialod v-model="dislogImportVisible" title="导入" width="30%"><el-form label-width="120px"><el-form-item label="分类文件"><el-upload class="upload=demo"action="http://localhost:8501/admin/product/category/importData":on-success="onUploadSuccess":headers="headers"><el-button type="primary">上传</el-button></el-upload></el-form-item></el-form>
</el-dialog><script setup>import {useApp} from '@/pinia/modules/app'const dialogImportVisible = ref(false)const headers = {token:useApp().authorization.token //从pinia中获取token，进行文件上传时将token设置到请求头}const importData = ()=>{dialogImportVisible.value=true}//上传文件成功后执行方法const onUploadSuccess = async(response,file)=>{ElMessage.success('操作成功')dialogImportVisible.value=falseconst{data} = await FindCategoryByParentId(0)list.value=data;}
</script>