-
2022-01-14 14:44:03
一、环境
1、开发工具:idea2018.1
2、jar 管理:maven 3.3.3
3、项目类型:springboot
二、pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.6</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.ygq</groupId> <artifactId>apps</artifactId> <version>0.0.1-SNAPSHOT</version> <name>apps</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <!--springboot项目初始maven--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!--mysql连接--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-core</artifactId> <version>3.4.1</version> </dependency> <!--easyexcel 核心坐标-若整合到自己项目仅导入以下两个即可--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <dependency> <groupId>com.github.whvcse</groupId> <artifactId>easy-captcha</artifactId> <version>1.6.2</version> </dependency> <!--工具包BeanUtils--> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.6</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
三、代码
1、ExcelUtils (核心工具类)
import com.alibaba.excel.EasyExcel; import com.alibaba.excel.util.DateUtils; import com.ygq.apps.domain.Student; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.BeanUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * excel工具类 * */ public class ExcelUtils { /** * Excel导出 * * @param response response * @param fileName 文件名 * @param sheetName sheetName * @param list 数据List * @param pojoClass 对象Class */ public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class<?> pojoClass) throws IOException { if(StringUtils.isBlank(fileName)){ //当前日期 fileName = DateUtils.format(new Date()); } response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("UTF-8"); fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), pojoClass).sheet(sheetName).doWrite(list); } /** * Excel导出,先sourceList转换成List<targetClass>,再导出 * * @param response response * @param fileName 文件名 * @param sheetName sheetName * @param sourceList 原数据List * @param targetClass 目标对象Class */ public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList, Class<?> targetClass) throws Exception { List<Object> targetList = new ArrayList (sourceList.size()); for(Object source : sourceList){ Object target = targetClass.newInstance(); BeanUtils.copyProperties(source, target); targetList.add(target); } exportExcel(response, fileName, sheetName, targetList, targetClass); } public static List<Object> importExcelToTarget( MultipartFile file,Class<?> targetClass) throws Exception { List<Object> list = EasyExcel.read(file.getInputStream()).head(targetClass).sheet().doReadSync(); return list; } }
2、controller层接口
@RestController @RequestMapping("excel") public class EasyExcelController { @Autowired StudentService studentService; /** * 导入 * @param file */ @PostMapping("import") public void importExcel(@RequestParam("file") MultipartFile file) { try { List<Object> list = ExcelUtils.importExcelToTarget(file, Student.class); for (Object student : list) { Student stu= (Student) student; System.out.println(stu); } } catch (Exception e) { System.out.println("导入失败" + e); } } /** * 导出 * @param response */ @GetMapping("export") public void exporttExcel(HttpServletResponse response) { try { List<Student> list = studentService.list(); ExcelUtils.exportExcel(response,null,null,list,Student.class); } catch (Exception e) { System.out.println("导出失败" + e); } } @GetMapping("list") public List<Student> list() { List<Student> list = studentService.list(); System.out.println(list); return list; } }
3、实体注解配置方式
import com.alibaba.excel.annotation.ExcelProperty; import lombok.*; @Data @AllArgsConstructor @NoArgsConstructor public class Student { @ExcelProperty("用户id",index=0) private Long id; @ExcelProperty("姓名",index=1) private String name; @ExcelProperty("年龄",index=2) private Integer age; @ExcelProperty("性别",index = 3,converter = SexConverter.class) private Integer sex; @ExcelProperty("地址",index=4)//导出Excel表格的字段名称 private String address; @ExcelIgnore//导出时忽略某个字段 private Long product_id; }
字段转换
import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.converters.Converter; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.property.ExcelContentProperty; public class SexConverter implements Converter<Integer> { @Override public Class supportJavaTypeKey() { return null; } @Override public CellDataTypeEnum supportExcelTypeKey() { return null; } @Override public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { return null; } @Override public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { if(integer == 1){ return new CellData("男"); }else { return new CellData("女"); } } }
四、多sheet导入导出
4.1 多sheet导出
public class Test{ @Data @ColumnWidth(20) public static class TestVO { @ExcelProperty( value = "姓名",index = 0) private String name; @ExcelProperty( value = "年龄",index = 1) private int age; @ExcelProperty( value = "学校",index = 2) private String school; } /** * 多个sheet导入测试 * @throws FileNotFoundException */ @Test public void sheetImport() throws FileNotFoundException { // 输出流 OutputStream outputStream = null; outputStream = new FileOutputStream(new File("D:/1.xlsx")); // 导出的数据 List<TestVO> dataList = new ArrayList<>(); for (int i = 0; i < 10; i++) { TestVO testVO = new TestVO(); testVO.setAge(i + 20); testVO.setName("vo" + i); testVO.setSchool("school" + i); dataList.add(testVO); } // 标题 List<String> headList = Arrays.asList("姓名", "年龄", "学校"); // 测试多sheel导出 ExcelWriter excelWriter = EasyExcel.write(outputStream).build(); WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class).build(); WriteSheet test2 = EasyExcel.writerSheet(1, "test2").head(TestVO.class).build(); excelWriter.write(dataList,test1).write(dataList,test2); excelWriter.finish(); } }
如果导出需加样式, 示例
// 表头样式 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 单元格样式 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 初始化表格样式 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); WriteSheet test1 = EasyExcel.writerSheet(0, "test1").head(TestVO.class). registerWriteHandler(horizontalCellStyleStrategy).build();
4.2 多sheet导入
public class Test{ @Data @ColumnWidth(20) public static class TestVO { @ExcelProperty( value = "姓名",index = 0) private String name; @ExcelProperty( value = "年龄",index = 1) private int age; @ExcelProperty( value = "学校",index = 2) private String school; } @Data @ColumnWidth(20) public static class TestVO1 { @ExcelProperty( value = "姓名",index = 0) private String name; @ExcelProperty( value = "年龄",index = 1) private int age; @ExcelProperty( value = "学校",index = 2) private String school; } /** * 测试导入多个sheet导入 * @throws Exception */ @Test public void read() throws Exception { String filePath = "D:/1.xlsx"; InputStream inputStream = null; inputStream = new FileInputStream(new File(filePath)); AnalysisEventListenerImpl<Object> listener = new AnalysisEventListenerImpl<>(); ExcelReader excelReader = EasyExcel.read(inputStream,listener).build(); // 第一个sheet读取类型 ReadSheet readSheet1 = EasyExcel.readSheet(0).head(TestVO.class).build(); // 第二个sheet读取类型 ReadSheet readSheet2 = EasyExcel.readSheet(1).head(TestVO1.class).build(); // 开始读取第一个sheet excelReader.read(readSheet1); List<Object> list = listener.getDatas(); list.forEach((user)->{ TestVO user1= (TestVO) user; System.out.println(user1.getName()+", "+user1.getAge()+", "+user1.getSchool()); }); // 清空之前的数据 listener.getDatas().clear(); // 开始读取第二个sheet excelReader.read(readSheet2); System.out.println("---------------------------------"); List<Object> list2 = listener.getDatas(); list2.forEach((user)->{ TestVO1 user2= (TestVO1) user; System.out.println(user2.getName()+", "+user2.getAge()+", "+user2.getSchool()); }); } }
更多相关内容 -
java使用EasyExcel导入导出excel(csdn)————程序.pdf
2021-12-01 22:37:35java使用EasyExcel导入导出excel(csdn)————程序 -
EasyExcel导入导出
2022-06-27 20:57:02EasyExcel导入导出Spring Boot + EasyExcel 导入导出
一 常用注解
@ExcelProperty 指定当前字段对应excel中的那一列。可以根据名字或者Index去匹配。当然也可以不写,默认第一个字段就是index=0,以此类推。千万注意,要么全部不写,要么全部用index,要么全部用名字去匹配。千万别三个混着用,除非你非常了解源代码中三个混着用怎么去排序的。 @ExcelIgnore 默认所有字段都会和excel去匹配,加了这个注解会忽略该字段 @DateTimeFormat 日期转换,用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat @NumberFormat 数字转换,用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat @ExcelIgnoreUnannotated默认不加ExcelProperty 的注解的都会参与读写,加了不会参与
二 依赖
<!-- easyexcel 主要依赖 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.4</version> </dependency>
三 监听
/** * EasyExcel 导入监听 */ public class ExcelListener extends AnalysisEventListener { //可以通过实例获取该值 private List<Object> datas = new ArrayList<Object>(); @Override public void invoke(Object o, AnalysisContext analysisContext) { datas.add(o);//数据存储到list,供批量处理,或后续自己业务逻辑处理。 doSomething(o);//根据自己业务做处理 } private void doSomething(Object object) { //1、入库调用接口(得到数据后进行处理(存储到数据库)) } //返回结果集对象 public List<Object> getDatas() { return datas; } public void setDatas(List<Object> datas) { this.datas = datas; } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // datas.clear();//解析结束销毁不用的资源 } }
注意
监听器中不能直接使用@Autowired和@Resource注入,要使用构造方法 /** * 提供带参构造方法,在这里需要通过构造方法的方式获取对应的service层 * 谁调用这个监听器谁提供需要的service * @param clueService */ public ExcelListener(ITbClueService clueService) { this.clueService = clueService; this.resultDTO = new ImportResultDTO(); }
四 实体类(用于封装excel的每一行数据)
@Data public class TbClueExcelVo{ /** 客户手机号 手机号(11位手机号,不可有空格) */ @ExcelProperty(value = "手机号(11位手机号,不可有空格)",index = 0) private String phone; /** 渠道 */ @ExcelProperty(value = "渠道来源",index = 1) private String channel; /** 活动编号 (来源于活动列表8位字母或数字)*/ @ExcelProperty(value = "活动编号(来源于活动列表8位字母或数字)",index = 2) private String activityCode; /** "客户姓名 **/ @ExcelProperty(value = "客户姓名",index = 3) private String name; /** 意向学科 */ @ExcelProperty(value = "意向学科",index = 4) private String subject; /** 意向级别 */ @ExcelProperty(value = "意向级别",index = 5) private String level; /** 性别 */ @ExcelProperty(value = "性别",index = 6) private String sex; /** 年龄 */ @ExcelProperty(value = "年龄",index = 7) private Long age; }
五 controller
public R importData(MultipartFile file) throws Exception { ExcelListener excelListener = new ExcelListener(tbClueService);//tb..Service防止空指针 EasyExcel.read(file.getInputStream(), TbClueExcelVo.class, excelListener).sheet().doRead(); return R.success(excelListener.getDatas()); }
六、接口导出Excel (HttpServletResponse response, HttpServletRequest request)
hutool工具类实现
try { String filenames = "111111"; String userAgent = request.getHeader("User-Agent"); if (userAgent.contains("MSIE") || userAgent.contains("Trident")) { filenames = URLEncoder.encode(filenames, "UTF-8"); } else { filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1"); } response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx"); EasyExcel.write(response.getOutputStream(), Test.class) .sheet("sheet") //此步骤是开启EasyEXCEL自适应列宽 .registerWriteHandler(new CustomCellWriteHandler()) .doWrite(testList); } catch (Exception e) { }
自适应列宽
/** * EasyEXCEL自适应列宽 * 在导出时注册registerWriteHandler(new CustomCellWriteHandler()) */ public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > 255) { columnWidth = 255; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }
七 本地导入、导出
List<Test> testList = new ArrayList<>(); try { String strUrl = "C:\\Users\\Administrator\\Desktop\\json.xlsx"; File multipartFile = new File(strUrl); InputStream inputStream = new FileInputStream(multipartFile); //实例化实现了AnalysisEventListener接口的类 ExcelListener listener = new ExcelListener(); //传入参数 ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener); //读取信息 excelReader.read(new Sheet(1, 0, Test.class)); //获取数据 List<Object> list = listener.getDatas(); if (list.size() > 1) { for (int i = 0; i < list.size(); i++) { Testobj = (Test) list.get(i); } } } catch (Exception e) { System.out.println(e.getMessage()); } try { String strUrl = "C:\\Users\\Administrator\\Desktop\\json"+System.currentTimeMillis()+".xlsx"; EasyExcel.write(strUrl,Test.class).sheet("sheet").doWrite(testList); } catch (Exception e) { }
-
easyexcel导入导出
2022-05-15 17:23:58'收起' : '展开' }} 查询 重置 列表 导入模板下载 导入 导出 import request from '@/utils/request' import dowrequest from "@/utils/downloadrequest"; /** * 下载导入模板 * @param {*} id */ export function ...<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" ); } }
-
easyExcel导入导出
2021-06-03 21:30:46项目中会遇到导入Excel的需求,使用POI的话可能会比较麻烦,最近使用阿里的easyExcel过程记录下。下例为本地环境测试: 一、新建Springboot项目并添加依赖 easyExcel依赖: <dependency> <groupId>...项目中会遇到导入Excel的需求,使用POI的话可能会比较麻烦,最近使用阿里的easyExcel过程记录下。下例为本地环境测试:
一、新建Springboot项目并添加依赖
easyExcel依赖:
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency>
lombok依赖(不是必需):
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency>
二、新建Model
@Data public class Demo extends BaseRowModel { @ExcelProperty(index = 0, value = "ID") private String id; @ExcelProperty(index = 1,value = "姓名") private String name; @ExcelProperty(index = 2,value = "地址") private String address; }
说明:easyExcel是根据index读取Excel中的每列数据,value可以不用写,但是为了清晰建议写上;如果给前端返回时也用此model,会返回cellStyleMap什么的,可以用@jsonIgnore注解忽略掉。
本地Excel截图:
三、编写相关utils
ExcelUtil
public class ExcelUtil<T> { /*** * 读取Excel方法 * 要求类必须继承BaseRowModel */ public static <T extends BaseRowModel> ArrayList<T> readExcel(MultipartFile excel, Class<T> clazz) { try { InputStream in = new BufferedInputStream(excel.getInputStream()); GeneralExcelListener<T> listener = new GeneralExcelListener<>(); ExcelReader excelReader = new ExcelReader(in, ExcelTypeEnum.XLS, listener); excelReader.read(new Sheet(1, 1, clazz)); return listener.getSuccessDatas(); } catch (Exception e) { return new ArrayList<T>(); } } /** * 导出 Excel :一个 sheet,带表头 * * @param response HttpServletResponse * @param list 数据 list,每个元素为一个 BaseRowModel * @param fileName 导出的文件名 * @param sheetName 导入文件的 sheet 名 * @param object 映射实体类,Excel 模型 */ public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, object.getClass()); sheet.setSheetName(sheetName); writer.write(list, sheet); writer.finish(); } /** * 导出文件时为Writer生成OutputStream */ private static OutputStream getOutputStream(String fileName, HttpServletResponse response) { try { return response.getOutputStream(); } catch (IOException e) { throw new RuntimeException("导出文件时为Writer生成OutputStream失败!"); } } /** * 返回 ExcelReader * * @param excel 需要解析的 Excel 文件 * @param excelListener new ExcelListener() */ private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) { String filename = excel.getOriginalFilename(); if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) { throw new RuntimeException("文件格式错误!"); } InputStream inputStream; try { inputStream = new BufferedInputStream(excel.getInputStream()); return new ExcelReader(inputStream, null, excelListener, false); } catch (IOException e) { e.printStackTrace(); } return null; } }
ExcelListener
public class ExcelListener extends AnalysisEventListener { @Override public void invoke(Object o, AnalysisContext analysisContext) { } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }
GeneralExcelListener
public class GeneralExcelListener<T> extends AnalysisEventListener<T> { private final Logger logger = LoggerFactory.getLogger(this.getClass()); //自定义用于暂时存储data。 //可以通过实例获取该值 private ArrayList<T> successDatas = new ArrayList<>(); public GeneralExcelListener() { } @Override public void invoke(T o, AnalysisContext analysisContext) { //此处是读取Excel每行数据,可通过analysisContext进行分析 successDatas.add(o); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } public ArrayList<T> getSuccessDatas() { return successDatas; } }
四、使用示例
导入示例
@PostMapping(value = "/import") public ModelAndView import(@RequestParam(value = "file", required = true) MultipartFile file) { ModelAndView mv = new ModelAndView(); //一行代码即可获取解析得到的Excel中的数据 List<Test> datas = ExcelUtil.readExcel(file, Test.class); //然后对获取的数据,在service层进行业务处理 ResultMsg result = provShareConfigService.insertList(datas); return mv; }
导出示例
@PostMapping(value = "/export") public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject ob) { //前端传来的参数 String date = ob.getString("date"); String fileName = "退赔导出"; String sheetName = "sheet1"; //从数据库得到的list集合 List<Test> list = testService.selectAll(date); //一行代码即可导出 ExcelUtil.writeExcel(response, list, fileName, sheetName, new Test()); }
五、编写测试用例(导入)
由于前端提供的一般都是MultipartFile,本地测试的话进行了转换。
@Test public void test2() throws IOException { File file = new File("C:\\Users\\Light\\Desktop\\demo.xlsx"); FileInputStream inputStream = new FileInputStream(file); MultipartFile multipartFile = new MockMultipartFile(file.getName(),file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(),inputStream); //使用ExcelUtil读取 List<Demo> datas = ExcelUtil.readExcel(multipartFile, Demo.class); System.out.println(datas); }
执行后可以看到控制台打印信息:
[Demo(id=1, name=张三, address=北京), Demo(id=2, name=李四, address=上海), Demo(id=3, name=王五, address=广州), Demo(id=4, name=田六, address=深圳)]
拿到转换后的集合,可以对数据进行处理,然后进行持久化操作。导出自己可以做完持久化后进行尝试。
参考网上教程: 原文地址
-
EasyExcel导入导出工具类
2022-06-02 13:02:30/** * @Author: 海绵宝宝 * @Explain: easyExcel导入导出工具类 * @DateTime: 2022/6/2 12:58 * @Params: * @Return */ @Slf4j public class ExcelUtil { public ExcelUtil() { } /** * 导出execl * * @param ... -
java使用EasyExcel导入导出excel
2022-03-02 21:43:58一、准备工作 1、导包 | <``dependency``> ... } } | 以上,就生成完毕了 四、结果 以上就是java使用EasyExcel导入导出excel的详细内容,更多关于java 用EasyExcel导入导出excel的资料请关注脚本之家其它相关文章! -
Java easyexcel 导入导出总结
2022-03-18 09:36:120.前提-----引入依赖 <...easyexcel</artifactId> <version>2.1.1</version> </dependency> 1.导入 1.实体 package org.mnur.marketing.api.dto.vo; import -
简单好用的EasyExcel导入导出工具类
2022-05-26 14:37:48在Spring-Boot项目中引入EasyExcel依赖, <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </... -
EasyExcel导入导出下载模板(带下拉)
2022-03-22 16:13:59EasyExcel导入导出网上资料很多,这里就不多做描述了,总之就是一款比较轻而小,易用的excel操作工具包;这里在项目中使用到,做下笔记。以及表格中下拉框的实现; 声明:本文思路是借鉴于某大神的,然后自己进行二... -
Spring Boot + EasyExcel 导入导出,好用到爆!
2022-01-01 00:21:22点击关注公众号,利用碎片时间学习来源:www.jianshu.com/p/4e6aa6342b33EasyExcel是阿里巴巴开源poi插件之一,主要解决了poi框架使用复杂,sax解析模... -
EasyExcel导入导出详解
2020-11-20 13:47:40先说POI,有过报表导入导出经验的同学,应该听过或者使用。 Apache POI是Apache软件基金会的开源函式库,提供跨平台的Java API实现Microsoft Office格式档案读写。但是存在如下一些问题: 1.1 学习使用成本较高 对... -
easyexcel导入导出简单实现
2021-06-07 15:19:41easyexcel的开发文档地址:https://www.yuque.com/easyexcel/doc/quickstart 简单前端页面搭建效果: 前端一个vue页面代码,注意用的是elementui组件 <template> <div id="app"> <el-container&... -
使用EasyExcel导入导出Excel
2022-05-11 00:17:22使用EasyExcel导入导出Excel 1、官方网站 https://github.com/alibaba/easyexcel 快速开始:https://www.yuque.com/easyexcel/doc/easyexcel 使用场景 在日常开发中 我们难免需要导入数据 可以用EasyExcel来解决 2... -
Spring Boot + EasyExcel导入导出,简直太好用了!
2022-08-01 07:09:52在EasyExcel执行write方法之后,获得ExcelWriterBuilder类,通过该类的registerWriteHandler方法可以设置一些处理策略。/***设置单元格样式(仅用于示例)**@return样式策略*///表头策略//表头水平对齐居中//背景色//... -
Spring Boot + EasyExcel 导入导出,好用到爆,可以扔掉 POI 了
2021-11-22 22:49:15官方doc地址:https://www.yuque.com/easyexcel/doc/easyexcelEasyExcel优势注解式自定义操作。输入输出简单,提供输入输出过程的接口支持一定程度的单元格合并等灵活化操作二、常用注解@ExcelProperty指定当前字段... -
SpringBoot集成EasyExcel导入导出excel表格
2021-12-25 17:17:251.demo 1.引入依赖 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba<...easyexcel</artifactId> <version>2 -
Springboot 整合 easyexcel导入导出excel
2020-04-19 15:40:011. 引入核心依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> ... -
EasyExcel导入导出excel工具类
2019-12-07 16:51:54easyexcel依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beat1</v... -
easyExcel导入导出,插入数据到数据库(实现下拉菜单)
2021-08-13 16:48:18首先,引入easyExcel的依赖包 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency> ... -
Springboot整合easyExcel导入导出Excel
2019-12-10 19:30:12最近公司有个需求要求可以导入、导出excel,因此在此记录学习一下如何使用Springboot整合easyExcel; 需求: 数据库中有张user表,有个业务要求可以导入、导出“用户名单.xls”表 一、准备: 创建项目: 关于... -
Easyexcel导入导出多个sheet
2021-05-07 13:41:33EasyExcel对于导入导出的操作十分简洁,记录一下多个sheet且内容不一致的导入导出。 导出下载 提示:其中部分代码操作Dao层可以删除,可以自己创建ExportUserExcel 对象进行测试,思路数据映射到excel中。 这里... -
快速入门,使用EasyExcel导入导出文件
2019-08-30 10:36:12maven依赖包 <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <...easyexcel</artifactId> ... -
Springboot+EasyExcel导入导出
2020-05-28 18:58:13为什么要用EasyExcel 由于apache poi和jxl,excelPOI都有一个严重的问题,就是非常消耗内存,特别处理数据量多时,速度慢并且时有异常发生,所以改用由阿里研发的easyExcel更可靠一些,它的官方建议对于1000行以内的... -
SpringBoot使用easyExcel导入导出Excel文件
2020-12-31 13:48:23SpringBoot使用easyExcel导入导出Excel文件 一、项目原因 最近公司业务上有导入导出Excel的需求,自己有点看不懂,于是上网看了一些大神的思路,学会了一些,给大家分享一下。 二、项目的思路 Excel导入 浏览文件夹...
收藏数
4,420
精华内容
1,768