POI导入和导出到excel

使用到的依赖:

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

版本自己控制。
两种导出方式,HSSF和XSSF，其中HSSF是将数据一次性写入到内存里，再一次性写出到excel，对于内存消耗比较大，数据量太大的话会导致内存溢出，但是效率比较快，最大值63556。
XSSF是读一部分再写一部分，再读再写，内存消耗会小一些，但是效率比HSSF慢。
使用的时候，HSSF加入poi的依赖就可以使用，而XSSF需要加上面两个依赖。

HSSF只操作.xls文件(97–03版excel)一个sheet中行有限制,最大是65536（将数据一次性写入内容，再写出）

1、使用HSSF导出到excel

public class WirteExcelHSSFTest {
    public static void main(String[] args) throws Exception{
        //创建文件输出流
        FileOutputStream out = new FileOutputStream("E:\\upload\\test.xls");
        //创建一个工作簿
        Workbook wb = new HSSFWorkbook();
        for(int j = 0; j< 10; j++){
            //创建sheet--工作表
            Sheet sheet = wb.createSheet();
            wb.setSheetName(j,"sheet"+ j);//指定sheet得名称
            for(int rowNum = 0; rowNum < 65536; rowNum++){
                //创建一行
                Row row = sheet.createRow(rowNum);//创建第一行
                //一行创建10个单元格
                for(int cellNum = 0; cellNum < 10; cellNum++){
                    //在行里创建单元格
                    Cell cell = row.createCell(cellNum);
                    //向单元格中写入数据
                    cell.setCellValue(cellNum);
                }
            }
        }


        wb.write(out);//输出文件内容

    }

}

2、使用XSSF导出数据到excel

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;

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

public class WriteEexceXSSFTest {
    public static void main(String[] args) throws Exception {
        //创建SXSSFworkBook（关闭自动刷新并在内存中积累所有的行）
        SXSSFWorkbook wb = new SXSSFWorkbook(-1);
        //创建一个sheet
        Sheet sheet = wb.createSheet();
        for(int rownum = 0; rownum < 100000;rownum++){
            //创建一行
            Row row = sheet.createRow(rownum);
            //创建单元格
            for(int cellNum = 0; cellNum < 10; cellNum++){
                Cell cell = row.createCell(cellNum);
                //单元格地址
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
            //达到一万行后就向磁盘写入一次
            if(rownum % 10000 == 0){
                //保留最后100行并冲洗所有的其他行
                ((SXSSFSheet)sheet).flushRows(100);
            }
        }
        FileOutputStream out = new FileOutputStream("E:\\upload\\test.xls");
        wb.write(out);//将临时文件合并，写入最终文件
        out.close();
    }
}

3、使用HSSF将excel数据插入到数据库

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;

public class ReadExcelHSSF {
    public static void main(String[] args) throws Exception{
        ReadExcelHSSF readExcelHSSF = new ReadExcelHSSF();
        readExcelHSSF.readXls();
    }
    public void readXls() throws Exception{
        //文件输入流
        InputStream is = new FileInputStream("E:/upload/test.xls");
        //创建workBook--工作簿
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        //解析workbook的内容
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            //得到workBook中某个sheet(序号是从0开始)
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if(hssfSheet == null){
                continue;
            }
            //遍历循环row
            for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum();rowNum++){
                //读取每一行数据，rowNum指定行下标从0开始
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                //读取单元格内容
                for(int cellNum= 0; cellNum <= hssfRow.getLastCellNum(); cellNum ++){
                    //读取一行中的某个单元格的内容（cellNum指定单元格的下标，从0开始）
                    HSSFCell cell = hssfRow.getCell(cellNum);
                    if(cell == null){
                        continue;
                    }
                    //插入到数据库，我这里就不演示了，直接打印
                    System.out.println(cell.getStringCellValue());
                }
            }
        }
    }
}

4、使用XSSF将excel数据读取到数据库

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.record.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class ReadExcelXSSFTest implements HSSFListener {
    private SSTRecord sstrec;

    /**
     * This method listens for incoming records and handles them as required.
     *
     * @param record
     *            The record that was found while reading.
     */
    public void processRecord(Record record) {

        switch (record.getSid()) {
            // the BOFRecord can represent either the beginning of a sheet or the
            // workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == bof.TYPE_WORKBOOK) {
                    System.out.println("Encountered workbook");
                    // assigned to the class level member
                } else if (bof.getType() == bof.TYPE_WORKSHEET) {
                    System.out.println("Encountered sheet reference");
                }
                break;
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                System.out.println("New sheet named: " + bsr.getSheetname());
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                System.out.println("Row found, first column at "
                        + rowrec.getFirstCol() + " last column at "
                        + rowrec.getLastCol());
                break;
            case NumberRecord.sid:
                NumberRecord numrec = (NumberRecord) record;
                System.out.println("Cell found with value " + numrec.getValue()
                        + " at row " + numrec.getRow() + " and column "
                        + numrec.getColumn());
                break;
            // SSTRecords store a array of unique strings used in Excel.
            case SSTRecord.sid:
                sstrec = (SSTRecord) record;
                for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
                    System.out.println("String table value " + k + " = "
                            + sstrec.getString(k));
                }
                break;
            case LabelSSTRecord.sid:
                LabelSSTRecord lrec = (LabelSSTRecord) record;
                System.out.println(lrec.getRow()+"String cell found with value "
                        + sstrec.getString(lrec.getSSTIndex()));
                break;
        }
    }

    /**
     * Read an excel file and spit out what we find.
     *
     * @param args
     *            Expect one argument that is the file to read.
     * @throws IOException
     *             When there is an error processing the file.
    官方例子读取大数据量xls文件没有内存溢出问题
     */
    public static void main(String[] args) throws IOException {
        // create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = new FileInputStream("E:\\upload\\test.xls");
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = poifs.createDocumentInputStream("Workbook");
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        //添加一个事件驱动
        req.addListenerForAllRecords(new ReadExcelXSSFTest());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        fin.close();
        // and our document input stream (don't want to leak these!)
        din.close();
        System.out.println("done.");
    }
}

5、实战中将poi封装成一个工具类，项目中直接调用类中的方法，返回下载的链接地址：

import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
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.java.pojo.Ypxx;

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

	// 定义工作表
	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则需要使用此方法手动刷新内存
	 * 
	 * @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;
	}
}

6、将数据库查询出的列表数据导出到excel

//实现excel的导出功能
    @RequestMapping("/exportYpxxSubmit")
    @ResponseBody
    public SubmitResultInfo exportYpxxSubmit(YpxxQueryVo ypxxQueryVo) throws Exception{
        //调用封装类执行导出
        //1.定义导出数据的title
        List<String> filedNames = new ArrayList<>();
        filedNames.add("流水号");
        filedNames.add("通用名");
        filedNames.add("剂型");
        filedNames.add("规格");
        filedNames.add("转换系数");
        filedNames.add("生产企业");
        filedNames.add("商品名称");
        filedNames.add("中标价格");
        filedNames.add("交易状态");
        //2.告诉导出数据list中的对象的属性，让excelExportSXXSSF通过反射货到对象的值
        List<String> fildCodes = new ArrayList<>();
        fildCodes.add("bm");        //药品流水号
        fildCodes.add("mc");
        fildCodes.add("jx");
        fildCodes.add("gg");
        fildCodes.add("zhxs");
        fildCodes.add("scqymc");
        fildCodes.add("spmc");
        fildCodes.add("zbjg");
        fildCodes.add("jyzt");
        //3.开始到处
        String filePath = "E:\\upload";
        String filePrefix = "uploadypxx";
        int flushRows = 100;        //-1表示关闭自动刷新，手动控制写入磁盘时机，其他数据表示 多少数据在内存中保存超过的就写入到磁盘
        ExcelExportSXXSSF excelExportSXXSSF = ExcelExportSXXSSF.start(filePath,"http://localhost:80/",filePrefix, filedNames, fildCodes, flushRows);
        //导出得数据通过service取得
        List<YpxxCustom> list = ypxxService.findYpxxList(ypxxQueryVo);
        //执行导出
        excelExportSXXSSF.writeDatasByObject(list);
        //输出文件,返回下载文件的http地址，已经包括目录
        String webpath = excelExportSXXSSF.exportFile();
        System.out.println("下载路径：" + webpath);
        //list就是查询到的目录列表    webpath就是下载地址
        return ResultUtil.createSubmitResult(ResultUtil.createSuccess(Config.MESSAGE,313,new Object[]{list.size(),webpath}));
    }