package com.oemp.club.util;
import java.awt.Color;
import java.awt.List;
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.oemp.util.UtilTools;
public class ExportExcel {
/**
* PIO导出
* title 以,分隔
*/
public static String DownLoadWeeklyReportExcel(String[] title,String path) throws Exception{
String title_list = "活动名称,公司名称,省/市,联系人,职位,手机号,EMAIL,错误原因";
title=title_list.split(",");
XSSFWorkbook wb = null;
try {
// 第一步，创建一个webbook，对应一个Excel文件
wb = new XSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet("固定周报模板");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
sheet.setDefaultRowHeight((short)(290));
for(int i=0;i<50;i++){
sheet.setColumnWidth(i,10*300); //设置宽度
}
//总标题
Row row1 =sheet.createRow(0);
row1.setHeightInPoints(30);
Cell cell = row1.createCell(0);
cell.setCellStyle(reportStyle(wb));
cell.setCellValue("妈妈俱乐部微信数据周报"+UtilTools.formatDate(new Date(),"yyyy-MM-dd"));
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//报表1
int tb=1;
createTableNote(wb,sheet,tb,"1. SUMMARY （注册&粉丝 BY WEEK）","时间：默认最近13自然周");
//表格头
String title1="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet,tb,title1);
tb=tb+2;
createTableContent(wb,sheet,tb);
/*
for(int i=0;i<list.size();i++){
SqlBean detail = list.get(i);
row = sheet.createRow(i + 1);
for(int k=0;k<cloumn.length;k++){
String labelText = "";
if(detail.containsKey(cloumn[k].trim())){
if(detail.get(cloumn[k].trim())!=null){
labelText = detail.get(cloumn[k].trim()).toString();
}
}
row.createCell(k).setCellValue(labelText);
}
}*/
// 报表2
int tb1=tb+12;
createTableNote(wb,sheet,tb1,"2. Top5 Follow source","时间：默认最近1自然周");
String title2="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet,tb1,title2);
tb1=tb1+2;
createTableContent(wb,sheet,tb1);
// 报表3
int tb2=tb1+12;
createTableNote(wb,sheet,tb2,"3. Top3 UnFollow source","时间：默认最近1自然周");
String title3="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet,tb2,title3);
tb2=tb2+2;
createTableContent(wb,sheet,tb2);
// 报表4
int tb3=tb2+12;
createTableNote(wb,sheet,tb3,"5. MEMBER STAGE","时间：截至最近1周周日为止");
String title4="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet,tb3,title4);
tb3=tb3+2;
createTableContent(wb,sheet,tb3);
// 报表5
int tb4=tb3+12;
createTableNote(wb,sheet,tb4,"6. Menu Click","时间：默认最近13自然周 ");
String title5="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet,tb4,title5);
tb4=tb4+2;
createTableContent(wb,sheet,tb4);
//第二个sheet
sheet2(wb);
}catch(Exception ex){
ex.printStackTrace();
}finally{
if (wb != null) {
FileOutputStream fout = new FileOutputStream(path);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return path;
}
/**
* create table content
*/
public static void createTableContent(XSSFWorkbook wb,Sheet sheet,int tb){
for(int i=0;i<10;i++){
Row row = sheet.createRow(i + tb + 1 );
for(int k=0;k<27;k++){
Cell cell= row.createCell(k);
cell.setCellValue(i+"&"+k);
if(i==9){
cell.setCellStyle(cellSummayStyle(wb));
}else{
cell.setCellStyle(cellStyle(wb));
}
}
}
}
/**
* create table Note
*/
public static void createTableNote(XSSFWorkbook wb,Sheet sheet,int tb,String cell1,String cell2){
//小标题
Row table2_row =sheet.createRow(tb);
Cell table2_cell =table2_row.createCell(0);
table2_cell.setCellValue(cell1);
table2_cell.setCellStyle(titleStyle(wb));
//小标题
Row table2_row2 =sheet.createRow(tb+1);
Cell table2_cell2 =table2_row2.createCell(0);
table2_cell2.setCellValue(cell2);
table2_cell2.setCellStyle(titleStyle2(wb));
Cell table2_cell3 =table2_row2.createCell(2);
table2_cell3.setCellValue("关注来源：默认全部 ");
table2_cell3.setCellStyle(titleStyle2(wb));
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(tb,tb,0,2));
}
/**
* 动态表头样式
*/
public static void createTableHeader(XSSFWorkbook wb,Sheet sheet,int rows,String title){
Row row4 =sheet.createRow(rows+2);
String [] header = title.split(",");
for(int i=0;i<header.length;i++){
Cell cell4 =row4.createCell(i);
cell4.setCellValue(header[i]);
if(i<2){
cell4.setCellStyle(cellHeaderStyle1(wb));
}else if(i>=2&&i<7){
cell4.setCellStyle(cellHeaderStyle2(wb));
}else if(i>=7&&i<11){
cell4.setCellStyle(cellHeaderStyle3(wb));
}else{
cell4.setCellStyle(cellHeaderStyle4(wb));
}
}
}
/**
* 第二个sheet
*
*
* 新版用法 3.8版
sheet.addMergedRegion(new CellRangeAddress(
1, //first row (0-based) from 行
2, //last row (0-based) to 行
1, //first column (0-based) from 列
1 //last column (0-based) to 列
));
*
*
*/
public static boolean sheet2(XSSFWorkbook wb){
Sheet sheet2 = wb.createSheet("固定周报模板Weekly-粉丝统计by Day");
sheet2.setDefaultRowHeight((short)(290));
for(int i=0;i<50;i++){
sheet2.setColumnWidth(i,10*300); //设置宽度
}
//总标题
Row row1 =sheet2.createRow(0);
row1.setHeightInPoints(30);
Cell cell = row1.createCell(0);
cell.setCellStyle(reportStyle(wb));
cell.setCellValue("妈妈俱乐部微信数据周报"+UtilTools.formatDate(new Date(),"yyyy-MM-dd"));
sheet2.addMergedRegion(new CellRangeAddress(0,0,0,3));
int tb=1;
createTableNote(wb,sheet2,tb,"4. DETAIL（注册&粉丝 BY DAY）","时间：默认最近1自然周");
//表格头
String title="时间,渠道来源,新增粉丝数,取消关注人数,取关率,净增粉丝数,粉丝总数,新增会员数,新增会员注册率,会员总数,注册率,新增备孕会员,新增孕早会员,新增孕中会员,新增孕晚,新增0-6个月,新增7-12个月会员,新增1-3岁会员,新增3岁以上会员,新增备孕会员,新增孕早占比,新增孕中占比,新增孕晚,新增0-6个月,新增7-12个月占比,新增1-3岁占比,新增3岁以上占比";
createTableHeader(wb,sheet2,tb,title);
createTableContent(wb,sheet2,tb+2);
return true;
}
/**
* 自定义颜色
* 蓝色(大标题)
*/
public static Color blue(){
return new Color(0,112,192);
}
/**
* 自定义颜色
* 淡蓝色
*/
public static Color blue2(){
return new Color(0,176,240);
}
/**
* 自定义颜色
* 绿色
*/
public static Color green(){
return new Color(146,208,80);
}
/**
* 自定义颜色
* 黄色
*/
public static Color orange(){
return new Color(255,192,0);
}
/**
* 自定义颜色
* 白色
*/
public static Color white(){
return new Color(255,255,255);
}
/**
* 自定义颜色
* 黑色
*/
public static Color black(){
return new Color(0,0,0);
}
/**
* 自定义颜色
* 粉红色
*/
public static Color pink(){
return new Color(255,55,174);
}
/**
* 自定义颜色
* 灰色
*/
public static Color gray(){
return new Color(217,217,217);
}
/**
* 自定义单元格样式
* 大标题
* @param args
*/
public static CellStyle reportStyle(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(new XSSFColor(blue()));
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setWrapText(true);
return style;
}
/**
* 自定义单元格样式
* 表头
* @param args
*/
public static CellStyle titleStyle(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);// 字体颜色
font.setFontHeightInPoints((short)10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 自定义单元格样式
* 表头
* @param args
*/
public static CellStyle titleStyle2(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);// 字体颜色
font.setFontHeightInPoints((short)10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellStyle(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
/*
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
*/
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellSummayStyle(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(gray()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellHeaderStyle1(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
/*style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);*/
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(blue()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellHeaderStyle2(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
/*style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);*/
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(blue2()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellHeaderStyle3(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
/*style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);*/
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(green()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellHeaderStyle4(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
/*style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);*/
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(orange()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 自定义单元格样式
* 内容
* @param args
*/
public static CellStyle cellHeaderStyle5(XSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
XSSFFont font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);// 字体颜色
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
font.setFontName("微软雅黑");
// 把字体应用到当前的样式
style.setFont(font);
/*style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);*/
XSSFCellStyle styleTemp=((XSSFCellStyle)style);
styleTemp.setAlignment(CellStyle.ALIGN_CENTER);
styleTemp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//控制颜色
styleTemp.setFillForegroundColor(new XSSFColor(pink()));
styleTemp.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
public static void main(String[] args) {
try {
DownLoadWeeklyReportExcel(null,"D:\\test.xlsx");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
