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

转载于:https://www.cnblogs.com/Bouger/p/7148884.html