使用springboot +easyexcel通过web来下载Excel文件
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 (Exception e) {log.info("下载异常,文件:"+fileName,e);// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = MapUtils.newHashMap();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());try {response.getWriter().println(JSON.toJSONString(map));} catch (IOException ex) {log.error("response写入失败,文件:"+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;}}}}}
合并单元格
为了下面的效果
增加一个registerWriteHandler
List<HighwayData> highwayData = Arrays.asList(new HighwayData("鲁衡管理处", "都香高速", 320000, 330000, 315000, 305000, 300000, 280000, 1850000),new HighwayData("鲁衡管理处", "大永高速", 280000, 315000, 305000, 330000, 300000, 300000, 1860000),new HighwayData("鲁衡管理处", "格巧高速", 320000, 315000, 305000, 300000, 330000, 280000, 1850000),new HighwayData("鲁衡管理处", "清水高速", 320000, 330000, 315000, 300000, 300000, 280000, 1850000),new HighwayData("鲁衡管理处", "串新高速", 280000, 330000, 315000, 300000, 330000, 280000, 1860000),new HighwayData("鲁衡管理处", "邵阳环西高速", 280000, 315000, 305000, 330000, 300000, 300000, 1860000),new HighwayData("镇维管理处", "昭泸高速", 320000, 315000, 305000, 300000, 330000, 280000, 1850000),new HighwayData("镇维管理处", "镇纳高速", 320000, 330000, 315000, 300000, 300000, 280000, 1850000));
自定义合并的逻辑，如果是固定行数的合并，可以参考官方demo类LoopMergeStrategy
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;public class CellMergeStrategy implements RowWriteHandler {private final int mergeColumnIndex;public CellMergeStrategy(int mergeColumnIndex) {this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {if (isHead) {return;}Sheet sheet = writeSheetHolder.getSheet();//怎么把row转换成SXSSFRow，获取SXSSFRow中的rowIndexSXSSFRow sr = (SXSSFRow) row;//行号int rowIndex = sr.getRowNum();String currentData = sr.getCell(mergeColumnIndex).getStringCellValue();String previousData = sheet.getRow(rowIndex - 1).getCell(mergeColumnIndex).getStringCellValue();// 如果当前单元格数据与上一个单元格数据相同，则合并if (currentData.equals(previousData)) {CellRangeAddress cellRangeAddress;cellRangeAddress = new CellRangeAddress(rowIndex - 1, rowIndex ,mergeColumnIndex, mergeColumnIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);}}
}
