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