Excel百万级别数据POI简单导出

1.准备工具类

package cn.itcast.utils;

import org.springframework.stereotype.Component;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
@Component
public class DownloadUtil {
	
	/**
	 * @param filePath 要下载的文件路径
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(new File(filePath), returnName, response, delFlag);
	}


	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(file, returnName, response, delFlag);
	}
	
	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
		// 下载文件
		FileInputStream inputStream = null;
		ServletOutputStream outputStream = null;
		try {
			if(!file.exists()) return;
			response.reset();
			//设置响应类型	PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。  
			response.setContentType("application/octet-stream;charset=utf-8");

			//设置响应的文件名称,并转换成中文编码
			//returnName = URLEncoder.encode(returnName,"UTF-8");
			returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
			
			//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
			response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
			
			//将文件读入响应流
			inputStream = new FileInputStream(file);
			outputStream = response.getOutputStream();
			int length = 1024;
			int readLength=0;
			byte buf[] = new byte[1024];
			readLength = inputStream.read(buf, 0, length);
			while (readLength != -1) {
				outputStream.write(buf, 0, readLength);
				readLength = inputStream.read(buf, 0, length);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				inputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			//删除原文件
			
			if(delFlag) {				
				file.delete();
			}
		}
	}

	/**
	 * by tony 2013-10-17
	 * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
	 * @param response HttpServletResponse	写入response
	 * @param returnName 返回的文件名
	 */
	public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
		response.setContentType("application/octet-stream;charset=utf-8");
		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
		response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
		response.setContentLength(byteArrayOutputStream.size());
		
		ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
		byteArrayOutputStream.writeTo(outputstream);					//写到输出流
		byteArrayOutputStream.close();									//关闭
		outputstream.flush();											//刷数据
	}
}

2.准备文本样式

  //大标题的样式
    public CellStyle bigTitle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)16);
        font.setBold(true);//字体加粗
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        return style;
    }

    //小标题的样式
    public CellStyle title(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short)12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线
        return style;
    }

    //文字样式
    public CellStyle text(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("Times New Roman");
        font.setFontHeightInPoints((short)10);

        style.setFont(font);

        style.setAlignment(HorizontalAlignment.LEFT);				//横向居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线

        return style;
    }

样式不能添加太多 否则会报错

3.实现代码

@RequestMapping("/printExcelMillion")
    public void printExcelMillion(String inputDate) throws Exception {
        List<ContractProductVo> contractProductVoList = contractService.findContractProductVoListByShipTime(inputDate, getCompanyId());
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("出货表");
        sheet.setColumnWidth(1,26*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(2,16*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(3,26*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(4,16*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(5,16*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(6,16*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(7,16*256); //1代表一个字母宽度的256分之一
        sheet.setColumnWidth(8,16*256); //1代表一个字母宽度的256分之一
        //开始设置大标题
        Row BigTitleRow = sheet.createRow(0);
        BigTitleRow.setHeightInPoints(36);
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
        Cell cell = BigTitleRow.createCell(1);
        cell.setCellStyle(bigTitle(workbook));
        cell.setCellValue(inputDate.replaceAll("-0","年").replaceAll("-","年")+"月份出货单");

        //开始设置小标题
        Row titleRow = sheet.createRow(1);
        titleRow.setHeightInPoints(26);
        String[] titles = {"客户","合同号","货号","数量","工厂","工厂交期","船期","贸易条款"};
        for (int i = 1; i <= 8; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(titles[i-1]);
            cell.setCellStyle(title(workbook));
        }

        //开始设置文本
        Integer rowIndex = 2;
        Row row = null;
        for (ContractProductVo contractProductVo : contractProductVoList) {
            for (int i = 1; i < 6050; i++) {
                row = sheet.createRow(rowIndex);
                row.setHeightInPoints(24);
                //客户	合同号	货号	数量	工厂	工厂交期	船期	贸易条款

                cell = row.createCell(1);
                cell.setCellValue(contractProductVo.getCustomName());

                cell = row.createCell(2);
                cell.setCellValue(contractProductVo.getContractNo());

                cell = row.createCell(3);
                cell.setCellValue(contractProductVo.getProductNo());

                cell = row.createCell(4);
                cell.setCellValue(contractProductVo.getCnumber());

                cell = row.createCell(5);
                cell.setCellValue(contractProductVo.getFactoryName());

                cell = row.createCell(6);
                cell.setCellValue(contractProductVo.getDeliveryPeriod());

                cell = row.createCell(7);
                cell.setCellValue(contractProductVo.getShipTime());

                cell = row.createCell(8);
                cell.setCellValue(contractProductVo.getTradeTerms());

                rowIndex++;
            }
        }
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.write(byteArrayOutputStream);
        downloadUtil.download(byteArrayOutputStream,response,"出货单.xlsx");
    }

服务器没有大数据量 使用循环来模拟导出大数据