alibaba开发的快速、简单避免OOM的java处理Excel工具
文档:https://github.com/alibaba/easyexcel
在查阅了网上的各种实现方法结合poi的使用经验,综合研究测试后,封装一套方法,与各位探讨学习.
遗憾的是没有弄出冻结窗格的方法,poi中的方法如下,如有哪位可以实现easyexcel冻结的方法,望分享.
XSSFSheet sheet= workbook.createSheet(sheetName);
sheet.createFreezePane(0,1);
以下是service层的下载执行:设置表头,表内容,样式
/**
* 下载信息
* @param hm
*/
private void downInfo(HashMap hm) {
String[] hearArr = new String[] {"身份证号","金额","信息","状态","生日","性别","照片","创建时间"};
String[] orderArr = new String[] {"CERT_NO","AMOUNT","INFO","STATUS","BIRTHDAY","GENDER","HASPHOTO","CREATETIME"};
List<String> orderList = Arrays.asList(orderArr);
//4000对应Excel列宽的值15.5
Integer[] widthArr = new Integer[] {5200,1600,5200,1600,2900,1600,1600,5500};
Sheet sheet = new Sheet(1);//第1张sheet
sheet.setHead(ExcelUtil.createHead(hearArr));
sheet.setSheetName("sheet1");
//sheet.setAutoWidth(true);//默认的自动列宽,非自适应
sheet.setColumnWidthMap(ExcelUtil.setColunmWidth(widthArr));
Map<Integer,CellStyleEnum> styleMap = new HashMap();
styleMap.put(-1, CellStyleEnum.DEFAULT_HEAD);
styleMap.put(1, CellStyleEnum.AMOUNT_FORMAT);
List<HashMap<String, Object>> resultList = service.queryInfo(hm);
ExcelUtil.downExcel(response,"测试信息" + Tool.getNowStr(),sheet,ExcelUtil.createData(resultList,orderList),styleMap);
}
以下是CellStyleEnum类,直观的绑定样式名称和样式方法,便于自定义各种样式
/**
* 单元格样式类
* @author Administrator
*
*/
public enum CellStyleEnum{
//暂定以下几种格式,如有需要再扩充
DEFAULT_HEAD("默认标题行样式","defaultHeadLineStyle"),
NORMAL("四周细边框", "normalStyle"),
AMOUNT_FORMAT("金额数字类型0.00", "amountFormat"),
INTEGER_FORMAT("整数数字类型0","integerFormat"),
RED_FONT("红色字体","redFont");
// 成员变量
private String name;
private String methodName;
/**
* 构造方法
* @param name 名称
* @param methodName ExcelUtil.ExcelStyleHandler对应的方法名
*/
private CellStyleEnum(String name, String methodName) {
this.name = name;
this.methodName = methodName;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMethodName() {
return methodName;
}
public void setMethodName(String methodName) {
this.methodName = methodName;
}
}
以下是ExcelUtil类,封装了excel下载所需功能
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.WriteContext;
import com.alibaba.excel.event.WriteHandler;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.ExcelBuilderImpl;
/**
* 基于alibaba的easyexcel的Excel工具类
* 优点:相对于poi,极大的减小内存消耗,并避免oom问题
* 文档:https://github.com/alibaba/easyexcel
* @author Administrator
*
*/
@SuppressWarnings({"unchecked","unused"})
public class ExcelUtil {
private static Logger logger = Logger.getLogger(ExcelUtil.class);
/**
* 每个单元格在写入前,都会调用本处理器
* 线程安全的单例模式
* @author Administrator
*
*/
private static class ExcelStyleHandler implements WriteHandler{
Cell cellObj = null;
Workbook workbook = null;
CellStyle cellStyle = null;
DataFormat dataFormat = null;
org.apache.poi.ss.usermodel.Font font = null;
/**
* key-列号/行号 ,>=0:列号, -1:单标题行第一行,其他特殊类型可以按需设定
* value-设置样式的方法名,详见CellStyleEnum类的设置
*/
Map<Integer,CellStyleEnum> styleMap = null;
private static ExcelStyleHandler instance;
/** 私有化构造器 */
private ExcelStyleHandler() {}
/** 获取单例 */
public static ExcelStyleHandler getInstance() {
if(null == instance) {
synchronized(ExcelStyleHandler.class) {
if(null == instance) {
instance= new ExcelStyleHandler();
}
}
}
return instance;
}
@Override
public void cell(int i, Cell cell) {
try {
cellObj = cell;
workbook = cell.getSheet().getWorkbook();
cellStyle = workbook.createCellStyle();
dataFormat = workbook.createDataFormat();
font = workbook.createFont();
int rowIndex = cell.getRowIndex();
//设置标题行格式
if(rowIndex == 0) {
String methodName = styleMap.get(-1).getMethodName();
if(methodName != null) {
ExcelStyleHandler.class.getDeclaredMethod(methodName).invoke(getInstance());
}
//defaultHeadLineStyle();
}
//设置列内容格式
if(rowIndex > 0) {
normalStyle();//设置细线框
//设置指定列样式
for (Integer key : styleMap.keySet()) {
if(key >= 0 && key == i) {//仅设置列的样式
ExcelStyleHandler.class.getDeclaredMethod(styleMap.get(key).getMethodName()).invoke(getInstance());
}
}
}
cell.setCellStyle(cellStyle);
}catch(Exception e){
throw new RuntimeException(e.getMessage());
}
}
/**
* 去除成员变量绑定的对象,释放内存
*/
private void release() {
cellObj = null;
workbook = null;
cellStyle = null;
dataFormat = null;
font = null;
}
/**
* 金额0.00格式
* @param cell
* @param cellStyle
* @param dataFormat
*/
private void amountFormat() {
cellObj.setCellValue(Double.parseDouble(cellObj.getStringCellValue()));
cellStyle.setDataFormat(dataFormat.getFormat("0.00"));
}
/**
* 整数0格式
* @param cell
* @param cellStyle
* @param dataFormat
*/
private void integerFormat() {
cellObj.setCellValue(Double.parseDouble(cellObj.getStringCellValue()));
cellStyle.setDataFormat(dataFormat.getFormat("0"));
}
/**
* 默认标题行样式
* @param cellStyle
* @param font
*/
private void defaultHeadLineStyle() {
normalStyle();
middleStyle();
fillColorStyle(IndexedColors.AQUA.getIndex());
font.setBold(true);
font.setFontHeightInPoints((short) 14);
cellStyle.setFont(font);
}
/**
* 红色字体
* @param cellStyle
* @param font
*/
private void redFont() {
font.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(font);
}
/**
* 字体颜色设置
* @param cellStyle
* @param font
*/
private void fontColorStyle(short color) {
font.setColor(color);
cellStyle.setFont(font);
}
/**
* 填充色设置
* @param cellStyle
* @param color
*/
private void fillColorStyle(short color) {
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
/**
* 水平垂直居中
* @param cellStyle
*/
private void middleStyle() {
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
/**
* 常规-四周细边框
* @param cellStyle
*/
private void normalStyle() {
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
}
@Override
public void row(int i, Row row) {}
@Override
public void sheet(int i, org.apache.poi.ss.usermodel.Sheet sheet) {}
}
/**
* 导出单张Sheet的Excel
* @param response HttpServletResponse
* @param fileName 文件名
* @param sheet Sheet
* @param data Sheet中的内容
* @param styleMap
*/
public static void downExcel(HttpServletResponse response,String fileName,Sheet sheet,List<List<Object>> data,Map<Integer,CellStyleEnum> styleMap) {
try (ServletOutputStream out = response.getOutputStream()){////java7新特性 在try()语句的括号里声明和初始化资源 会对实现了Closeable的接口会自动关闭
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
synchronized (ExcelStyleHandler.getInstance()){//保证并发时的线程安全,数据不被污染
ExcelStyleHandler handler = ExcelStyleHandler.getInstance();
handler.styleMap = styleMap;
ExcelWriter writer = new ExcelWriter(null,out, ExcelTypeEnum.XLSX, true,handler);
Workbook workbook = getWorkbook(writer);
//要写多个sheet就多次执行write方法(暂时不用,就不做改造)
writer.write1(data, sheet);
//必须要调用finish()，否则数据不会写入文件
writer.finish();
out.flush();
handler.release();
}
} catch (Exception e) {
throw new RuntimeException("导出excel表格失败!" + e.getMessage());
}
}
/**
* 创建Excel标题行
* @param arr
* @return
*/
public static List<List<String>> createHead(String[] arr){
List<List<String>> head = new ArrayList();
List<String> headCol = null;
for (String colName : arr) {
headCol = new ArrayList() ;
headCol.add(colName);
head.add(headCol);//每加一次,就增加一列标题
}
return head;
}
/**
* 创建Excel内容
* @param list 数据集合
* @param order map.key的顺序
* @return
*/
public static List<List<Object>> createData(List<HashMap<String,Object>> list,List<String> order){
List<List<Object>> data = new ArrayList();
List<Object> dataLine = null;
Iterator<Map.Entry<String, Object>> it = null;
String key = null;
Object value = null;
int size = order.size();
Entry<String, Object> next = null;
int index = -1;
for (HashMap<String,Object> map : list) {
dataLine = Arrays.asList(new Object[size]);//该方法初始化的集合,可以根据下标添加元素时不报越界的错误
it = map.entrySet().iterator();
while(it.hasNext()) {
next = it.next();
key = next.getKey();
value = next.getValue();
index = order.indexOf(key);
if(index > -1) {//存在所需的字段,则加入
dataLine.set(index, value);
}
}
data.add(dataLine);
}
return data;
}
/**
* 设置指定列的列宽
* @param widthArr
* @return
*/
public static Map<Integer, Integer> setColunmWidth(Integer[] widthArr){
Map<Integer, Integer> columnWidthMap = new HashMap();
for (int i = 0; i < widthArr.length; i++) {
columnWidthMap.put(i, widthArr[i]);
}
return columnWidthMap;
}
/**
* 获取Workbook对象
* @param writer
* @return
*/
private static Workbook getWorkbook(ExcelWriter writer) {
Workbook workbook = null;
try {
Class<?> clazz1 = Class.forName("com.alibaba.excel.ExcelWriter");
Field[] fs = clazz1.getDeclaredFields();
for (Field field : fs) {
//要设置属性可达，不然会抛出IllegalAccessException异常
field.setAccessible(true);
if ("excelBuilder".equals(field.getName())) {
ExcelBuilderImpl excelBuilder = (ExcelBuilderImpl) field.get(writer);
Class<?> clazz2 = Class.forName("com.alibaba.excel.write.ExcelBuilderImpl");
Field[] fs2 = clazz2.getDeclaredFields();
for (Field field2 : fs2) {
field2.setAccessible(true);
if ("context".equals(field2.getName())) {
WriteContext context = (WriteContext) field2.get(excelBuilder);
workbook = context.getWorkbook();
}
break;
}
}
break;
}
} catch (Exception e) {
throw new RuntimeException("获取Wookbook失败!" + e.getMessage());
}
return workbook;
}
}
网友评论