poi解决大数据量导出

2015-11-20 08:36:10 zbzhangzi 阅读数 132
3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量的导出。
	    3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作,只是SXSSFWorkbook只支持.xlsx格式,不支持.xls格式。 
        DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    	Random rand = new Random();
     	// 导出Excel文件名
        String xlsName = "xxx_"+df.format(new Date())+rand.nextInt(100)+".xlsx";
        //创建excel文件,内存只有100条记录
        Workbook wb = new SXSSFWorkbook(100); 
        //一个sheet存储的记录条数
        int record = 5000;
        //总记录数
        int recordTotal = listtemp.size();
        //sheet个数(循环几次就有几个sheet,1个sheet存放5000条数据)
        int shTotal = recordTotal % EXCEL_SHEETRECORD == 0?recordTotal/EXCEL_SHEETRECORD:recordTotal/EXCEL_SHEETRECORD + 1;
        //最后一个sheet中记录的条数
	    int lastRecord = recordTotal % EXCEL_SHEETRECORD == 0?record:recordTotal % EXCEL_SHEETRECORD;
	    for(int shIndex = 0; shIndex < shTotal; shIndex++){
			// 创建工作表 
	        Sheet sh = wb.createSheet("sheet"+shIndex);
	    	//最后一个sheet
			if(shIndex == shTotal - 1){
		        // 写表头 创建第一行(表头,即列名)
		        Row row = sh.createRow((short)0);
		        // 给列添加名字
		        row.createCell((short)0).setCellValue("xxx");
		        row.createCell((short)1).setCellValue("xxx");
		        row.createCell((short)2).setCellValue("xxx");
		        row.createCell((short)3).setCellValue("xxx");		        // 数据写入EXCEL
		        for(short i = 0 ; i < lastRecord ; i++){
		        	// 创建数据行
		        	Row rowTmp = sh.createRow((short)(i+1));
		        	//最后一个sheet的记录的索引
		        	int ii = EXCEL_SHEETRECORD * shIndex + i;
		        	// 得到一个对象(即一条记录)
		        	Object[] oj = listtemp.get(ii);
			        // Create a cell and put a value in it.
			        rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
			        rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
			        rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
			        rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
			        			    }
			}else{
				// 写表头 创建第一行(表头,即列名)
		        Row row = sh.createRow((short)0);
		        // 给列添加名字
		        row.createCell((short)0).setCellValue("xxx");
		        row.createCell((short)1).setCellValue("xxx");
		        row.createCell((short)2).setCellValue("xxx");
		        row.createCell((short)3).setCellValue("xxx");		        // 数据写入EXCEL
		        for(short i = 0 ; i < listtemp.size() ; i++){
		        	// 创建数据行
		        	Row rowTmp = sh.createRow((short)(i+1));
		        	//第shIndex个sheet的记录的索引
		        	int ii = EXCEL_SHEETRECORD * shIndex + i;
		        	// 得到一个对象(即一条记录)
		        	Object[] oj = listtemp.get(ii);
			        // Create a cell and put a value in it.
			        rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
			        rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
			        rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
			        rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
			        			    }
			}
	    }
        
        HttpServletResponse response = ServletActionContext.getResponse();
        //指定输出文件名
        response.setHeader("Content-Disposition","attachment;filename=" + xlsName); 
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        OutputStream os = null;        
    	try {
			os = response.getOutputStream();
			wb.write(response.getOutputStream());
			os.flush();
			os.close();
		} catch (IOException e) {
		}

 

 

 

 

2017-03-15 10:01:38 u012572955 阅读数 1716
<dependencies>
		<dependency>
			<groupId>dom4j</groupId>
			<artifactId>dom4j</artifactId>
			<version>1.6.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.35</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.10-FINAL</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.10-FINAL</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.10-FINAL</version>
		</dependency>
		<dependency>
			<groupId>org.apache.xmlbeans</groupId>
			<artifactId>xmlbeans</artifactId>
			<version>2.3.0</version>
		</dependency>

	</dependencies>


package com.tanlei.test.export;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.management.ManagementFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.ss.usermodel.Cell;
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.xssf.streaming.SXSSFWorkbook;

public class BigDataExport {

	public static void main(String[] args) throws Exception {
		String name = ManagementFactory.getRuntimeMXBean().getName();
		System.out.println(name);
		// get pid
		String pid = name.split("@")[0];
		System.out.println("Pid is:" + pid);

		BigDataExport tm = new BigDataExport();
		tm.jdbcex(true);
		Thread.sleep(1000 * 40);
	}

	public void jdbcex(boolean isClose) throws InstantiationException, IllegalAccessException, ClassNotFoundException,
			SQLException, IOException, InterruptedException {

		String xlsFile = "f:/poiSXXFSBigData.xlsx"; // 输出文件
		// 内存中只创建100个对象,写临时文件,当超过100条,就将内存中不用的对象释放。
		Workbook wb = new SXSSFWorkbook(100); // 关键语句
		Sheet sheet = null; // 工作表对象
		Row nRow = null; // 行对象
		Cell nCell = null; // 列对象

		// 使用jdbc链接数据库
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String url = "jdbc:mysql://xxx.xxx.xxx.xxx/dbname?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useFastDateParsing=false&zeroDateTimeBehavior=convertToNull";
		String user = "root";
		String password = "root1234";
		// 获取数据库连接
		Connection conn = DriverManager.getConnection(url, user, password);
		Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		String sql = "select * from tb_applyflow_rpt"; // 100万+测试数据
		ResultSet rs = stmt.executeQuery(sql);

		ResultSetMetaData rsmd = rs.getMetaData();
		long startTime = System.currentTimeMillis(); // 开始时间
		System.out.println("strat execute time: " + startTime);

		int rowNo = 0; // 总行号
		int pageRowNo = 0; // 页行号

		while (rs.next()) {
			// 打印300000条后切换到下个工作表,可根据需要自行拓展,2百万,3百万...数据一样操作,只要不超过1048576就可以
			if (rowNo % 300000 == 0) {
				System.out.println("Current Sheet:" + rowNo / 300000);
				sheet = wb.createSheet("我的第" + (rowNo / 300000) + "个工作簿");// 建立新的sheet对象
				sheet = wb.getSheetAt(rowNo / 300000); // 动态指定当前的工作表
				pageRowNo = 0; // 每当新建了工作表就将当前工作表的行号重置为0
			}
			rowNo++;
			nRow = sheet.createRow(pageRowNo++); // 新建行对象

			// 打印每行,每行有6列数据 rsmd.getColumnCount()==6 --- 列属性的个数
			for (int j = 0; j < rsmd.getColumnCount(); j++) {
				nCell = nRow.createCell(j);
				nCell.setCellValue(rs.getString(j + 1));
			}

			if (rowNo % 10000 == 0) {
				System.out.println("row no: " + rowNo);
			}
			// Thread.sleep(1); //休息一下,防止对CPU占用,其实影响不大
		}

		long finishedTime = System.currentTimeMillis(); // 处理完成时间
		System.out.println("finished execute  time: " + (finishedTime - startTime) / 1000 + "m");

		FileOutputStream fOut = new FileOutputStream(xlsFile);
		wb.write(fOut);
		fOut.flush(); // 刷新缓冲区
		fOut.close();

		long stopTime = System.currentTimeMillis(); // 写文件时间
		System.out.println("write xlsx file time: " + (stopTime - startTime) / 1000 + "m");

		if (isClose) {
			this.close(rs, stmt, conn);
		}
	}

	// 执行关闭流的操作
	private void close(ResultSet rs, Statement stmt, Connection conn) throws SQLException {
		rs.close();
		stmt.close();
		conn.close();
	}
}


2018-02-27 16:50:33 qq_34087560 阅读数 5796

maven 依赖 (版本必须一致

,否则使用SXSSFworkbook 时程序会报错)

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>

HSSFworkbook,XSSFworkbook,SXSSFworkbook 三者 区别

HSSFworkbook:操作Excel2003版本,扩展名为xls

XSSFworkbook:操作Excel2007版本,扩展名为xlsx

SXSSFworkbook :用于大数据量导出,当数据量超过 65536后 程序 会报错:Invalid row number (65536) outside allowable range (0..65535)


例子1:简单导出Excel

@Test
    public void test1() throws IOException {
        // 读取文件
        POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream("test.xls"));
        // 创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        // 获取第一个sheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        System.out.println(sheet.getSheetName());
        // 获取第一行
        HSSFRow row = sheet.getRow(0);
        // 获取第一行第一列
        HSSFCell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());

        // 创建一行
        HSSFRow row1 = sheet.createRow(1);
        // 创建单元格
        HSSFCell cell1 = row1.createCell(0);
        // 单元格赋值
        cell1.setCellValue("我是程序创建的内容");
        System.out.println(cell1.getStringCellValue());

        // 创建输出流
        FileOutputStream os = new FileOutputStream(new File("D:\\8888.xls"));
        // 输出文件
        workbook.write(os);
    }

例子2:导出查询数据

@Test
    public void test2() {
        // 模拟导出数据
        Object[] obj = new Object[]{"哈哈", "呵呵", "哼哼"};
        List<Object[]> list = new ArrayList<Object[]>();
        // HSSFWorkbook 只支持2003版本及以下版本Excel 且容量最大为65536
        for (int i = 0; i < 65536; i++) {
            list.add(obj);
        }

        export("test.xls", list, 2);
    }

    /**
     * poi 导出
     * @param fileName
     * @param objs
     * @param rowIndex
     */
    private void export(String fileName, List<Object[]> objs, int rowIndex) {
        POIFSFileSystem fs = null;
        FileOutputStream os = null;
        try {
            fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName));
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 创建一个工作簿
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFCellStyle style = setCellStyle(workbook);
            // 获取一个sheet页
            HSSFSheet sheet = workbook.getSheetAt(0);

            for (int i = rowIndex - 1; i < objs.size(); i++) {
                // 创建行
                HSSFRow row = sheet.createRow(i);

                // 创建列
                for (int j = 0; j < objs.get(i).length; j++) {
                    HSSFCell cell = row.createCell(j);
                    // 设置单元格样式
                    cell.setCellStyle(style);
                    cell.setCellValue(objs.get(i)[j].toString());
                }
            }
            // 创建输出流
             os = new FileOutputStream(new File("D:\\8888.xls"));
            // 输出文件
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 设置样式
     * @param workbook
     */
    private HSSFCellStyle setCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        // 字号
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        // 左右居中 上下居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

例子3:大数据量导出

/**
     * 大数据量导出
     * @throws IOException
     */
    @Test
    public void text2() throws IOException {

        XSSFWorkbook xssfWorkbook = new   XSSFWorkbook(Thread.currentThread().getContextClassLoader().getResourceAsStream("bigdata.xlsx"));
        SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100);

        Sheet sh = wb.getSheetAt(0);
        for(int rownum = 1; rownum < 75537; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 10; cellnum++){
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }

//        // Rows with rownum < 900 are flushed and not accessible
//        for(int rownum = 0; rownum < 900; rownum++){
//            Assert.assertNull(sh.getRow(rownum));
//        }
//
//        // ther last 100 rows are still in memory
//        for(int rownum = 900; rownum < 1000; rownum++){
//            Assert.assertNotNull(sh.getRow(rownum));
//        }

        FileOutputStream out = new FileOutputStream("D:\\sxssf.xlsx");
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }

2018-09-25 20:18:05 qq_35206261 阅读数 28696

一. 简介

          excel导出,如果数据量在百万级,会出现俩点内存溢出的问题:

          1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决;

          2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的会自动缓存在硬盘的临时目录上,同时,并不会存在页面卡顿的情况;

          3. 为了能够使用不同的mapper并分批写数据, 采用了外观模式和模板方法模式,大体分三步:

              a. 根据总数量生成excel,确定sheet的数量和写标题;

              b. 写数据,在可变的匿名内部类中实现写入逻辑;
              c. 转换输出流进行下载;

          4. 使用案例在3.3 ServiceImpl中,可自行书写。

          5. 最近太忙,写的太仓促,性能我感觉还有一倍的优化空间,比如循环次数,现在存在无意义空循环(这个耗时比较多)的情况,缓冲流,mybatis的浮标等,待优化........   

          6. 优化空间很大  刚试了下 把空循环去掉  4个字段 90W条数据  40s  180W  75s  300W 122s

          7. 转战阿里开源的EasyExcel了, 那个内存控制在kb级别,绝对不会内存溢出。使用说明请参见博主的另一篇文章:

阿里开源(EasyExcel)---导出EXCEL

          8. 导入的也有,请参见另一篇文章: 阿里开源(EasyExcel)---导入EXCEL

二. 工具代码

2.1 配置

2.1.1 pom.xml

      <!-- poi -->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.17</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.17</version>
      </dependency>

注: 如果是springboot2.0,则不需要poi依赖,如果是1.0,则需要poi依赖,并且poi和poi-ooxml的版本要保持一致。

          别的依赖我就不加了。

2.1.2 application.yml

# pagehelper
pagehelper:
    helperDialect: mysql
    reasonable: false # 如果没有数据  返回空 而非最后一页的数据
    supportMethodsArguments: true
    params: count=countSql
    returnPageInfo: check

注:  reasonable一定要为false, 其他的我就不粘了。

2.2 ExcelConstant

package com.yzx.caasscs.constant;

/**
 * @author qjwyss
 * @date 2018/9/19
 * @description EXCEL常量类
 */
public class ExcelConstant {

    /**
     * 每个sheet存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;


    /**
     * 每个sheet的写入次数 5
     */
    public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;


}

注: xlsx模式的excel每个sheet最多存储104W,此处我就每个sheet存储了 100W数据;每次查询20W数据; 自己根据内存来调合适的大小,写入次数待优化。

2.3 写数据委托类

package com.yzx.caasscs.util;

import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description EXCEL写数据委托类
 */
public interface WriteExcelDataDelegated {

    /**
     * EXCEL写数据委托类  针对不同的情况自行实现
     *
     * @param eachSheet     指定SHEET
     * @param startRowCount 开始行
     * @param endRowCount   结束行
     * @param currentPage   分批查询开始页
     * @param pageSize      分批查询数据量
     * @throws Exception
     */
    public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;


}

2.4 DateUtil工具类(非必须)

package com.yzx.caasscs.util;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description 日期工具类
 */
public class DateUtil {

    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";


    /**
     * 将日期转换为字符串
     *
     * @param date   DATE日期
     * @param format 转换格式
     * @return 字符串日期
     */
    public static String formatDate(Date date, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        return simpleDateFormat.format(date);
    }


}

2.5 POI工具类

package com.yzx.caasscs.util;


import com.yzx.caasscs.constant.ExcelConstant;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/18
 * @description POI导出工具类
 */
public class PoiUtil {

    private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);

    /**
     * 初始化EXCEL(sheet个数和标题)
     *
     * @param totalRowCount 总记录数
     * @param titles        标题集合
     * @return XSSFWorkbook对象
     */
    public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {

        // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
                (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));

        // 根据总记录数创建sheet并分配标题
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
            SXSSFRow headRow = sheet.createRow(0);

            for (int j = 0; j < titles.length; j++) {
                SXSSFCell headRowCell = headRow.createCell(j);
                headRowCell.setCellValue(titles[j]);
            }
        }

        return wb;
    }


    /**
     * 下载EXCEL到本地指定的文件夹
     *
     * @param wb         EXCEL对象SXSSFWorkbook
     * @param exportPath 导出路径
     */
    public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        try {
            fops = new FileOutputStream(exportPath);
            wb.write(fops);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 下载EXCEL到浏览器
     *
     * @param wb       EXCEL对象XSSFWorkbook
     * @param response
     * @param fileName 文件名称
     * @throws IOException
     */
    public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {

        response.setHeader("Content-disposition", "attachment; filename="
                + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出Excel到本地指定路径
     *
     * @param totalRowCount           总记录数
     * @param titles                  标题
     * @param exportPath              导出路径
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);

        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;


                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToLocalPath(wb, exportPath);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


    /**
     * 导出Excel到浏览器
     *
     * @param response
     * @param totalRowCount           总记录数
     * @param fileName                文件名称
     * @param titles                  标题
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);


        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;

                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToWebsite(wb, response, fileName);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


}

三. 使用DEMO

3.1 Controller

package com.yzx.caasscs.controller.organiza;

import com.yzx.caasscs.controller.BaseController;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import springfox.documentation.annotations.ApiIgnore;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户控制类
 */
@Api(tags = {"UserController"}, description = "用户Controller")
@RestController
@RequestMapping(value = "/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;


    @ApiOperation("导出用户EXCEL")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "loginUid", value = "登录用户UID", required = true),
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "uid", value = "用户UID", required = true)
    })
    @GetMapping("/export")
    public ResultVO<Void> exportUser(@ApiIgnore UserVO userVO, HttpServletResponse response) throws Exception {
        return this.userService.export(userVO, response);
    }

}

3.2 Service

package com.yzx.caasscs.service.organiza;

import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICE
 */
public interface UserService {


    /**
     * 导出用户EXCEL
     *
     * @param userVO
     * @return VOID
     * @throws Exception
     */
    ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception;


}

3.3 ServiceImpl

package com.yzx.caasscs.service.impl.organiza;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.yzx.caasscs.constant.middleware.RedisConstant;
import com.yzx.caasscs.constant.organiza.UserApartmentConstant;
import com.yzx.caasscs.constant.organiza.UserConstant;
import com.yzx.caasscs.constant.organiza.UserRoleConstant;
import com.yzx.caasscs.constant.sys.SysLogConstant;
import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserApartment;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserRole;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserApartmentMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserRoleMapper;
import com.yzx.caasscs.mapper.dscaasscs.sys.RoleMenuMapper;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.service.sys.SysLogService;
import com.yzx.caasscs.util.CommonUtil;
import com.yzx.caasscs.util.DateUtil;
import com.yzx.caasscs.util.PoiUtil;
import com.yzx.caasscs.util.WriteExcelDataDelegated;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserApartmentVO;
import com.yzx.caasscs.vo.organiza.UserRoleVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import com.yzx.caasscs.vo.sys.MenuVO;
import com.yzx.caasscs.vo.sys.RoleMenuVO;
import com.yzx.caasscs.vo.sys.SysLogVO;
import net.sf.json.JSONObject;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICEIMPL
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private HttpServletRequest request;


    @Override
    public ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception {

        // 总记录数
        Integer totalRowCount = this.userMapper.selectUserVOCount(userVO);

        // 导出EXCEL文件名称
        String filaName = "用户EXCEL";

        // 标题
        String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};

        // 开始导入
        PoiUtil.exportExcelToWebsite(response, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
            @Override
            public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {

                PageHelper.startPage(currentPage, pageSize);
                List<UserVO> userVOList = userMapper.selectUserVOList(userVO);

                if (!CollectionUtils.isEmpty(userVOList)) {

                    // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
                    for (int i = startRowCount; i <= endRowCount; i++) {
                        SXSSFRow eachDataRow = eachSheet.createRow(i);
                        if ((i - startRowCount) < userVOList.size()) {

                            UserVO eachUserVO = userVOList.get(i - startRowCount);
                            // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------

                            eachDataRow.createCell(0).setCellValue(eachUserVO.getUsername() == null ? "" : eachUserVO.getUsername());
                            eachDataRow.createCell(1).setCellValue(eachUserVO.getPassword() == null ? "" : eachUserVO.getPassword());
                            eachDataRow.createCell(2).setCellValue(eachUserVO.getUserState() == null ? "" : (eachUserVO.getUserState() == 1 ? "启用" : "停用"));
                            eachDataRow.createCell(3).setCellValue(eachUserVO.getNickname() == null ? "" : eachUserVO.getNickname());
                            eachDataRow.createCell(4).setCellValue(eachUserVO.getPosition() == null ? "" : eachUserVO.getPosition());
                            eachDataRow.createCell(5).setCellValue(eachUserVO.getMobile() == null ? "" : eachUserVO.getMobile());
                            eachDataRow.createCell(6).setCellValue(eachUserVO.getEmail() == null ? "" : eachUserVO.getEmail());
                            if (null != eachUserVO.getCreateUid()) {
                                eachDataRow.createCell(7).setCellValue(eachUserVO.getCreateUid());
                            }
                            if (null != eachUserVO.getCreateTime()) {
                                eachDataRow.createCell(8).setCellValue(DateUtil.formatDate(eachUserVO.getCreateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                            if (null != eachUserVO.getUpdateUid()) {
                                eachDataRow.createCell(9).setCellValue(eachUserVO.getUpdateUid());
                            }
                            if (null != eachUserVO.getUpdateTime()) {
                                eachDataRow.createCell(10).setCellValue(DateUtil.formatDate(eachUserVO.getUpdateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                        }
                    }
                }

            }
        });

        return ResultVO.getSuccess("导出用户EXCEL成功");
    }


}

3.4 mapper

package com.yzx.caasscs.mapper.dscaasscs.organiza;

import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.vo.organiza.UserVO;

import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/29
 * @description 用户MAPPER
 */
public interface UserMapper {


    /**
     * 查询用户列表
     *
     * @param userVO
     * @return UserVO集合
     * @throws Exception
     */
    List<UserVO> selectUserVOList(UserVO userVO) throws Exception;


    /**
     * 查询用户数量
     *
     * @param userVO
     * @return 用户数量
     * @throws Exception
     */
    Integer selectUserVOCount(UserVO userVO) throws Exception;

}

3.5 mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper" >

  <resultMap id="BaseResultMap" type="com.yzx.caasscs.entity.dscaasscs.organiza.User" >
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="state" property="state" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>


  <!-- 查询用户分页列表返回MAP -->
  <resultMap id="SelectUserVOListMap" type="com.yzx.caasscs.vo.organiza.UserVO">
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="user_state" property="userState" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="apartment_name" property="apartmentName" jdbcType="VARCHAR" />
    <result column="role_names" property="roleNames" jdbcType="VARCHAR" />
  </resultMap>


  <sql id="Base_Column_List" >
    uid, username, password, state, nickname, position, mobile, email, create_uid, create_time,
    update_uid, update_time
  </sql>


  <!-- 查询用户列表 -->
  <select id="selectUserVOList" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultMap="SelectUserVOListMap">
    SELECT
		U.uid, U.username, U.state AS user_state, U.nickname, U.position, U.mobile, U.email, U.create_uid, U.create_time,
		U.update_uid, U.update_time,
		A.apartment_name,
		(
			SELECT
				GROUP_CONCAT( R.role_name ) AS role_name_list
			FROM
				user_role AS UR
				LEFT JOIN role AS R ON R.rid = UR.rid
			WHERE
				UR.state > 0
				AND UR.uid = U.uid
			GROUP BY UR.uid
		) role_names
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
    ORDER BY U.create_time DESC

  </select>


  <!-- 查询用户列表数量 -->
  <select id="selectUserVOCount" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultType="java.lang.Integer" >
    SELECT
        COUNT(1)
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
  </select>
  

</mapper>

4. 测试

每个sheet存100W,每次查询写20W,每条记录10个字段,时间如下:

数据量 时间
100W 3.5分钟
150W 7分钟

 

 

2019-03-21 10:11:14 qq_23303245 阅读数 1068

前言:

适合小白,大神绕道。这个是最近运维项目所用的技术。之前使用的是poi的HSSFWorkbook,发现在导出5万多数据量的时候,jvm就不行了。非常慢,还报内存溢出。
解决办法:在配置tomcat的时候增加下面这句
-Xms128m -Xms1024m -XX:MaxPermSize=512m
方法可行,但是当数据量十几万的时候还是会报内存溢出。然后就想到分sheet页。写完后发现还是不行。
总结就是HSSFWorkbook和XSSFWorkbook都只是小数据量导出还可以。大了的话jvm就承受不了了。HSSFWorkbook这个导出格式xls,XSSFWorkbookd和SXSSFWorkbook格式为.xlsx。废话不说代码走起。

Controller层:
@RequestMapping(value = "/exportPartner", consumes = "multipart/form-data")
	public final void exportBank(HttpServletRequest request, HttpServletResponse response,  @RequestParam("fileName") String fileName,@RequestParam("headInfos") String headInfos) throws Exception {
			//rsts后端查询的数据
			//前端可选导出的头字段
			JSONArray jsonArray = JSONArray.parseArray((headInfos));
			List<Map<String, Object>> headers = (List) jsonArray;
			(new ExportUtil<PtMdmPartnerV>()).exportUtil(rsts, headers, fileName, request, response);
		}
	}
ExportUtil.java
/****
	 * 
	 * 导出
	 * @param  rsts  数据集合
	 * @param  headInfoList 表头列集合
	 * @param  fileName     文件名称
	 * */
	public void exportUtil(List<T> rsts,List<Map<String, Object>> headInfoList,String fileName,
			HttpServletRequest httpServletRequest,HttpServletResponse httpServletResponse)throws Exception{
		httpServletRequest.setCharacterEncoding("UTF-8");
		httpServletResponse.setCharacterEncoding("UTF-8");
		httpServletResponse.setContentType("application/x-download");

		//fileName  是一个完整导出的文件名称  :  1.xls   2.csv   3.pdf==
		//通过截取最后几位得到文件类型    
		String[] types = StringUtils.split(fileName, ".");
		String type= types[types.length-1];
		fileName = URLEncoder.encode(fileName, "UTF-8");
		//处理文件名中有空格 encode之后变成+号
		fileName = fileName.replace("+", "%20"); 
		httpServletResponse.addHeader("Content-Disposition", "attachment;filename="+ fileName);
		fileName=  URLDecoder.decode(fileName);
		if("xls".equals(type)){
			exportExcelUtil(rsts,headInfoList,fileName,httpServletRequest,httpServletResponse);
		}else if("xlsx".equals(type)){
			exportExcelUtilPro(rsts,headInfoList,fileName,httpServletRequest,httpServletResponse);
		}else if("csv".equals(type)){
			exportCsvUtil(rsts,headInfoList,fileName,httpServletRequest,httpServletResponse);
		}else{//pdf
			exportPdfUtil(rsts,headInfoList,fileName,httpServletRequest,httpServletResponse);
		}	
	}

	/***导出excel升级版*/
	private void exportExcelUtilPro(List<T> rsts,List<Map<String, Object>> headInfoList,String fileName,
			HttpServletRequest httpServletRequest,HttpServletResponse httpServletResponse)throws Exception{
		OutputStream out = httpServletResponse.getOutputStream();
        try {
        	(new PoiUtil<T>()).exportExcelPro(fileName.replace(".xlsx", ""), headInfoList, rsts, out);
			out.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

PoiUtil.java

/**
	 * 导出升级版
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param out
	 * @throws ClassNotFoundException
	 */
	public void exportExcelPro(String title, List<Map<String, Object>> headers, List<T> dataset, OutputStream out) throws Exception {
		int limit = 60000;
		long startTime = System.currentTimeMillis();
		logger.info("导出开始:" + startTime);
		// 声明一个工作薄
		//内存中保留 1000 条数据,以免内存溢出,其余写入 硬盘
		SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
		int count = dataset.size();
		if (count > limit) {
			int k = (dataset.size() + limit) / limit;
			for (int i = 1; i <= k; i++) {
				if (i < k) {
					// 生成sheet
					Sheet sheet = workbook.createSheet(title + i);
					// 设置表格默认列宽度为15个字节
					sheet.createFreezePane(2, 1);
					sheet.setDefaultColumnWidth((short) 15);
					// 设置并获取到需要的样式
					writeHeaderPro(workbook, sheet, headers);
					writeContentPro(workbook, sheet, 1, headers, dataset.subList((i - 1) * limit, i * limit), "yyyy-MM-dd HH:mm:ss");
				} else {
					// 生成最后一个sheet
					SXSSFSheet sheetT = workbook.createSheet(title + i);
					// 设置表格默认列宽度为15个字节
					sheetT.setDefaultColumnWidth((short) 15);
					writeHeaderPro(workbook, sheetT, headers);
					writeContentPro(workbook, sheetT, 1, headers, dataset.subList((i - 1) * limit, dataset.size()), "yyyy-MM-dd HH:mm:ss");
				}
			}
		} else {
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth((short) 15);
			writeHeaderPro(workbook, sheet, headers);
			writeContentPro(workbook, sheet, 1, headers, dataset, "yyyy-MM-dd HH:mm:ss");
		}
		logger.info("导出花费的时间:" + (System.currentTimeMillis() - startTime)/1000 +"秒");
		try {
			workbook.write(out);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	/**
	 * 写入表头信息
	 * 
	 * @param hssfWorkbook
	 * @param hssfSheet
	 * @param headInfoList
	 *            List<Map<String, Object>> key: title 列标题 width 列宽 dataKey 列对应的
	 *            field名
	 */
	public void writeHeaderPro(SXSSFWorkbook workbook, Sheet sheet, List<Map<String, Object>> headers) {
		// 生成一个样式
		XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 产生表格标题行
		Row row = sheet.createRow(0);
		row.setHeight((short) 380);
		Cell headCell = null;
		Map<String, Object> headInfo = null;
		for (int i = 0, len = headers.size(); i < len; i++) {
			headInfo = headers.get(i);
			if (headInfo.get("ch").toString().equals("1")) {
				headCell = row.createCell(i);
				headCell.setCellStyle(style);
				headCell.setCellValue(headInfo.get("title").toString());
			}
		}
	}

	/**
	 * 导出升级版
	 * 
	 * @param workbook
	 * @param sheet
	 * @param startIndex
	 * @param headers
	 * @param dataList
	 * @param pattern
	 * @throws ClassNotFoundException
	 */
	public void writeContentPro(SXSSFWorkbook workbook, Sheet sheet, int startIndex, List<Map<String, Object>> headers, List<T> dataList, String pattern){
		// 生成并设置数据行样式
		XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		Font font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		Font font3 = workbook.createFont();
		font3.setColor(HSSFColor.BLUE.index);
		// 把字体应用到当前的样式
		style.setFont(font);
		int index = startIndex;
		Row row = null;
		Map<String, Object> headInfo = null;
		//使用过JSONArray.转换转换速度太慢。
		//使用过JSONObject 转换转换速度太慢。
		List<T> rsts = (List<T>) dataList;
		Object value = null;
		for (int i = 0; i < rsts.size(); i++) {
			//拿到每条数据转成map
			//这个地方很是影响性能,每条数据都要这样转。
			Map<String, Object> mapList = GetMap(rsts.get(i));
			row = sheet.createRow(index);
			row.setHeightInPoints(16);
			int columnIndex = 0;
			for (int j = 0, jlen = headers.size(); j < jlen; j++) {
				headInfo = headers.get(j);
				if (headInfo.get("ch").toString().equals("1")) {
					Cell cell = row.createCell(columnIndex);
					cell.setCellStyle(style);
					//根据前端的动态头获取对应数据
					value = mapList.get(headInfo.get("dataKey").toString());
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (value == null) {
						textValue = "";
					} else {
						// 其它数据类型都当作字符串简单处理
						textValue = value.toString();
					}
					// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							XSSFRichTextString richString = new XSSFRichTextString(textValue);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
					columnIndex++;
				}
			}
			index++;
		}
	}
	
	//利用java反射。得到每一条数据转换成Map对象
	public Map<String, Object> GetMap(Object dataList){
		Map<String, Object> mapItem = new HashMap<String, Object>();
		@SuppressWarnings("unchecked")
		T t = (T) dataList;
		// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
		Field[] fields = t.getClass().getDeclaredFields();
		for (short j = 0; j < fields.length; j++) {
			Field field = fields[j];
			String fieldName = field.getName();
			String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
			try {
				Class<? extends Object> tCls = t.getClass();
				Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
				Object value = getMethod.invoke(t, new Object[] {});
				mapItem.put(fieldName, value);
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// 清理资源
			}
			
		}
		return mapItem;
	}

这里由于异常多我用throws Exception抛。在本地导出没问题,放到服务器上导出没反应也没报错,所以说具体异常具体抛,别老想省事

代码完。

存在一个问题:当导出头字段过多,数据量过大。会也报内存溢出。
在这里插入图片描述
这里是3完多数据量。200个头字段。还能正常导出
当导出13万的时候就会报内存溢出。