导读

　　下午抽空封装一个通用导出Excel工具类。之前还写过一篇EasyPoi导入参数校验，批注导出，点我直达

添加依赖

<!-- easy poi --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.1.0</version></dependency>

注意依赖版本超过4.1.0导出图片时有问题哟

EasyPoi工具类

时间工具类

import java.time.LocalDateTime;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.Date;/*** 时间工具类** @Author：chenyanbin*/public class TimeUtil {public static final String YYYY_MM_DD_HH_MM_SS = \"yyyy-MM-dd HH:mm:ss\";public static final String YYYY_MM_DD = \"yyyy-MM-dd\";public static final String YYMMDD = \"yyMMdd\";public static final String YYYYMMDD = \"yyyyMMdd\";/*** 格式化日期** @param time 当前时间* @return*/public static String format(Date time) {return format(time, YYYY_MM_DD_HH_MM_SS);}/*** 格式化日期** @param time    当前时间* @param pattern 格式化规则* @return*/public static String format(Date time, String pattern) {DateTimeFormatter dtf = DateTimeFormatter.ofPattern(pattern);ZoneId zoneId = ZoneId.systemDefault();return dtf.format(time.toInstant().atZone(zoneId));}/*** timestamp 转 字符串** @param timestamp* @return*/public static String dateToStr(long timestamp) {return dateToStr(timestamp, YYYY_MM_DD_HH_MM_SS);}/*** timestamp 转 字符串** @param timestamp 时间戳* @param pattern   格式化规则* @return*/public static String dateToStr(long timestamp, String pattern) {DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);ZoneId zoneId = ZoneId.systemDefault();String timeStr = formatter.format(new Date(timestamp).toInstant().atZone(zoneId));return timeStr;}/*** 字符串 转 date** @param time* @return*/public static Date strToDate(String time) {return strToDate(time, YYYY_MM_DD_HH_MM_SS);}/*** 字符串 转 date** @param time    当前时间* @param pattern 格式化规则* @return*/public static Date strToDate(String time, String pattern) {DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);LocalDateTime localDateTime = LocalDateTime.parse(time, formatter);return Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());}/*** 当前时间格式化** @return*/public static String currentTimeFormat() {LocalDateTime nowDate = LocalDateTime.now();DateTimeFormatter dtf = DateTimeFormatter.ofPattern(YYYY_MM_DD_HH_MM_SS);return dtf.format(nowDate);}/*** 当前时间格式化** @param pattern 格式化规则* @return*/public static String currentTimeFormat(String pattern) {LocalDateTime nowDate = LocalDateTime.now();DateTimeFormatter dtf = DateTimeFormatter.ofPattern(pattern);return dtf.format(nowDate);}}

导出样式

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import org.apache.poi.ss.usermodel.*;/*** Easy Poi 导出样式* @Author：chenyanbin*/public class EasyPoiExcelExportStylerUitl implements IExcelExportStyler {private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat(\"TEXT\");private static final short FONT_SIZE_TEN = 10;private static final short FONT_SIZE_ELEVEN = 11;private static final short FONT_SIZE_TWELVE = 12;/*** 大标题样式*/private CellStyle headerStyle;/*** 每列标题样式*/private CellStyle titleStyle;/*** 数据行样式*/private CellStyle styles;public EasyPoiExcelExportStylerUitl(Workbook workbook) {this.init(workbook);}/*** 初始化样式** @param workbook*/private void init(Workbook workbook) {this.headerStyle = initHeaderStyle(workbook);this.titleStyle = initTitleStyle(workbook);this.styles = initStyles(workbook);}/*** 大标题样式** @param color* @return*/@Overridepublic CellStyle getHeaderStyle(short color) {return headerStyle;}/*** 每列标题样式** @param color* @return*/@Overridepublic CellStyle getTitleStyle(short color) {return titleStyle;}/*** 数据行样式** @param parity 可以用来表示奇偶行* @param entity 数据内容* @return 样式*/@Overridepublic CellStyle getStyles(boolean parity, ExcelExportEntity entity) {return styles;}/*** 获取样式方法** @param dataRow 数据行* @param obj     对象* @param data    数据*/@Overridepublic CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {return getStyles(true, entity);}/*** 模板使用的样式设置*/@Overridepublic CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {return null;}/*** 初始化--大标题样式** @param workbook* @return*/private CellStyle initHeaderStyle(Workbook workbook) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, (short) 14, true));return style;}/*** 初始化--每列标题样式** @param workbook* @return*/private CellStyle initTitleStyle(Workbook workbook) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));//背景色//        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setDataFormat(STRING_FORMAT);return style;}/*** 初始化--数据行样式** @param workbook* @return*/private CellStyle initStyles(Workbook workbook) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));//背景色//        style.setFillForegroundColor(IndexedColors.RED.getIndex());//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setDataFormat(STRING_FORMAT);return style;}/*** 基础样式** @return*/private CellStyle getBaseCellStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();//下边框style.setBorderBottom(BorderStyle.THIN);//左边框style.setBorderLeft(BorderStyle.THIN);//上边框style.setBorderTop(BorderStyle.THIN);//右边框style.setBorderRight(BorderStyle.THIN);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//上下居中style.setVerticalAlignment(VerticalAlignment.CENTER);//设置自动换行style.setWrapText(true);return style;}/*** 字体样式** @param size   字体大小* @param isBold 是否加粗* @return*/private Font getFont(Workbook workbook, short size, boolean isBold) {Font font = workbook.createFont();//字体样式font.setFontName(\"宋体\");//是否加粗font.setBold(isBold);//字体大小font.setFontHeightInPoints(size);return font;}}

导出工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.util.List;import java.util.concurrent.CountDownLatch;import java.util.concurrent.LinkedBlockingDeque;import java.util.concurrent.ThreadPoolExecutor;import java.util.concurrent.TimeUnit;import java.util.concurrent.atomic.AtomicReference;/*** EasyPoi 导出工具类** @Author：chenyanbin*/@Slf4jpublic class EasyPoiExportUtil {private static final Long KEEP_ALIVE_TIME = 60L;private static final int APS = Runtime.getRuntime().availableProcessors();private static final ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(APS * 2, APS * 4, KEEP_ALIVE_TIME, TimeUnit.SECONDS, new LinkedBlockingDeque<>());/*** 导出Excel 一对多关系** @param list        Excel数据集合* @param title       Excel第一行标题，如果设置为null，默认不显示* @param sheetName   sheet名称* @param pojoClass   泛型List的对象* @param fileName    导出文件名* @param type        excel类型 HSSF || XSSF* @param isOneToMany 是否一对多* @param response    响应体*/public static void exportOneToManyExcel(List<?> list,String title,String sheetName,Class<?> pojoClass,String fileName,ExcelType type,boolean isOneToMany,HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName, type);exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);AtomicReference<Workbook> workbook = new AtomicReference<>();workbookHandler(workbook, exportParams, pojoClass, list);if (workbook.get() == null) {return;}//判断是否是一对多if (isOneToMany) {setRowHeight(workbook.get());}downLoadExcel(fileName, response, workbook.get());}/*** 导出excel** @param list         Excel数据集合* @param title        Excel第一行标题，如果设置为null，默认不显示* @param sheetName    sheet名称* @param pojoClass    泛型List的对象* @param fileName     导出文件名* @param setRowHeight 是否行高自适应* @param response     响应体*/public static void exportOneExcel(List<?> list,String title,String sheetName,Class<?> pojoClass,String fileName,boolean setRowHeight,HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);AtomicReference<Workbook> workbook = new AtomicReference<>();workbookHandler(workbook, exportParams, pojoClass, list);if (workbook.get() == null) {return;}//判断是否根据内容自适应行高if (setRowHeight) {Sheet sheet = workbook.get().getSheetAt(0);for (int i = 0; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);setRowHeight(row);}}downLoadExcel(fileName, response, workbook.get());}/*** 下载Excel** @param fileName* @param response* @param workbook*/private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {ServletOutputStream outputStream = null;try {outputStream = response.getOutputStream();response.setCharacterEncoding(\"UTF-8\");response.setHeader(\"content-Type\", \"application/vnd.ms-excel\");response.setHeader(\"Pragma\", \"No-cache\");response.setHeader(\"Content-Disposition\",\"attachment;filename=\" + URLEncoder.encode(fileName, \"UTF-8\"));workbook.write(outputStream);outputStream.flush();} catch (IOException e) {throw new RuntimeException(e.getMessage());} finally {if (outputStream != null) {try {outputStream.close();} catch (IOException e) {log.error(\"excel导出关闭输出流异常：{}\", e.getMessage());}}}}/*** 一对多，设置行高*/private static void setRowHeight(Workbook workbook) {Sheet sheet = workbook.getSheetAt(0);//设置第4列的列宽为60（下标从0开始），TestExportSub2Vo 不知道为什么设置了列宽但是不起作用，只能在这里单独设置sheet.setColumnWidth(3, 60 * 256);for (int i = 0; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);if (i == 0) {//设置第一行的行高（表格标题）row.setHeightInPoints(35);} else if (i == 1) {//设置第二行的行高（表格表头）row.setHeightInPoints(25);} else {//设置其他行的行高根据内容自适应setRowHeight(row);}}}private static void setRowHeight(Row row) {//根据内容长度设置行高int enterCnt = 0;for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {int rwsTemp = row.getCell(j).toString().length();//这里取每一行中的每一列字符长度最大的那一列的字符if (rwsTemp > enterCnt) {enterCnt = rwsTemp;}}//设置默认行高为35row.setHeightInPoints(35);//如果字符长度大于35，判断大了多少倍，根据倍数来设置相应的行高if (enterCnt > 35) {float d = enterCnt / 35;float f = 35 * d;/*if (d>2 && d<4){f = 35*2;}else if(d>=4 && d<6){f = 35*3;}else if (d>=6 && d<8){f = 35*4;}*/row.setHeightInPoints(f);}}private static void workbookHandler(AtomicReference<Workbook> workbook, ExportParams exportParams, Class<?> pojoClass, List<?> list) {CountDownLatch latch = new CountDownLatch(1);THREAD_POOL_EXECUTOR.execute(() -> {try {workbook.set(ExcelExportUtil.exportExcel(exportParams, pojoClass, list));} finally {latch.countDown();}});try {latch.await();} catch (InterruptedException e) {log.error(\"多线程导出等待异常：{}\", e.getMessage());}}}

根据url下载图片转字节数组

import lombok.extern.slf4j.Slf4j;import java.io.ByteArrayOutputStream;import java.io.InputStream;import java.net.HttpURLConnection;import java.net.URL;/*** Http工具类** @Author：chenyanbin*/@Slf4jpublic class HttpUtil {/*** 获取网络图片转成字节流** @param strUrl 完整图片地址* @return 图片资源数组*/public static byte[] getNetImgByUrl(String strUrl) {try {URL url = new URL(strUrl);HttpURLConnection conn = (HttpURLConnection) url.openConnection();conn.setRequestMethod(\"GET\");conn.setConnectTimeout(2 * 1000);// 通过输入流获取图片数据InputStream inStream = conn.getInputStream();// 得到图片的二进制数据byte[] btImg = readInputStream(inStream);return btImg;} catch (Exception e) {e.printStackTrace();}return null;}/*** 从输入流中获取字节流数据** @param inStream 输入流* @return 图片流*/private static byte[] readInputStream(InputStream inStream) throws Exception {ByteArrayOutputStream outStream = new ByteArrayOutputStream();// 设置每次读取缓存区大小byte[] buffer = new byte[1024 * 10];int len = 0;while ((len = inStream.read(buffer)) != -1) {outStream.write(buffer, 0, len);}inStream.close();return outStream.toByteArray();}}

导出实体类vo

import cn.afterturn.easypoi.excel.annotation.Excel;import io.swagger.annotations.ApiModel;import lombok.Data;/*** @Author：chenyanbin*/@Data@ApiModel(value = \"ProductFilingExportExcelVo对象\", description = \"商品备案明细导出信息\")public class ProductFilingExportExcelVo {/*** 主键*/private Long id;@Excel(name = \"商品编码\", width = 20)private String productCode;@Excel(name = \"HS编码\", width = 25)private String hsCode;@Excel(name = \"商品名称\", width = 40)private String productName;@Excel(name = \"品牌\")private String brandName;@Excel(name = \"规格型号\", width = 20)private String specificationModel;@Excel(name = \"申报要素\", width = 40)private String reportElements;@Excel(name = \"产品成分\", width = 50)private String productComposition;@Excel(name = \"生产企业\", width = 25)private String enterprise;@Excel(name = \"生产国/地区\", width = 20)private String country;@Excel(name = \"适用标准\", width = 20)private String standards;@Excel(name = \"商品条码\", width = 30)private String productBarCode;@Excel(name = \"功能\", width = 40)private String function;@Excel(name = \"其他说明\", width = 40)private String remark;@Excel(name = \"商品正面\", type = 2, imageType = 2)private byte[] productFrontPic;/*** 临时商品正面url*/private String tempProductFrontUrl;@Excel(name = \"商品背面\", type = 2, imageType = 2)private byte[] productBackPic;/*** 临时商品背面url*/private String tempProductBackUrl;@Excel(name = \"商品其他面\", width = 15, type = 2, imageType = 2)private byte[] productOtherPic;/*** 临时商品其他url*/private String tempProductOtherUrl;}

dao查询sql语句

<resultMap id=\"exportExcelMap\" type=\"ProductFilingExportExcelVo\"><id column=\"id\" property=\"id\"/><result column=\"product_code\" property=\"productCode\"/><result column=\"hs_code\" property=\"hsCode\"/><result column=\"product_name\" property=\"productName\"/><result column=\"brand_name\" property=\"brandName\"/><result column=\"specification_model\" property=\"specificationModel\"/><result column=\"report_elements\" property=\"reportElements\"/><result column=\"product_composition\" property=\"productComposition\"/><result column=\"enterprise\" property=\"enterprise\"/><result column=\"country\" property=\"country\"/><result column=\"standards\" property=\"standards\"/><result column=\"product_bar_code\" property=\"productBarCode\"/><result column=\"function\" property=\"function\"/><result column=\"remark\" property=\"remark\"/><result column=\"temp_product_front_url\" property=\"tempProductFrontUrl\"/><result column=\"temp_product_back_url\" property=\"tempProductBackUrl\"/><result column=\"temp_product_other_url\" property=\"tempProductOtherUrl\"/></resultMap><!-- excel导出 --><select id=\"exportExcel\" resultMap=\"exportExcelMap\">SELECTbpfd.id,bpfd.product_code,bpfd.hs_code,bpfd.product_name,bb.brand_name,bpfd.specification_model,bpfd.report_elements,bpfd.product_composition,bpfd.enterprise,bpfd.country,bpfd.standards,bpfd.product_bar_code,bpfd.`function`,bpfd.remark,bof.url_path temp_product_front_url,bob.url_path temp_product_back_url,boo.url_path temp_product_other_urlFROMbasic_product_filing_detail bpfdLEFT JOIN basic_brand bb ON bb.id = bpfd.brand_idLEFT JOIN basic_oss bof ON ( bof.id = bpfd.product_front_id AND bof.del_flag = 0 )LEFT JOIN basic_oss bob ON ( bob.id = bpfd.product_back_id AND bob.del_flag = 0 )LEFT JOIN basic_oss boo ON ( boo.id = bpfd.product_other_id AND boo.del_flag = 0 )WHEREbpfd.product_filing_id = #{filing_id}ORDER BYbpfd.id DESC</select>

controller层

@ApiOperation(\"导出\")@GetMapping(\"export_detail_excel\")public void exportDetailExcel(HttpServletResponse response,@ApiParam(value = \"主表主键id\", required = true) @RequestParam(value = \"id\") Long id) {cacheService.exportDetailExcel(response, id);}

service层

import javax.servlet.http.HttpServletResponse;public interface ProductFilingCacheService {/*** 导出* @param response 响应体* @param id 主表主键id*/void exportDetailExcel(HttpServletResponse response, Long id);}

service实现类！！！！private static final Long KEEP_ALIVE_TIME = 60L;private static final int APS = Runtime.getRuntime().availableProcessors();private static final ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(APS * 2, APS * 4, KEEP_ALIVE_TIME, TimeUnit.SECONDS, new LinkedBlockingDeque<>());@Overridepublic void exportDetailExcel(HttpServletResponse response, Long id) {List<ProductFilingExportExcelVo> excelList = filingDetailMapper.exportExcel(id);CountDownLatch latch = new CountDownLatch(excelList.size());//转换图片字节码excelList.forEach(obj -> {try {if (StringUtils.isNotBlank(obj.getTempProductFrontUrl())) {obj.setProductFrontPic(HttpUtil.getNetImgByUrl(obj.getTempProductFrontUrl()));}if (StringUtils.isNotBlank(obj.getTempProductBackUrl())) {obj.setProductBackPic(HttpUtil.getNetImgByUrl(obj.getTempProductBackUrl()));}if (StringUtils.isNotBlank(obj.getTempProductOtherUrl())) {obj.setProductOtherPic(HttpUtil.getNetImgByUrl(obj.getTempProductOtherUrl()));}} finally {latch.countDown();}});try {latch.await();} catch (InterruptedException e) {log.error(\"导出Excel等待下载文件流异常:{}\", e.getMessage());}//获取货主名称String cargoOwnerName = \"\";ProductFilingDO productFilingDO = filingMapper.selectById(id);if (productFilingDO != null) {Long createUserId = productFilingDO.getCreateUserId();JsonData<String> jsonData = userControllerFeign.getCargoOwnerNameById(createUserId);if (JsonData.isSuccess(jsonData)) {cargoOwnerName = jsonData.getData();}}String fileName = cargoOwnerName + \"-\" + TimeUtil.currentTimeFormat(TimeUtil.YYMMDD) + \".xls\";//导出EasyPoiExportUtil.exportOneExcel(excelList,fileName.replace(\".xls\", \"\"),\"商品备案明细\",ProductFilingExportExcelVo.class,fileName,false,response);}

如果需要设置导出行高自适应，使用如下导出!!!!! false改成true//导出EasyPoiExportUtil.exportOneExcel(excelList,fileName.replace(\".xls\", \"\"),\"商品备案明细\",ProductFilingExportExcelVo.class,fileName,true,response);

演示