poi导出xlsx（Excel2007），分多个sheet

Excel2007以上版本导出Excel，并分成多个sheet

使用Apache POI导出Excel(.xlsx)
Excel <=2003 数据限制，行(65536)列(256)
Excel =2007 数据限制，行(1048576)列(16384)

---

Apache POI官方网站
Apache POI使用详解
package exportexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BuildXLSX_V2 {

@SuppressWarnings({ "resource", "unchecked" })
public static void main(String[] args) throws IOException {
	LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
	titleMap.put("rowId", "序号");
	titleMap.put("stuName", "姓名");
	titleMap.put("stuNum", "学号");
	titleMap.put("stuGender", "性别");
	titleMap.put("stuAdmission", "入学日期");
	 
	//需要导出的数据
	List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
	/*dataList.add(new String[]{"东邪","17232401001","男","2015年9月"});
	dataList.add(new String[]{"西毒","17232401002","女","2016年9月"});
	dataList.add(new String[]{"南帝","17232401003","男","2017年9月"});
	dataList.add(new String[]{"北丐","17232401004","男","2015年9月"});
	dataList.add(new String[]{"中神通","17232401005","女","2017年9月"});*/
	
	List<StudentBean> dataList2 = new ArrayList<StudentBean>();
	StudentBean student = new StudentBean();
	student.setRowId(1);
	student.setStuName("张三");
	student.setStuNum("17232401001");
	student.setStuGender("男");
	student.setStuAdmission(new Date());
	dataList2.add(student);
	dataList2.add(student);
	dataList2.add(student);
	dataList2.add(student);
	dataList2.add(student);
	buildExcel(dataList2, 3, "学生信息表", "2017届学生信息表", titleMap);
}
/**
 * @param <T>
 * @since
 * @param dataList 数据源
 * @param rowMaxCount 每个sheet最大记录条数
 * @param fileName 文件名
 * @param sheetTitle sheet名
 * @param titleMap 表格头
 */
@SuppressWarnings("resource")
public static <T> void buildExcel(List<T> dataList, int rowMaxCount, String fileName,String sheetTitle,LinkedHashMap<String,String> titleMap){
	try {
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = "C:/";
		//文件名
		String exportFileName = fileName+"_"+now+".xlsx";
		
		// 声明一个工作薄
		XSSFWorkbook workBook = null;
		workBook = new XSSFWorkbook();
		// 获取数据总条数
		int count = dataList.size();
		// 需要分多少个sheet
		int sheetCount = count % rowMaxCount > 1 ? count / rowMaxCount + 1 : count / rowMaxCount;
		// 拆分大的List为多个小的List
		List<List<T>> splitList = null;
		if (dataList != null && !dataList.isEmpty()) {
			splitList = getSplitList(dataList, rowMaxCount, sheetCount);
		} else {
			throw new Exception("源数据不存在");
		}
		//循环dataList 看需要生成几个sheet
		for(int i=0;i<splitList.size();i++){
		// 生成一个表格
		XSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(i,"学生信息_"+(i+1));
		//最新Excel列索引,从0开始
        int lastRowIndex = sheet.getLastRowNum();
        if (lastRowIndex > 0) {
            lastRowIndex++;
        }
        if(sheetTitle!=null){
			// 合并单元格
			//参数：起始行号，终止行号， 起始列号，终止列号
			//CellRangeAddress（int firstRow, int lastRow, int firstCol, int lastCol）
	        sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, titleMap.size()));
	        // 产生表格标题行
	        XSSFCell cellMerged= sheet.createRow(lastRowIndex).createCell(lastRowIndex);
	        cellMerged.setCellValue(new XSSFRichTextString(sheetTitle));
	        lastRowIndex++;
        }
		// 创建表格列标题行 
		XSSFRow titleRow = sheet.createRow(lastRowIndex);
		Iterator<String> colIteratorV=titleMap.values().iterator();
		int h = 0;
		while(colIteratorV.hasNext()){
			Object value = colIteratorV.next();
			titleRow.createCell(h).setCellValue(value.toString());
			h++;
		}
		//插入需导出的数据
		Class<? extends Object> clazz = null;
		List<T> subList = new ArrayList<T>();
		subList = splitList.get(i);
		for(int j=0;j<subList.size();j++){
			clazz = subList.get(0).getClass();
			XSSFRow row = sheet.createRow(j+lastRowIndex+1);
			Iterator<String> colIteratorK=titleMap.keySet().iterator();
			int k = 0;
			while(colIteratorK.hasNext()){
				Object key = colIteratorK.next();
				Method method = clazz.getMethod(getMethodName(key.toString()));
				Object obj = method.invoke(subList.get(j));
				row.createCell(k).setCellValue(obj==null?"":obj.toString());
				k++;
			}
		}
			
		}
		File  file = new File(basePath+exportFileName);
		//文件输出流
		FileOutputStream outStream = new FileOutputStream(file);
		workBook.write(outStream);
		outStream.flush();
		outStream.close();
		System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
	} catch (Exception e) {
		e.printStackTrace();
	}
	
	
}

/**
 * 分割list
 * @param dataList  数据源
 * @param rowMaxCount 每个sheet最大记录条数
 * @param sheetCount 需要分多少个sheet
 * @return
 */
public static <T> List<List<T>> getSplitList(List<T> dataList, int rowMaxCount,
		int sheetCount) {
	List<List<T>> subList = new ArrayList<List<T>>();
	for (int i = 1; i <= sheetCount; i++) {
		if (i == 1) {
			// 第一个list
			if(dataList.size()>=rowMaxCount){
				subList.add(dataList.subList(0, rowMaxCount));
			}else{
				subList.add(dataList.subList(0, dataList.size()));
			}
		} else if (i == sheetCount) {
			// 最后一个listn
			subList.add(dataList.subList((sheetCount - 1) * rowMaxCount, dataList.size()));
		} else {
			subList.add(dataList.subList((i - 1) * rowMaxCount , i * rowMaxCount));
		}
	}
	return subList;
}

/**
* 获取方法名
* @param 属性名
* */
private static String getMethodName(String fieldName){
return "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
}

}

StudentBean 文件

package exportexcel;

import java.util.Date;

public class StudentBean {
/学号*/
private int rowId;
/姓名/
private String stuName;
/**学号/
private String stuNum;
/性别*/
private String stuGender;
/入学日期/
private Date stuAdmission;
/**总成绩/
private int stuCountScore;
/**备注*/
String remark;

public int getRowId() {
	return rowId;
}
public void setRowId(int rowId) {
	this.rowId = rowId;
}
public String getStuName() {
	return stuName;
}
public void setStuName(String stuName) {
	this.stuName = stuName;
}
public String getStuNum() {
	return stuNum;
}
public void setStuNum(String stuNum) {
	this.stuNum = stuNum;
}
public String getStuGender() {
	return stuGender;
}
public void setStuGender(String stuGender) {
	this.stuGender = stuGender;
}
public Date getStuAdmission() {
	return stuAdmission;
}
public void setStuAdmission(Date stuAdmission) {
	this.stuAdmission = stuAdmission;
}
public int getStuCountScore() {
	return stuCountScore;
}
public void setStuCountScore(int stuCountScore) {
	this.stuCountScore = stuCountScore;
}
public String getRemark() {
	return remark;
}
public void setRemark(String remark) {
	this.remark = remark;
}

}

需要的jar包
poi-3.15.jar
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
commons-collections4-4.1.jar
xmlbeans-2.3.0.jar