JAVA 百万级数据导出

最新推荐文章于 2025-09-22 08:30:00 发布

原创最新推荐文章于 2025-09-22 08:30:00 发布 · 872 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

关注要点

使用SXSSFWorkbook 专门处理大数据，对于大型excel的创建且不会内存溢出的。它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。
使用excel分页技术，假设如果有500w条数据，一下导入一个excel的sheet页中，想想打开excel也需要一段时间吧，慢的话有可能导致程序无法加载，或者直接结束进程的情况。
为了防止List装载数据过大造成内存溢出，采取分段获取数据方式，并在每次向row中加载完数据后对List进行手动回收，并将对象释放从而避免内存溢出。（如需实现方式请留言）。

/**
 * 百万级数据导出
 */
package XXX.XXX.XXX.XXX.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


/**
 * @author 紫气东来
 */
public class XxServiceImpl implements XxService {
	
	private XxDao dao;
	private SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	private String fileName = df.format(Calendar.getInstance().getTime());
	private static String sheetName = "sheet_";
	private static final String SUFFIX = ".xlsx";
	//private String filePath = "D:" + File.separator + "XXX" + File.separator + "XXX" + File.separator + "XXX" + File.separator + fileName + SUFFIX;
	private String filePath = File.separator + "XXX" + File.separator + "XXX" + File.separator + "XXX" + File.separator + fileName + SUFFIX;
	public XxServiceImpl() {
	}

	public void setDao(XxDao dao) {
		this.dao = dao;
	}

	@SuppressWarnings("unchecked")
	@Override
	public File exportFile() {
		long startTime = System.currentTimeMillis();
		System.out.println("导出开始时间："
				+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS")
						.format(startTime));
		File file = null;
		List ls = this.dao.queryResult();//获取数据源
		Integer dataCount = ls.size();
		if (0 == dataCount) return null;
		FileOutputStream out = null;
		SXSSFWorkbook workbook = null;
		SXSSFSheet sheet = null;
		int maxDataSize = 5000;
		int sheetCnt = 0;
		if (dataCount > maxDataSize) {
			if (0 == dataCount / maxDataSize) sheetCnt = dataCount / maxDataSize;
			else sheetCnt = dataCount / maxDataSize + 1;
		} else sheetCnt = 1;
		workbook = new SXSSFWorkbook(100);
		workbook.setCompressTempFiles(true);
		for (int i = 0; i < sheetCnt; i++) {
			sheet = (SXSSFSheet) workbook.createSheet(sheetName + (i + 1));
			sheet.setDefaultColumnWidth(25);
			SXSSFRow row = null;
			setHeader(row, sheet);
			Map<String, String> resultMap = new HashMap<String, String>();
			int curMaxDataSize = dataCount > (i + 1) * maxDataSize ? (i + 1)
					* maxDataSize : dataCount;
			int idx = 1;
			for (int j = i * maxDataSize; j < curMaxDataSize; j++) {
				resultMap = (Map<String, String>) ls.get(j);
				row = (SXSSFRow) sheet.createRow(idx ++);
				setRow(row, resultMap);
			}
			resultMap.clear();
			resultMap = null;
		}
		try {
			file = createFile(filePath);
			out = new FileOutputStream(file);
			workbook.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			System.err.println(e.getMessage());
		} catch (IOException e) {
			System.err.println(e.getMessage());
		}
		long endTime = System.currentTimeMillis();
		System.out.println("导出结束时间："
				+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS")
						.format(endTime));
		System.out.println("当前程序耗时：" + (endTime - startTime) + "ms" + " | 共:"
				+ dataCount + "条数据");
		return file;
	}
	
	private File createFile(String src) throws IOException {
		String path = src.substring(0, src.lastIndexOf(File.separator));
		String fileName = src.substring(src.lastIndexOf(File.separator) + 1, src.length());
		File f = new File(path);
		if (!f.exists()) f.mkdirs();
		File file = new File(f, fileName);
		if (!file.exists()) file.createNewFile();
		return file;
	}
	
	public void isToFile(InputStream is, File file) throws IOException {
		OutputStream os = null;
		try {
			os = new FileOutputStream(file);
			int len = 0;
			byte[] buffer = new byte[8192];
			while ((len = is.read(buffer)) != -1)
				os.write(buffer, 0, len);
		} finally {
			os.close();
			is.close();
		}
	}

	private void setHeader(SXSSFRow row, SXSSFSheet sheet) {
		row = (SXSSFRow) sheet.createRow(0);
		setRow(row, null);
	}
	
	private void setRow(SXSSFRow row, Map<String, String> resultMap) {
		if (resultMap == null) {
			getCell(row, 0).setCellValue("header1");
			getCell(row, 1).setCellValue("header2");
			getCell(row, 2).setCellValue("header3");
			getCell(row, 3).setCellValue("header4");
			getCell(row, 4).setCellValue("header5");
		} else {
			getCell(row, 0).setCellValue(resultMap.get("NAME1"));
			getCell(row, 1).setCellValue(resultMap.get("NAME2"));
			getCell(row, 2).setCellValue(resultMap.get("NAME3"));
			getCell(row, 3).setCellValue(resultMap.get("NAME4"));
			getCell(row, 4).setCellValue(resultMap.get("NAME5"));
		}
	}

	private Cell getCell(Row row, int index) {
		Cell cell = row.getCell(index);
		if (cell == null) cell = row.createCell(index);
		return cell;
	}

}