表格导出工具类
package com.walmart.vendor.app.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.walmart.alohaframework.core.exception.BaseRuntimeException;
import com.walmart.vendor.app.constants.enums.ErrorInfoEnum;
import com.walmart.vendor.app.datasource.vendor_neg_support.model.dto.CategoryItemDetailInfoDTO;
import com.walmart.vendor.app.datasource.vendor_neg_support.model.vo.CategoryItemQueryInfo;
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.util.IOUtils;
import org.springframework.core.io.ClassPathResource;
public class ExcelHelper {
private ExcelWriter excelWriter = null;
private WriteSheet writeSheet = null;
/**
* @param response: 输出流对象
* @param template: 导出使用的模板文件地址
* @param dataList: 填充到导出文件的数据
* @Title: exportExcel
* @Description: 使用导出模板文件导出Excel
* @author: esther
* @throws:
* @return: null
*/
public <T> void exportExcel(HttpServletResponse response, AbstractMergeStrategy mergeStrategy, String template, String fileName, List<T> dataList) {
if (CollectionUtils.isEmpty(dataList)) {
dataList = new ArrayList<>();
}
BufferedOutputStream bos = null;
InputStream templateInput = null;
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
templateInput = new ClassPathResource(template).getInputStream();
bos = new BufferedOutputStream(response.getOutputStream());
ExcelWriterBuilder excelWriterBuilder = null;
if(null != mergeStrategy){
excelWriterBuilder = EasyExcel.write(bos).registerWriteHandler(mergeStrategy).withTemplate(templateInput);
}else{
excelWriterBuilder = EasyExcel.write(bos).withTemplate(templateInput);
}
ExcelWriter excelWriter = excelWriterBuilder.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
excelWriter.fill(dataList, writeSheet);
excelWriter.finish();
bos.flush();
} catch (Exception ex) {
throw new BaseRuntimeException(ErrorInfoEnum.FAIL_CUSTOM_MSG+"|"+"导出excel异常", ex);
} finally {
IOUtils.closeQuietly(templateInput);
}
}
public <T> void exportDetailExcelAppendly(
HttpServletResponse response,
AbstractCellStyleStrategy mergeStrategy,
String template,
String fileName,
Map<String,List<T>> map) {
BufferedOutputStream bos = null;
InputStream templateInput = null;
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
templateInput = new ClassPathResource(template).getInputStream();
bos = new BufferedOutputStream(response.getOutputStream());
ExcelWriterBuilder excelWriterBuilder = null;
if(null != mergeStrategy){
excelWriterBuilder = EasyExcel.write(bos).registerWriteHandler(mergeStrategy).withTemplate(templateInput);
}else{
excelWriterBuilder = EasyExcel.write(bos).withTemplate(templateInput);
}
ExcelWriter excelWriter = excelWriterBuilder.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
int count=0;
for(Entry<String,List<T>> entry: map.entrySet()){
List<T> list=entry.getValue();
if(CollectionUtils.isEmpty(list)){
continue;
}
count+=list.size();
excelWriter.fill(list, writeSheet);
}
if(count==0){
CategoryItemDetailInfoDTO dto=new CategoryItemDetailInfoDTO();
excelWriter.fill(Arrays.asList(dto),writeSheet);
}
excelWriter.finish();
bos.flush();
} catch (Exception ex) {
throw new BaseRuntimeException(ErrorInfoEnum.FAIL_CUSTOM_MSG+"|"+"导出excel异常", ex);
} finally {
IOUtils.closeQuietly(templateInput);
}
}
/**
* @param fs: 导入文件二进制流
* @param readListener: 读取时候行数据监听处理
* @param clazz: 返回的class对象
* @Title: readExcel
* @Description: 读取导入的Excel数据
* @author: esther
* @throws:
* @return: null
*/
public <T> List<T> readExcel(InputStream fs, AnalysisEventListener<T> readListener, Class<T> clazz) {
return EasyExcel
.read(fs)
.registerReadListener(readListener)
.head(clazz)
.sheet()
.headRowNumber(1)
.autoTrim(true)
.doReadSync();
}
}
导出策略示例
package com.test.common.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;
/**
* @Description: 自定义样式策略
*/
public class CustomerCellStyleStrategy extends AbstractCellStyleStrategy {
private WriteCellStyle headWriteCellStyle;
private List<WriteCellStyle> contentWriteCellStyleList;
private CellStyle headCellStyle;
private List<CellStyle> contentCellStyleList;
public CustomerCellStyleStrategy() {
this.headWriteCellStyle = buildHeadWriteCellStyle();
this.contentWriteCellStyleList = Collections.singletonList(buildContentWriteCellStyle());
}
private static WriteCellStyle buildContentWriteCellStyle() {
//表内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//内容字体
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("微软雅黑");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
private static WriteCellStyle buildHeadWriteCellStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//表头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("黑体");
headWriteFont.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(headWriteFont);
return headWriteCellStyle;
}
/**
* 编程方式自定义表格样式
*
* @return
*/
public static List<WriteHandler> getCustomWriteHandlerList() {
List<WriteHandler> handlerList = new ArrayList<>();
WriteHandler writeHandler = new HorizontalCellStyleStrategy(buildHeadWriteCellStyle(), buildContentWriteCellStyle());
handlerList.add(writeHandler);
return handlerList;
}
/**
* 编程方式自动表格宽度
*
* @param headList 表头宽度集合
* @return
*/
public static Map<Integer, Integer> getLongestColumnWidthMap(List<List<String>> headList) {
Map<Integer, Integer> columnWidth = new HashMap<>();
for (int i = 0; i < headList.size(); i++) {
List<String> list = headList.get(i);
//取最后一行表头
String title = list.get(list.size() - 1);
//参见 com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy.setColumnWidth
columnWidth.put(i, title.getBytes().length * 250);
}
return columnWidth;
}
/**
* 编程方式自动表格宽度
*
* @param clazz 导出实体类
* @return
*/
public static Map<Integer, Integer> getLongestColumnWidthMap(Class clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
List<ExcelProperty> propertyList = new ArrayList<>();
for (Field declaredField : declaredFields) {
ExcelProperty excelProperty = declaredField.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
propertyList.add(excelProperty);
}
}
List<ExcelProperty> headList = propertyList.stream()
.sorted(Comparator.comparing(ExcelProperty::order))
.collect(Collectors.toList());
Map<Integer, Integer> columnWidth = new HashMap<>();
for (int i = 0; i < headList.size(); i++) {
ExcelProperty excelProperty = headList.get(i);
String[] value = excelProperty.value();
//取最后一行表头
String title = value[value.length - 1];
//参见 com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy.setColumnWidth
columnWidth.put(i, title.getBytes().length * 250);
}
return columnWidth;
}
public CustomerCellStyleStrategy(WriteCellStyle headWriteCellStyle,
List<WriteCellStyle> contentWriteCellStyleList) {
this.headWriteCellStyle = headWriteCellStyle;
this.contentWriteCellStyleList = contentWriteCellStyleList;
}
public CustomerCellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
this.headWriteCellStyle = headWriteCellStyle;
contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
contentWriteCellStyleList.add(contentWriteCellStyle);
}
@Override
protected void initCellStyle(Workbook workbook) {
if (headWriteCellStyle != null) {
headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
}
if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
contentCellStyleList = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
if (headCellStyle == null) {
return;
}
cell.setCellStyle(headCellStyle);
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
return;
}
cell.setCellStyle(contentCellStyleList.get(relativeRowIndex % contentCellStyleList.size()));
}
}