上一篇关于XSSFPivotTable的相关源码

utils类:

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.SpreadsheetVersion.EXCEL2007;

public class ExcelUtils {


    /**
     * 将数据保存到EXCEL文件并生成数据透视表
     * <remark>
     * 需要使用实体类来装载数据，
     * 因为是使用反射获取所有方法后使用get方法来取到数据值的，
     * 且使用属性上的@TitltName标签来获取该字段对应的表格标题的
     * </remark>
     *
     * @param dataList 数据。
     * @param type     类型 1 xls 2 xlsx
     * @return
     * @throws Exception
     */
    public static Workbook toExcelMergeCell(List<?> dataList, int type) throws Exception {
        Workbook workbook;
        CellStyle textStyle;
        DataFormat format;
        if (type == 1) {
            workbook = new XSSFWorkbook();
        } else if (type == 2) {
            workbook = new SXSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        textStyle = workbook.createCellStyle();
        format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        List<Method> methodList = null;//用来遍历object的get方法
        Sheet sheet = workbook.createSheet("数据列表");
        //sheet.setColumnWidth(2, 50);
        int index = sheet.getPhysicalNumberOfRows();

        //迭代保存数据，暂未合并单元格
        for (int i = 0; i < dataList.size(); i++) {
            Object object = dataList.get(i);
            if (methodList == null) {
                Method[] methods = object.getClass().getMethods();
                methodList = new ArrayList<>();
                Row rowHead = sheet.createRow(index);
                int c = 0;
                Field[] fields = object.getClass().getDeclaredFields();
                for (Field field : fields) {

                    for (int m = 0; m < methods.length; m++) {
                        if (methods[m].getName().toLowerCase().equals("get" + field.getName().toLowerCase())) {
                            methodList.add(methods[m]);
                            Cell cell = rowHead.createCell(c);
                            setCellValue(cell, field.getAnnotation(TitleName.class).value());
                            c++;
                        }
                    }
                }
                rowHead.createCell(methodList.size()).setCellValue("行号");
            }

            Row row = sheet.createRow(index + 1);
            row.createCell(methodList.size()).setCellValue(i + 1);//设置序号

            for (int m = 0; m < methodList.size(); m++) {//保存列数据
                Object value = methodList.get(m).invoke(object);
                Cell cell = row.createCell(m);
                cell.setCellStyle(textStyle);
                Object textValue = getValue(value);
                setCellValue(cell, textValue);
            }
            index++;
        }

        System.out.println("save data finish!");
        System.out.println("保存为数据透视表 start");
        workbook = toPivotTabl