poi-3.11 SXSSFWorkbook 导出Execl 含Sheet分页 返回InputStream

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.hssf.util.HSSFColor;
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.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.google.common.collect.Lists;

public class ExeclExportUtils {

	public static InputStream execute(String[] cellTitle, List<String[]> cellData) {
		InputStream in = null;
		SXSSFWorkbook workBook = null;
		try {
			// 声明一个工作薄
			workBook = new SXSSFWorkbook();
			// 标题样式
			CellStyle cellTitleStyle = workBook.createCellStyle();
			// 设置水平对齐的样式为居中对齐;
			cellTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);
			// 设置垂直对齐的样式为居中对齐;
			cellTitleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
			// 设置背景颜色
			cellTitleStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
			// solid 填充 foreground 前景色
			cellTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			// 边框
			cellTitleStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
			cellTitleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
			cellTitleStyle.setBorderBottom(IndexedColors.BLACK.getIndex());// 设置下边框
			// 标题字体样式
			Font cellTitleFont = workBook.createFont();
			cellTitleFont.setColor(HSSFColor.WHITE.index); // 将字体设置为“白色”
			cellTitleFont.setFontHeightInPoints((short) 12); // 将字体大小设置为12px
			cellTitleFont.setFontName("华文细黑"); // 将“华文细黑”字体应用到当前单元格上
			cellTitleStyle.setFont(cellTitleFont);

			// 数据 - 根据数据大小拆分多页
			int sheetNum = 50000;
			List<List<String[]>> listData = Lists.partition(cellData, sheetNum);
			for (int i = 0; i < listData.size(); i++) {
				Sheet sheetDetail = workBook.createSheet();
				workBook.setSheetName(i, "" + i);
				// 创建表格标题行 第一行
				Row titleRow = sheetDetail.createRow(0);
				titleRow.setHeightInPoints(20);
				for (int j = 0; j < cellTitle.length; j++) {
					Cell cell = titleRow.createCell(j);
					cell.setCellStyle(cellTitleStyle);
					cell.setCellValue(cellTitle[j]);
				}
				// 插入导出的数据
				List<String[]> detailDataList = listData.get(i);
				for (int j = 0; j < detailDataList.size(); j++) {
					Row row = sheetDetail.createRow(j + 1);
					for (int k = 0; k < cellTitle.length; k++) {
						String valStr = detailDataList.get(j)[k];
						if (valStr.trim().length() == 0 || "null".equals(valStr)) {
							valStr = "";
						}
						row.createCell(k).setCellValue(valStr);
					}
				}
				// 调整宽度
				for (int j = 0; j < cellTitle.length; j++) {
					sheetDetail.setColumnWidth(j, cellTitle[j].length() * 1000);
				}
				// 固定第一行标题
				sheetDetail.createFreezePane(1, 1, 1, 1);
			}
			//临时缓冲区
	        ByteArrayOutputStream out = new ByteArrayOutputStream();
	        //创建临时文件  文件输出流
	        workBook.write(out);
	        byte [] bookByteAry = out.toByteArray();
	        in = new ByteArrayInputStream(bookByteAry);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != workBook) {
				try {
					workBook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return in;
	}
}

下载

//设置标题头
			String[] cellTitle = new String[];
//设置填写数据
			List<String[]> cellData = new ArrayList<>();
//使用方法
			InputStream inputStream =  ExeclExportUtils.execute(cellTitle, cellData);
			 //获取自己数组
			byte[] buffer = new byte[1024];
	        int len = 0;
	        ByteArrayOutputStream bos = new ByteArrayOutputStream();
	        while((len = inputStream.read(buffer)) != -1) {
	            bos.write(buffer, 0, len);
	        }
	        byte[] getData = bos.toByteArray();
	        File filePath = new File("D:\\test\\1.xlsx");
	        FileOutputStream fos = new FileOutputStream(filePath);
	        fos.write(getData);
            fos.close();
        	bos.close();
            inputStream.close();