pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency> <dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>1.2.1</version>
</dependency>
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
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.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes; import com.chinaway.g7s.common.G7sException;
import com.chinaway.g7s.service.BaseService;
import com.chinaway.intelli.intelli_core.common.ErrorEnum;
import com.github.pagehelper.PageInfo;
import com.monitorjbl.xlsx.StreamingReader; /**
* excel导入导出工具类
* @author shihaiming
*
*/
public class ExcelUtil { private static Logger log = LoggerFactory.getLogger(ExcelUtil.class); /**
* @param filePath 文件保存绝对路径+文件名称
* @param params 分页查询数据的条件
* @param baseService 相关service
* @param cls 相关实体类
* @param titles excel表头
* @param headFields 导出的实体类的字段名
* @param isDownload false时不弹出下载文件，为true时浏览器会弹出下载文件
* @throws Exception
*/
public static void exportExcel(String filePath, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量
params.put("pageSize", pageSize);
params.put("pageNum", 1);
List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); int total = (int) pageInfo.getTotal();
//每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数
int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){
for (int i = 1; i <= export_times; i++) {
for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
} dataList.clear();
params.put("pageNum", i+1);
params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
list = baseService.findByPage(params);
pageInfo = new PageInfo(list);
dataList = pageInfo.getList();
dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
}
} else {
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); FileOutputStream fOut = new FileOutputStream(filePath);
if (fOut != null) {
try {
wb.write(fOut);
fOut.flush(); // 刷新缓冲区
} catch (Exception e) {
e.printStackTrace();
}
fOut.close();
}
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
} long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); //isDownload为true时，让浏览器弹出下载文件
if (isDownload) {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
try {
File file = new File(filePath);
String fileName = file.getName();// 获取日志文件名称
InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
//然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
// 判断是否火狐浏览器
String agent = request.getHeader("User-Agent");
boolean isFirefox = (agent != null && agent.contains("Firefox"));
if (isFirefox) {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF8");
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Content-Length", "" + file.length());
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
os.write(buffer);// 输出文件
os.flush();
os.close();
} catch (Exception e) {
log.error("exportExcel download出错: {}", e.getMessage());
}
}
} public static String exportExcelAndUpload(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
String path = null;
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量
params.put("pageSize", pageSize);
params.put("pageNum", 1);
List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); int total = (int) pageInfo.getTotal();
//每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数
int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){
for (int i = 1; i <= export_times; i++) {
for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
} dataList.clear();
params.put("pageNum", i+1);
params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
list = baseService.findByPage(params);
pageInfo = new PageInfo(list);
dataList = pageInfo.getList();
dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
}
} else {
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null;
InputStream is = null;
try {
bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
path = OSSUtil.upload(is, fileName, "xlsx");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (bos != null) {
try {
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (is != null) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return path;
} public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
String path = null;
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量
params.put("pageSize", pageSize);
params.put("pageNum", 1);
List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); int total = (int) pageInfo.getTotal();
//每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数
int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){
for (int i = 1; i <= export_times; i++) {
for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
} dataList.clear();
params.put("pageNum", i+1);
params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
list = baseService.findByPage(params);
pageInfo = new PageInfo(list);
dataList = pageInfo.getList();
dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
}
} else {
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
ByteArrayOutputStream bos = null;
InputStream is = null;
try {
bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
path = OSSUtil.upload(is, fileName, "xlsx");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (bos != null) {
try {
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (is != null) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return path;
} public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, String methodName, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
String path = null;
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量
params.put("pageSize", pageSize);
params.put("pageNum", 1); Class clz = baseService.getClass();
Object obj = clz.newInstance();
//获取方法
Method m = clz.getDeclaredMethod(methodName, Map.class);
//调用方法
List list = (List) m.invoke(obj, params); // Method method2 = baseService.getClass().getMethod("methodName", Class.forName("java.util.Map"));
// List list = (List) method2.invoke( params);
// List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); int total = (int) pageInfo.getTotal();
//每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数
int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){
for (int i = 1; i <= export_times; i++) {
for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
} dataList.clear();
params.put("pageNum", i+1);
params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
list = (List) m.invoke(obj, params);
pageInfo = new PageInfo(list);
dataList = pageInfo.getList();
dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
}
} else {
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
ByteArrayOutputStream bos = null;
InputStream is = null;
try {
bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
path = OSSUtil.upload(is, fileName, "xlsx");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (bos != null) {
try {
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (is != null) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return path;
} public static void main(String[] args) throws Exception {
long beginTime = System.currentTimeMillis();
// List[] list = getDataFromExcel("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0);
// List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8.xlsx", 0);
// List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0);
long endTime = System.currentTimeMillis();
log.info("Cast time : " + (endTime - beginTime)); // if (list != null && list.length > 0){
// for (int i = 0; i < list.length; i++) {
// System.out.println(list[i]);
// }
// } } /**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
* @param sheetIndex 第几个sheet
* @throws IOException
*
*/
public static List[] getDataFromExcelByCache(InputStream fis, int sheetIndex) throws Exception {
List[] data = null;
Workbook wookbook = null; try {
//获取一个绝对地址的流
// fis = new FileInputStream(filePath); wookbook = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数，默认是10
.bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024
.open(fis); //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
Sheet sheet = wookbook.getSheetAt(sheetIndex); //获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
log.info("====总行数"+totalRowNum);
// if (totalRowNum > 200000) {
// throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行");
// } data = new List[totalRowNum];
List<Object> rowData = null;
int totalCell = 0;
//遍历所有的行
for (Row row : sheet) {
if(row.getRowNum() == 0) {
totalCell = row.getLastCellNum();
}
//遍历所有的列
if (row.getRowNum() > 0) {
rowData = new ArrayList<Object>();
for (int i = 0; i < totalCell; i++) {
if(row == null || row.getCell(i) == null) {
rowData.add("");
} else {
rowData.add(row.getCell(i).getStringCellValue());
}
}
data[row.getRowNum()-1] = rowData;
}
}
} catch (Exception e) {
log.error("excel读取报错：{}", e.getMessage());
} finally {
if (fis != null) {
try {
fis.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (wookbook != null) {
try {
wookbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return data;
} /**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
* @param sheetIndex 第几个sheet
* @throws IOException
*
*/
public static List[] getDataFromExcel(String filePath, int sheetIndex) throws IOException {
List[] data = null;
//判断是否为excel类型文件
if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")) {
throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel格式无效");
} FileInputStream fis =null;
Workbook wookbook = null; try {
//获取一个绝对地址的流
fis = new FileInputStream(filePath); if(filePath.endsWith(".xls")) {
//2003版本的excel，用.xls结尾
wookbook = new HSSFWorkbook(fis);
} else if(filePath.endsWith(".xlsx")) {
//2007版本的excel，用.xlsx结尾
wookbook = new XSSFWorkbook(fis);
} //得到一个工作表
Sheet sheet = wookbook.getSheetAt(sheetIndex); //获得表头
Row rowHead = sheet.getRow(0); log.info("表头的数量：" + rowHead.getPhysicalNumberOfCells()); //获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
log.info("总数量："+totalRowNum);
// if (totalRowNum > 200000) {
// throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行");
// }
data = new List[totalRowNum];
List<Object> rowData = null;
//获得所有数据
for(int i = 1 ; i <= totalRowNum ; i++) {
//获得第i行对象
Row row = sheet.getRow(i);
// //获得获得第i行第0列的 String类型对象
// Cell cell = row.getCell((short)0);
// name = cell.getStringCellValue().toString();
//
// //获得一个数字类型的数据
// cell = row.getCell((short)1);
// latitude = cell.getStringCellValue(); rowData = new ArrayList<Object>();
for (int k = 0; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell((short)k);
rowData.add(cell);
}
data[i-1] = rowData;
}
} catch (Exception e) {
log.error("excel读取报错：{}", e.getMessage());
} finally {
if (fis != null) {
fis.close();
}
if (wookbook != null) {
wookbook.close();
}
}
return data;
} /**
* 将 List<JavaBean>对象转化为List<Map>
* @param beanList
* @return
* @throws Exception
*/
public static <T> List<Map<String, Object>> convertListBeanToListMap(List<T> beanList, Class<T> T)
throws Exception {
List<Map<String, Object>> mapList = new ArrayList<>();
for (int i = 0, n = beanList.size(); i < n; i++) {
Object bean = beanList.get(i);
Map<String, Object> map = convertBeanToMap(bean);
mapList.add(map);
}
return mapList;
} /**
* 将一个 JavaBean 对象转化为一个 Map
* @param bean
* @return
* @throws IntrospectionException
* @throws IllegalAccessException
* @throws InvocationTargetException
*/
public static Map<String, Object> convertBeanToMap(Object bean)
throws IntrospectionException, IllegalAccessException, InvocationTargetException
{
Class<? extends Object> type = bean.getClass();
Map<String, Object> returnMap = new HashMap<>();
BeanInfo beanInfo = Introspector.getBeanInfo(type); PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (int i = 0; i < propertyDescriptors.length; i++)
{
PropertyDescriptor descriptor = propertyDescriptors[i];
String propertyName = descriptor.getName();
if (!"class".equals(propertyName))
{
Method readMethod = descriptor.getReadMethod();
Object result = readMethod.invoke(bean, new Object[0]);
if (result != null)
{
returnMap.put(propertyName, result);
}
else
{
returnMap.put(propertyName, null);
}
}
}
return returnMap;
} public static void exportExcel(String filePath, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象 // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); //每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 if(list != null && list.size() > 0) {
PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
}
dataList.clear();
long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); } else {
sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(0); //动态指定当前的工作表 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} FileOutputStream fOut = new FileOutputStream(filePath);
if (fOut != null) {
try {
wb.write(fOut);
fOut.flush(); // 刷新缓冲区
} catch (Exception e) {
e.printStackTrace();
}
fOut.close();
}
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
} long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); //isDownload为true时，让浏览器弹出下载文件
if (isDownload) {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
try {
File file = new File(filePath);
String fileName = file.getName();// 获取日志文件名称
InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
//然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
// 判断是否火狐浏览器
String agent = request.getHeader("User-Agent");
boolean isFirefox = (agent != null && agent.contains("Firefox"));
if (isFirefox) {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF8");
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Content-Length", "" + file.length());
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
os.write(buffer);// 输出文件
os.flush();
os.close();
} catch (Exception e) {
log.error("exportExcel download出错: {}", e.getMessage());
}
}
} public static String exportListExcelAndUpload(String fileName, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
long startTime = System.currentTimeMillis(); // 开始时间
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null; // 列对象
String path = null; // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
Workbook wb = new SXSSFWorkbook(500); // 关键语句
Font font = wb.createFont();
font.setBold(true);
CellStyle headCellStyle = wb.createCellStyle();
headCellStyle.setFont(font); //每个sheet保存多少行
int sheetNum = 1000000;
int rowNo = 0; // 总行号
int pageRowNo = 0; // 页行号 if(list != null && list.size() > 0) {
PageInfo pageInfo = new PageInfo(list);
List dataList = pageInfo.getList();
List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); for (int n = 0; n < dataMap.size(); n++) {
//打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
if(rowNo%sheetNum==0){
sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表
pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
} nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数
for(int j=0;j<headFields.length;j++){
nCell = nRow.createCell(j);
nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
}
rowNo++;
}
dataList.clear();
long finishedTime = System.currentTimeMillis(); // 处理完成时间
log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); } else {
sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象
sheet = wb.getSheetAt(0); //动态指定当前的工作表 Row row = sheet.createRow(pageRowNo++); //新建行对象
Cell cell = row.createCell(0);
for (int k = 0; k < titles.length; k++) {
cell = row.createCell(k);
cell.setCellStyle(headCellStyle);
cell.setCellValue(new XSSFRichTextString(titles[k]));
}
nRow = sheet.createRow(pageRowNo++); //新建行对象
} long stopTime = System.currentTimeMillis(); // 写文件时间
log.info("exportListExcelAndUpload write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null;
InputStream is = null;
try {
bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] barray = bos.toByteArray();
is = new ByteArrayInputStream(barray);
path = OSSUtil.upload(is, fileName, "xlsx");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null){
try {
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (bos != null) {
try {
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (is != null) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return path;
} }