package org.ctzk.jcpc.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
/**
* org.ctzk.jcpc.utils
*
* @author luozhen
* @version V1.0
* @date 2020-2-12 05:15
* @description
*/
public class EasyExcelUtil {
private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
OutputStream out = null;
String encodedFileName = "";
try {
if (fileName.toLowerCase().endsWith(".xlsx")) {
encodedFileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else {
encodedFileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1") + ".xlsx";
}
response.setHeader("Content-Disposition", "attachment;filename=\"" + encodedFileName + "\"");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
out = response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
log.error("创建文件失败!");
}
return out;
}
/**
* 设置样式
*
* @return
*/
private static HorizontalCellStyleStrategy cellStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
// 设置底边框;
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 设置自动换行
headWriteCellStyle.setWrapped(true);
// 设置垂直居中
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置水平居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 15);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置底边框;
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
contentWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
contentWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
contentWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 设置自动换行
contentWriteCellStyle.setWrapped(true);
// 设置垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
/**
* 通过模板,创建excel导出
*
* @param response
* @param dataList
* @param templateFileName
* @param fileName
*/
public static void writeExcelForTemplate(HttpServletResponse response, List dataList, Map map, String templateFileName, String fileName) {
ExcelWriter excelWriter = EasyExcel.write(getOutputStream(response, fileName)).registerWriteHandler(cellStyle()).withTemplate(templateFileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
excelWriter.fill(dataList, writeSheet);
excelWriter.fill(map, writeSheet);
excelWriter.finish();
}
/**
* 自定义表头导出excel
*
* @param response
* @param headList
* @param dataList
* @param fileName
*/
public static void writeExcel(HttpServletResponse response, List headList, List dataList, String fileName) {
EasyExcel.write(getOutputStream(response, fileName)).head(headList).sheet("Sheet1").doWrite(dataList);
}
}
最后使用阿里的easyexcel
©著作权归作者所有,转载或内容合作请联系作者
- 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
- 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
- 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...