spring boot整合Easy Excel(导入导出数据)

1、在pom.xml文件里,添加依赖
 

        <!--alibaba-easyexcel的使用--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.3</version></dependency><!-- 引入 HttpServletResponse 等  --><dependency><groupId>org.apache.tomcat.embed</groupId><artifactId>tomcat-embed-core</artifactId><version>9.0.63</version><scope>compile</scope><optional>true</optional></dependency>

2、创建工具类

package com.ynkbny.config.easyExcel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
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 com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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 org.springframework.util.StringUtils;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Set;public class EasyExcelUtils {private static final Log log = LogFactory.getLog(EasyExcelUtils.class);/*** 单sheet版本Excel读取* 从Excel中读取文件,读取的文件是一个DTO类** @param inputStream 文件流* @param clazz       行数据类型*/public static <T> List<T> readExcelOneSheet(InputStream inputStream, final Class<?> clazz) {// 1.创建监听类ExcelListener<T> listener = new ExcelListener<>();// 2.构建工作簿对象的输入流ExcelReader excelReader = EasyExcel.read(inputStream, clazz, listener).build();// 3.构建工作表对象的输入流,默认是第一张工作表ReadSheet readSheet = EasyExcel.readSheet(0).build();// 4.读取信息,每读取一行都会调用监听类的 invoke 方法excelReader.read(readSheet);// 5.关闭流,如果不关闭,读的时候会创建临时文件,到时磁盘会崩的excelReader.finish();return listener.getDataList();}/*** 多sheet版本Excel读取** @param <T>      行数据的类型* @param filePath 文件路径* @param clazz    行数据的类型* @return 所有信息*/public static <T> List<T> readExcelAllSheet(String filePath, final Class<?> clazz) {ExcelListener<T> listener = new ExcelListener<>();// 读取全部sheet// 这里需要注意 ExcelListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写EasyExcel.read(filePath, clazz, listener).doReadAll();return listener.getDataList();}/*** 网页上的下载导出,只有一个工作表** @param fileName  文件名* @param clazz     类的字节码文件,行数据的类型* @param dataList  导出的数据* @param sheetName 工作表名* @param response  响应体* @throws IOException 异常对象*/public static void writeWeb(String fileName, final Class<?> clazz, List<?> dataList, String sheetName, HttpServletResponse response) throws IOException {// 1.指定响应体内容类型response.setContentType("application/vnd.ms-excel");// 2.指定编码方式response.setCharacterEncoding("utf-8");// 3.URLEncoder.encode可以防止中文乱码:import java.net.URLEncoderfileName = URLEncoder.encode(fileName, "UTF-8");// 4.指定响应标头response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");// 5.获取工作簿对象的输出流ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();// 6.设置工作表的名称if (!StringUtils.hasText(sheetName)) {sheetName = "sheet1";}// 7.指定写用哪个class去写WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(clazz).build();// 8.将 dataList 中的数据逐行写入工作表中excelWriter.write(dataList, writeSheet);// 9.finish关闭流excelWriter.finish();// 10.关闭流response.getOutputStream().close();}/*** 网页上的下载导出,只有一个工作表** @param fileName 文件名* @param dataList 导出的数据* @param response 响应体* @throws IOException 异常对象*/public static void writeWeb(String fileName, List<?> dataList, HttpServletResponse response) throws IOException {// 1.指定响应体内容类型response.setContentType("application/vnd.ms-excel");// 2.指定编码方式response.setCharacterEncoding("utf-8");// 3.URLEncoder.encode可以防止中文乱码:import java.net.URLEncoderfileName = URLEncoder.encode(fileName, "UTF-8");// 4.指定响应标头response.setHeader("Strict-Transport-Security", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
//        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "public");// 5.获取工作簿对象的输出流ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();// 6.设置工作表的名称// 7.指定写用哪个class去写WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1").head(dataList.get(0).getClass()).build();// 8.将 dataList 中的数据逐行写入工作表中excelWriter.write(dataList, writeSheet);// 9.finish关闭流excelWriter.finish();// 10.关闭流response.getOutputStream().close();}/*** 写出数据到文件** @param response* @param data* @param fileName* @param clazz* @param sheetName* @param <T>* @throws Exception*/public static <T> void writeExcelList(HttpServletResponse response, List<List<T>> data, String fileName, Class<?> clazz, String sheetName) throws Exception {OutputStream out = getOutputStream(fileName, response);ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());ExcelWriter excelWriter = excelWriterBuilder.build();ExcelWriterSheetBuilder excelWriterSheetBuilder;WriteSheet writeSheet;for (int i = 1; i <= data.size(); i++) {excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);excelWriterSheetBuilder.sheetNo(i);excelWriterSheetBuilder.sheetName(sheetName + i);writeSheet = excelWriterSheetBuilder.build();excelWriter.write(data.get(i - 1), writeSheet);}excelWriter.finish();out.close();}private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {fileName = URLEncoder.encode(fileName, "UTF-8");//  response.setContentType("application/vnd.ms-excel"); // .xls// .xlsxresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");return response.getOutputStream();}/*** 获取默认表头内容的样式** @return*/private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {/** 表头样式 **/WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景色(浅灰色)// 可以参考:https://www.cnblogs.com/vofill/p/11230387.htmlheadWriteCellStyle.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);}/*** 导出 Excel到指定目录 :单个 sheet,带表头,** @param tableData* @param fileName  导出的路径+文件名  例如:   file/test.xlsx* @param sheetName 导入文件的 sheet 名* @throws Exception*/public static void writeExcelAutoColumnWidth(String fileName, List<?> tableData, String sheetName, Class<?> clazz) throws Exception {// 根据用户传入字段 假设我们要忽略 dateEasyExcel.write(fileName, clazz).sheet(sheetName).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(tableData);}/*** 导出 Excel到指定目录 :单个 sheet,带表头,** @param fileName  导出的路径+文件名  例如:   file/test.xlsx* @param tableData*/public static void writeExcelWithOneSheet1(String fileName, List<?> tableData, String sheetName, Class<?> clazz, Set<String> excludeColumnFiledNames) {// 根据用户传入字段 假设我们要忽略 dateEasyExcel.write(fileName, clazz).excludeColumnFiledNames(excludeColumnFiledNames).sheet(sheetName).registerWriteHandler(styleWrite(false)).doWrite(tableData);}/*** 导出 Excel到指定目录 :单个 sheet,带表头,** @param fileName  导出的路径+文件名  例如:   file/test.xlsx* @param tableData*/public static void writeExcelWithOneSheet1(String fileName, List<?> tableData) {// 根据用户传入字段 假设我们要忽略 dateEasyExcel.write(fileName, tableData.get(0).getClass()).sheet("Sheet1").registerWriteHandler(styleWrite(false)).doWrite(tableData);}/*** 设置Excel样式** @param isWrapped 设置 自动换行* @return*/public static HorizontalCellStyleStrategy styleWrite(boolean isWrapped) {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置为红色// headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 18);headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定//contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 背景绿色//contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 11);//设置 自动换行contentWriteCellStyle.setWrapped(isWrapped);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentWriteCellStyle.setWriteFont(contentWriteFont);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭//EasyExcel.write(fileName, DemoData.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板")//    .doWrite(data());}}
package com.ynkbny.config.easyExcel;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.List;/*** excel通用读取监听类**/
@Slf4j
@Getter
@NoArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {/*** 自定义用于暂时存储data 可以通过实例获取该值*/private final List<T> dataList = new ArrayList<>();/*** 每解析一行都会回调invoke()方法** @param data 每一行的数据*/@Overridepublic void invoke(T data, AnalysisContext context) {dataList.add(data);log.info("读取的一条信息:{}", data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("{}条数据,解析完成", dataList.size());}
}

3、有了上面的工具类,基本上就可以自己根据需求去使用了,后面是我自己使用的场景,导出实体

package com.ynkbny.user.model.excel;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;import java.math.BigDecimal;
import java.util.Date;@TableName(value ="user_energy_day")
@Data
@HeadRowHeight(100)
public class UserEnergyDay {/*** 企业名称*/@ColumnWidth(40)@ExcelProperty("企业名称")private String name;/*** 电能耗数值*/@ColumnWidth(20)@ExcelProperty("电能耗数值(kw·h)")private BigDecimal energyConsumption;/*** 创建时间*/@ColumnWidth(15)@ExcelProperty("时间")private String createTime;}

 4、Controller

package com.ynkbny.web.user.controller;import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.ynkbny.common.base.BaseResponse;
import com.ynkbny.common.utils.ResultUtils;
import com.ynkbny.config.easyExcel.EasyExcelUtils;
import com.ynkbny.user.manager.UserEnergyManager;
import com.ynkbny.user.model.excel.UserEnergyDay;
import com.ynkbny.user.model.excel.UserEnergyMonth;
import com.ynkbny.user.model.vo.PieVo;
import com.ynkbny.user.model.vo.UserProductEnergyConsumptionVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.List;
import java.util.Map;@Api(tags = "用户端电能耗")
@RestController
@RequestMapping("/user/energy")
@Validated
public class UserEnergyController {@ResourceUserEnergyManager userEnergyManager;@ApiOperation("根据条件 导出 全厂电能耗信息")@GetMapping("/exportUserEnergy")public void exportUserEnergy(HttpServletResponse response,@ApiParam("开始时间") @NotNull String startTime,@ApiParam("结束时间") String endTime,@ApiParam("社会信用代码") @NotNull String enterpriseId,@ApiParam("日、月、年标识") @NotNull String range) throws IOException, ParseException {List list = userEnergyManager.exportUserEnergy(startTime, endTime, enterpriseId, range);if("1".equals(range) && ObjectUtils.isEmpty(list)) {list.add(new UserEnergyDay());}if("2".equals(range) && ObjectUtils.isEmpty(list)) {list.add(new UserEnergyDay());}if("3".equals(range) && ObjectUtils.isEmpty(list)) {list.add(new UserEnergyMonth());}EasyExcelUtils.writeWeb("模板_"+enterpriseId, list, response);}}