代码
1.工具类ExportUtil
/**
* 实体类导出到excel
* <br/>
* headers: 导出excel的标头,标题需要按照T的属性定义顺序编写 <br/>
* dataList: 要被导出的数据<br/>
* ****************************<br/>
* eg:
* class T{<br/>
* private int id;<br/>
* private String name;<br/>
* private String age;<br/>
* }<br/>
* headers[]{编号, 姓名, 年龄}<br/>
* *****************************<br/>
* @param path
* @param dataList
* @throws IOException
*/
public static <T> void exportExcel(String[] headers, OutputStream out, List<T> dataList) throws IOException {
// 实例化Excel表格
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 导出到Excel
exportToExcel2(headers, dataList, workbook, out);
}
/**
* 自定义类型导出excel
* @param out<br/>
* @param dataList<br/>
* @param keys-Map数据的key集合<br/>
* @param headers-excel的标题<br/>
* keys的顺序需要和headers顺序保持一致<br/>
* *******************************<br/>
* eg:<br/>
* headers[]{编号,名称,创建日期}<br/>
* keys[]{id,name,createTime}<br/>
* ******************************<br/>
* @throws IOException
*/
public static void exportExcel(ServletOutputStream out, List<Map<String, Object>> dataList, String[] keys,
String[] headers) throws IOException {
// 实例化Excel表格
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 导出到Excel
exportToExcel2(headers, keys, dataList, workbook, out);
}
/**
* @param headers
* @param list
* @param wb
* @param out
* @throws IOException
*/
private static <T> void exportToExcel2(String[] headers, List<T> list, SXSSFWorkbook wb, OutputStream out)
throws IOException {
// 创建一个sheet表单
SXSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth(25);
sheet.setDefaultRowHeight((short) 10);
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
SXSSFRow row = sheet.createRow(0);
SXSSFCell cell = null;
XSSFRichTextString txt = null;
// 设置标头
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
txt = new XSSFRichTextString(headers[i]);
cell.setCellValue(txt);
}
Iterator<T> it = list.iterator();
int index = 0;
Object tmp = null;
SXSSFCell cl;
String MethodName;
Class clz;
String name;
Method me;
int index2;
try {
while (it.hasNext()) {
if (index > 29999) { // 大于30000行创建新的sheet
sheet = wb.createSheet();
sheet.setDefaultColumnWidth(25);
sheet.setDefaultRowHeight((short) 10);
row = sheet.createRow(0);
// 设置标头
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
txt = new XSSFRichTextString(headers[i]);
cell.setCellValue(txt);
}
index = 0;
}
index++;
row = sheet.createRow(index);
T t = it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
index2 = 0;
for (int j = 0; j < fields.length; j++) {
name = fields[j].getName();
MethodName = "get".concat(name.substring(0, 1).toUpperCase().concat(name.substring(1)));
clz = t.getClass();
me = clz.getMethod(MethodName, new Class[] {});
tmp = me.invoke(t, new Object[] {});
cl = row.createCell(index2);
cl.setCellStyle(style);
if (StrUtil.isBlankOrNull(String.valueOf(tmp))) {
tmp = "-";
}
if (tmp instanceof Date) {
Date date = (Date) tmp;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
tmp = sdf.format(date);
}
cl.setCellValue(String.valueOf(tmp));
index2++;
if (j == headers.length - 1) {
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 将文件输出到客户端浏览器
try {
wb.write(out);
out.flush();
out.close();
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param headers
* @param keys
* @param list
* @param wb
* @param out
* @throws IOException
*/
private static void exportToExcel2(String[] headers, String[] keys, List<Map<String, Object>> list, SXSSFWorkbook wb,
ServletOutputStream out) throws IOException {
// 创建一个sheet表单
SXSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth(25);
sheet.setDefaultRowHeight((short) 10);
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
SXSSFRow row = sheet.createRow(0);
SXSSFCell cell = null;
XSSFRichTextString txt = null;
// 设置标头
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
txt = new XSSFRichTextString(headers[i]);
cell.setCellValue(txt);
}
Iterator<Map<String, Object>> it = list.iterator();
int index = 0;
Object tmp = null;
Map<String, Object> map;
SXSSFCell cl;
try {
while (it.hasNext()) {
if (index > 29999) { // 大于30000行创建新的sheet
sheet = wb.createSheet();
sheet.setDefaultColumnWidth(25);
sheet.setDefaultRowHeight((short) 10);
row = sheet.createRow(0);
// 设置标头
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
txt = new XSSFRichTextString(headers[i]);
cell.setCellValue(txt);
}
index = 0;
}
index++;
row = sheet.createRow(index);
map = it.next();
for (int i = 0; i < keys.length; i++) {
cl = row.createCell(i);
cl.setCellStyle(style);
tmp = map.get(keys[i]);
if (StringUtil.isBlankOrNull(String.valueOf(tmp))) {
tmp = "-";
}
if (tmp instanceof Date) {
Date date = (Date) tmp;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
tmp = sdf.format(date);
}
cl.setCellValue(String.valueOf(tmp));
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 将文件输出到客户端浏览器
try {
wb.write(out);
out.flush();
out.close();
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- controller调用代码
@ResponseBody
@RequestMapping(value = "/exportOrderInfo", produces = "application/json;charset=utf-8;")
public String exportInfo(HttpServletRequest request, HttpServletResponse response) {
response.setContentType("application/binary;charset=UTF-8");
List<OrderInfo> List = orderService.getOrderInfo(paramMap);
// String path = "C:\\Users\\Administrator\\Desktop\\work_temp_store";
// SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
// String filename = "table_" + format.format(new Date()) + ".xlsx";
// File saveDir = new File(path);
// if (!saveDir.exists()) {
// saveDir.mkdirs();// 如果文件不存在则创建文件夹
// }
// 文件路径
// path = path + File.separator + filename;
// File tempFile = new File(path); // 初始化临时文件;
// OutputStream out = new FileOutputStream(tempFile);
// ServletOutputStream out = new FileOutputStream(tempFile);
ServletOutputStream out = response.getOutputStream();
// 时间格式化
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
// 要保存的文件名
String fileName = new String(("OrderInfo" + format.format(new Date())).getBytes(), "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Type", "application/force-download");
response.setHeader("Content-Length", "chunked");
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
String[] headers = { "订单编号","订单人","订单时间"... };//需与属性顺序保持一致
ExcelUtil.exportExcel(headers, out, list);
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
return res.toJsonString();
}
3.OrderInfo
public class OrderInfo{
private String orderId;
private String userName;
private String createTime;
....
//excel更具上面定义顺序遍历属性
}
- 缺陷是当在web项目中导出时如果数据量过大会造成等待时间过久而超时的问题,解决方案是多线程,异步导出,或者分页分批处理打包成zip或最后合并。