【笑小枫的SpringBoot系列】【十三】JAVA使用EasyExcel导出excel |
您所在的位置：网站首页 › excel排序不好使了 › 【笑小枫的SpringBoot系列】【十三】JAVA使用EasyExcel导出excel |
|
功能背景
简单的说下这个功能的背景需求吧，有类似需求的可以复用，果然导入还没写完，导出的功能接踵而来，一块写了吧 实现excel导出（依旧废话...） 多个sheet页一起导出 第一个sheet页数据表头信息有两行 样式稍微美观，列宽可以自定义等 数据量稍微有些大（多个sheet页总量50w左右） 项目引入依赖如果是从上一篇看过来的，就不用看项目引入了🙈 gradle: compile "com.alibaba:easyexcel:3.1.0" 复制代码maven: com.alibaba easyexcel 3.1.0 复制代码注意： 3+版本的的easyexcel，使用poi 5+版本时，需要手动排除：poi-ooxml-schemas，例如： com.alibaba easyexcel 3.1.0 poi-ooxml-schemas org.apache.poi 复制代码 项目编码如多个表格样式相同，可编写一个父类，将样式定义在父类上，子类继承父类即可。 在config.bean.excel包下定义经销商信息对象ExportCompany.java，代码如下👇 package com.maple.demo.config.bean.excel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentFontStyle; import com.alibaba.excel.annotation.write.style.HeadFontStyle; import com.alibaba.excel.annotation.write.style.HeadStyle; import com.alibaba.excel.enums.poi.FillPatternTypeEnum; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.Date; /** * @author 笑小枫 * @date 2022/7/22 */ @Data @Builder @NoArgsConstructor @AllArgsConstructor @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) @HeadFontStyle(fontHeightInPoints = 12) @ContentFontStyle(fontHeightInPoints = 11) @ColumnWidth(20) public class ExportCompany { // -------------------- 基本信息 start ------------- @ExcelProperty({"基本信息", "公司名称"}) private String companyName; @ExcelProperty({"基本信息", "省份"}) private String province; @ExcelProperty({"基本信息", "成立时间"}) private Date startDate; @ExcelProperty({"基本信息", "企业状态"}) private String entStatus; @ColumnWidth(30) @ExcelProperty({"基本信息", "博客地址"}) private String csdnAddress; // ---------------- 基本信息 end --------------------- // ---------------- 经营信息 start --------------------- @ExcelProperty({"经营信息", "员工数"}) private String employeeMaxCount; @ExcelProperty({"经营信息", "网站地址"}) private String netAddress; @ExcelProperty({"经营信息", "所属区域省"}) private String businessProvinceName; @ExcelProperty({"经营信息", "所属区域市"}) private String businessCityName; @ExcelProperty({"经营信息", "所属区域区县"}) private String businessAreaName; // ---------------- 经营信息 end --------------------- } 复制代码 在config.bean.excel包下定义联系人信息对象ExportContact.java，代码如下👇 package com.maple.demo.config.bean.excel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentFontStyle; import com.alibaba.excel.annotation.write.style.HeadFontStyle; import com.alibaba.excel.annotation.write.style.HeadStyle; import com.alibaba.excel.enums.poi.FillPatternTypeEnum; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; /** * @author 笑小枫 * @date 2022/7/22 */ @Data @Builder @NoArgsConstructor @AllArgsConstructor @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) @HeadFontStyle(fontHeightInPoints = 12) @ContentFontStyle(fontHeightInPoints = 11) @ColumnWidth(20) public class ExportContact { @ExcelProperty("公司名称") private String companyName; @ExcelProperty("姓名") private String name; @ExcelProperty("身份证号码") private String idCard; @ExcelProperty("电话号码") private String mobile; @ExcelProperty("职位") private String contactPostName; } 复制代码 在controller包下编写TestExportExcelController.java进行测试，代码如下： package com.maple.demo.controller; import com.alibaba.excel.EasyExcelFactory; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.maple.demo.config.bean.excel.ExportCompany; import com.maple.demo.config.bean.excel.ExportContact; import io.swagger.annotations.Api; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author 笑小枫 * @date 2022/7/22 */ @Slf4j @RestController @RequestMapping("/example") @Api(tags = "实例演示-导出Excel") public class TestExportExcelController { @GetMapping("/exportExcel") public void exportExcel(HttpServletResponse response) { try (OutputStream out = response.getOutputStream()) { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("笑小枫测试导出", "UTF-8").replaceAll("\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); handleExcel(out); out.flush(); } catch (Exception e) { log.error(e.getMessage()); } } private void handleExcel(OutputStream out) { try (ExcelWriter excelWriter = EasyExcelFactory.write(out).build()) { WriteSheet dealerSheet = EasyExcelFactory.writerSheet(0, "经销商信息").head(ExportCompany.class).build(); WriteSheet contactSheet = EasyExcelFactory.writerSheet(1, "联系人").head(ExportContact.class).build(); excelWriter.write(getCompany(), dealerSheet); excelWriter.write(getContact(), contactSheet); } } private List getCompany() { List companyList = new ArrayList(); for (int i = 0; i |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |