前提:
由于本人采取的是利用反射的方式,这样的好处,可以兼容,导出所有类型的数据,例如,订单,充值记录等等。
既然是公有的接口,肯定有一定的约束规范。
(1)导出的表头需要自己设置一个动态数组
(2) 头部的宽度需要自己设置,也可以自己写一个方法,根据字体的长度,形成一个头部的动态数组长度。
(3)返回的实体类,一定要遵循,跟头部一一对应的原则,例如:
导出的实体类,name要显示在excel的第一列,名字就得放在number这个字段的前面
1.pom.xml配置
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
2.前端代码
2.1jsp页面代码
<div class="float-r">
<a class="red_btn_big addOrEditFoodCategory" type="add" onclick="list.exportReCharge()">+ 导出充值记录</a>
</div>
2.2 js代码
/**
* 导出充值交易信息
*/
exportReCharge : function() {
var methodOfPayment = $('#methodOfPayment option:selected').val();
var paymentStatus = $('#paymentStatus option:selected').val();
var serviceType = $('#serviceType option:selected').val();
var startTime = $('#date0').val();
var endTime = $('#date1').val();
var keywords = $('#keywords').val();
layer.confirm('确定导出充值数据吗?', {
icon : 3,
title : '提示'
}, function(index) {
window.location.href = "export?search=" + keywords
+ "&channels=" + methodOfPayment + "&state="
+ paymentStatus + "&serviceType=" + serviceType
+ "&startTime=" + startTime + "&endTime=" + endTime;
layer.close(index);
});
}
2.3后台controller代码
@RequestMapping("export")
public void exportCharge(HttpServletRequest request, HttpServletResponse response, OrderExportVo exportVo) {
Principal principal = getLoginAdminInfo();
if (null == principal) {
logger.info("--------------获取登录用户身份信息为空!");
return;
}
HashMap<String, Object> conditionMap = new HashMap<>();
try {
boolean superAdmin = false; // 是否为超级管理员
if(principal.getOperatorId() != null && StringUtils.isBlank(principal.getTreeIds())){// 非超级管理员
superAdmin = true;
}
PayOrderInfo payOrderInfo = new PayOrderInfo();
payOrderInfo.setState(exportVo.getState());
payOrderInfo.setServiceType(exportVo.getServiceType());
payOrderInfo.setChannels(exportVo.getChannels());
conditionMap.put("payOrderInfo", payOrderInfo);
conditionMap.put("superAdmin", superAdmin);
conditionMap.put("operatorId", principal.getOperatorId());
conditionMap.put("adminId", principal.getTreeIds());
conditionMap.put("search", exportVo.getSearch());
conditionMap.put("startTime", exportVo.getStartTime());
conditionMap.put("endTime", exportVo.getEndTime());
Collection<OrderVo> dataset = payOrderInfoMapper.getPayOrderList(conditionMap);
for (OrderVo orderVo : dataset) {
String channelsName = "";
if(orderVo.getChannels() != null){
channelsName = ChannelsType.getType(orderVo.getChannels()).getDesc();
}
orderVo.setChannelsName(channelsName);
orderVo.setStateName(OrderState.getType(orderVo.getState()).getDesc());
String serviceTypeName="";
if(orderVo.getServiceType() != null){
serviceTypeName = ServiceType.getType(orderVo.getServiceType()).getDesc();
}
orderVo.setServiceTypeName(serviceTypeName);
}
ExportExcelUtils<OrderVo> excelUtils = new ExportExcelUtils<>();
SXSSFWorkbook exportExcel = null;
String[] headers = { "用户名","手机号","充值金额","退款金额", "余额", "充值平台", "订单号", "支付时间", "支付状态", "支付类型", "操作者" };
int[] widths = {68,90,80,80,80,85,189,140,68,68,68};
exportExcel = excelUtils.exportExcel("充值", headers,widths, dataset, response.getOutputStream());
String currentDate = TimeUtils.getCurrentTimeStr("yyyyMMddHHmmss");
excelUtils.createSXSSFWorkbook(exportExcel, response, "充值记录" + currentDate + ".xlsx");
} catch (IOException e) {
logger.info("导出充值交易记录失败!", e.getMessage());
}
}
2.4 excel辅助类
package com.parwa.web.util;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 导出Excel工具
* @author wude
*
* @param <T>
*/
public class ExportExcelUtils<T> {
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
exportExcel(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out) {
exportExcel(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
/**
* 生成excel
* @param title sheet名称
* @param headers 表头
* @param width 宽
* @param dataset 当前list对象
* @param out
* @return
*/
public SXSSFWorkbook exportExcel(String title, String[] headers,int[] width,Collection<T> dataset, OutputStream out) {
SXSSFWorkbook exportExcel = exportBigDataExcel(title, headers,width,dataset, out, "yyyy-MM-dd HH:mm:ss");
return exportExcel;
}
/**
* 创建excel文档
* @param wb
* @param response
* @param fileName 文件名称
* @throws IOException
*/
public void createSXSSFWorkbook(Workbook wb,HttpServletResponse response,String fileName) throws IOException {
ServletOutputStream out = null;
try {
// 设置响应头
response.addHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("GBK"), "ISO8859_1"));
out = response.getOutputStream();
wb.write(out);
} catch (Exception e) {
logger.info("导出excel报错!",e.getMessage());
} finally {
if (out != null) {
out.close();
}
}
}
/**
* 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param title 列宽度
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public SXSSFWorkbook exportBigDataExcel(String title, String[] headers,int[] width,Collection<T> dataset, OutputStream out, String pattern) {
/** 声明一个工作簿 **/
SXSSFWorkbook workbook = new SXSSFWorkbook(100); //100为内存缓存数据
if (dataset == null || dataset.size() == 0) {
// 防止数据为空的情况下,excel无法打开
workbook.createSheet();
return workbook;
}
/** 声明一个表格 **/
Sheet sheet = workbook.createSheet(title);
/** 设置表格默认列宽度为15个字节 **/
sheet.setDefaultColumnWidth((int) 16);
sheet.autoSizeColumn(1);
/** 生成样式,用于表格标题行 **/
CellStyle style = workbook.createCellStyle();
/** 设置样式 **/
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setAlignment(CellStyle.ALIGN_CENTER);
/** 生成字体 **/
Font font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.index);
font.setFontHeightInPoints((short) 13);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
/** 将字体应用到样式中 **/
style.setFont(font);
/** 样式二,用于表格内容行 **/
CellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(IndexedColors.WHITE.index);
style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setBorderRight(CellStyle.BORDER_THIN);
style2.setBorderTop(CellStyle.BORDER_THIN);
style2.setAlignment(CellStyle.ALIGN_CENTER); //水平布局:居中;
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
/** 字体二 **/
Font font2 = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font2.setColor(IndexedColors.BLACK.index);
style2.setFont(font2);
/** 声明画图顶级管理器 **/
Drawing patriarch = sheet.createDrawingPatriarch();
/** 设置表格标题行 **/
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
sheet.setColumnWidth(i, (int) (width[i] * 35.7));
}
/** 以下是数据内容 **/
Iterator<T> it = dataset.iterator();
int index = 0;
while(it.hasNext() && it != null){
index++;
row = sheet.createRow(index);
T t = (T) it.next();
/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
Field[] fields = t.getClass().getDeclaredFields();
for(int i = 0;i < fields.length;i++){
if(i == headers.length){
break;
}
Cell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
/** 判断值的类型后进行强制类型转换 **/
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "是";
if (!bValue) {
textValue ="否";
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
/** 有图片时,设置行高为60px **/
row.setHeightInPoints(60);
/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, Workbook.PICTURE_TYPE_JPEG));
} else if(value instanceof Calendar){
Calendar cale = Calendar.getInstance();
Date tasktime = cale.getTime();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = df.format(tasktime);
} else{
/** 其它数据类型都当作字符串简单处理 **/
if(value != null){
textValue = value.toString();
}
}
/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
if(textValue!=null){
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if(matcher.matches()){
/** 是数字当作double处理 **/
cell.setCellValue(Double.parseDouble(textValue));
}else{
XSSFRichTextString richString = new XSSFRichTextString(textValue);
Font font3 = workbook.createFont();
font3.setColor(IndexedColors.BLACK.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}else{
cell.setCellValue("");
}
} catch (Exception e) {
e.printStackTrace();
logger.info("导出excel报错!",e.getMessage());
} finally {
//清理资源
}
cell.setCellStyle(style2);
}
}
return workbook;
}
/**
* 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public HSSFWorkbook exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
/** 声明一个工作簿 **/
HSSFWorkbook workbook = new HSSFWorkbook();
/** 声明一个表格 **/
HSSFSheet sheet = workbook.createSheet(title);
/** 设置表格默认列宽度为15个字节 **/
sheet.setDefaultColumnWidth((int) 16);
sheet.autoSizeColumn(1);
/** 生成样式,用于表格标题行 **/
HSSFCellStyle style = workbook.createCellStyle();
/** 设置样式 **/
style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
/** 生成字体 **/
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
/** 将字体应用到样式中 **/
style.setFont(font);
/** 样式二,用于表格内容行 **/
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
/** 字体二 **/
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font2.setColor(HSSFColor.BLACK.index);
style2.setFont(font2);
/** 声明画图顶级管理器 **/
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
/** 设置表格标题行 **/
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
/** 以下是数据内容 **/
Iterator<T> it = dataset.iterator();
int index = 0;
while(it.hasNext() && it != null){
index++;
row = sheet.createRow(index);
T t = (T) it.next();
/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
Field[] fields = t.getClass().getDeclaredFields();
for(int i = 0;i < fields.length;i++){
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
/** 判断值的类型后进行强制类型转换 **/
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "是";
if (!bValue) {
textValue ="否";
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
/** 有图片时,设置行高为60px **/
row.setHeightInPoints(60);
/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else if(value instanceof Calendar){
Calendar cale = Calendar.getInstance();
Date tasktime = cale.getTime();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = df.format(tasktime);
} else{
/** 其它数据类型都当作字符串简单处理 **/
if(value != null){
textValue = value.toString();
}
}
/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
if(textValue!=null){
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if(matcher.matches()){
/** 是数字当作double处理 **/
cell.setCellValue(Double.parseDouble(textValue));
}else{
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLACK.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}else{
cell.setCellValue("");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
//清理资源
}
}
}
return workbook;
}
public static void main(String[] args) {
Timestamp time = TimeUtils.getCurrentTime();
if(time instanceof Date){
System.out.println("时间类型");
}
}
}
可能存在问题
实体类
因为set get是遵守驼峰规则的,按道理生成的get方法应该是getHMaxTemp,但是这里却不是,就是因为h后面接的是大写字母,如果是thMaxTemp,生成的get方法,就是getThMaxTemp,所以编码的时候,保持一个良好的习惯很重要,但是,项目用了一段时间,如果只是这个大小写的问题,就大改动,这样改动量也太大勒,这里我分享另外一种方法。
找到这个工具类的String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);,如上图所示换成下面的图片效果
需要增加一个辅助反射类
ReflectUtil.java
package com.cloudtech.web.util;
import java.lang.reflect.Field;
import java.util.ArrayList;
import com.cloudtech.web.entity.RuleCode;
public class ReflectUtil {
/**
* 使用反射设置变量值
*
* @param target 被调用对象
* @param fieldName 被调用对象的字段,一般是成员变量或静态变量,不可是常量!
* @param value 值
* @param <T> value类型,泛型
*/
public static <T> void setValue(Object target,String fieldName,T value) {
try {
Class c = target.getClass();
Field f = c.getDeclaredField(fieldName);
f.setAccessible(true);
f.set(target, value);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 使用反射获取变量值
*
* @param target 被调用对象
* @param fieldName 被调用对象的字段,一般是成员变量或静态变量,不可以是常量
* @param <T> 返回类型,泛型
* @return 值
*/
public static <T> T getValue(Object target,String fieldName) {
T value = null;
try {
Class c = target.getClass();
Field f = c.getDeclaredField(fieldName);
f.setAccessible(true);
value = (T) f.get(target);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return value;
}
public static void main(String[] args) {
RuleCode code = new RuleCode();
code.setId(1234);
//Object value = ReflectUtil.getValue(code, "id");
System.out.println(code.getId());
/* String s ="avgSpd gt hThreeMaxSpd";
String replace = s.replace("gt", "");
String[] split = replace.split(" ");
System.out.println();*/
}
}
3.对比
建议用后面这种,毕竟可以兼容实体类字段命名不规范的问题
4.优化后excel辅助类
注意:使用的时候,使用反射方式,去掉多余的代码,使用SXSSFWorkbook,性能更优
package com.cloudtech.web.util;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 导出Excel工具
*
* @author wude
*
* @param <T>
*/
public class ExportExcelUtils<T> {
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
exportExcel(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
/**
* 生成excel
*
* @param title
* sheet名称
* @param headers
* 表头
* @param width
* 宽
* @param dataset
* 当前list对象
* @param out
* @return
*/
public SXSSFWorkbook exportExcel(String title, String[] headers, int[] width, Collection<T> dataset,
OutputStream out) {
SXSSFWorkbook exportExcel = exportBigDataExcel(title, headers, width, dataset, out, "yyyy-MM-dd HH:mm:ss");
return exportExcel;
}
public SXSSFWorkbook exportMapExcel(String title, String[] headers, int[] width,
LinkedHashMap<String, LinkedHashMap<String, Object>> dataset, OutputStream out) {
SXSSFWorkbook exportExcel = exportBigMapDataExcel(title, headers, width, dataset, out, "yyyy-MM-dd HH:mm:ss");
return exportExcel;
}
/**
* 创建excel文档
*
* @param wb
* @param response
* @param fileName
* 文件名称
* @throws IOException
*/
public void createSXSSFWorkbook(Workbook wb, HttpServletResponse response, String fileName) throws IOException {
ServletOutputStream out = null;
try {
// 设置响应头
response.addHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859_1"));
out = response.getOutputStream();
wb.write(out);
} catch (Exception e) {
logger.info("导出excel报错!", e.getMessage());
} finally {
if (out != null) {
out.close();
}
}
}
/**
* 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param title
* 表格标题名
* @param title
* 列宽度
* @param headers
* 表格属性列名数组
* @param dataset
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
* 此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out
* 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
* 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public SXSSFWorkbook exportBigDataExcel(String title, String[] headers, int[] width, Collection<T> dataset,
OutputStream out, String pattern) {
/** 声明一个工作簿 **/
SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 100为内存缓存数据
if (dataset == null || dataset.size() == 0) {
// 防止数据为空的情况下,excel无法打开
workbook.createSheet();
return workbook;
}
/** 声明一个表格 **/
Sheet sheet = workbook.createSheet(title);
/** 设置表格默认列宽度为15个字节 **/
sheet.setDefaultColumnWidth((int) 16);
sheet.autoSizeColumn(1);
/** 生成样式,用于表格标题行 **/
CellStyle style = workbook.createCellStyle();
/** 设置样式 **/
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setAlignment(CellStyle.ALIGN_CENTER);
/** 生成字体 **/
Font font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.index);
font.setFontHeightInPoints((short) 13);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
/** 将字体应用到样式中 **/
style.setFont(font);
/** 样式二,用于表格内容行 **/
CellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(IndexedColors.WHITE.index);
style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setBorderRight(CellStyle.BORDER_THIN);
style2.setBorderTop(CellStyle.BORDER_THIN);
style2.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局:居中;
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
/** 字体二 **/
Font font2 = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font2.setColor(IndexedColors.BLACK.index);
style2.setFont(font2);
/** 声明画图顶级管理器 **/
Drawing patriarch = sheet.createDrawingPatriarch();
/** 设置表格标题行 **/
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
sheet.setColumnWidth(i, (int) (width[i] * 35.7));
}
/** 以下是数据内容 **/
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext() && it != null) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (i == headers.length) {
break;
}
Cell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
try {
Object value = ReflectUtil.getValue(t, fieldName);
/** 判断值的类型后进行强制类型转换 **/
String textValue = null;
if (value == null) {
cell.setCellValue("");
} else {
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "是";
if (!bValue) {
textValue = "否";
}
cell.setCellValue(textValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else if (value instanceof Integer) {
if (value != null) {
int intValue = new BigDecimal(value.toString()).intValue();
cell.setCellValue(intValue);
}
} else {
/** 其它数据类型都当作字符串简单处理 **/
if (value != null) {
textValue = value.toString();
cell.setCellValue(textValue);
}
}
}
} catch (Exception e) {
e.printStackTrace();
logger.info("导出excel报错!", e.getMessage());
} finally {
// 清理资源
}
cell.setCellStyle(style2);
}
}
return workbook;
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public SXSSFWorkbook exportBigMapDataExcel(String title, String[] headers, int[] width,
LinkedHashMap<String, LinkedHashMap<String, Object>> dataset, OutputStream out, String pattern) {
/** 声明一个工作簿 **/
SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 100为内存缓存数据
if (dataset == null || dataset.size() == 0) {
// 防止数据为空的情况下,excel无法打开
workbook.createSheet();
return workbook;
}
/** 声明一个表格 **/
Sheet sheet = workbook.createSheet(title);
/** 设置表格默认列宽度为15个字节 **/
sheet.setDefaultColumnWidth((int) 16);
sheet.autoSizeColumn(1);
/** 生成样式,用于表格标题行 **/
CellStyle style = workbook.createCellStyle();
/** 设置样式 **/
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setAlignment(CellStyle.ALIGN_CENTER);
/** 生成字体 **/
Font font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.index);
font.setFontHeightInPoints((short) 13);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
/** 将字体应用到样式中 **/
style.setFont(font);
/** 样式二,用于表格内容行 **/
CellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(IndexedColors.WHITE.index);
style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setBorderRight(CellStyle.BORDER_THIN);
style2.setBorderTop(CellStyle.BORDER_THIN);
style2.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局:居中;
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
/** 字体二 **/
Font font2 = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font2.setColor(IndexedColors.BLACK.index);
style2.setFont(font2);
/** 声明画图顶级管理器 **/
Drawing patriarch = sheet.createDrawingPatriarch();
/** 设置表格标题行 **/
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
sheet.setColumnWidth(i, (int) (width[i] * 35.7));
}
/** 以下是数据内容 **/
int index = 0;
for (Entry<String, LinkedHashMap<String, Object>> key : dataset.entrySet()) {
index++;
row = sheet.createRow(index);
Cell cell = row.createCell(0);
cell.setCellValue(key.getKey());
cell.setCellStyle(style2);
LinkedHashMap<String, Object> hourValues = key.getValue();
int i = 1;
for (Entry<String, Object> hourKey : hourValues.entrySet()) {
cell = row.createCell(i);
try {
/** 判断值的类型后进行强制类型转换 **/
String textValue = hourKey.getValue().toString();
cell.setCellValue(textValue);
} catch (Exception e) {
e.printStackTrace();
logger.info("导出excel报错!", e.getMessage());
} finally {
// 清理资源
}
cell.setCellStyle(style2);
i++;
}
}
/*
* Iterator<T> it = (Iterator<T>) dataset.keySet().iterator(); int index
* = 0; while (it.hasNext() && it != null) { index++; row =
* sheet.createRow(index); T t = (T) it.next();
*//** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
/*
* Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0; i
* < fields.length; i++) { if (i == headers.length) { break; } Cell cell
* = row.createCell(i);
*
* Field field = fields[i]; String fieldName = field.getName(); //
* String getMethodName = "get" + fieldName.substring(0, //
* 1).toUpperCase() + fieldName.substring(1);
*
* try { Class tCls = t.getClass(); Object value =
* ReflectUtil.getValue(t, fieldName); // Method getMethod =
* tCls.getMethod(getMethodName, new // Class[] {}); // Object value =
* getMethod.invoke(t, new Object[] {});
*//** 判断值的类型后进行强制类型转换 **/
/*
* String textValue = null; if (value instanceof Boolean) { boolean
* bValue = (Boolean) value; textValue = "是"; if (!bValue) { textValue =
* "否"; } } else if (value instanceof Date) { Date date = (Date) value;
* SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue =
* sdf.format(date); } else if (value instanceof byte[]) {
*//** 有图片时,设置行高为60px **/
/*
* row.setHeightInPoints(60);
*//** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
/*
* sheet.setColumnWidth(i, (short) (35.7 * 80)); byte[] bsValue =
* (byte[]) value; XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0,
* 1023, 255, (short) 6, index, (short) 6, index);
* anchor.setAnchorType(2); patriarch.createPicture(anchor,
* workbook.addPicture(bsValue, Workbook.PICTURE_TYPE_JPEG)); } else if
* (value instanceof Calendar) { Calendar cale = Calendar.getInstance();
* Date tasktime = cale.getTime(); SimpleDateFormat df = new
* SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); textValue =
* df.format(tasktime); } else {
*//** 其它数据类型都当作字符串简单处理 **/
/*
* if (value != null) { textValue = value.toString(); } }
*//** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
/*
* if (textValue != null) { Pattern p =
* Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher =
* p.matcher(textValue); if (matcher.matches()) {
*//** 是数字当作double处理 **//*
* cell.setCellValue(Double.parseDouble(
* textValue)); } else { XSSFRichTextString
* richString = new
* XSSFRichTextString(textValue); Font font3 =
* workbook.createFont();
* font3.setColor(IndexedColors.BLACK.index);
* richString.applyFont(font3);
* cell.setCellValue(richString); } } else {
* cell.setCellValue(""); } } catch (Exception
* e) { e.printStackTrace();
* logger.info("导出excel报错!", e.getMessage()); }
* finally { // 清理资源 }
* cell.setCellStyle(style2); } }
*/
return workbook;
}
/**
* 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param dataset
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
* 此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out
* 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
* 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public HSSFWorkbook exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out,
String pattern) {
/** 声明一个工作簿 **/
HSSFWorkbook workbook = new HSSFWorkbook();
/** 声明一个表格 **/
HSSFSheet sheet = workbook.createSheet(title);
/** 设置表格默认列宽度为15个字节 **/
sheet.setDefaultColumnWidth((int) 16);
sheet.autoSizeColumn(1);
/** 生成样式,用于表格标题行 **/
HSSFCellStyle style = workbook.createCellStyle();
/** 设置样式 **/
style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
/** 生成字体 **/
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
/** 将字体应用到样式中 **/
style.setFont(font);
/** 样式二,用于表格内容行 **/
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
/** 字体二 **/
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font2.setColor(HSSFColor.BLACK.index);
style2.setFont(font2);
/** 声明画图顶级管理器 **/
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
/** 设置表格标题行 **/
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
/** 以下是数据内容 **/
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext() && it != null) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
/** 判断值的类型后进行强制类型转换 **/
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "是";
if (!bValue) {
textValue = "否";
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
/** 有图片时,设置行高为60px **/
row.setHeightInPoints(60);
/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,
index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else if (value instanceof Calendar) {
Calendar cale = Calendar.getInstance();
Date tasktime = cale.getTime();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = df.format(tasktime);
} else {
/** 其它数据类型都当作字符串简单处理 **/
if (value != null) {
textValue = value.toString();
}
}
/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
/** 是数字当作double处理 **/
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLACK.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
} else {
cell.setCellValue("");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
return workbook;
}
public static void main(String[] args) {
Timestamp time = TimeUtils.getCurrentTime();
if (time instanceof Date) {
System.out.println("时间类型");
}
}
}
4.性能分析
列有69列,导出excel分析
优化前:
通过图片,我们可以发现sql的时间很长,当然跟我的列有关,69列
5w数据需要31.42s,而1w数据是6.5s,我们是不是可以做一些优化。列入拿1w作为切割,5w数据分为5次查询出来,再组装数据,是不是可以提升查询数据。所以,这里需要使用多线程。这样整个导出的时长就短了不少。
优化后:
通过数据分析,发现,性能方面有质的提高,而且缓存数大小,性能没有什么明显的提示,所以建议缓存数还是设置为100把
缓存数:
设置为100后,超过100的部分会写入硬盘,而不是所有的都存放在内存中
性能优化部分:
/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
/** 是数字当作double处理 **/
cell.setCellValue(Double.parseDouble(textValue));
} else {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
Font font3 = workbook.createFont();
font3.setColor(IndexedColors.BLACK.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
} else {
cell.setCellValue("");
}
主要是正则方面的判断,消耗勒大量的数据,导致时间很长