前言
EasyExcel 是一款基于Java的开源Excel操作工具，它提供了简单且强大的 API，使开发人员可以轻松地读写、操作和生成Excel文件。
EasyExcel 支持 Excel 文件的导入和导出，可以处理大量数据，具有高性能和低内存占用。它可以读取 Excel 文件中的数据，并将数据转换为 Java 对象，也可以将Java对象写入Excel文件。
EasyExcel 还提供了丰富的格式化选项和功能，如设置单元格样式、合并单元格、设置公式等。同时，EasyExcel 还支持多线程操作，可以在处理大量数据时提高处理效率。由于其简单易用的特点，EasyExcel 被广泛应用于数据导入导出、报表生成、数据分析等领域。
一、引入easyexcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
<exclusions>
<exclusion>
<groupId>poi-ooxml-schemas</groupId>
<artifactId>org.apache.poi</artifactId>
</exclusion>
</exclusions>
</dependency>二、创建实体类
package com.ruoyi.exportData.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@ColumnWidth(25)
@ContentRowHeight(30)
@HeadRowHeight(50)
@Builder
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
public class ExportListVo {
@ExcelProperty(value = "名称")
private String name;
@ExcelProperty(value = "数据")
private String value;
}
easyexcel常用注解
@ExcelProperty 用于标识excel中的字段，可以指定字段在Excel中的列索引或列名
@ColumnWith:：设置列宽
@ColumnWidth： 全局列宽
@ContentFontStyle： 用于设置单元格内容字体格式的注解
@ContentLoopMerge：用于设置合并单元格
@ContentRowHeight：用于设置行高
@ContentStyle：设置内容格式
@HeadFontStyle：用于定制标题字体格式
@HeadRowHeight：设置标题行行高
@HeadStyle：设置标题样式
@ExcelIgnore：不将该字段转换成Excel
@ExcelIgnoreUnannotated：没有注解的字段都不转换
三、controller层接口
package com.ruoyi.web.controller.zx.export;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.ruoyi.baseconsulttopics.service.IBaseconsultTopicsService;
import com.ruoyi.committee.service.ICommitteeService;
import com.ruoyi.common.domain.ResultVo;
import com.ruoyi.document.service.IDocumentService;
import com.ruoyi.exportData.vo.ExportListVo;
import com.ruoyi.meet.service.IMeetService;
import com.ruoyi.network.service.INetworkService;
import com.ruoyi.news.service.INewsService;
import com.ruoyi.proposal.service.IProposalService;
import com.ruoyi.publicopinion.service.IPublicopinionService;
import com.ruoyi.scholarly.service.IScholarlyLearningService;
import com.ruoyi.scholarly.service.IScholarlyReadService;
import com.ruoyi.transformdocument.service.TransformDocumentService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/export")
@Slf4j
public class ExportDataIndexController {
@Autowired
private IProposalService proposalService;
@Autowired
private IPublicopinionService publicopinionService;
@Autowired
private INetworkService networkService;
@Autowired
private ICommitteeService committeeService;
@Autowired
private IMeetService meetService;
@Autowired
private IDocumentService documentService;
@Autowired
private INewsService newsService;
@Autowired
private IScholarlyLearningService scholarlyLearningService;
@Autowired
private TransformDocumentService transformDocumentService;
@Autowired
private IBaseconsultTopicsService baseconsultTopicsService;
@GetMapping("/exportDataIndex")
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()) {
//设置内容样式
WriteCellStyle contentStyle = new WriteCellStyle();
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//居中
contentStyle.setWrapped(true);//自动换行
//设置头部样式
WriteCellStyle headerStyle = new WriteCellStyle();
headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置策略
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headerStyle,contentStyle);
WriteSheet proposalSheet = EasyExcelFactory.writerSheet(0, "委员提案").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet publicopinionSheet = EasyExcelFactory.writerSheet(1, "社情民意").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet consultationSheet = EasyExcelFactory.writerSheet(2, "协商议政").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet committeeSheet = EasyExcelFactory.writerSheet(3, "委员信息").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet meetSheet = EasyExcelFactory.writerSheet(4, "会议活动").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet documentSheet = EasyExcelFactory.writerSheet(5, "公文流转").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
WriteSheet provinceSheet = EasyExcelFactory.writerSheet(6, "云南全省政协").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
excelWriter.write(getProposal(), proposalSheet);
excelWriter.write(getPublicopinion(), publicopinionSheet);
excelWriter.write(getConsultation(),consultationSheet);
excelWriter.write(getCommittee(),committeeSheet);
excelWriter.write(getMeet(),meetSheet);
excelWriter.write(getDocument(),documentSheet);
excelWriter.write(getProvinceWide(),provinceSheet);
}
}
//首页-委员提案
private List<ExportListVo> getProposal() {
List<ExportListVo> list = new ArrayList<>();
int proposalCount = proposalService.proposalCount(null, area);
List<ResultVo> proposalKind = proposalService.proposalKind(null, area);
ExportListVo vo = new ExportListVo("委员提案总数",proposalCount+" 条");
list.add(vo);
for (ResultVo result : proposalKind) {
ExportListVo kind = new ExportListVo();
kind.setName(result.getName());
kind.setValue(result.getValue()+" 条");
list.add(kind);
}
return list;
}
//首页-社情民意
private List<ExportListVo> getPublicopinion() {
List<ExportListVo> list = new ArrayList<>();
int opinionCount = publicopinionService.opinionCount(null, area, null);
List<ResultVo> opinionType = publicopinionService.opinionType(null, area);
ExportListVo vo = new ExportListVo("社情民意总数",opinionCount+" 条");
list.add(vo);
for (ResultVo result : opinionType) {
ExportListVo type = new ExportListVo(result.getName(),result.getValue()+" 条");
list.add(type);
}
return list;
}
//首页-网络议政/远程协商
public List<ExportListVo> getConsultation(){
List<ExportListVo> list = new ArrayList<>();
List<ResultVo> netWorkCount = networkService.netWorkCount(null, area);
List<ResultVo> remoteCount = networkService.remoteCount(null, area);
List<ResultVo> networkConduct = networkService.networkConduct(area);
List<ResultVo> networkClosed = networkService.networkClosed(area);
List<ResultVo> remoteConduct = networkService.remoteConduct(area);
List<ResultVo> remoteClosed = networkService.remoteClosed(area);
ExportListVo vo = new ExportListVo("网络议政总数",netWorkCount.get(0).getValue()+" 条");
ExportListVo vo1 = new ExportListVo("远程协商总数",remoteCount.get(0).getValue()+" 条");
ExportListVo vo2 = new ExportListVo("网络议政-进行中",networkConduct.get(0).getValue()+" 条");
ExportListVo vo3 = new ExportListVo("网络议政-已结束",networkClosed.get(0).getValue()+" 条");
ExportListVo vo4 = new ExportListVo("远程协商-进行中",remoteConduct.get(0).getValue()+" 条");
ExportListVo vo5 = new ExportListVo("远程协商-已结束",remoteClosed.get(0).getValue()+" 条");
list.add(vo);
list.add(vo1);
list.add(vo2);
list.add(vo3);
list.add(vo4);
list.add(vo5);
return list;
}
//首页-委员信息
public List<ExportListVo> getCommittee(){
List<ExportListVo> list = new ArrayList<>();
int committeeCount = committeeService.committeeCount(area,null);//委员人数
int standingCommitteeCount = committeeService.standingCommmitteeCount(area,null);//常委人员
int officeCount = committeeService.officeCount(area);//机关人
ExportListVo vo = new ExportListVo("委员人数",committeeCount+" 人");
ExportListVo vo1 = new ExportListVo("常委人数",standingCommitteeCount+" 人");
ExportListVo vo2 = new ExportListVo("机关人数",officeCount+" 人");
list.add(vo);
list.add(vo1);
list.add(vo2);
List<ResultVo> partiesCount = committeeService.partiesCount(area,null);//委员构成
List<ResultVo> ageCount = committeeService.ageCount(area,null);//年龄
List<ResultVo> genderCount = committeeService.genderCount(area,null);//性别
List<ResultVo> nationCount = committeeService.nationCount(area,null);//民族
list.add(new ExportListVo("党派",null));
for (ResultVo result : partiesCount) {
ExportListVo parties = new ExportListVo(result.getName(), result.getValue() + " 人");
list.add(parties);
}
list.add(new ExportListVo("年龄",null));
for (ResultVo result : ageCount) {
ExportListVo age = new ExportListVo(result.getName(), result.getValue() + " 人");
list.add(age);
}
list.add(new ExportListVo("性别",null));
for (ResultVo result : genderCount) {
ExportListVo gender = new ExportListVo(result.getName(), result.getValue() + " 人");
list.add(gender);
}
list.add(new ExportListVo("民族",null));
for (ResultVo result : nationCount) {
ExportListVo nation = new ExportListVo(result.getName(), result.getValue() + "人");
list.add(nation);
}
return list;
}
//首页-会议活动
public List<ExportListVo> getMeet(){
List<ExportListVo> list = new ArrayList<>();
List<ResultVo> meetCount = meetService.meetCount(null,area);
list.add(new ExportListVo("会议活动总数",meetCount.get(0).getValue()+" 次"));
List<ResultVo> yearCount = meetService.yearCount(area);
for (ResultVo result : yearCount) {
list.add(new ExportListVo(result.getName(),result.getValue()+" 次"));
}
return list;
}
//首页-公文流转
public List<ExportListVo> getDocument(){
List<ExportListVo> list = new ArrayList<>();
int documentCount = documentService.documentCount(null, area);
list.add(new ExportListVo("公文流转总数",documentCount+" 条"));
List<ResultVo> yearCount = documentService.yearCount(null, area);
for (ResultVo result : yearCount) {
list.add(new ExportListVo(result.getName(),result.getValue()+" 条"));
}
return list;
}
//首页-云南全省政协
public List<ExportListVo> getProvinceWide(){
List<ExportListVo> list = new ArrayList<>();
int committeeCount = committeeService.committeeCount(area,null);//委员人数
int standingCommitteeCount = committeeService.standingCommmitteeCount(area,null);//常委人员
int officeCount = committeeService.officeCount(area);//机关人
int opinionCount = publicopinionService.opinionCount(null, area, null);//社情民意
int newsCount = newsService.newsCount(null, area);//政协新闻
int proposalCount = proposalService.proposalCount(null, area);//委员提案
List<ResultVo> netWorkCount = networkService.netWorkCount(null, area);//网络议政
List<ResultVo> remoteCount = networkService.remoteCount(null, area);//远程协商
Integer readCount = scholarlyLearningService.getCount("", area);
List<ResultVo> meetCount = meetService.meetCount(null,area);//会议活动
int documentCount = documentService.documentCount(null, area);//公文流转
int exchangeDocumentCount = transformDocumentService.exchangeDocumentCount();//公文交换
int baseTopicCount = baseconsultTopicsService.baseTopicCount(area);//协商在基层
list.add(new ExportListVo("委员人数",committeeCount+" 人"));
list.add(new ExportListVo("常委人数",standingCommitteeCount+" 人"));
list.add(new ExportListVo("机关人数",officeCount+" 人"));
list.add(new ExportListVo("政协新闻",newsCount+" 条"));
list.add(new ExportListVo("社情民意",opinionCount+"条"));
list.add(new ExportListVo("委员提案",proposalCount+" 条"));
list.add(new ExportListVo("网络议政",netWorkCount.get(0).getValue()+" 次"));
list.add(new ExportListVo("远程协商",remoteCount.get(0).getValue()+ " 次"));
list.add(new ExportListVo("书香政协",readCount+" 条"));
list.add(new ExportListVo("会议活动",meetCount.get(0).getValue()+" 次"));
list.add(new ExportListVo("公文流转",documentCount+" 条"));
list.add(new ExportListVo("公文交换",exchangeDocumentCount+" 条"));
list.add(new ExportListVo("协商在基层",baseTopicCount+" 条"));
return list;
}
}
四、在浏览器地址栏输入地址：http://localhost:8997/export/exportDataIndex
进行数据导出
最后导出的文件如下所示：
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
