目录
excel导出(支持多工作表)
maven依赖
<!--springboot-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.5.6</version>
</dependency>
<!--实体类-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<!-- excel操作-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
相关实例
启动文件
package com.ntt.web;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
@SpringBootApplication
public class CmsApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(CmsApplication.class, args);
}
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
return application.sources(CmsApplication.class);
}
}
server:
port: 9999
控制类
package com.ntt.web.controller;
import com.ntt.web.pojo.Student;
import com.ntt.web.utils.ExcelUtils;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.time.LocalDateTime;
import java.util.*;
@RestController
@RequestMapping("/api/v1")
public class StuController {
/**
* 导出
* @param response
* @throws Exception
*/
@GetMapping(value = "/download")
public void download(HttpServletResponse response) throws Exception{
// 模拟查询匹配数据
List<Student> list = new ArrayList<>();
list.add(new Student().setName("小白").setAge(10).setCreateTime(LocalDateTime.now()));
list.add(new Student().setName("小黑").setAge(11).setCreateTime(LocalDateTime.now()));
// 导出
LinkedHashMap<String, List<?>> resultMap = new LinkedHashMap<>();
resultMap.put("结果", list);
ExcelUtils.dataType = Arrays.asList(new Student());
ExcelUtils.httpSaveByExcel(response, resultMap);
}
}
实体类
package com.ntt.web.pojo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.time.LocalDateTime;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
public class Student {
private static final long serialVersionUID = 1L;
/**
* 姓名(原始)
*/
@ExcelProperty(value = "姓名")
private String name;
/**
* 年龄
*/
@ExcelProperty(value = "年龄")
private Integer age;
/**
* 创建时间
*/
@ExcelIgnore
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
}
工具类
package com.ntt.web.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
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.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtils {
public static List<?> dataType = null; // 导出的数据实体类型
/**
* Web导出结果(http-excel-多sheet)
* @param response
* @param resultMap
* @throws Exception
*/
public static void httpSaveByExcel(HttpServletResponse response, LinkedHashMap<String,List<?>> resultMap) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
try{
// 获取文件结果
SimpleDateFormat dft = new SimpleDateFormat("yyyyMMdd");
String fileName = URLEncoder.encode(dft.format(new Date()), "UTF-8"); // 20220916
// 响应信息构建
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 新建ExcelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
// 根据传递过来的map将结果导出到多个sheet
Integer sheetNum = 0; // sheet工作表序号
if (resultMap != null && !resultMap.isEmpty()){
for(Map.Entry<String, List<?>> entry : resultMap.entrySet()){
String rmKey = entry.getKey(); // sheet工作表名称
List<?> resultVal = entry.getValue(); // sheet工作表数据
if (rmKey != null){
resultVal = (resultVal != null && !resultVal.isEmpty()) ? resultVal : new ArrayList<>();
excelWriter.write(
resultVal, // 相关数据
EasyExcel.writerSheet(sheetNum, rmKey)
.head(dataType.get(sheetNum).getClass())
.registerWriteHandler(myHorizontalCellStyleStrategy())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build() // 工作表创建
);
sheetNum ++;
}
}
}
// 关闭流
excelWriter.finish();
}catch (Exception e){
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(e);
}
}
/**
* 本地导出结果(excel-单sheet|多sheet)
* @param resultMap
* @param resultPath
*/
public static void localSaveByExcel(LinkedHashMap<String,List<?>> resultMap, String resultPath) throws IOException {
// 指定输出的文件流
File outFile = new File(resultPath);
OutputStream outputStream = null;
ExcelWriter excelWriter = null;
try{
outputStream = new FileOutputStream(resultPath);
// 优先创建文件
if (!outFile.exists()){
File parentFile = outFile.getParentFile();
if (!parentFile.exists()){
parentFile.mkdirs();
}
outFile.createNewFile();
}
// 新建ExcelWriter
excelWriter = EasyExcel.write(outputStream).build();
// 根据传递过来的map将结果导出到多个sheet
Integer sheetNum = 0; // sheet工作表序号
if (resultMap != null && !resultMap.isEmpty()){
for(Map.Entry<String, List<?>> entry : resultMap.entrySet()){
String rmKey = entry.getKey(); // sheet工作表名称
List<?> resultVal = entry.getValue(); // sheet工作表数据
if (rmKey != null){
resultVal = (resultVal != null && !resultVal.isEmpty()) ? resultVal : new ArrayList<>();
excelWriter.write(
resultVal, // 相关数据
EasyExcel.writerSheet(sheetNum, rmKey)
.head(dataType.get(sheetNum).getClass())
.registerWriteHandler(myHorizontalCellStyleStrategy())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build() // 工作表创建
);
sheetNum ++;
}
}
}
}catch (Exception e){
e.printStackTrace();
}
finally {
if (excelWriter != null) {
excelWriter.finish();
}
if (outputStream != null){
outputStream.close();
}
}
}
/**
* 设置表头 和内容样式
* @return
*/
public static HorizontalCellStyleStrategy myHorizontalCellStyleStrategy(){
//1 表头样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//表头前景设置浅绿色
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("等线");
headWriteFont.setFontHeightInPoints((short)11);
headWriteCellStyle.setWriteFont(headWriteFont);
//内容样式 多个样式则隔行换色
List<WriteCellStyle> listCntWritCellSty = new ArrayList<>();
//2 内容样式策略 样式一
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
//内容字体大小
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short)11);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//背景设置白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
//设置自动换行
contentWriteCellStyle.setWrapped(false);
//设置垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//设置背景黄色
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//设置水平靠左
//contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
//设置边框样式
setBorderStyle(contentWriteCellStyle);
//内容风格可以定义多个。
listCntWritCellSty.add(contentWriteCellStyle);
// 水平单元格风格综合策略(表头 + 内容));
return new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
}
/**
* 设置边框样式
* @param contentWriteCellStyle
*/
private static void setBorderStyle(WriteCellStyle contentWriteCellStyle){
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
}
}
