java poi生成数据透视表

/**
     * 导出EXCEL---poi
     */
    @Override
    public void pivot(HttpServletResponse response) throws Exception{
        //创建excel在内存中 .xls
        Workbook wb = new XSSFWorkbook();
        //创建一个sheet页
        Sheet sheet = wb.createSheet("患者信息一览表");
//        //获取单元格格式信息
        ExcelCellPojo pojo = new ExcelCellPojo(wb);
        //表头
        String[] headStr={"id","入ICU时间","出院时间","转科时间","诊断情况","转归","呼吸机使用","高流量","PICCO","ECMO","CRRT"};
        //开始的行号
        int rowIndex=0;
        //创建标题栏
        Row title =sheet.createRow(rowIndex);
        //设置行高
        title.setHeightInPoints(75);
        title.createCell(0).setCellValue("患者信息一览表");
        title.getCell(0).setCellStyle(pojo.getTitleCellStyle());
        //创建合并单元格
        CellRangeAddress cra=new CellRangeAddress(0,0,0,headStr.length-1);
        sheet.addMergedRegion(cra);
        // 下边框
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
        // 左边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
        // 有边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
        // 上边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
        //创建表头的一行
        Row header = sheet.createRow(++rowIndex);
        //设置行高
        header.setHeightInPoints(60);
        //循环创建表头赋值
        for (int i = 0; i < headStr.length; i++) {
            //设置列宽度 -第1列
            sheet.setColumnWidth(i,5400);
            //创建表头第一列并且赋值
            header.createCell(i).setCellValue(headStr[i]);
            //设置单元格样式
            header.getCell(i).setCellStyle(pojo.getHeaderStyle());
        }
        //查询数据
        List<TestPatientInf> patientList = super.selectAll();
        for (TestPatientInf patientInf:patientList) {
            ++rowIndex;
            //从第2行开始创建
            Row row = sheet.createRow(rowIndex);
            row.setHeightInPoints(40);
            row.createCell(0).setCellValue(patientInf.getId());
            row.getCell(0).setCellStyle(pojo.getCellStyle());
            row.createCell(1).setCellValue(patientInf.getIcuTimeIn());
            row.getCell(1).setCellStyle(pojo.getDateCellStyle());
            row.createCell(2).setCellValue(patientInf.getDischargeTime());
            row.getCell(2).setCellStyle(pojo.getDateCellStyle());
            row.createCell(3).setCellValue(patientInf.getCollegeTime());
            row.getCell(3).setCellStyle(pojo.getDateCellStyle());
            row.createCell(4).setCellValue(patientInf.getDiagnosis());
            row.getCell(4).setCellStyle(pojo.getCellStyle());
            row.createCell(5).setCellValue(patientInf.getOutcome());
            row.getCell(5).setCellStyle(pojo.getCellStyle());
            row.createCell(6).setCellValue(StringUtil.isNullOrBlank(patientInf.getHxjsy())?"无":patientInf.getHxjsy());
            row.getCell(6).setCellStyle(pojo.getCellStyle());
            row.createCell(7).setCellValue(StringUtil.isNullOrBlank(patientInf.getGll())?"无":patientInf.getGll());
            row.getCell(7).setCellStyle(pojo.getCellStyle());
            row.createCell(8).setCellValue(patientInf.getPicco()==null?0:patientInf.getPicco());
            row.getCell(8).setCellStyle(pojo.getDoubleCellStyle());
            row.createCell(9).setCellValue(patientInf.getEcmo()==null?0:patientInf.getEcmo());
            row.getCell(9).setCellStyle(pojo.getCellStyle());
            row.createCell(10).setCellValue(patientInf.getCrrt()==null?0:patientInf.getCrrt());
            row.getCell(10).setCellStyle(pojo.getCellStyle());
        }

        //创建数据透视表--新的sheet页码
        //为需要汇总和创建分析的数据创建缓存
        XSSFSheet pivotSheet  = (XSSFSheet)wb.createSheet("患者信息透视表");
        //左边起始单元格
        CellReference leftStart=new CellReference(ExcelUtils.getCellLocation(2,1));
        //右边结束单元格
        CellReference rightEnd=new CellReference(ExcelUtils.getCellLocation(rowIndex+1,headStr.length));
        // 数据透视表生产的起点单元格位置
        CellReference ptStartCell = new CellReference("A4");
        //创建数据透视表格
        AreaReference area = new AreaReference(leftStart, rightEnd, SpreadsheetVersion.EXCEL2007);
        XSSFPivotTable pivotTable = pivotSheet.createPivotTable(area,ptStartCell,sheet);
        //头上的列标签
        pivotTable.addColLabel(1);
        //透视表 列值
        pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0,"计数项:入ICU时间");
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 8,"求和项:PICCO");
        //透视表 行标签
        pivotTable.addRowLabel(0);
        //透视表 行的值
        pivotTable.addRowLabel(1);
        pivotTable.addRowLabel(2);
        pivotTable.addRowLabel(3);
        pivotTable.addRowLabel(4);
        ExcelUtils.exportExcel(wb,"test_aaa.xlsx",response);
    }

package cn.com.zhengya.framework.utils.excel.entity;


import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;

/**
 * Excel默认样式
 * @author  luwl
 */
public class ExcelCellPojo {
    /**
     * 表头样式



语言
方法


1387
抖音创业网

5Hrj0

抖音是哪个公司的老板是谁「详解」

2849
2008.07.03 06-53-43

     */
    private CellStyle headerStyle;
    /**
     * 默认单元格样式
     */
    private CellStyle cellStyle;
    /**
     * 标题栏样式
     */
    private CellStyle titleCellStyle;
    /**
     * 日期单元格样式
     */
    private  CellStyle dateCellStyle;
    /**
     * 数字类型单元格样式
     */
    private CellStyle doubleCellStyle;

    public ExcelCellPojo(Workbook wb){
        this.setHeaderStyle(wb);
        this.setCellStyle(wb);
        this.setDateCellStyle(wb);
        this.setTitleCellStyle(wb);
        this.setDoubleCellStyle(wb);
    }

    public CellStyle getHeaderStyle() {
        return headerStyle;
    }

    public CellStyle getCellStyle() {
        return cellStyle;
    }

    public CellStyle getDateCellStyle() {
        return dateCellStyle;
    }

    public CellStyle getTitleCellStyle() {
        return titleCellStyle;
    }

    public CellStyle getDoubleCellStyle() {
        return doubleCellStyle;
    }

    private void setHeaderStyle(Workbook wb){
        // 生成表头单元样式
        headerStyle = wb.createCellStyle();
        //表头样式
        headerStyle.setFillForegroundColor((short)1);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //表头边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        //水平方向-居中对齐
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直方向-垂直居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //字体
        Font font = wb.createFont();
        //字体大小
        font.setFontHeightInPoints((short) 16);
        //字体加粗
        font.setBold(true);
        headerStyle.setFont(font);
    }

    private void setCellStyle(Workbook wb){
        // 生成单元格式样（基础式样）
        cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        //水平方向-居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直方向-垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
    }

    private void setDateCellStyle(Workbook wb){
        // 生成单元格式样-日期类型（yyyy/mm/dd日期格式）
        dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(this.cellStyle);
        DataFormat format = wb.createDataFormat();
        dateCellStyle.setDataFormat(format.getFormat("yyyy/mm/dd"));
    }

    private void setTitleCellStyle(Workbook wb) {
        //生成标题单元格样式
        titleCellStyle = wb.createCellStyle();
        //表头样式
        titleCellStyle.setFillForegroundColor((short)1);
        titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //表头边框
        titleCellStyle.setBorderBottom(BorderStyle.THIN);
        titleCellStyle.setBorderLeft(BorderStyle.THIN);
        titleCellStyle.setBorderRight(BorderStyle.THIN);
        titleCellStyle.setBorderTop(BorderStyle.THIN);
        //水平方向-居中对齐
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直方向-垂直居中
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //字体
        Font font = wb.createFont();
        //字体大小
        font.setFontHeightInPoints((short) 20);
        titleCellStyle.setFont(font);
    }

    private void setDoubleCellStyle(Workbook wb) {
        // 生成单元格式样（基础式样）
        doubleCellStyle = wb.createCellStyle();
        doubleCellStyle.setBorderBottom(BorderStyle.THIN);
        doubleCellStyle.setBorderLeft(BorderStyle.THIN);
        doubleCellStyle.setBorderRight(BorderStyle.THIN);
        doubleCellStyle.setBorderTop(BorderStyle.THIN);
        //水平方向-居中对齐
        doubleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直方向-垂直居中
        doubleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        doubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        doubleCellStyle.setWrapText(true);
    }

}

 /**
     * Http导出Excel
     * @param response
     */
    public static void exportExcel(Workbook wb,String fileName,HttpServletResponse response){
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream out = null;
        try {
            // 通过流将excel写出
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            byte[] bytes = bos.toByteArray();
            // 获取输出流
            out = response.getOutputStream();
            // 设置头信息
            response.setContentLength(bytes.length);
            response.setHeader("Content-disposition", "attachment;filename="
                    + new String(fileName.getBytes("GBK"), "ISO8859-1"));
            // 通过流将excel写出
            wb.write(out);
            wb.close();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

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