ExportUtil.java
package com.rd.lh.util.excel;
import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.Date;
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.commons.beanutils.PropertyUtilsBean;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.rd.ifaes.common.util.BigDecimalUtils;
import com.rd.ifaes.common.util.DateUtils;
import com.rd.ifaes.common.util.StringUtils;
/**
* excel 导出工具类
* @author lh
* @version 3.0
* @since 2016-11-8
*
*/
public class ExportUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExportUtil.class);
private static final int sheetMaxCount = 1000000;//单个sheet最多写入行数
public static <T> void exportExcel(String title, String[] headers, String[] fields, int startRow, Workbook wb, List<T> data) throws IOException {
Sheet sheet = null;
startRow = startRow>0?startRow+2:startRow;
int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
for (T obj : data) {
int sheetIndex = index/sheetMaxCount;
if (index % sheetMaxCount == 0) {
sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
sheet = wb.getSheetAt(sheetIndex);
sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
pageRowNo = 2;
createHeader(sheet, title, headers);
}else{
sheet = wb.getSheetAt(sheetIndex);
}
index++;
@SuppressWarnings("unchecked")
Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
Row nRow = sheet.createRow(pageRowNo++); // 新建行对象
for (int j = 0; j < columnCount; j++) {
Cell cell = nRow.createCell(j);
setCellValue(sheet, cell, map.get(fields[j]));
}
}
}
/**
* write Workbook
* @param wb
* @param filePath
* @throws IOException
*/
public static void writeWorkbook(Workbook wb, String filePath)throws IOException{
FileOutputStream fos = new FileOutputStream(filePath + "/workbook.xlsx");
wb.write(fos);
fos.flush();
fos.close();
wb.close();
}
/**
* responseWorkbook
* @param title
* @param wb
* @param request
* @param response
* @throws IOException
*/
public static void responseWorkbook(String title, Workbook wb,HttpServletRequest request, HttpServletResponse response)throws IOException{
String sFileName = title + ".xlsx";
// 火狐浏览器导出excel乱码
String agent = request.getHeader("User-Agent");
// 判断是否火狐浏览器
boolean isFirefox = agent != null && agent.contains("Firefox");
if (isFirefox) {
sFileName = new String(sFileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
sFileName = URLEncoder.encode(sFileName, "UTF8");
}
response.setHeader("Content-Disposition", "attachment; filename=".concat(sFileName));
response.setHeader("Connection", "close");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
wb.write(response.getOutputStream());
}
/**
* 设置单元格的值
* @param cell
* @param cellVal
*/
public static void setCellValue(Sheet sheet, Cell cell, Object cellVal){
if(cellVal == null || String.class.equals(cellVal.getClass())){
cell.setCellValue(StringUtils.isNull(cellVal));
}else if(Integer.class.equals(cellVal.getClass()) || int.class.equals(cellVal.getClass())){
cell.setCellValue(Integer.valueOf(cellVal.toString()));
}else if(Long.class.equals(cellVal.getClass()) || long.class.equals(cellVal.getClass())){
cell.setCellValue(Integer.valueOf(cellVal.toString()));
}else if(Double.class.equals(cellVal.getClass()) || double.class.equals(cellVal.getClass())){
cell.setCellValue(Double.valueOf(cellVal.toString()));
}else if(Float.class.equals(cellVal.getClass()) || float.class.equals(cellVal.getClass())){
cell.setCellValue(Float.valueOf(cellVal.toString()));
}else if(BigDecimal.class.equals(cellVal.getClass())){
cell.setCellValue(BigDecimalUtils.round(cellVal.toString()).doubleValue());
}else if(Date.class.equals(cellVal.getClass())){
cell.setCellValue(DateUtils.formatDateTime((Date)cellVal));
}else if(Timestamp.class.equals(cellVal.getClass())){
cell.setCellValue(DateUtils.formatDateTime((Timestamp)cellVal));
}else{
cell.setCellValue(StringUtils.isNull(cellVal));
}
cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(3));
}
/**
* JavaBean转Map
*
* @param obj
* @return
*/
public static Map<String, Object> beanToMap(Object obj) {
Map<String, Object> params = new HashMap<>(0);
try {
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
for (int i = 0; i < descriptors.length; i++) {
String name = descriptors[i].getName();
if (!StringUtils.equals(name, "class")) {
params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
}
}
} catch (Exception e) {
LOGGER.error("URLDecoder fail :", e);
}
return params;
}
/**
* 创建表头
* @param sheet
* @param headers
*/
private static void createHeader(Sheet sheet, String title, String[] headers){
//设置标题
Row tRow = sheet.createRow(0);
Cell hc = tRow.createCell(0);
hc.setCellValue(new XSSFRichTextString(title));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));// 合并标题行:起始行号,终止行号, 起始列号,终止列号
hc.setCellStyle(sheet.getWorkbook().getCellStyleAt(1));
//设置表头
Row nRow = sheet.createRow(1);
for (int i = 0; i < headers.length; i++) {
Cell cell = nRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(2));
}
}
/**
* 创建Workbook
* @return
*/
public static Workbook createWorkbook(){
Workbook wb = new SXSSFWorkbook(100);
CellStyle hcs = wb.createCellStyle();
hcs.setBorderBottom(BorderStyle.THIN);
hcs.setBorderLeft(BorderStyle.THIN);
hcs.setBorderRight(BorderStyle.THIN);
hcs.setBorderTop(BorderStyle.THIN);
hcs.setAlignment(HorizontalAlignment.CENTER);
Font hfont = wb.createFont();
hfont.setFontName("宋体");
hfont.setFontHeightInPoints((short) 16);// 设置字体大小
hfont.setBold(true);// 加粗
hcs.setFont(hfont);
CellStyle tcs = wb.createCellStyle();
tcs.setBorderBottom(BorderStyle.THIN);
tcs.setBorderLeft(BorderStyle.THIN);
tcs.setBorderRight(BorderStyle.THIN);
tcs.setBorderTop(BorderStyle.THIN);
Font tfont = wb.createFont();
tfont.setFontName("宋体");
tfont.setFontHeightInPoints((short) 12);// 设置字体大小
tfont.setBold(true);// 加粗
tcs.setFont(tfont);
CellStyle cs = wb.createCellStyle();
cs.setBorderBottom(BorderStyle.THIN);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderTop(BorderStyle.THIN);
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);// 设置字体大小
return wb;
}
}
测试用例:
package com.rd.ifaes.othertest.service;
import javax.annotation.Resource;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.rd.ifaes.common.orm.Page;
import com.rd.lh.util.excel.ExportUtil;
import com.rd.ifaes.core.sys.domain.Log;
import com.rd.ifaes.core.sys.service.LogService;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:spring-context.xml"})
public class LogTest {
int pageSize = 10000;
@Resource
private LogService logService;
@Test
public void testExport()throws Exception{
long startTime = System.currentTimeMillis();
Log model = new Log();
model.setPage(null);
int count = logService.getCount(null);
int totalPage = ( count%pageSize)==0?count/pageSize: count/pageSize+1;
Page<Log> page = new Page<>();
page.setPageSize(pageSize);
page.setCount(count);
model.setPage(page);
String title = "日志记录";
String[] headers = {"主键","日志类型","IP","访问页面","请求类型","参数","访问时间","耗时"};
String[] fields = {"uuid", "logType","ip","requestUri","requestMethod","params","createTime","takeTime"};
Workbook wb = ExportUtil.createWorkbook();
Page<Log> logPage = null;
for (int i = 0; i < totalPage; i++) {
page.setPage(i);
logPage = logService.findPage(model);
ExportUtil.exportExcel(title, headers, fields, i * pageSize, wb, logPage.getRows());
//System.out.println("page:"+i+", rows:"+logPage.getRows().size());
}
ExportUtil.writeWorkbook(wb, "d:/temp");
long endTime = System.currentTimeMillis();
System.out.println("take time :"+(endTime - startTime));
}
}