一、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); }
三、最终效果
