目录
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();
在应用场景方面,便携式IV功率测试仪广泛应用于光伏电站的日常运维、光伏组件生产过程中的质量控制以及光伏项目的前期评估等环节。在光伏电站运维中,定期对光伏组件进行IV测试,可以及时发现性能下降或损坏的组件,为电站的运维提供有力支持。首先,从工作原理来看,光伏电站便携式IV功率测试仪通过模拟太阳光照射光伏组件,并测量组件在不同电压下的电流输出,从而绘制出IV曲线。此外,测试仪还可以计算光伏组件的功率输出、转换效率等参数,为用户提供全面的性能评估。
文章浏览阅读3.6k次。os: ubuntu 16.04postgresql: 9.6.8ip 规划192.168.56.102 node2 postgresqlhelp create indexpostgres=# \h create indexCommand: CREATE INDEXDescription: define a new indexSyntax:CREATE [ UNIQUE ..._load_categories_hash postgres
文章浏览阅读4.8k次。使用face++,先获取key和secret下方是人脸识别,还添加了画出人脸轮廓的正方形下方是人脸识别,还添加了画出人脸轮廓的正方形 import requests#网络访问控件 from json import JSONDecoder#互联网数据交换标准格式 import cv2 as cv#图像处理控件 http_url =&amp;amp;quot;https://a..._face++人脸识别 html5
文章浏览阅读322次。很多时候要对秘要进行持久化加密,此时的加密采用md5。采用对称加密的时候就采用DES方法了import java.io.IOException;import java.security.MessageDigest;import java.security.SecureRandom;import javax.crypto.Cipher;import javax.crypto.SecretKey;im..._java desencrypt.encrypt(pass)
文章浏览阅读145次。题目链接:https://www.acwing.com/problem/content/description/222/给定整数N,求1<=x,y<=N且GCD(x,y)为素数的数对(x,y)有多少对。GCD(x,y)即求x,y的最大公约数。输入格式输入一个整数N输出格式输出一个整数,表示满足条件的数对数量。数据范围1≤N≤10^7输入样例:4..._线性筛预处理质数表, 并求出欧拉函数, 预处理前缀和即可 bzoj2818boj
文章浏览阅读710次,点赞13次,收藏7次。它的名称有点不同 - aarch64-linux-android-addr2line。尽管该实用程序的名称不包含单词arm,但它成功地解密了arm64-v8下的堆栈跟踪,并通常感知arm64-v8的字符。是 NDK 自带的调试工具,可以用来分析 so 崩溃时输出的的内存地址。之后就是通过 cmd 进入到这个路径。找到了 64 位所需的实用程序。_arm-linux-androideabi
flyfish
【代码】Zookeeper集群部署和单机部署。
文章浏览阅读871次。是的,这是可能的并且不是太困难,但解决方案非常不理想,因为SSH在TCP上运行并且具有合理的开销.服务器必须在其配置文件sshd_config中:PermitTunnel point-to-point然后,您需要在两台计算机上都是root用户.您使用以下命令连接到服务器:ssh -w any root@server连接后,使用两个系统中的命令ip link来了解在每个系统中创建的tunN设备,并在..._ljnux上ipv6转发配置
文章浏览阅读88次。vim编辑器文本编辑器,字处理器ASCIInano,sedvi:VisualInterfacevim:VIiMproved全屏编辑器,模式化编辑器vim模式:编辑模式(命令模式)1.输入模式2.末行模式模式转换:编辑-->输入:i:在当前光标所在字符的前面,转为输入模式;a:在当前光标所在字符的后面,转为输入模式;o:在当前光标所在行的下方,新...
文章浏览阅读245次。【PConline海选导购】晃眼间,秋风又起,让人振奋的开学季又要到来了!虽然说没有学习压力的暑假,在家着实悠哉,但想到回校后可以跟小伙伴们一起各种开黑吃鸡,是不是就感到很兴奋呢?说到“吃鸡”这种吃配置的游戏,就不得不说游戏本了,毕竟普通的笔记本电脑很难给我们带来畅快的游戏体验。而近年来游戏本市场俨然成为了各大厂商的必争之地,而随着开学季的到来,各大厂商更是推出了众多促销活动,下面就让我们一起来看..._计应专业游戏本
看到codepen里面有的按钮搞得很炫酷,但其实也不是很难,就学习记录一下。