大批量数据excel下载—本文作者只试了51万数据的下载，用时7秒
大家好，又见面了，我是你们的朋友全栈君。
一.背景:
现在的项目里，有诸多下载功能，随着数据越来越多，下载的时间也越来越长，很影响用户体验，为了解决这一问题，我不得不挺身而出，斩破难关。项目中原本用的是poi-HSSFWorkbook，但是如果是50万数据量下载，回经历一个漫长的等待过程，然后内存溢出。jxl也不用想了，估计也差不多。
二.两种方法：
后来从网上搜索发现针对大数据量的导出有两条路可以走：第一：用poi-SXSSFWorkbook；第二：用io流的方式。
1. 好吧，先试了第一种SXSSFWorkbook的方式解决问题，最后我水平有限，没能成功的使用第一种SXSSFWorkbook的思路解决50万数据的导出问题，因为系统也崩了。不过我觉得失败的原因是我代码写的有问题，没有正确使用SXSSFWorkbook用的不对，所以虽然我没有成功，但是我还是要贴出这个思路的两个博客，各位看客可以尝试下，我觉得这个思路是可行的：
http://blog.csdn.net/qq_29631809/article/details/72785338 https://www.programcreek.com/java-api-examples/index.php?api=org.apache.poi.xssf.streaming.SXSSFWorkbook
2. 那么只能把希望留给第二种io流的方式了。
先给大家一个连接，我就是从这个连接的内容获取的思路，大家先看看，不知道你们有没有什么想法：
http://blog.csdn.net/xx123698/article/details/48782013
不愿意看此链接的，可以直接看我截的图：
三.具体思路
到此，相信诸位看官已经有了方向，不过具体实现起来，仍是很模糊。我来叙述一下我的思路，以下5步：
四.思路明白了，就是上代码了
1.Controller类
package com.quanran.controller;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import springfox.documentation.annotations.ApiIgnore;
import com.github.pagehelper.Page;
import com.quanran.HardwareMonitoringVo;
import com.quanran.TerminalService;
* <p>Discription:[查询设备监听列表并导出]</p>
* Created on 2018年2月2日 下午5:45:37
* @param hardwareMonitoringVo 封装查询参数officeBuildingId和terminalDeviceType的实体类
* @param pageNum 当前页数
* @param pageSize 每页显示的条数,如果查询所有传 -1
* @param response 返回给前台的response对象
* @author:[全冉]
@Api(value = "设备管理相关接口", description = "设备管理相关接口")
@RestController
@RequestMapping("/terminal")
public class EcmTerminalController {
@Resource
private TerminalService terminalService;
* <p>Discription:[查询设备监听列表并导出]</p>
* Created on 2017年12月11日
* @param response response
* @author:[全冉]
@ApiOperation("查询设备监听列表并导出[[email protected]]")
@GetMapping("/exportHardwareMonitoring")
@ApiImplicitParams({
@ApiImplicitParam(name = "officeBuildingId", value = "办公区", required = true, paramType = "query"),
@ApiImplicitParam(name = "terminalDeviceType", value = "1:pad 2:终端机", required = false, paramType = "query"),
@ApiImplicitParam(name = "pageNum", value = "当前页码", required = true, paramType = "query"),
@ApiImplicitParam(name = "pageSize", value = "当页大小,如果查询所有 pageSize = -1", required = true, paramType = "query")
public void exportHardwareMonitoring(@ApiIgnore() HardwareMonitoringVo hardwareMonitoringVo,
@ApiParam(value = "当前页码", required = true) @RequestParam(required = true) int pageNum,
@ApiParam(value = "当页大小,如果查询所有 pageSize = -1", required = true) @RequestParam(required = true) int pageSize,
HttpServletResponse response) {
terminalService.exportHardwareMonitoring(hardwareMonitoringVo, new Page<HardwareMonitoringVo>(pageNum, pageSize), response);
}2.TerminalService类
package com.quanran.service;
import javax.servlet.http.HttpServletResponse;
import com.github.pagehelper.Page;
import com.quanran.dao.dto.HardwareMonitoringVo;
* Description: [设备管理服务]
* Created on 2017年11月09日
* @author <a href="mailto: [email protected]">全冉</a>
* @version 1.0
public interface TerminalService {
* <p>Discription:[查询设备监听列表并导出]</p>
* Created on 2018年2月2日 下午5:54:06
* @param hardwareMonitoringVo 封装前台传过来的查询参数
* @param page 分页对象，封装了pageNum和pageSize两个参数
* @param response response对象
* @author:[全冉]
void exportHardwareMonitoring(HardwareMonitoringVo hardwareMonitoringVo, Page<HardwareMonitoringVo> page, HttpServletResponse response);
}3.TerminalServiceImpl类
package com.quanran.service.impl;
import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import com.github.pagehelper.Page;
import com.quanran.common.log.LogMessage;
import com.quanran.dao.dto.HardwareMonitoringVo;
import com.quanran.dao.mapper.HardwareMonitoringMapper;
import com.quanran.service.TerminalService;
import com.quanran.service.multithreading.HardwareMonitoringThread;
import com.quanran.visitor.common.util.ExcelExportUtils;
* Description: [设备管理服务实现]
* Created on 2017年11月09日
* @author <a href="mailto: [email protected]">全冉</a>
* @version 1.0
@Component
public class TerminalServiceImpl implements TerminalService {
@Resource
private HardwareMonitoringMapper hardwareMonitoringMapper;
* 打印日志
private static final Logger LOGGER = LoggerFactory.getLogger(TerminalServiceImpl.class);
public void exportHardwareMonitoring(HardwareMonitoringVo hardwareMonitoringVo, Page<HardwareMonitoringVo> hardwareMonitorings, HttpServletResponse response) {
LOGGER.info("进入【TerminalServiceImpl-exportHardwareMonitoring】方法，开始毫秒为：【"+System.currentTimeMillis()+"】");
try {
* 第一步:准备一些参数
// 此次导出的总行数
Integer allCount = hardwareMonitoringMapper.selectCount(hardwareMonitoringVo);
// excel表头
List<String> headList = new ArrayList<String>(Arrays.asList("设备编号","区域",
"终端设备","硬件","异常原因","异常时间","状态","终端机名称"));
// 计算此次导出需要多少个临时的excle文件
Integer num = allCount/ExcelExportUtils.EXCELSIZE;
if (allCount%ExcelExportUtils.EXCELSIZE != 0) {
num++;
// 存储临时excel的临时文件夹路径
String path = ExcelExportUtils.getTemExcelDirectory();
* 第二步:多线程查询并生成临时的excel文件
threadSelectAndCreateTemExcel(hardwareMonitoringVo, response, ExcelExportUtils.EXCELSIZE, allCount, num, path);
* 第三步:下载
ExcelExportUtils.downloadTemExcel("硬件监控", response, path, num, ExcelExportUtils.EXCELSIZE, headList);
* 第三步:删除
ExcelExportUtils.deleteDir(new File(path));
} catch (Exception e) {
e.printStackTrace();
LOGGER.info("【TerminalServiceImpl-exportHardwareMonitoring】方法异常，异常毫秒为：【"+System.currentTimeMillis()+"】");
LOGGER.info("结束【TerminalServiceImpl-exportHardwareMonitoring】方法，结束毫秒为：【"+System.currentTimeMillis()+"】");
* <p>Discription:[多线程查询并生成临时的excel文件]</p>
* Created on 2018年1月29日 下午2:13:19
* @param hardwareMonitoringVo 查询条件的参数对象
* @param response response对象
* @param excelSize 每次查询并生成一个临时excel文件的条数
* @param allCount 此次请求导出的总行数
* @param num 此次请求需要多少个临时的excle文件
* @param path 存储临时excel文件的临时文件夹路径
* @author:[全冉]
private void threadSelectAndCreateTemExcel(HardwareMonitoringVo hardwareMonitoringVo, HttpServletResponse response,
Integer excelSize, Integer allCount, Integer num, String path) {
try {
LOGGER.info("进入【TerminalServiceImpl-threadSelectAndCreateTemExcel】方法，当前时间为：【"+System.currentTimeMillis()+"】");
// 生成一个线程计数器，每当一个线程的run方法完毕，num的值就减1
CountDownLatch latch = new CountDownLatch(num);
// 用多线程分批从数据库读取数据，每批都会生成一个临时的excel，分了几个批次就有几个excel
for (int i = 0; i < num; i ++) {
hardwareMonitoringVo.setFromIndex(i*excelSize);
// 最后一个线程，查询的条数需要计算
if (i == num -1) {
excelSize = allCount - (i-1)*excelSize;
hardwareMonitoringVo.setHowManyCount(excelSize);
// 每个线程都需要传一个新对象，不然多线程操作的都是同一个结果集，结果不正确
HardwareMonitoringVo vo = new HardwareMonitoringVo();
vo.setOfficeBuildingId(hardwareMonitoringVo.getOfficeBuildingId());
vo.setTerminalDeviceType(hardwareMonitoringVo.getTerminalDeviceType());
vo.setFromIndex(hardwareMonitoringVo.getFromIndex());
vo.setHowManyCount(hardwareMonitoringVo.getHowManyCount());
HardwareMonitoringThread thread = new HardwareMonitoringThread(vo, hardwareMonitoringMapper, response, excelSize, path, latch);
thread.start();
// latch计数器的值不为0，则线程会阻塞，一直等着所有线程都跑完才会继续向下执行
latch.await();
LOGGER.info("结束【TerminalServiceImpl-threadSelectAndCreateTemExcel】方法，当前时间为：【"+System.currentTimeMillis()+"】");
} catch (InterruptedException e) {
LOGGER.error(LogMessage.getNew()
.add("【TerminalServiceImpl-threadSelectAndCreateTemExcel】方法出现异常")
.add("===入参1====", hardwareMonitoringVo.toString())
.add("===入参2====", excelSize)
.add("===入参3====", allCount)
.add("===入参4====", num)
.add("===入参5====", path)
.add("===错误信息====", e)
.add(e).toString());
LOGGER.info("【TerminalServiceImpl-threadSelectAndCreateTemExcel】方法出现异常，当前时间为：【"+System.currentTimeMillis()+"】");
}4.HardwareMonitoringThread类
package com.quanran.service.multithreading;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import javax.servlet.http.HttpServletResponse;
import com.quanran.dao.dto.ExportHardwareMonitoringVo;
import com.quanran.dao.dto.HardwareMonitoringVo;
import com.quanran.dao.mapper.HardwareMonitoringMapper;
import com.quanran.visitor.common.util.ExcelExportUtils;
* <p>Description: [硬件监控的多线程类]</p>
* Created on 2018年2月2日 下午5:58:02
* @author <a href="mailto: [email protected]">全冉</a>
* @version 1.0
public class HardwareMonitoringThread extends Thread {
* 含有分批信息的监控对象
HardwareMonitoringVo hmVo;
* 操作数据库的mapper对象
HardwareMonitoringMapper hardwareMonitoringMapper;
* response对象
HttpServletResponse response;
* 每个批次查询并生成一个临时excel文件的行数
Integer excelSize;
* 存储临时excel文件的文件夹
String path;
* 线程计数器
CountDownLatch latch;
* <p>Discription:[构造函数:利用构造函数传参]</p>
* @coustructor 方法.
public HardwareMonitoringThread(HardwareMonitoringVo hardwareMonitoringVo, HardwareMonitoringMapper hardwareMonitoringMapper, HttpServletResponse response, Integer excelSize, String path, CountDownLatch latch) {
super();
this.hmVo = hardwareMonitoringVo;
this.hardwareMonitoringMapper = hardwareMonitoringMapper;
this.response = response;
this.excelSize = excelSize;
this.path = path;
this.latch = latch;
* <p>Discription:[线程的具体操作]</p>
* Created on 2018年1月29日 下午5:10:06
* @author:[全冉]
@Override
public void run() {
// 利用分页limit实现分批，每个线程hmVo对象的fromIndex和howManyCount属性值都不一样
List<ExportHardwareMonitoringVo> everyQueryList = hardwareMonitoringMapper.selectWhere(hmVo);
// 存储要导出到临时excel文件的行数据
List<List<String>> recordList = new ArrayList<List<String>>();
if (null != everyQueryList && everyQueryList.size() > 0) {
for (int i = 0; i < everyQueryList.size(); i++) {
List<String> rowData = ExcelExportUtils.changList(everyQueryList.get(i),
"id", "officeBuildingName", "terminalDeviceTypeDescription",
"hardwareTypeDescription", "abnormalCause", "abnormalTime", "statusDescription", "terminalName");
recordList.add(rowData);
// 将当前线程查询的数据导入到临时文件夹里一个名为"temExcelFile"的excel文件里
ExcelExportUtils.writeExcelToTemDir("temExcelFile", path, recordList);
// 每个线程完毕，让线程计数器的值减1
latch.countDown();
}5.ExcelExportUtils类
package com.quanran.visitor.common.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.springframework.util.ClassUtils;
import org.springframework.web.util.HtmlUtils;
* <p>Description: [大批量excel下载的工具类]</p>
* Created on 2018年2月2日 下午6:00:47
* @author <a href="mailto: [email protected]">全冉</a>
* @version 1.0
public class ExcelExportUtils {
* 多线程会并发生成临时文件的文件名，所以用同一对象加锁控制
final static String LOCKOBJECT = "lockObject";
* 每次查询并生成一个临时excel文件的行数
public final static Integer EXCELSIZE = 30000;
* 工作空间下的项目名称
final static String PROJECTNAME = "didi-visitor";
* 此属性值作为单文件下载和多文件打包下载的一个标准：即要下载的总数据条数大于此值，则进行多文件打包下载；要是下载的总数据条数小于此值，则进行单文件下载。
final static Integer COUNT = 250000;
* <p>Discription:[生成一个UUID]</p>
* Created on 2018年1月18日 下午7:54:11
* @return String 生成的UUID
* @author:[全冉]
private static String getUUID() {
String uuid = UUID.randomUUID().toString();
//去掉“-”符号
return uuid.replaceAll("-", "");
* <p>Discription:[不同的系统下，每次操作都生成一个临时的文件夹]</p>
* Created on 2018年1月18日 下午7:56:56
* @return String 返回临时文件夹的路径
* @author:[全冉]
public static String getTemExcelDirectory() {
String path = ""; // 临时文件夹路径
String sep = File.separator; // 平台共用路径间隔符
String linuxTemExcelDirectory = sep+"usr"+sep+"local"; // linux系统临时excel的目录
String pathSuffix = "temExcelFolder" + sep + getUUID();
String osName = System.getProperty("os.name");
if (osName.indexOf("Windows") >= 0 || osName.indexOf("window") >= 0 ) {
//windows系统走此
String sourcePath = ClassUtils.getDefaultClassLoader().getResource("").getPath().substring(1);
String[] sourcePathArray = sourcePath.split("/");
for (int i=0;i<sourcePathArray.length;i++) {
if (PROJECTNAME.equals(sourcePathArray[i])) {
path += pathSuffix;
break;
path += sourcePathArray[i]+sep;
} else {
//linux系统走此
path = linuxTemExcelDirectory + sep + pathSuffix;
return path;
* <p>Discription:[每个线程都调此方法将数据导入到临时文件夹里一个名为"temExcelFile_X"的excel文件里]</p>
* Created on 2018年1月19日 上午10:45:53
* @param fileName 文件名称
* @param path 存临时excel文件的文件夹路径
* @param recordList 要导入临时excel的数据
* @author:[全冉]
public static void writeExcelToTemDir(String fileName, String path, List<List<String>> recordList) {
BufferedWriter buff = null;
try {
// 创建临时excel文件时，需要生成不同的名字，这块代码可能并发执行，有可能存在多个线程同时操作同一个excel文件，所以加锁
synchronized (LOCKOBJECT) {
// 临时文件夹路径不存在就创建
File file = new File(path);
if (!file.exists()) {
file.mkdirs();
// 临时文件夹下所有文件名字组成的字符串数组
String[] children = file.list();
String filePath = path + File.separator + fileName + "_" + (children.length+1) + ".xls";
System.out.println("文件名为：【"+fileName + "_" + (children.length+1) + ".xls"+"】");
OutputStreamWriter ow = new OutputStreamWriter(new FileOutputStream(filePath),"GBK");
// 生成文件
buff = new BufferedWriter(ow);
// 临时excel文件里的每一行数据
List<String> currentRecord = new ArrayList<String>();
StringBuffer currentSb = null;
for (Integer j = 0; j < recordList.size(); j ++) {
currentRecord = recordList.get(j);
currentSb = new StringBuffer();
// 循环，将一行数据的每个列都追加到一个可变字符串上
for (int m = 0; m < currentRecord.size(); m ++) {
if (m == currentRecord.size()-1) {
currentSb.append(currentRecord.get(m));
} else {
currentSb.append(currentRecord.get(m)).append("\t");
// 往临时excel里写入当前行的数据
buff.write(currentSb.toString());
// 往临时excel里写入换行
buff.write("\r\n");
buff.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (buff != null) {
buff.close();
buff = null;
// 召唤jvm的垃圾回收器
System.gc();
} catch (IOException e) {
e.printStackTrace();
* <p>Discription:[将临时文件从临时文件下载到本地]</p>
* Created on 2018年1月29日 下午6:58:18
* @param fileName 下载的文件名称
* @param response response对象
* @param path 存储临时excel的临时文件夹路径
* @param num 临时的excle文件个数
* @param excelSize 临时excel文件的行数
* @param headList excel表头
* @author:[全冉]
public static void downloadTemExcel(String fileName, HttpServletResponse response, String path, Integer num, Integer excelSize, List<String> headList) {
File file = new File(path);
if (file.isDirectory()) {
String[] children = file.list();
// 判断是单文件下载还是多文件打包下载
Integer allRecordCount = excelSize*children.length;
if (allRecordCount <= COUNT) {
// 单文件下载(下载到本地是一个excel文件)
singleFileDownload(fileName, path, children, response, headList);
if (allRecordCount > COUNT) {
// 多文件打包下载
multiFileDownload(fileName, path, children, allRecordCount, COUNT, response, headList);
* <p>Discription:[单文件下载]</p>
* Created on 2018年1月29日 下午7:12:34
* @param fileName 下载的文件名称
* @param path 存储临时excel的临时文件夹路径
* @param children path路径下的所有临时excel的名字拼成的字符串数组
* @param response response对象
* @param headList excel表头
* @author:[全冉]
private static void singleFileDownload(String fileName, String path, String[] children, HttpServletResponse response, List<String> headList) {
InputStream fis = null;
OutputStream os = null;
File outfile = null;
byte[] buffer = null;
try {
// 生成表头
StringBuffer headSb = new StringBuffer();
for (int i = 0; i < headList.size(); i ++) {
if (i == headList.size()-1) {
headSb.append(headList.get(i)).append("\r\n");
} else {
headSb.append(headList.get(i)).append("\t");
byte[] headBuffer = headSb.toString().getBytes("GBK");
// 将表头的字节长度也加进到下载的文件字节长度里
long countLength = headBuffer.length;
for (int i = 0; i < children.length; i ++) {
outfile = new File(path, children[i]);
countLength += outfile.length();
// 设置response对象，获取response的输出流
response.reset(); //重置结果集
response.addHeader("Content-Disposition", "attachment;filename=" + new String((fileName+".xls").getBytes("utf-8"), "iso8859-1")); //返回头 文件名
response.addHeader("Content-Length", "" + countLength); //返回头 文件大小
response.setContentType("application/octet-stream"); //设置数据种类
os = new BufferedOutputStream(response.getOutputStream());
// 将表头插入到excel中
fis = new BufferedInputStream(new ByteArrayInputStream(headBuffer));
fis.read(headBuffer); //读取文件流
os.write(headBuffer); // 输出文件
os.flush();
// 将每一个临时excel都导出
for (int i = 0; i < children.length; i ++) {
outfile = new File(path, children[i]);
fis = new BufferedInputStream(new FileInputStream(outfile));
buffer = new byte[fis.available()];
fis.read(buffer); //读取文件流
os.write(buffer); // 输出文件
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (os != null) {
os.close();
os = null;
if (fis != null) {
fis.close();
fis = null;
// 召唤jvm的垃圾回收器
System.gc();
} catch (IOException e) {
e.printStackTrace();
* <p>Discription:[多个文件，打包下载]</p>
* Created on 2018年1月29日 下午7:26:21
* @param zipName 压缩包名称
* @param path 存储临时excel的临时文件夹路径
* @param children path路径下的所有临时excel的名字拼成的字符串数组
* @param allRecordCount 所有临时excel文件的行数之和
* @param count 下载到客户端的excel最多能装的记录条数
* @param response response对象
* @param headList excel表头
* @author:[全冉]
private static void multiFileDownload(String zipName, String path, String[] children, Integer allRecordCount,
Integer count, HttpServletResponse response, List<String> headList) {
// 生成表头
StringBuffer headSb = new StringBuffer();
for (int i = 0; i < headList.size(); i ++) {
if (i == headList.size()-1) {
headSb.append(headList.get(i)).append("\r\n");
} else {
headSb.append(headList.get(i)).append("\t");
// 计算下载到客户端的zip包里会有多少个excel文件
Integer excelNum = allRecordCount/count;
if (allRecordCount%count != 0) {
excelNum++;
// 临时文件里多少个excel生成一个zip包里的excel
Integer temNum = children.length/excelNum;
// beforeList的值为往压缩包里放入新文件的依据；afterList的值为压缩包里关闭一个新文件流的依据
List<Integer> beforeList = new ArrayList<Integer>();
List<Integer> afterList = new ArrayList<Integer>();
for (int i = 0; i < children.length; i ++) {
if (i%temNum == 0) {
beforeList.add(i);
if (i != 0 && i%temNum == 0) {
afterList.add(i-1);
if (i == children.length-1) {
afterList.add(i);
ZipOutputStream zipos = null;
DataOutputStream os = null;
InputStream is = null;
try {
// 解决不同浏览器压缩包名字含有中文时乱码的问题
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename=" + new String((zipName+".zip").getBytes("utf-8"), "iso8859-1"));
// 设置压缩流：直接写入response，实现边压缩边下载
zipos = new ZipOutputStream(new BufferedOutputStream(response.getOutputStream()));
// 设置压缩方式
zipos.setMethod(ZipOutputStream.DEFLATED);
// 压缩包里多个文件的名字下标
Integer nameIndex = 1;
// 循环将文件写入压缩流
for (int i = 0; i < children.length; i ++) {
// 添加ZipEntry对象到压缩流中
if (beforeList.contains(i)) {
zipos.putNextEntry(new ZipEntry(zipName+"_"+nameIndex+".xls"));
nameIndex++;
// 表头输入到文件中
os = new DataOutputStream(zipos);
is = new BufferedInputStream(new ByteArrayInputStream(headSb.toString().getBytes("GBK")));
byte[] b = new byte[100];
int length = 0;
while ((length = is.read(b)) != -1) {
os.write(b, 0, length);
is.close();
is = null;
// 生成当前File对象
File file = new File(path, children[i]);
// 将压缩流写入文件流
os = new DataOutputStream(zipos);
is = new FileInputStream(file);
byte[] b = new byte[100];
int length = 0;
while ((length = is.read(b)) != -1) {
os.write(b, 0, length);
is.close();
is = null;
// 关闭当前Entry对象的压缩流
if (afterList.contains(i)) {
zipos.closeEntry();
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null) {
is.close();
is = null;
if (os != null) {
os.close();
os = null;
if (zipos != null) {
zipos.close();
zipos = null;
// 召唤jvm的垃圾回收器
System.gc();
} catch (IOException e) {
e.printStackTrace();
* <p>Discription:[实体类装换为字符串List集合]</p>
* Created on 2018年1月19日 下午1:59:50
* @param obj Objec的子类
* @param propertyNames 多个属性
* @return List<String> 返回的一行excel数据,均为String类型
* @author:[全冉]
public static List<String> changList(Object obj, String... propertyNames) {
List<String> list = new ArrayList<String>();
String value = "";
Object object = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for(String propertyName : propertyNames){
try {
object = Reflections.invokeGetter(obj, propertyName);
if (object == null ) {
//value = "" ; nothing to do
} else if(object instanceof Date) {
value = sdf.format((Date) object);
} else if(object instanceof String) {
value = HtmlUtils.htmlUnescape(object.toString());
} else {
value = object.toString();
} catch (Exception e) {
throw new RuntimeException(e.getClass().getName()+":"+e.getMessage());
list.add(value);
object = null;
value = "";
return list;
* <p>Discription:[递归删除目录下的所有子子孙孙文件和文件件，最后再删除当前空目录]</p>
* Created on 2018年1月17日 下午6:01:30
* @param dir 将要删除的文件目录
* @return Boolean true:如果所有删除成功，返回true
* false:如果某一个删除失败，后续的不在删除，并且返回false
* @author:[全冉]
public static Boolean deleteDir(File dir) {
if (dir.isDirectory()) {
String[] children = dir.list();
// 递归删除目录中的子目录下
for (int i=0; i<children.length; i++) {
boolean success = deleteDir(new File(dir, children[i]));
if (!success) {
return false;
// 目录此时为空，可以删除
return dir.delete();
}6.Reflections类
package com.quanran.visitor.common.util;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.InvocationTargetException;
* <p>Description: [反射工具类.
* 提供调用getter/setter方法, 访问私有变量, 调用私有方法, 获取泛型类型Class, 被AOP过的真实类等工具函数]</p>
* Created on 2015-04-17
* @author quanran
* @version 1.0
@SuppressWarnings("rawtypes")
public class Reflections {
private static final String SETTER_PREFIX = "set";
private static final String GETTER_PREFIX = "get";
private static final String CGLIB_CLASS_SEPARATOR = "$$";
private static final Logger LOOGER = LoggerFactory.getLogger(Reflections.class);
* <p>Discription:[调用Getter方法.
* 支持多级，如：对象名.对象名.方法]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Object invokeGetter(Object obj, String propertyName) {
Object object = obj;
for (String name : StringUtils.split(propertyName, ".")){
String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(name);
object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});
return object;
* <p>Discription:[调用Setter方法.
* 支持多级，如：对象名.对象名.方法]</p>
* Created on 2015-04-17
* @author:[quanran]
public static void invokeSetter(Object obj, String propertyName, Object value) {
Object object = obj;
String[] names = StringUtils.split(propertyName, ".");
for (int i=0; i<names.length; i++){
if(i<names.length-1){
String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);
object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});
}else{
String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);
invokeMethodByName(object, setterMethodName, new Object[] { value });
* <p>Discription:[直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Object getFieldValue(final Object obj, final String fieldName) {
Field field = getAccessibleField(obj, fieldName);
if (field == null) {
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");
Object result = null;
try {
result = field.get(obj);
} catch (IllegalAccessException e) {
LOOGER.error("不可能抛出的异常{}", e.getMessage());
return result;
* <p>Discription:[直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数]</p>
* Created on 2015-04-17
* @author:[quanran]
public static void setFieldValue(final Object obj, final String fieldName, final Object value) {
Field field = getAccessibleField(obj, fieldName);
if (field == null) {
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");
try {
field.set(obj, value);
} catch (IllegalAccessException e) {
LOOGER.error("不可能抛出的异常:{}", e.getMessage());
* <p>Discription:[直接调用对象方法, 无视private/protected修饰符.
* 用于一次性调用的情况，否则应使用getAccessibleMethod()函数获得Method后反复调用.
* 同时匹配方法名+参数类型]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Object invokeMethod(final Object obj, final String methodName, final Class<?>[] parameterTypes,
final Object[] args) {
Method method = getAccessibleMethod(obj, methodName, parameterTypes);
if (method == null) {
throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
try {
return method.invoke(obj, args);
} catch (Exception e) {
throw convertReflectionExceptionToUnchecked(e);
* <p>Discription:[直接调用对象方法, 无视private/protected修饰符，
* 用于一次性调用的情况，否则应使用getAccessibleMethodByName()函数获得Method后反复调用.
* 只匹配函数名，如果有多个同名函数调用第一个]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Object invokeMethodByName(final Object obj, final String methodName, final Object[] args) {
Method method = getAccessibleMethodByName(obj, methodName);
if (method == null) {
throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
try {
return method.invoke(obj, args);
} catch (Exception e) {
throw convertReflectionExceptionToUnchecked(e);
* <p>Discription:[循环向上转型, 获取对象的DeclaredField, 并强制设置为可访问.
* 如向上转型到Object仍无法找到, 返回null]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Field getAccessibleField(final Object obj, final String fieldName) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(fieldName, "fieldName can't be blank");
for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
try {
Field field = superClass.getDeclaredField(fieldName);
makeAccessible(field);
return field;
} catch (NoSuchFieldException e) {//NOSONAR
// Field不在当前类定义,继续向上转型
continue;// new add
return null;
* <p>Discription:[循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问.
* 如向上转型到Object仍无法找到, 返回null.
* 匹配函数名+参数类型。
* 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args)]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Method getAccessibleMethod(final Object obj, final String methodName,
final Class<?>... parameterTypes) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(methodName, "methodName can't be blank");
for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
try {
Method method = searchType.getDeclaredMethod(methodName, parameterTypes);
makeAccessible(method);
return method;
} catch (NoSuchMethodException e) {
// Method不在当前类定义,继续向上转型
continue;// new add
return null;
* <p>Discription:[循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问.
* 如向上转型到Object仍无法找到, 返回null.
* 只匹配函数名。
* 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args)]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
public static Method getAccessibleMethodByName(final Object obj, final String methodName) {
Validate.notNull(obj, "object can't be null");
Validate.notBlank(methodName, "methodName can't be blank");
for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
Method[] methods = searchType.getDeclaredMethods();
for (Method method : methods) {
if (method.getName().equals(methodName)) {
makeAccessible(method);
return method;
return null;
* <p>Discription:[改变private/protected的方法为public，尽量不调用实际改动的语句，避免JDK的SecurityManager抱怨]</p>
* Created on 2015-04-17
* @author:[quanran]
public static void makeAccessible(Method method) {
if ((!Modifier.isPublic(method.getModifiers()) || !Modifier.isPublic(method.getDeclaringClass().getModifiers()))
&& !method.isAccessible()) {
method.setAccessible(true);
* <p>Discription:[改变private/protected的成员变量为public，尽量不调用实际改动的语句，避免JDK的SecurityManager抱怨]</p>
* Created on 2015-04-17
* @author:[quanran]
public static void makeAccessible(Field field) {
if ((!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers()) || Modifier
.isFinal(field.getModifiers())) && !field.isAccessible()) {
field.setAccessible(true);
* <p>Discription:[通过反射, 获得Class定义中声明的泛型参数的类型, 注意泛型必须定义在父类处
* 如无法找到, 返回Object.class.
* eg.
* public UserDao extends HibernateDao<User>]</p>
* Created on 2015-04-17
* @return object 对象
* @author:[quanran]
@SuppressWarnings("unchecked")
public static <T> Class<T> getClassGenricType(final Class clazz) {
return getClassGenricType(clazz, 0);
* <p>Discription:[通过反射, 获得Class定义中声明的父类的泛型参数的类型.
* 如无法找到, 返回Object.class.
* 如public UserDao extends HibernateDao<User,Long>]</p>
* Created on 2015-04-17
* @return class 类
* @author:[quanran]
public static Class getClassGenricType(final Class clazz, final int index) {
Type genType = clazz.getGenericSuperclass();
if (!(genType instanceof ParameterizedType)) {
LOOGER.warn(clazz.getSimpleName() + "'s superclass not ParameterizedType");
return Object.class;
Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
if (index >= params.length || index < 0) {
LOOGER.warn("Index: " + index + ", Size of " + clazz.getSimpleName() + "'s Parameterized Type: "
+ params.length);
return Object.class;
if (!(params[index] instanceof Class)) {
LOOGER.warn(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");
return Object.class;
return (Class) params[index];
* @param instance 实例
* @return Class 类
public static Class<?> getUserClass(Object instance) {
Assert.notNull(instance, "Instance must not be null");
Class clazz = instance.getClass();
if (clazz != null && clazz.getName().contains(CGLIB_CLASS_SEPARATOR)) {
Class<?> superClass = clazz.getSuperclass();
if (superClass != null && !Object.class.equals(superClass)) {
return superClass;
return clazz;
* <p>Discription:[将反射时的checked exception转换为unchecked exception]</p>
* Created on 2015-04-17
* @return RuntimeException 异常
* @author:[quanran]
public static RuntimeException convertReflectionExceptionToUnchecked(Exception e) {
if (e instanceof IllegalAccessException || e instanceof IllegalArgumentException
|| e instanceof NoSuchMethodException) {
return new IllegalArgumentException(e);
} else if (e instanceof InvocationTargetException) {
return new RuntimeException(((InvocationTargetException) e).getTargetException());