赞
踩
目录
3、导出的时候,特殊数据类型,需要设置转换类,不然注解导出方式会报错
4、web导出excel(将excel数据写入到response输出流中)
本博客不是写给小白的,小白可以去官网看案例: 写Excel | Easy Excel
基于easyExcel 3.2.0 版本
- package com.xinba.statistical.rules.base.utils.excel;
-
- import com.alibaba.excel.enums.CellDataTypeEnum;
- import com.alibaba.excel.metadata.CellData;
- import com.alibaba.excel.metadata.Head;
- 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);
-
- @Override
- protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> 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<CellData> 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;
- }
- }
- }
- }
-
- }
- package com.xinba.statistical.rules.base.utils.excel;
-
- 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.setDataFormat((short)49);//设置单元格格式是:文本格式,方式长数字文本科学计数法
-
- contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
-
- return contentWriteCellStyle;
- }
-
- }
-
- @ExcelProperty(value = "进站日期", converter = LocalDateTimeConverter.class, index = 20)
- private LocalDateTime arriveTime;
- package com.xinba.statistical.rules.base.utils.excel;
-
- import com.alibaba.excel.converters.Converter;
- import com.alibaba.excel.enums.CellDataTypeEnum;
- import com.alibaba.excel.metadata.CellData;
- import com.alibaba.excel.metadata.GlobalConfiguration;
- import com.alibaba.excel.metadata.property.DateTimeFormatProperty;
- import com.alibaba.excel.metadata.property.ExcelContentProperty;
-
- import java.time.LocalDateTime;
- import java.time.format.DateTimeFormatter;
-
- public class LocalDateTimeConverter implements Converter<LocalDateTime> {
-
- private static final String DEFAULT_DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
-
- @Override
- public Class<LocalDateTime> supportJavaTypeKey() {
- return LocalDateTime.class;
- }
-
- @Override
- public CellDataTypeEnum supportExcelTypeKey() {
- return CellDataTypeEnum.STRING;
- }
-
- @Override
- public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
- GlobalConfiguration globalConfiguration) {
- DateTimeFormatProperty dateTimeFormatProperty = contentProperty.getDateTimeFormatProperty();
- String dateTimeFormatPattern = DEFAULT_DATETIME_FORMAT;
- if(null != dateTimeFormatProperty){
- dateTimeFormatPattern = dateTimeFormatProperty.getFormat();
- }
- return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern(dateTimeFormatPattern));
- }
-
- @Override
- public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
- GlobalConfiguration globalConfiguration) {
- DateTimeFormatProperty dateTimeFormatProperty = contentProperty.getDateTimeFormatProperty();
- String dateTimeFormatPattern = DEFAULT_DATETIME_FORMAT;
- if(null != dateTimeFormatProperty){
- dateTimeFormatPattern = dateTimeFormatProperty.getFormat();
- }
- return new CellData<>(value.format(DateTimeFormatter.ofPattern(dateTimeFormatPattern)));
- }
-
- }
此转换类中的easyExcel 3.3.2版本中,变成了WriteCellData, 具体参考Converter<T>接口中的声明,适当调整即可
- // 直接用浏览器或者用postman
- response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
- response.setCharacterEncoding("utf-8");
- // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
- String fileName = String.format("文件名称"+System.currentTimeMillis(), taskCode);
- fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
- response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
-
- OutputStream out = response.getOutputStream();
- // 设置单元格样式
- HorizontalCellStyleStrategy horizontalCellStyleStrategy =
- new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
- // 列宽策略设置
- ExcelCellWidthStyleStrategy widthStyleStrategy = new ExcelCellWidthStyleStrategy();
- ExcelWriter excelWriter = EasyExcel.write(out)
- .registerWriteHandler(horizontalCellStyleStrategy)
- .registerWriteHandler(widthStyleStrategy)
- .build();
- // 总体统计 sheet
- WriteSheet totalSheet = EasyExcel.writerSheet(0, "总体统计").head(StatisticsAnalyseDivisorVo.class).build();
- excelWriter.write(total,totalSheet);
- // web导出,这里必须要有这个finish,不然导出的文件是空的,官方文档的案例没写
- excelWriter.finish();
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。