Java学习之Apache poi导出Excel

Apache poi简介

Apache POI - the Java API for Microsoft Documents，Apache POI 是用Java编写的免费开源的跨平台的 Java API，它可以创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

学习目标：使用HSSF 和 XSSF操作excel文档。

使用HSSF 和 XSSF导出excel

Hssf导出excel

pom.xml

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

HSSF只操作.xls文件（97-03版本excel），一个sheet中行有限制，最大65536行。

package poi;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

/**
 * poi测试导出excel文件，数据量大出现内存溢出
 * 
 * @author Thinkpad
 *
 */
public class WriteExcelHSSF {

    public static void main(String[] args) throws IOException {

        // 第一步：创建workbook工作簿（excel文档）
        // 创建文件输出流
        FileOutputStream out = new FileOutputStream("d:/workbook.xls");
        // 创建一个工作簿
        Workbook wb = new HSSFWorkbook();

        for (int j = 0; j < 1; j++) {
            // 第二步：创建一个sheet工作表
            Sheet s = wb.createSheet();// 创建1个sheet
            wb.setSheetName(j, "sheet" + j);// 指定sheet的名称
            // xls文件最大支持65536行
            for (int rownum = 0; rownum < 65536; rownum++) {// 创建行,.xls一个sheet中的行数最大65535
                // 第三步：在sheet中创建row行
                Row r = s.createRow(rownum);

                for (int cellnum = 0; cellnum < 10; cellnum++) {// 一行创建10个单元格
                    // 第四步：在row行中创建cell单元格
                    Cell c = r.createCell(cellnum);
                    // 第五步：向cell中写数据
                    c.setCellValue(cellnum);
                }
            }
        }
        System.out.println("int..............");
        // 第六步：输出excel文件（写文件）
        wb.write(out);

        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        out.close();
    }

}

使用上边的测试代码，在一个工作簿中导出多个sheet，出现内存溢出

出现内存溢出原因，HSSF工作原理，将excel中所有数据填充到java对象中，进行文件写操作。

缺点：
如果数据大，引起内存溢问题。

优点：
编程方便，如果数据量小，速度很快的。

XSSF导出 excel

XSSF操作03以上版本（07版本）excel，扩展名.xlsx，工作表行数没有限制

package poi;

import java.io.FileOutputStream;

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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * 采用SXSSF导出excel不出现内存溢出
 * 
 * @author mrt
 *
 */
public class WriteExcelSXSSF1 {

    public static void main(String[] args) throws Throwable {

        // 第一步：创建一个工作簿 -1：关闭自动刷新
        // SXSSFWorkbook wb = new SXSSFWorkbook(XXXX);（自动刷新） 保持内存中有XXXX条记录，超过部分写入磁盘
        SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows
                                                  // in memory
        // 第二步：创建一个工作表 创建一个sheet
        Sheet sh = wb.createSheet();
        for (int rownum = 0; rownum < 100000; rownum++) {
            //第三步：在sheet中创建行  创建一个行
            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);
            }

            // manually control how rows are flushed to disk
            if (rownum % 10000 == 0) {// 一万行向磁盘写一次

                //第六步：将内容写入磁盘  由于-1设置关闭自动刷新 需要人工主动刷新  调用：
                ((SXSSFSheet) sh).flushRows(100); // retain 100 last rows and flush all others
                // Thread.sleep(1000);
                System.out.println("写入....");
                // ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
                // this method flushes all rows
            }

        }
        FileOutputStream out = new FileOutputStream("d:/test.xlsx");
        //第七步：输出文件
        wb.write(out);// 将临时文件合并，写入最终文件

        out.close();

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

    }

}

跟踪代码：
向磁盘刷新数据，生成临时文件：

临时文件内容就是向excel中写的内容，
最后执行文件合并，将文件输出。

缺点：写数据时速度慢

优点：写大数据量时不会发生内存溢出

**从数据库中获取数据并导

TbUser .java

public class TbUser {

    private Long id;

    private String userName;

    private String password;

    private String name;

    private Integer age;

    private Boolean sex;

    private Date birthday;

    private Date created;

    private Date updated;

    //getter and setter

}

package cn.zto.mybatis.poi;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
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.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Before;

import cn.zto.mybatis.mapper.TbUserMapper;
import cn.zto.mybatis.pojo.TbUser;

/**
 * excel导出的封装类
 * 
 * @author miaoruntu
 *
 */
public class ExcelExportSXXSSF {

    private TbUserMapper tbUserMapper;

    /*定义工作表*/
    private SXSSFWorkbook wb;

    /*定义工作表中的sheet*/
    private Sheet sh;

    /*定义保存在内存中的数量,-1表示手动控制*/
    private int flushRows;

    /* 导出文件行数 */
    private int rownum;

    /*导出文件列数 */
    private int colnum;

    /*导出文件的存放路径 */
    private String filePath;

    /*下载导出文件的路径 */
    private String fileWebPath;

    /*文件名称前缀 */
    private String filePrefix;

    /*导出文件全路径 */
    private String fileAllPath;

    /*导出文件列标题 */
    private List<String> fieldNames;

    /*导出文件每列代码，用于反射获取对象属性值 */
    private List<String> fieldCodes;

    private ExcelExportSXXSSF() {

    }

    /**
     * 开始导出方法
     * @param filePath 导出文件存放物理路径
     * @param fileWebPath 导出文件web下载路径
     * @param filePrefix 导出文件名的前缀
     * @param flushRows 存放在内存的数据量
     * @param fieldNames 导出文件列标题
     * @param fieldCodes 导出数据对象的字段名称
     * @param flushRows 写磁盘控制参数
     * @return
     */
    public static ExcelExportSXXSSF start(String filePath, String fileWebPath, String filePrefix,
            List<String> fieldNames, List<String> fieldCodes, int flushRows) throws Exception {
        ExcelExportSXXSSF excelExportSXXSSF = new ExcelExportSXXSSF();
        excelExportSXXSSF.setFilePath(filePath);
        excelExportSXXSSF.setFileWebPath(fileWebPath);
        excelExportSXXSSF.setFilePrefix(filePrefix);
        excelExportSXXSSF.setFieldNames(fieldNames);
        excelExportSXXSSF.setFieldCodes(fieldCodes);
        excelExportSXXSSF.setWb(new SXSSFWorkbook(flushRows));// 创建workbook
        excelExportSXXSSF.setSh(excelExportSXXSSF.getWb().createSheet());// 创建sheet
        excelExportSXXSSF.writeTitles();
        return excelExportSXXSSF;
    }

    /**
     * 设置导入文件的标题 开始生成导出excel的标题
     * 
     * @throws Exception
     */
    private void writeTitles() throws Exception {
        rownum = 0;// 第0行
        colnum = fieldNames.size();// 根据列标题得出列数
        Row row = sh.createRow(rownum);
        for (int cellnum = 0; cellnum < colnum; cellnum++) {
            Cell cell = row.createCell(cellnum);
            cell.setCellValue(fieldNames.get(cellnum));
        }
    }

    /**
     * 向导出文件写数据
     * 
     * @param datalist 存放Object对象，仅支持单个自定义对象，不支持对象中嵌套自定义对象
     * @return
     */
    public void writeDatasByObject(List datalist) throws Exception {

        for (int j = 0; j < datalist.size(); j++) {
            rownum = rownum + 1;
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < fieldCodes.size(); cellnum++) {
                Object owner = datalist.get(j);
                Object value = invokeMethod(owner, fieldCodes.get(cellnum), new Object[] {});
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(value != null ? value.toString() : "");
            }

        }

    }

    /**
     * 向导出文件写数据
     * 
     * @param datalist 存放字符串数组
     * @return
     */
    public void writeDatasByString(List<String> datalist) throws Exception {
        rownum = rownum + 1;
        Row row = sh.createRow(rownum);
        int datalist_size = datalist.size();
        for (int cellnum = 0; cellnum < colnum; cellnum++) {
            Cell cell = row.createCell(cellnum);
            if (datalist_size > cellnum) {
                cell.setCellValue(datalist.get(cellnum));
            } else {
                cell.setCellValue("");
            }

        }
    }

    /**
     * 手动刷新方法,如果flushRows为-1则需要使用此方法手动刷新内存
     * 
     * @param flushRows
     * @throws Exception
     */
    public void flush(int flushNum) throws Exception {
        ((SXSSFSheet) sh).flushRows(flushNum);
    }

    /**
     * 导出文件的服务器地址
     * 
     * @throws Exception
     */
    public String exportFile() throws Exception {
        String filename = filePrefix + "_" + System.currentTimeMillis()+ ".xlsx";
        FileOutputStream out = new FileOutputStream(filePath + filename);
        wb.write(out);
        out.flush();
        out.close();
        setFileAllPath(fileWebPath + filename);
        return fileWebPath + filename;
    }

    /**
     * 反射方法，通过get方法获取对象属性
     * 
     * @param owner
     * @param fieldname
     * @param args
     * @return
     * @throws Exception
     */
    private Object invokeMethod(Object owner, String fieldname, Object[] args) throws Exception {

        String methodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
        Class ownerClass = owner.getClass();

        Class[] argsClass = new Class[args.length];

        for (int i = 0, j = args.length; i < j; i++) {
            argsClass[i] = args[i].getClass();
        }

        Method method = ownerClass.getMethod(methodName, argsClass);
        return method.invoke(owner, args);
    }

    public SXSSFWorkbook getWb() {
        return wb;
    }

    public void setWb(SXSSFWorkbook wb) {
        this.wb = wb;
    }

    public Sheet getSh() {
        return sh;
    }

    public void setSh(Sheet sh) {
        this.sh = sh;
    }

    public int getFlushRows() {
        return flushRows;
    }

    public void setFlushRows(int flushRows) {
        this.flushRows = flushRows;
    }

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public String getFileWebPath() {
        return fileWebPath;
    }

    public void setFileWebPath(String fileWebPath) {
        this.fileWebPath = fileWebPath;
    }

    public List<String> getFieldNames() {
        return fieldNames;
    }

    public void setFieldNames(List<String> fieldNames) {
        this.fieldNames = fieldNames;
    }

    public List<String> getFieldCodes() {
        return fieldCodes;
    }

    public void setFieldCodes(List<String> fieldCodes) {
        this.fieldCodes = fieldCodes;
    }

    public int getRownum() {
        return rownum;
    }

    public String getFilePrefix() {
        return filePrefix;
    }

    public void setFilePrefix(String filePrefix) {
        this.filePrefix = filePrefix;
    }

    public int getColnum() {
        return colnum;
    }

    public String getFileAllPath() {
        return fileAllPath;
    }

    public void setFileAllPath(String fileAllPath) {
        this.fileAllPath = fileAllPath;
    }

    public static void main(String[] args) throws Exception {
        /**
         * 导出文件存放物理路径
         * 
         * @param fileWebPath 导出文件web下载路径
         * @param filePrefix 导出文件名的前缀
         * @param flushRows 存放在内存的数据量
         * @param fieldNames 导出文件列标题
         * @param fieldCodes 导出数据对象的字段名称
         * @param flushRows
         */
        // 导出文件存放的路径，并且是虚拟目录指向的路径
        String filePath = "d:/upload/linshi/";
        // 导出文件的前缀
        String filePrefix = "tb_user";
        // -1表示关闭自动刷新，手动控制写磁盘的时机，其它数据表示多少数据在内存保存，超过的则写入磁盘
        int flushRows = 100;

        // 指导导出数据的title
        List<String> fieldNames = new ArrayList<String>();
        fieldNames.add("编号");
        fieldNames.add("用户名");
        fieldNames.add("密码");
        fieldNames.add("姓名");
        fieldNames.add("年龄");
        fieldNames.add("性别");
        fieldNames.add("生日");
        fieldNames.add("创建日期");
        fieldNames.add("更新日期");

        // 告诉导出类数据list中对象的属性，让ExcelExportSXXSSF通过反射获取对象的值
        List<String> fieldCodes = new ArrayList<String>();
        fieldCodes.add("id");
        fieldCodes.add("userName");
        fieldCodes.add("password");
        fieldCodes.add("name");
        fieldCodes.add("age");
        fieldCodes.add("sex");
        fieldCodes.add("birthday");
        fieldCodes.add("created");
        fieldCodes.add("updated");

        // 注意：fieldCodes和fieldNames个数必须相同且属性和title顺序一一对应，这样title和内容才一一对应

        // 开始导出，执行一些workbook及sheet等对象的初始创建
        ExcelExportSXXSSF excelExportSXXSSF = ExcelExportSXXSSF.start(filePath, "/upload/", filePrefix,
                fieldNames, fieldCodes, flushRows);

        // 准备导出的数据，将数据存入list，且list中对象的字段名称必须是刚才传入ExcelExportSXXSSF的名称
        List<TbUser> list=new ExcelExportSXXSSF().setUp();

        // 执行导出
        excelExportSXXSSF.writeDatasByObject(list);
        // 输出文件，返回下载文件的http地址 虚拟目录不用管
       // String webpath = excelExportSXXSSF.exportFile();

       // System.out.println(webpath);

    }

    /**
     * 从数据库中获取数据，得到集合
     * @throws Exception
     */
    public List<TbUser> setUp() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"test");
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //通过sqlSession获取到动态代理的实现类
        this.tbUserMapper = sqlSession.getMapper(TbUserMapper.class);
        return this.tbUserMapper.getAllInfo();
    }

}

TbUserMapper.xml省略…