POI 百万条记录导出工具类

 

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));
	}	

}

 

 

评论

2 楼 di1984HIT 2017-11-23  

xuexile~~~~~

1 楼 walker_v5 2016-11-10