POI导出复杂的excel；excel公共样式类；excel拼接定制类；数据科学计数法转为普通值

一、excel公共样式类（包含数据科学计数法转为普通值）

package com.thinkgem.jeesite.common.utils.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Font;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;

/**
 * EXCEL报表工具类.
 *
 * @author Jeelon
 */
public class ExportComplexExcel {

    private HSSFWorkbook wb = null;
    private HSSFSheet sheet = null;

    public HSSFSheet getSheet() {
        return sheet;
    }
    public void setSheet(HSSFSheet sheet) {
        this.sheet = sheet;
    }
    public HSSFWorkbook getWb() {
        return wb;
    }
    public void setWb(HSSFWorkbook wb) {
        this.wb = wb;
    }

    /**
     * @param wb
     * @param sheet
     */
    public ExportComplexExcel(HSSFWorkbook wb, HSSFSheet sheet) {
        // super();
        sheet.setColumnWidth((short)0, (short)(10));
        sheet.setColumnWidth((short)1, (short)(10));
        sheet.setColumnWidth((short)2, (short)(400*10));
        sheet.setColumnWidth((short)3, (short)(300*10));
        sheet.setColumnWidth((short)4, (short)(400*10));
        sheet.setColumnWidth((short)5, (short)(400*10));
        sheet.setColumnWidth((short)6 ,(short)(300*10));
        sheet.setColumnWidth((short)7 ,(short)(300*10));
        sheet.setColumnWidth((short)8 ,(short)(300*10));
        sheet.setColumnWidth((short)9, (short)(300*10));
        sheet.setColumnWidth((short)10, (short)(300*10));
        sheet.setColumnWidth((short)11, (short)(300*10));
        sheet.setColumnWidth((short)12, (short)(300*10));
        sheet.setColumnWidth((short)13, (short)(300*10));
        sheet.setColumnWidth((short)14, (short)(300*10));
        sheet.setZoom(4,5);
        this.wb = wb;
        this.sheet = sheet;
    }

    /**
     * 创建通用EXCEL头部 --居中 加粗字体 合并单元格
     * @param headString 头部字符
     * @param colfrom    起始列
     * @param colto      结束列
     * @param rowfrom    起始行数，最小从0开始
     * @param rowto      结束行数，最小从0开始
     * @author  lushiqin
     */
    public void createNormalHead(String headString, int colfrom,int colto,int rowfrom ,int rowto,int intfont) {
        HSSFRow row = sheet.createRow(rowfrom);
        //row.setHeightInPoints(20);
        // 设置第一行
        HSSFCell cell = row.createCell(colfrom);
         row.setHeight((short) intfont);

        // 定义单元格为字符串类型
        cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
        cell.setCellValue(new HSSFRichTextString(headString));

        // 指定合并区域
        /**
         * public Region(int rowFrom, short colFrom, int rowTo, short colTo)
         */
        sheet.addMergedRegion(new Region((short)rowfrom, (short)colfrom, (short)rowto, (short) colto));

        // 定义单元格格式，添加单元格表样式，并添加到工作簿
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格水平对齐类型
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行

        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short)12);
        font.setFontHeight((short)intfont);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 创建通用EXCEL头部 --居左 加粗字体 合并单元格
     * @param headString 头部字符
     * @param colfrom    起始列
     * @param colto      结束列
     * @param rowfrom    起始行数，最小从0开始
     * @param rowto      结束行数，最小从0开始
     * @author  lushiqin
     */
    public void createHead(String headString, int colfrom,int colto,int rowfrom ,int rowto,int intfont) {
        HSSFRow row = sheet.createRow(rowfrom);
        //row.setHeightInPoints(20);
        // 设置第一行
        HSSFCell cell = row.createCell(colfrom);
        row.setHeight((short) intfont);

        // 定义单元格为字符串类型
        cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
        cell.setCellValue(new HSSFRichTextString(headString));

        // 指定合并区域
        /**
         * public Region(int rowFrom, short colFrom, int rowTo, short colTo)
         */
        sheet.addMergedRegion(new Region((short)rowfrom, (short)colfrom, (short)rowto, (short) colto));

        // 定义单元格格式，添加单元格表样式，并添加到工作簿
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格水平对齐类型
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short)12);
        font.setFontHeight((short)intfont);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 设置报表标题
     *
     * @param rowfrom 标题字符串数组
     * @param colfrom 列起始
     * @param colto 列终止
     * @param top 单元格的top-border值
     * @param bottom 单元格的bottom-border值
     * @param left 单元格的left-border值
     * @param right 单元格的right-border值
     * @param color 背景颜色的index值 0为不设置，10为红色，8为黑色 具体的值可以参考HSSFColor类
     * @author  lushiqin
     */
    //---unused---
    public void setRowCellStyle(int rowfrom, int colfrom, int colto, int top, int bottom, int left, int right, int color) {

        HSSFRow row = sheet.createRow(rowfrom);
        row.setHeightInPoints(20);

        // 定义单元格格式，添加单元格表样式，并添加到工作簿
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格水平对齐类型
        /*cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行*/
        cellStyle.setBorderBottom((short) bottom);
        cellStyle.setBorderTop((short) top);
        cellStyle.setBorderLeft((short) left);
        cellStyle.setBorderRight((short) right);
        if (color != 0) {
            cellStyle.setFillBackgroundColor((short) color);
        }
        // 多一行的多列，进行设置格式
        for (int i = colfrom; i <= colto; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 设置数据报表的头部行 --灰色底 字体加粗 居中
     * @param   columHeader 标题字符串数组
     * @param   row 文数据从哪一行开始
     * @author  lushiqin
     */
    public void createColumHeader(String[] columHeader,int row, int colfrom,int colto) {

        // 设置列头 在第三行
        HSSFRow row2 = sheet.createRow(row);

        // 指定行高
        row2.setHeight((short) 350);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行

        // 单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 150);
        cellStyle.setFont(font);

        // 设置单元格背景色
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFCell cell3 = null;
        for (int i = colfrom; i <=colto; i++) {
            cell3 = row2.createCell(i);
            cell3.setCellType(HSSFCell.ENCODING_UTF_16);
            cell3.setCellStyle(cellStyle);
            cell3.setCellValue(new HSSFRichTextString(columHeader[i-colfrom]));
        }
    }

    /**
     * 创建单元格 可指定颜色的index  居左-加粗
     *  @param   row   标题字符串数组
     *  @param   col   列
     *  @param   value 单元格的值
     *  @param   color 字体颜色设置 //   常用颜色的index值  red:10  green:17  normal:32767
     * @author  lushiqin
     */

    public HSSFCell createCell(HSSFRow row, int col,Object value,int color,int celltype) {
        HSSFCell cell = row.createCell(col);
        HSSFCellStyle datastyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short)180);
        font.setColor((short)color);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        datastyle.setFont(font); //字体样式设置
        datastyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐

        cell.setCellType(HSSFCell.ENCODING_UTF_16);
        cell.setCellStyle(datastyle);
        //cell.setCellType(celltype);
        cell.setCellValue(String.valueOf(value));

        return cell;

    }

    /**
     * 数据科学计数转Double
     * @param data
     * @return
     */
    public static Double scientificJudge(Object data){
        String  str =data.toString();
        if(str.contains(".") && str.contains("E")){
            BigDecimal db = new BigDecimal(str);
           System.out.println("--db.toPlainString()--"+db.doubleValue());
            return db.doubleValue();
        }else{
            return Double.valueOf(str);
        }

    }

    /**
     * 科学记数转整数
     * @param str
     * @return
     */
    public static String scientificJudge(String str){
        if(str.contains(".") && str.contains("E")){
            BigDecimal db = new BigDecimal(str);
            return db.toPlainString();
        }else{
            return str;
        }

    }
    /**
     * 创建单元格  可指定单元格的样式 转换数据显示格式 整数和小数的显示样式
     *  @param   row     标题字符串数组
     *  @param   col     列
     *  @param   data    单元格的值
     *  @param   contextstyle   传入样式
     *  @author  lushiqin
     */
    public HSSFCell createCell(HSSFRow row, int col,Object data,CellStyle contextstyle,int celltype) {
        HSSFCell contentCell = row.createCell(col);
        contentCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        //System.out.println("--data--"+data);
        //System.out.println("--data.toString()--"+data.toString());
        Boolean isNum = false;//data是否为数值型
        Boolean isInteger=false;//data是否为整数
        Boolean isPercent=false;//data是否为百分数
        int length=0;
        if (data != null || "".equals(data)) {
            //判断data是否为数值型
            isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
            //判断data是否为整数（小数部分是否为0）
            isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
            //判断data是否为百分数（是否包含“%”）
            isPercent=data.toString().contains("%");
        }

        //如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
        if (isNum && !isPercent) {
            HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
            if (isInteger) {
                contextstyle.setDataFormat(df.getBuiltinFormat("#,##0"));//数据格式只显示整数
               /*contextstyle.setDataFormat(df.getFormat("@"));//数据格式只显示整数

                DecimalFormat df2 = new DecimalFormat("0");
                String whatYourWant = df2.format(contentCell.getNumericCellValue());
                contentCell.setCellValue(whatYourWant);*/
                contentCell.setCellStyle(contextstyle);
            }

//            else if (Double.parseDouble(data.toString())<1) {
//                contextstyle.setDataFormat(df.getBuiltinFormat("0.00%"));//数据格式只显示整数
//                // 设置单元格格式
//                contentCell.setCellStyle(contextstyle);
//                // 设置单元格内容为double类型
//                contentCell.setCellValue(Double.parseDouble(data.toString()));
//
//            }
            else if (Double.parseDouble(data.toString())>=1){
                // contextstyle.setDataFormat(df.getFormat("@"));//text类型
                contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
                // 设置单元格格式
                contentCell.setCellStyle(contextstyle);
                // 设置单元格内容为double类型
                contentCell.setCellValue(Double.parseDouble(data.toString()));

              /* DataFormatter formatter = new DataFormatter();
                String var_name = formatter.formatCellValue(contentCell);
                //System.out.println("--var_name--"+var_name);
                contentCell.setCellValue(var_name);*/

                /* DecimalFormat df2 = new DecimalFormat("#,##0.00");
                String whatYourWant = df2.format(contentCell.getNumericCellValue());
                //contentCell.setCellStyle(contextstyle);
                contentCell.setCellValue(whatYourWant);*/
            }
        }
        else if(data.toString().contains(".") && data.toString().contains("E")){
            BigDecimal db = new BigDecimal(data.toString());
            HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
            contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
            // 设置单元格格式
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为double类型
            //contentCell.setCellValue(Double.parseDouble(data.toString()));
            contentCell.setCellValue(scientificJudge(data));
        }
        else {
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为字符型
            contentCell.setCellValue(data.toString());
        }
        return contentCell;

    }

    public HSSFCell createPonitCell(HSSFRow row, int col,Object data,HSSFCellStyle contextstyle,int celltype) {
        HSSFCell contentCell = row.createCell(col);
        //HSSFCellStyle contextstyle = wb.createCellStyle();
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short)180);
        contextstyle.setFont(font); //字体样式设置
        contextstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        contentCell.setCellType(HSSFCell.ENCODING_UTF_16);

        Boolean isNum = false;//data是否为数值型
        Boolean isInteger=false;//data是否为整数
        Boolean isPercent=false;//data是否为百分数
        int length=0;
        if (data != null || "".equals(data)) {
            //判断data是否为数值型
            isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
            //判断data是否为整数（小数部分是否为0）
            isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
            //判断data是否为百分数（是否包含“%”）
            isPercent=data.toString().contains("%");
        }

        if (isNum && !isPercent) {
            HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
            contextstyle.setDataFormat(df.getBuiltinFormat("0.00%"));//数据格式只显示整数
            // 设置单元格格式
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为double类型
            contentCell.setCellValue(Double.parseDouble(data.toString()));
        }
        else {
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为字符型
            contentCell.setCellValue(data.toString());
        }

        return contentCell;

    }

    public HSSFCell createPonitCell(HSSFRow row, int col,Object data,int celltype) {
        HSSFCell contentCell = row.createCell(col);
        HSSFCellStyle contextstyle = wb.createCellStyle();
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short)180);
        contextstyle.setFont(font); //字体样式设置
        contextstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        contentCell.setCellType(HSSFCell.ENCODING_UTF_16);

        Boolean isNum = false;//data是否为数值型
        Boolean isInteger=false;//data是否为整数
        Boolean isPercent=false;//data是否为百分数
        int length=0;
        if (data != null || "".equals(data)) {
            //判断data是否为数值型
            isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
            //判断data是否为整数（小数部分是否为0）
            isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
            //判断data是否为百分数（是否包含“%”）
            isPercent=data.toString().contains("%");
        }

        if (isNum && !isPercent) {
            HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
            contextstyle.setDataFormat(df.getBuiltinFormat("0.00%"));//数据格式只显示整数
            // 设置单元格格式
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为double类型
            contentCell.setCellValue(Double.parseDouble(data.toString()));
        }
        else {
            contentCell.setCellStyle(contextstyle);
            // 设置单元格内容为字符型
            contentCell.setCellValue(data.toString());
        }

        return contentCell;

    }

    /**
     * 写入EXCEL文件
     * @param   fileName 文件名
     * @author  lushiqin
     */
    public void outputExcel(String fileName) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(fileName));
            wb.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建通用报表第二行
     *
     * @param params
     *            统计条件数组
     * @param colfrom
     *            需要合并的起始列
     * @param colfrom
     *            需要合并的结束列
     * @param rowfrom
     *             指定起始行
     * @param rowto
     *             指定结束行
     */
    @SuppressWarnings("deprecation")
    public void createNormalTwoRow(String[] params, int colfrom,int colto,int rowfrom ,int rowto) {
        // 创建第二行
        HSSFRow row1 = sheet.createRow(rowfrom);

        row1.setHeight((short) 400);

        HSSFCell cell2 = row1.createCell(0);

        cell2.setCellType(HSSFCell.ENCODING_UTF_16);
        cell2.setCellValue(new HSSFRichTextString("时间：" + params[0] + "至"
                + params[1]));

        // 指定合并区域
        /**
         * public Region(int rowFrom, short colFrom, int rowTo, short colTo)
         */
        sheet.addMergedRegion(new Region(rowfrom, (short) colfrom, rowto, (short) colto));

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行

        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 250);
        cellStyle.setFont(font);

        cell2.setCellStyle(cellStyle);
    }

    /**
     * 创建内容单元格
     *
     * @param wb
     *            HSSFWorkbook
     * @param row
     *            HSSFRow
     * @param col
     *            short型的列索引
     * @param align
     *            对齐方式
     * @param val
     *            列值
     */
    public void createCell(HSSFWorkbook wb, HSSFRow row, int col, short align,
                           String val) {
        HSSFCell cell = row.createCell(col);
        cell.setCellType(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(new HSSFRichTextString(val));
        HSSFCellStyle cellstyle = wb.createCellStyle();
        cellstyle.setAlignment(align);
        cell.setCellStyle(cellstyle);
    }

    /**
     * 创建合计行
     *
     * @param colSum
     *            需要合并到的列索引
     * @param cellValue
     */
    @SuppressWarnings("deprecation")
    public void createLastSumRow(int colSum, String[] cellValue) {

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
        cellStyle.setWrapText(true);// 指定单元格自动换行

        // 单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 250);
        cellStyle.setFont(font);
        // 获取工作表最后一行
        HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
        HSSFCell sumCell = lastRow.createCell(0);

        sumCell.setCellValue(new HSSFRichTextString("合计"));
        sumCell.setCellStyle(cellStyle);
        // 合并 最后一行的第零列-最后一行的第一列
        sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,
                sheet.getLastRowNum(), (short) colSum));// 指定合并区域

        for (int i = 2; i < (cellValue.length + 2); i++) {
            // 定义最后一行的第三列
            sumCell = lastRow.createCell(i);
            sumCell.setCellStyle(cellStyle);
            // 定义数组 从0开始。
            sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));
        }
    }

}

二、excel拼接公共类

public void importExcelTest(String gameId,String gameName ,String filepath,String date,String date1 ,String date2,String month){

        //创建excel对象和sheet表单
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        ExportComplexExcel exportExcel = new ExportComplexExcel(wb, sheet);

        // 单元格样式
        HSSFCellStyle datastyle = wb.createCellStyle();
        HSSFCellStyle boldstyle = wb.createCellStyle();

        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeight((short)180);
        datastyle.setFont(font); //字体样式设置
        datastyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

        HSSFFont font2 = wb.createFont();
        font2.setFontName("微软雅黑");
        font2.setFontHeight((short)180);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldstyle.setFont(font2);
        boldstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

        HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
        //datastyle.setDataFormat(df.getBuiltinFormat("#.00"));//保留两位小数点

        //获取excel表格数据
        // 创建表单头部
        exportExcel.createHead(gameName,2,18,1,1,600);
        // 创建表单中部
        /*exportExcel.createNormalHead("数据说",10,11,4,6,400);
        exportExcel.setRowCellStyle(3,10,11,0,20,0,0,0);
        exportExcel.setRowCellStyle(5,4,9,0,10,0,0,0);
        exportExcel.setRowCellStyle(5,12,21,0,10,0,0,0);*/

        //设置小标题
        exportExcel.createNormalHead("关键数据",2,3,4,4,300);

        HSSFRow row5 = sheet.createRow(5);
       // exportExcel.createCell(row5,4,"日期：",32767,1);
        //exportExcel.createCell(row5,5,date1,datastyle,1);

        DayStats todayDay=dayStatsService.queryTodayDayStats(gameId,date1);
        DayStats lastDay=dayStatsService.queryTodayDayStats(gameId,date2);
        HSSFRow row6 = sheet.createRow(6);
        HSSFRow row7 = sheet.createRow(7);
        HSSFRow row8 = sheet.createRow(8);

        exportExcel.createCell(row5,5,"新增设备：",32767,1);
        exportExcel.createCell(row5,8,"登录用户：",32767,1);
        exportExcel.createCell(row5,11,"充值金额：",32767,1);

        exportExcel.createCell(row6,4,date1,32767,1);
        exportExcel.createCell(row6,7,date1,32767,1);
        exportExcel.createCell(row6,10,date1,32767,1);

        exportExcel.createCell(row7,4,date2,32767,1);
        exportExcel.createCell(row7,7,date2,32767,1);
        exportExcel.createCell(row7,10,date2,32767,1);

        exportExcel.createCell(row8,4,"环比",32767,1);
        exportExcel.createCell(row8,7,"环比",32767,1);
        exportExcel.createCell(row8,10,"环比",32767,1);

        if (todayDay!=null){
            exportExcel.createCell(row6,5,todayDay.getDayDeviceRegNum()==null?"":todayDay.getDayDeviceRegNum(),32767,1);
            exportExcel.createCell(row6,8,todayDay.getDayLoginNum()==null?"":todayDay.getDayLoginNum(),32767,1);
            exportExcel.createCell(row6,11,todayDay.getDayPayment()==null?"":todayDay.getDayPayment(),32767,1);

            exportExcel.createCell(row7,5,lastDay.getDayDeviceRegNum()==null?"":lastDay.getDayDeviceRegNum(),32767,1);
            exportExcel.createCell(row7,8,lastDay.getDayLoginNum()==null?"":lastDay.getDayLoginNum(),32767,1);
            exportExcel.createCell(row7,11,lastDay.getDayPayment()==null?"":lastDay.getDayPayment(),32767,1);

            if(lastDay!=null){

                double divDevice=(todayDay.getDayDeviceRegNum()==null?0:todayDay.getDayDeviceRegNum())-(lastDay.getDayDeviceRegNum()==null?0:lastDay.getDayDeviceRegNum());
                double divLoginNum=(todayDay.getDayLoginNum()==null?0:todayDay.getDayLoginNum())-(lastDay.getDayLoginNum()==null?0:lastDay.getDayLoginNum());
                double divPayment=(todayDay.getDayPayment()==null?0:todayDay.getDayPayment())-(lastDay.getDayPayment()==null?0:lastDay.getDayPayment());

                String  divDevicePoint="0.01%";
                String  divLoginNumPoint="0.01%";
                String  divPaymentPoint="0.01%";
                String flag1="";
                String flag2="";
                String flag3="";

                NumberFormat nt = NumberFormat.getPercentInstance();
                //设置百分数精确度2即保留两位小数
                nt.setMinimumFractionDigits(2);
                if(lastDay.getDayDeviceRegNum()!=null&&lastDay.getDayDeviceRegNum()>0){
                    divDevicePoint=nt.format(divDevice/lastDay.getDayDeviceRegNum());
                }
                if(lastDay.getDayLoginNum()!=null&&lastDay.getDayLoginNum()>0){
                    divLoginNumPoint=nt.format(divLoginNum/lastDay.getDayLoginNum());
                }
                if(lastDay.getDayPayment()!=null&&lastDay.getDayPayment()>0){
                    divPaymentPoint=nt.format(divPayment/lastDay.getDayPayment());
                }
                if(divDevice>0){
                    flag1="+";
                    exportExcel.createCell(row8,5,flag1+divDevicePoint,10,1);
                }else if(divDevice<=0){
                    flag1="";
                    exportExcel.createCell(row8,5,flag1+divDevicePoint,17,1);
                }

                if(divLoginNum>0){
                    flag2="+";
                    exportExcel.createCell(row8,8,flag2+divLoginNumPoint,10,1);
                }else if(divLoginNum<=0){
                    flag2="";
                    exportExcel.createCell(row8,8,flag2+divLoginNumPoint,17,1);
                }

                if(divPayment>0){
                    flag3="+";
                    exportExcel.createCell(row8,11,flag3+divPaymentPoint,10,1);

                }else if(divPayment<=0){
                    flag3="";
                    exportExcel.createCell(row8,11,flag3+divPaymentPoint,17,1);

                }
            }

        }

        exportExcel.createNormalHead("基础数据",2,3,9,9,300);

        //设置表格的表头
        String[] params={"日期","新增设备","登录用户","充值金额","ARPPU","付费率","ARPU","新用户ARPPU","老用户ARPPU"
                ,"新用户付费率","老用户付费率","次日留存率","3日留存率","7日留存率","14日留存率","30日留存率","30日LTV"};
        exportExcel.createColumHeader(params,10,2,18);

        //按天汇总数据
        List<DayStats> list=dayStatsService.queryDayStats(gameId,date);
        logger.info("--按天汇总数据条数--"+list.size());
        int rows= list.size();

        for(int i = 0; i < rows; i++ ){
            boolean isHidden=false;
            DayStats  item=list.get(i);
            HSSFRow row =row = sheet.createRow(i+11);
            String mydate=item.getStatDate(); //2018-06-14
            logger.info("-----month-----"+month);
            logger.info("-----mydate.startsWith(month)-----"+mydate.startsWith(month));
            if(!mydate.startsWith(month)&&mydate.length()==10){
                row.setZeroHeight(true);
                exportExcel.createCell(row,2,mydate,datastyle,0);
                exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),datastyle,1);
                exportExcel.createCell(row,4,item.getDayLoginNum()==null?"":item.getDayLoginNum(),datastyle,1);
                exportExcel.createCell(row,5,item.getDayPayment()==null?"":item.getDayPayment(),datastyle,1);
                exportExcel.createCell(row,6,item.getArppu()==null?"":item.getArppu(),datastyle,1);
                //exportExcel.createPonitCell(row,7,item.getPermeability()==null?"":item.getPermeability(),datastyle,1);

                exportExcel.createCell(row,7,item.getPermeability()==null?"": String.format("%.2f",item.getPermeability()*100)+"%",datastyle,1);

                exportExcel.createCell(row,8,item.getArpu()==null?"":item.getArpu(),datastyle,1);
                exportExcel.createCell(row,9,item.getArppuNewuser()==null?"":item.getArppuNewuser(),datastyle,1);
                exportExcel.createCell(row,10,item.getArppuOlduser()==null?"":item.getArppuOlduser(),datastyle,1);
                exportExcel.createCell(row,11,item.getPermNewuser()==null?"":String.format("%.2f",item.getPermNewuser()*100)+"%",datastyle,1);
                exportExcel.createCell(row,12,item.getPermOlduser()==null?"":String.format("%.2f",item.getPermOlduser()*100)+"%",datastyle,1);
                exportExcel.createCell(row,13,item.getOneDayResidentNum()==null?"":String.format("%.2f",item.getOneDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,14,item.getThreeDayResidentNum()==null?"":String.format("%.2f",item.getThreeDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,15,item.getSevenDayResidentNum()==null?"":String.format("%.2f",item.getSevenDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,16,item.getFourteenDayResidentNum()==null?"":String.format("%.2f",item.getFourteenDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,17,item.getThirtyDayResidentNum()==null?"":String.format("%.2f",item.getThirtyDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,18,item.getLtv30()==null?"":String.format("%.2f",item.getLtv30()*100)+"%",datastyle,1);

            }

            else if(mydate.length()==7){
                mydate=mydate.substring(0,4)+"年"+mydate.substring(5,7)+"月汇总";
                exportExcel.createCell(row,2,mydate,boldstyle,0);
                exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),boldstyle,1);
                exportExcel.createCell(row,4,item.getDayLoginNum()==null?"":item.getDayLoginNum(),boldstyle,1);
                exportExcel.createCell(row,5,item.getDayPayment()==null?"":item.getDayPayment(),boldstyle,1);
                exportExcel.createCell(row,6,item.getArppu()==null?"":item.getArppu(),boldstyle,1);
                //exportExcel.createPonitCell(row,7,item.getPermeability()==null?"":item.getPermeability(),boldstyle,1);

                exportExcel.createCell(row,7,item.getPermeability()==null?"": String.format("%.2f",item.getPermeability()*100)+"%",boldstyle,1);

                exportExcel.createCell(row,8,item.getArpu()==null?"":item.getArpu(),boldstyle,1);
                exportExcel.createCell(row,9,item.getArppuNewuser()==null?"":item.getArppuNewuser(),boldstyle,1);
                exportExcel.createCell(row,10,item.getArppuOlduser()==null?"":item.getArppuOlduser(),boldstyle,1);
                exportExcel.createCell(row,11,item.getPermNewuser()==null?"":String.format("%.2f",item.getPermNewuser()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,12,item.getPermOlduser()==null?"":String.format("%.2f",item.getPermOlduser()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,13,item.getOneDayResidentNum()==null?"":String.format("%.2f",item.getOneDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,14,item.getThreeDayResidentNum()==null?"":String.format("%.2f",item.getThreeDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,15,item.getSevenDayResidentNum()==null?"":String.format("%.2f",item.getSevenDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,16,item.getFourteenDayResidentNum()==null?"":String.format("%.2f",item.getFourteenDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,17,item.getThirtyDayResidentNum()==null?"":String.format("%.2f",item.getThirtyDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,18,item.getLtv30()==null?"":String.format("%.2f",item.getLtv30()*100)+"%",boldstyle,1);

            }

            else if("总计".equals(mydate)||mydate.contains("测")){
                exportExcel.createCell(row,2,mydate,boldstyle,0);
                exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),boldstyle,1);
                exportExcel.createCell(row,4,item.getDayLoginNum()==null?"":item.getDayLoginNum(),boldstyle,1);
                exportExcel.createCell(row,5,item.getDayPayment()==null?"":item.getDayPayment(),boldstyle,1);
                exportExcel.createCell(row,6,item.getArppu()==null?"":item.getArppu(),boldstyle,1);
                //exportExcel.createPonitCell(row,7,item.getPermeability()==null?"":item.getPermeability(),boldstyle,1);

                exportExcel.createCell(row,7,item.getPermeability()==null?"": String.format("%.2f",item.getPermeability()*100)+"%",boldstyle,1);

                exportExcel.createCell(row,8,item.getArpu()==null?"":item.getArpu(),boldstyle,1);
                exportExcel.createCell(row,9,item.getArppuNewuser()==null?"":item.getArppuNewuser(),boldstyle,1);
                exportExcel.createCell(row,10,item.getArppuOlduser()==null?"":item.getArppuOlduser(),boldstyle,1);
                exportExcel.createCell(row,11,item.getPermNewuser()==null?"":String.format("%.2f",item.getPermNewuser()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,12,item.getPermOlduser()==null?"":String.format("%.2f",item.getPermOlduser()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,13,item.getOneDayResidentNum()==null?"":String.format("%.2f",item.getOneDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,14,item.getThreeDayResidentNum()==null?"":String.format("%.2f",item.getThreeDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,15,item.getSevenDayResidentNum()==null?"":String.format("%.2f",item.getSevenDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,16,item.getFourteenDayResidentNum()==null?"":String.format("%.2f",item.getFourteenDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,17,item.getThirtyDayResidentNum()==null?"":String.format("%.2f",item.getThirtyDayResidentNum()*100)+"%",boldstyle,1);
                exportExcel.createCell(row,18,item.getLtv30()==null?"":String.format("%.2f",item.getLtv30()*100)+"%",boldstyle,1);

            }

            else{
                exportExcel.createCell(row,2,mydate,datastyle,0);
                exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),datastyle,1);
                exportExcel.createCell(row,4,item.getDayLoginNum()==null?"":item.getDayLoginNum(),datastyle,1);
                exportExcel.createCell(row,5,item.getDayPayment()==null?"":item.getDayPayment(),datastyle,1);
                exportExcel.createCell(row,6,item.getArppu()==null?"":item.getArppu(),datastyle,1);
                //exportExcel.createPonitCell(row,7,item.getPermeability()==null?"":item.getPermeability(),datastyle,1);

                exportExcel.createCell(row,7,item.getPermeability()==null?"": String.format("%.2f",item.getPermeability()*100)+"%",datastyle,1);

                exportExcel.createCell(row,8,item.getArpu()==null?"":item.getArpu(),datastyle,1);
                exportExcel.createCell(row,9,item.getArppuNewuser()==null?"":item.getArppuNewuser(),datastyle,1);
                exportExcel.createCell(row,10,item.getArppuOlduser()==null?"":item.getArppuOlduser(),datastyle,1);
                exportExcel.createCell(row,11,item.getPermNewuser()==null?"":String.format("%.2f",item.getPermNewuser()*100)+"%",datastyle,1);
                exportExcel.createCell(row,12,item.getPermOlduser()==null?"":String.format("%.2f",item.getPermOlduser()*100)+"%",datastyle,1);
                exportExcel.createCell(row,13,item.getOneDayResidentNum()==null?"":String.format("%.2f",item.getOneDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,14,item.getThreeDayResidentNum()==null?"":String.format("%.2f",item.getThreeDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,15,item.getSevenDayResidentNum()==null?"":String.format("%.2f",item.getSevenDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,16,item.getFourteenDayResidentNum()==null?"":String.format("%.2f",item.getFourteenDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,17,item.getThirtyDayResidentNum()==null?"":String.format("%.2f",item.getThirtyDayResidentNum()*100)+"%",datastyle,1);
                exportExcel.createCell(row,18,item.getLtv30()==null?"":String.format("%.2f",item.getLtv30()*100)+"%",datastyle,1);

            }

        }

        int channelrow=12+rows;
        logger.info("--渠道详细数据起始行--"+channelrow);
        exportExcel.createNormalHead("渠道详情",2,3,channelrow,channelrow,300);

        //设置表格的表头
        String[] paramsDetail={"平台","新增设备","登录用户","付费用户","充值金额","ARPPU","付费率","ARPU"};
        exportExcel.createColumHeader(paramsDetail,channelrow+1,2,9);

        //渠道详细数据
        List<DayStats> listdetail= dayStatsService.queryDayStatsDetail(gameId,date1);
        logger.info("--今日渠道详细数据条数--"+listdetail.size());
        for(int i = 0; i < listdetail.size(); i++ ){
            DayStats  item=listdetail.get(i);
            HSSFRow row = sheet.createRow(i+channelrow+2);
            exportExcel.createCell(row,2,item.getChannelName()==null?"":item.getChannelName(),datastyle,0);
            exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),datastyle,1);
            exportExcel.createCell(row,4,item.getDayLoginNum()==null?"":item.getDayLoginNum(),datastyle,1);
            exportExcel.createCell(row,5,item.getDayPayerNum()==null?"":item.getDayPayerNum(),datastyle,1);
            exportExcel.createCell(row,6,item.getDayPayment()==null?"":item.getDayPayment(),datastyle,1);
            exportExcel.createCell(row,7,item.getArppu()==null?"":item.getArppu(),datastyle,1);
            exportExcel.createCell(row,8,item.getPermeability()==null?"":String.format("%.2f",item.getPermeability()*100)+"%",datastyle,1);
            exportExcel.createCell(row,9,item.getArpu()==null?"":item.getArpu(),datastyle,1);
        }

        int divrow=channelrow+listdetail.size()+3;
        exportExcel.createNormalHead("昨、前天数据差额",2,3,divrow+1,divrow+1,300);
        //设置表格的表头
        String[] paramsDiv={"渠道","新增设备","充值金额"};
        exportExcel.createColumHeader(paramsDiv,divrow+2,2,4);

        //昨前对比数据
        List<DayStats> listdiv= dayStatsService.queryRecentTwoDay(gameId,date1);
        for(int i = 0; i < listdiv.size(); i++ ){
            DayStats  item=listdiv.get(i);
            HSSFRow row = sheet.createRow(i+divrow+3);
            exportExcel.createCell(row,2,item.getChannelName()==null?"":item.getChannelName(),datastyle,0);
            exportExcel.createCell(row,3,item.getDayDeviceRegNum()==null?"":item.getDayDeviceRegNum(),datastyle,1);
            exportExcel.createCell(row,4,item.getDayPayment()==null?"":item.getDayPayment(),datastyle,1);
        }

        //写入excel文件
        exportExcel.outputExcel(filepath);

    }

三、最终效果