代码
public class EasyExcelUtil {
/**
* excel下载
*
* @param response 返回对象
* @param fileName 文件名
* @param sheetName 页签名称
* @param head 对象
* @param data 数据
*/
public static void writeExcel(HttpServletResponse response, String fileName, String sheetName, Class head, List data) {
try (OutputStream outputStream = response.getOutputStream()) {
setRespHeader(response, fileName);
EasyExcel.write(outputStream, head).sheet(sheetName).doWrite(data);
} catch (IOException e) {
log.error("excel下载失败", e);
throw new ServiceException("excel下载失败");
}
}
/**
* 设置响应头
*
* @param response
* @param fileName
* @throws UnsupportedEncodingException
*/
public static void setRespHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileNameUrl = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + fileNameUrl + ".xlsx");
response.setHeader("filename", fileNameUrl + ".xlsx");
}
/**
* 获取默认表头内容的样式
* @return
*/
private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
/** 表头样式 **/
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色（浅灰色）
// 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 字体大小
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/** 内容样式 **/
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 内容字体样式（名称、大小）
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置内容垂直居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置内容水平居中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 头样式与内容样式合并
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 导出
* @param response
* @param data
* @param fileName
* @param sheetName
* @param clazz
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
long exportStartTime = System.currentTimeMillis();
log.info("报表导出Size: "+data.size()+"条。");
setRespHeader(response,fileName);
EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);
log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
}
/**
* 导出 下拉框
* @param response
* @param data
* @param mapDropDown Map<Integer,String[]> key是列的index 从0开始 value 是数据集
* @param fileName
* @param sheetName
* @param clazz
*/
@SneakyThrows
public static void writeExcel(HttpServletResponse response, List<?> data, Map<Integer,String[]> mapDropDown, String fileName, String sheetName, Class<?> clazz){
long exportStartTime = System.currentTimeMillis();
log.info("报表导出Size: "+data.size()+"条。");
setRespHeader(response,fileName);
EasyExcel.write(response.getOutputStream(), clazz)
.excelType(ExcelTypeEnum.XLSX)
.sheet(sheetName)
.registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
.registerWriteHandler(new TemplateCellWriteHandlerDate(mapDropDown))
.doWrite(data);
log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
}
/**
* @author QiuYu
* @createDate 2020-11-16
* @param response
* @param data 查询结果
* @param fileName 导出文件名称
* @param clazz 映射实体class类
* @param <T> 查询结果类型
* @throws Exception
*/
public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class<?> clazz) throws Exception {
long exportStartTime = System.currentTimeMillis();
log.info("报表导出Size: "+data.size()+"条。");
//List<List<T>> lists = SplitListUtil.splitList(data,MAXROWS); // 分割的集合
List<List<T>> lists = CollectionUtil.split(data, MAXROWS); // 分割的集合
setRespHeader(response,fileName);
OutputStream out = response.getOutputStream();
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
ExcelWriter excelWriter = excelWriterBuilder.build();
ExcelWriterSheetBuilder excelWriterSheetBuilder;
WriteSheet writeSheet;
if (CollectionUtil.isNotEmpty(lists)){
for (int i =1;i<=lists.size();i++){
excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
excelWriterSheetBuilder.sheetNo(i);
excelWriterSheetBuilder.sheetName("sheet"+i);
writeSheet = excelWriterSheetBuilder.build();
excelWriter.write(lists.get(i-1),writeSheet);
}
}
out.flush();
excelWriter.finish();
out.close();
log.debug("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
}
}
/**
* excel通用单元格格式类下拉框赋值
*/
public class TemplateCellWriteHandlerDate implements SheetWriteHandler {
/**
* 模板的首行行高 ，通过构造器注入
*/
private Map<Integer, String[]> explicitListConstraintMap;
public TemplateCellWriteHandlerDate(Map<Integer, String[]> explicitListConstraintMap) {
this.explicitListConstraintMap = explicitListConstraintMap;
}
/**
* 设置阈值，避免生成的导入模板下拉值获取不到
*/
private static final Integer LIMIT_NUMBER = 50;
/**
* 返回excel列标A-Z-AA-ZZ
*
* @param num 列数
* @return java.lang.String
*/
private String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// TODO Auto-generated method stub
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
explicitListConstraintMap.forEach((k, v) -> {
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
// 如果下拉值总数大于100，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到
if (v.length > LIMIT_NUMBER) {
//定义sheet的名称
//1.创建一个隐藏的sheet 名称为 hidden + k
String sheetName = "hidden" + k;
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.createSheet(sheetName);
for (int i = 0, length = v.length; i < length; i++) {
// 开始的行数i，列数k
hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
String excelLine = getExcelLine(k);
// =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
// 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, rangeList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
// 设置存储下拉列值得sheet为隐藏
int hiddenIndex = workbook.getSheetIndex(sheetName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
sheet.addValidationData(validation);
});
}
}
用法
@GetMapping("/downloadExcel")
public void downloadExcelModel(HttpServletResponse response) {
List<CatalogExcel> list = new ArrayList<>();
Map<Integer,String[]> mapDropDown = new HashMap<>();
//0未确认，1已确认，2已生成
String[] confirmStas = {"未确认","已确认","已生成"};
mapDropDown.put(1,confirmStas);
ExcelUtils.writeExcel(response, list,mapDropDown,"导入模板" + DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"),"sheet", CatalogExcel.class);
}
导入解析
@Data
public class CatalogExcel implements Serializable {
/**
* 确认状态（0未确认，1已确认，2已生成）
*/
@ExcelProperty(value = "确认状态（0未确认，1已确认，2已生成）", index = 13, converter = ConfirmStasConverter.class)
@ColumnWidth(15)
private String confirmStas;
public static class ConfirmStasConverter implements Converter<String> {
@Override
public Class<?> supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
//0未确认，1已确认，2已生成
String confirmStasName = Optional.ofNullable(cellData).map(o -> cellData.getStringValue()).orElse("未确认");
return ConfirmStasEnum.getConfirmStasCode(confirmStasName);
}
@Override
public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
//0未确认，1已确认，2已生成
String confirmStas = Optional.ofNullable(value).orElse("0");
return new CellData(Objects.requireNonNull(ConfirmStasEnum.getConfirmStasName(confirmStas)));
}
}
}
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
