public class EasyExcelUtil {
/**
* 导出单个sheet
* @param excelRows 导出数据
* @param head 表头
* @param fileName 文件名称
* @param req
* @param resp
* @param writeHandler 自定义格式
* @param <T>
*/
public static <T> void exportAsXLSX(List<T> excelRows, Class<T> head, String fileName, HttpServletRequest req,HttpServletResponse resp, WriteHandler writeHandler) {
try (final OutputStream outputStream = resp.getOutputStream()) {
String userAgent = req.getHeader("User-Agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident")) {
setHeaders(resp, fileName, ExcelTypeEnum.XLSX);
} else {
// 非IE内核浏览器的处理：
setHeadersNoIe(resp, fileName, ExcelTypeEnum.XLSX);
}
EasyExcel.write(outputStream, head)
.registerWriteHandler(defaultCellStyle())
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.registerWriteHandler(writeHandler)
.sheet()
.doWrite(excelRows);
} catch (IOException e) {
throw new GException("导出失败，请重试");
}
}
/**
* 导出单个sheet
* @param excelRows 导出数据
* @param head 表头
* @param fileName 文件名称
* @param req
* @param resp
* @param <T>
*/
public static <T> void exportAsXLSDefined(List<T> excelRows, Class<T> head, String fileName, HttpServletRequest req, HttpServletResponse resp) {
try (final OutputStream outputStream = resp.getOutputStream()) {
resp.reset();
String userAgent = req.getHeader("User-Agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident")) {
setHeaders(resp, fileName, ExcelTypeEnum.XLS);
} else {
// 非IE内核浏览器的处理：
setHeadersNoIe(resp, fileName, ExcelTypeEnum.XLS);
}
EasyExcel.write(outputStream, head)
.excelType(ExcelTypeEnum.XLS)
.registerWriteHandler(defaultCellStyle())
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.registerWriteHandler(new RowWriteHandler())
.sheet()
.doWrite(excelRows);
} catch (IOException e) {
throw new GException("导出失败，请重试");
}
}
/**
* 导出多个sheet
* @param sheetName 文件名称
* @param sheetInfoList 导出bean的封装信息
* @param req
* @param resp
* @param <T>
*/
public static <T> void exportMuiltiSheetAsXLSDefined(String sheetName, List<SheetInfoBean> sheetInfoList, HttpServletRequest req, HttpServletResponse resp) {
try (final OutputStream outputStream = resp.getOutputStream()) {
ExcelWriter excelWriter = EasyExcel.write(outputStream).excelType(ExcelTypeEnum.XLS).build();
String userAgent = req.getHeader("User-Agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident")) {
setHeaders(resp, sheetName, ExcelTypeEnum.XLS);
} else {
// 非IE内核浏览器的处理：
setHeadersNoIe(resp, sheetName, ExcelTypeEnum.XLS);
}
for (int i = 0; i < sheetInfoList.size(); i++) {
resp.reset();
String childSheetName = sheetInfoList.get(i).getSheetName();
Class<?> headClass = sheetInfoList.get(i).getHeadClass();
List<?> dataList = sheetInfoList.get(i).getDataList();
WriteSheet writeSheet = EasyExcel
.writerSheet(i, childSheetName)
.head(headClass)
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.registerWriteHandler(new RowWriteHandler())
.build();
excelWriter.write(dataList, writeSheet);
}
excelWriter.finish();
} catch (IOException e) {
throw new GException("导出失败，请重试");
}
}
public static void setHeaders(HttpServletResponse resp, String fileName, ExcelTypeEnum excelType)
throws UnsupportedEncodingException {
resp.setContentType("application/vnd.ms-excel");
resp.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName + excelType.getValue());
}
public static void setHeadersNoIe(HttpServletResponse resp, String fileName, ExcelTypeEnum excelType)
throws UnsupportedEncodingException {
resp.setContentType("application/vnd.ms-excel");
resp.setCharacterEncoding("UTF-8");
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName + excelType.getValue());
}
public static String trim(String str) {
if (str == null) {
return null;
}
//'\u00A0'不换行空格； (char) 12288 圆角空格
str = StringUtils.trim(str.replace('\u00A0', ' ').replace((char) 12288, ' '));
return str;
}
public static HorizontalCellStyleStrategy defaultCellStyle() {
//表头样式
WriteCellStyle headWriteCellStyle = defaultHeadCellStyle();
//内容样式
WriteCellStyle contentWriteCellStyle = defaultContentCellStyle();
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
public static WriteCellStyle defaultContentCellStyle() {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setBold(false);
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
public static WriteCellStyle defaultHeadCellStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
headWriteFont.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
return headWriteCellStyle;
}
public class CellColorSheetWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
/**
* 考虑到导出数据量过大的情况，不对每一行的每一个单元格进行样式设置，只设置必要行中的某个单元格的样式
*/
//当前行的第i列
int j = cell.getColumnIndex();
int i = cell.getRowIndex();
if(j == 0) {
if(i != 0) {
Workbook workbook = cell.getSheet().getWorkbook();
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setWrapped(true); //设置自动换行
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setBold(false);
//设置字体颜色
contentWriteFont.setColor(IndexedColors.RED1.getIndex());
contentWriteCellStyle.setWriteFont(contentWriteFont);
CellStyle cellStyle = StyleUtil.buildContentCellStyle(workbook, contentWriteCellStyle);
//设置当前行第i列的样式
//cell.getRow().getCell(i).setCellStyle(cellStyle);
cell.setCellStyle(cellStyle);
}else {
Workbook workbook = cell.getSheet().getWorkbook();
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setWrapped(true);//设置自动换行
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
//设置字体颜色
headWriteFont.setColor(IndexedColors.RED1.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
//设置当前行第i列的样式
//cell.getRow().getCell(i).setCellStyle(cellStyle);
cell.setCellStyle(cellStyle);
}
}
}
}
public class SheetInfoBean {
/**
* sheet页名称
*/
private String sheetName;
/**
* sheet标题bean
*/
private Class<?> headClass;
/**
* sheet页数据
*/
private List<?> dataList;
public SheetInfoBean() {
}
public SheetInfoBean(String sheetName, Class<?> headClass, List<?> dataList) {
this.sheetName = sheetName;
this.headClass = headClass;
this.dataList = dataList;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public Class<?> getHeadClass() {
return headClass;
}
public void setHeadClass(Class<?> headClass) {
this.headClass = headClass;
}
public List<?> getDataList() {
return dataList;
}
public void setDataList(List<?> dataList) {
this.dataList = dataList;
}
@Override
public String toString() {
return "SheetInfoBean{" +
"sheetName='" + sheetName + '\'' +
", headClass=" + headClass +
", dataList=" + dataList +
'}';
}
}
浙公网安备 33010602011771号