当前位置： 首页 > news >正文

Excel 下载工具类

news 2025/5/14 3:11:59

pom文件

    <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.4</version></dependency>

Controller 类

@RestController
@Slf4j
public class TestController {@PostMapping(value = "/down")public void down(HttpServletResponse response) {log.info("hello java");TestDownloadData testDownloadData = new TestDownloadData("张三" ,"男",18 );TestDownloadData testDownloadData2 = new TestDownloadData("李四" ,"女",20 );TestDownloadData testDownloadData3 = new TestDownloadData("王五" ,"男",19 );List<TestDownloadData> list = Arrays.asList(testDownloadData, testDownloadData2, testDownloadData3);String fileName =String.format("测试_%s",getCurrentDateStr("yyyyMMddHHmmss"));ExcelUtil.download(response, fileName, list, TestDownloadData.class,"测试sheet");}public static String getCurrentDateStr( String pattern) {LocalDateTime localDateTime = LocalDateTime.now();DateTimeFormatter df = DateTimeFormatter.ofPattern(pattern);return df.format(localDateTime);}}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestDownloadData {@ExcelProperty(value = "姓名", index = 0)private String name;@ExcelProperty(value = "性别", index = 1)private String sex;@ExcelProperty(value = "年龄", index = 2)private Integer age;}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;/*** Excel工具类*/
@Slf4j
public class ExcelUtil {public static  <T> void download(HttpServletResponse response, String fileName, List<T> list,Class<T> clazz,String sheetName){try {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).registerWriteHandler(new ExcelCellWidthStyleStrategy()).registerWriteHandler(horizontalCellStyleStrategy).doWrite(list);} catch (IOException e) {log.info("下载异常,文件:"+fileName,e);}}
}

配置类

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;public class StyleUtils {private static final String[] _formats = new String[]{"General","0","0.00","#,##0","#,##0.00","\"$\"#,##0_);(\"$\"#,##0)","\"$\"#,##0_);[Red](\"$\"#,##0)","\"$\"#,##0.00_);(\"$\"#,##0.00)","\"$\"#,##0.00_);[Red](\"$\"#,##0.00)","0%", "0.00%", "0.00E+00","# ?/?", "# ??/??","m/d/yy", "d-mmm-yy","d-mmm", "mmm-yy","h:mm AM/PM","h:mm:ss AM/PM","h:mm", "h:mm:ss","m/d/yy h:mm","reserved-0x17","reserved-0x18","reserved-0x19","reserved-0x1A","reserved-0x1B","reserved-0x1C","reserved-0x1D","reserved-0x1E","reserved-0x1F","reserved-0x20","reserved-0x21","reserved-0x22","reserved-0x23","reserved-0x24","#,##0_);(#,##0)","#,##0_);[Red](#,##0)","#,##0.00_);(#,##0.00)","#,##0.00_);[Red](#,##0.00)","_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)","_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)","_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)","_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)","mm:ss","[h]:mm:ss","mm:ss.0","##0.0E+0","@" // 文本格式};/*** 标题样式* @return*/public static WriteCellStyle getHeadStyle(){// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景颜色
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
//        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 字体WriteFont headWriteFont = new WriteFont();headWriteFont.setFontName("宋体");//设置字体名字headWriteFont.setFontHeightInPoints((short)10);//设置字体大小headWriteFont.setBold(true);//字体加粗headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;// 样式headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;headWriteCellStyle.setWrapped(true);  //设置自动换行;headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适return headWriteCellStyle;}/*** 内容样式* @return*/public static WriteCellStyle getContentStyle(){// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 背景绿色// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 设置字体WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontHeightInPoints((short) 9);//设置字体大小contentWriteFont.setFontName("宋体"); //设置字体名字contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;//设置样式;contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中contentWriteCellStyle.setWrapped(true); //设置自动换行;contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适return contentWriteCellStyle;}}

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class ExcelCellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {// 可以根据这里的最大宽度，按自己需要进行调整,搭配单元格样式实现类中的，自动换行，效果更好private static final int MAX_COLUMN_WIDTH = 50;private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap(16);CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > MAX_COLUMN_WIDTH) {columnWidth = MAX_COLUMN_WIDTH;}Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = (CellData)cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch(type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}}

http://www.mrgr.cn/news/16219.html

相关文章：

  • nacos集群部署和VIP部署
  • 2024最新最全：网络安全人士【必备的30个安全工具】
  • Linux文件和目录常用命令
  • Tomcat与Servlet加载问题
  • shell脚本编写注意事项
  • YOLOv9改进策略【模型轻量化】| 利用PP-LCnet改进RepNCSPELAN4
  • shell脚本--正则表达式
  • 量化投资策略与技术学习PART1.1：量化选股之再谈多因子模型（二）
  • JDBC的使用及案例
  • 全国设计院排名 工程项目管理营业额二〇二三年排名
  • 基于STM32的RFID高速收费系统（论文+源码+实物）
  • Java 虚方法表（虚函数）
  • JS设计模式之“名片设计师” - 工厂方法模式
  • APP 数据抓取 - Charles 抓包工具的使用（Charles 端口配置、CA 证书配置、Charles Android 模拟器配置）
  • 又一个免费代码生成工具
  • gpt开发
  • JavaScript的对象详解
  • C--四种排序方法的补充
  • 足底筋膜炎怎么治疗效果好
  • 掌握AIGC的魔法：编写高质量提示词的艺术与科学