POI处理Excel文件实现导出

SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel。POI要求3.8以上，生成的文件格式要求是07及以上版本，因为excel07级以上版本的行数1048576，量很大，而03版本的只有6万多。

注意，实际过程中，数据承载根据数量量判断，一般用到SXSSFWorkbook，就别用list承载数据.

基于 maven 工程进行功能实现所需要的依赖;

 

 <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>

 工具类:

package com.hcy.util;

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.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;

/**
 * fileName:存excel的文件名
 * Title: 表头数组
 * listContent: 数据列表
 *
 */
public class SXSSFUtil {
    public final static String exportExcel(String fileName, String[] Title, List listContent, HttpServletResponse response){
        try {
            OutputStream os = response.getOutputStream();
            // 清除首部空白行
            response.reset();
            // 告诉浏览器文件名称，类型
            response.setHeader("Content-disposition","attachment; filename="
                    +new String(fileName.getBytes("GB2312"),"ISO8859-1"));
            // 设置要下载的类型
            response.setContentType("application/msexcel");

            // 内存中保留1000条数据
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            Sheet sheet = wb.createSheet("sheet1");
            // 创建行索引
            int excelRow = 0;
            // 创建第一行
            Row titleRow = sheet.createRow(excelRow++);
            // 写表头
            for (int i=0; i<Title.length;i++){
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(Title[i]);
            }

            Field[] fields = null;
            for (Object obj : listContent) {
                // 设置起始cell
                int i=0;
                // 获得该类所有声明的字段
                fields = obj.getClass().getDeclaredFields();
                // 创建新行 在excelRow索引
                Row contentRow = sheet.createRow(excelRow++);
                for (Field field : fields) {
                    field.setAccessible(true);
                    // 获得该字段的值
                    Object va = field.get(obj);
                    if (va == null){
                        va = "";
                    }
                    // 创建新cell
                    Cell cell = contentRow.createCell(i);
                    cell.setCellValue(va.toString());
                    i++;
                }
            }
            wb.write(os);
            os.close();

        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

        String result = "Excel 文件导出成功";
        return result;
    }
}

 Controller :

@RequestMapping("/download.do")

public String download(HttpServletResponse response){
    List<Account> accountList = accountServiceImpl.findAllAccount();
    String [] strings = {"account_id","recommender_id","login_name","login_password","status","create_date","pause_date","close_date","real_name","idcard_no","birthdate","gender","occipation","telephone","email","mailaddress","zipcode","qq","last_login_time","last_login_ip"};
    SXSSFUtil.exportExcel("daochu.Xlsx",strings,accountList,response);

    return "main";

}