一、在pom.xml中引入 alibaba easyexcel maven 版本为2.1.7
注意：！！！本案例不可用2.2.？以上的版本
<pre class="prettyprint hljs xml" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;"><dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
<pre class="prettyprint hljs xml" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;"><dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
二、编写ExcelVo
<pre class="prettyprint hljs less" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;">@Data
@ColumnWidth(value = 20) //列宽度 注解可以写在类上方，也可以写在字段上方
@ContentRowHeight(value = 30) //列高度
@HeadRowHeight(value = 35) //表头高度
public class ExcelVo {
@ExcelProperty(value = "表头单元格一", index = 0)
private String headOne;
@ExcelProperty(value = {"表头单元格二", "Come"}, index = 1)
private String headTwoCome;
@ExcelProperty(value = {"表头单元格二", "On"}, index = 2)
private String headTwoOn;
@ExcelProperty(value = {"表头单元格三", "Come"}, index = 3)
private String headThreeCome;
@ExcelProperty(value = {"表头单元格三", "On"}, index = 4)
private String headThreeOn;
@ExcelProperty(value = {"表头单元格四", "Come"}, index = 5)
private String headFourCome;
@ExcelProperty(value = {"表头单元格四", "On"}, index = 6)
private String headFourOn;
@ExcelProperty(value = {"表头单元格五", "Come"}, index = 7)
private String headFiveCome;
@ExcelProperty(value = {"表头单元格五", "On"}, index = 8)
private String headFiveOn;
@ExcelProperty(value = {"表头单元格六", "Come"}, index = 9)
private String headSixCome;
@ExcelProperty(value = {"表头单元格六", "On"}, index = 10)
private String headSixOn;
@ColumnWidth(value = 30)
@ExcelProperty(value = "表头单元格七", index = 11)
private String headSeven;
}
三、编写ExcelUtils
<pre class="prettyprint hljs gradle" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;">import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
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 springboot.redis.demo.model.RetailTargetExcelVo;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtils {
public static void exportExcel(HttpServletResponse response, List<ExcelVo> data)throws Exception{
//1、设置数据表格的样式
// ---------- 头部样式 ----------
WriteCellStyle headStyle = new WriteCellStyle();
// 字体样式
WriteFont headFont = new WriteFont();
headFont.setFontHeightInPoints((short) 11);
headFont.setFontName("宋体");
headFont.setColor(IndexedColors.BLACK.index);
headStyle.setWriteFont(headFont);
WriteCellStyle contentStyle = new WriteCellStyle();
//垂直居中
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置边框
// bodyStyle.setBorderTop(BorderStyle.DOUBLE);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
contentStyle.setBorderBottom(BorderStyle.THIN);
WriteFont writeFont = new WriteFont();
//加粗
//字体大小为11
writeFont.setFontHeightInPoints((short) 11);
writeFont.setFontName("宋体");
writeFont.setColor(IndexedColors.BLACK.index);
contentStyle.setWriteFont(writeFont);
// 创建单元格策略1 参数1为头样式【不需要头部，设置为null】，参数2位表格内容样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
// 创建策略2
// HorizontalCellStyleStrategy dataTableStrategy = new HorizontalCellStyleStrategy(headStyle,bodyStyle);
// 设置数据表格的行高 null表示使用原来的行高
// SimpleRowHeightStyleStrategy rowHeightStrategy3 = new SimpleRowHeightStyleStrategy( null, (short) 18);
//循环合并策略
// LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
//一次绝对合并策略
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(0, 1, 1, 2); //0，1表示第1行到第2行 1,2表示第2列到第3列
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy2 = new OnceAbsoluteMergeStrategy(0, 1, 3, 4);
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy3 = new OnceAbsoluteMergeStrategy(0, 1, 5, 6);
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy4 = new OnceAbsoluteMergeStrategy(0, 1, 7, 8);
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy5 = new OnceAbsoluteMergeStrategy(0, 1, 9, 10);
// response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("文件名", "UTF-8");
response.setHeader("content-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), ExcelVo.class)
// .registerWriteHandler(loopMergeStrategy) // 循环合并策略
.registerWriteHandler(onceAbsoluteMergeStrategy1)
.registerWriteHandler(onceAbsoluteMergeStrategy2)
.registerWriteHandler(onceAbsoluteMergeStrategy3)
.registerWriteHandler(onceAbsoluteMergeStrategy4)
.registerWriteHandler(onceAbsoluteMergeStrategy5)
// .registerWriteHandler(dataTableStrategy) //策略2
.registerWriteHandler(horizontalCellStyleStrategy); //策略1
excelWriterBuilder.sheet("sheet名称").doWrite(data);
}
}
四、编写ExcelService 准备数据，可以写在Controller层
<pre class="prettyprint hljs php" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;">public class ExcelService {
public static List<ExcelVo> getExcelExportData() {
ExcelVo excelVo = new ExcelVo();
List<ExcelVo> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
excelVo.setHeadOne("qqqq");
excelVo.setHeadTwoCome("wwww");
excelVo.setHeadTwoOn("eee");
excelVo.setHeadThreeCome("rrr");
excelVo.setHeadThreeOn("bgbd");
excelVo.setHeadFourCome("qoqo");
excelVo.setHeadFourOn("规格");
excelVo.setHeadFiveCome("项链");
excelVo.setHeadFiveOn("等等");
excelVo.setHeadSixCome("测试");
excelVo.setHeadSixOn("测试");
excelVo.setHeadSeven("测试");
list.add(excelVo);
}
return list;
}
}
五、编写Controller层，调用接口
<pre class="prettyprint hljs java" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;">@RestController
public class ExcelController {
//方式一：准备测试数据 ExcelService.getExcelExportData()
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
List<ExcelVo> data = ExcelService.getExcelExportData();
ExcelUtils.exportExcel(response,data);
}
//方式二：通过前端传值测试，前端传的excelVo 字段和ExcelVo类里面的字段对应
@GetMapping("/exportTwo")
public void aliExportDetail(@RequestBody List<ExcelVo> excelVo, HttpServletResponse response) throws Exception {
ExcelUtils.exportExcel(response,excelVo);
}
}
六、测试导出是否成功
在浏览器地址栏中访问： http://localhost:端口号/export
这样就可以弹出来要保存文件的地址，点击保存，就保存成功了
如果不知道端口号，
配置一个application.yml文件 在文件中简单配置server port 就可以，如果有用到redis和数据库，就要配置redis和数据库
<pre class="prettyprint hljs yaml" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto;"># 端口号配置
server:
port: 8081
# spring相关 配置
spring:
redis: # 配置redis
database: 0
host: localhost
port: 6379
datasource:# 配置数据库
name:
url: jdbc:mysql://ip地址:3306/test?useSSL=false
username:
password:
driver-class-name: com.mysql.jdbc.Driver #mysql 驱动程序
profiles:
active: def,dev,master
# 配置mybatis
mybatis:
mapper-locations: classpath:*mapper/*.xml
type-aliases-package: com.oda.mall.entity
