poi生成excel————从数据库中查询导出生成excel

package com.security.util;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;


import com.jfinal.plugin.activerecord.Db;
import com.security.entity.Channel;


public class FileExport {
/**
* jasperReport导出Excel 此方式导出的excel没有网格线(好像直接用流导出方式都没有)
* 在此直接用poi导出，当然jasperReport导出中也应用了poi(需要加入poi的jar包)
*/
public static void exportRecordXls(HttpServletResponse response,
String package_name, String md5, String appName) {
// 创建一工作空间
HSSFWorkbook workbook = new HSSFWorkbook();
getExcelWookBook(workbook, package_name, md5, appName);
response.setContentType("application/xls");
ServletOutputStream sos = null;
try {
String fileName =appName + "_" + MyDate.timestamp();
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes(), "ISO8859-1") + ".xls");
sos = response.getOutputStream();
workbook.write(sos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sos != null) {
try {
sos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

public static void saveExportXls(String package_name, String md5,
String appName) {
// 创建一工作空间
HSSFWorkbook workbook = new HSSFWorkbook();
getExcelWookBook(workbook, package_name, md5, appName);
FileOutputStream fileOutputStream = null;
try {
String x=String.valueOf((int)(Math.random()*50));
String fileName = appName +"_" + MyDate.timestamp() + x +".xls";
String saveFilePath = ReadPro.getValue("saveExcelPath");
File logoFile = new File(saveFilePath);
if (!logoFile.exists()) {
logoFile.mkdirs();
}
String filepath = saveFilePath + File.separator + fileName ;// icon存放地址
fileOutputStream = new FileOutputStream(filepath);// 指定路径与名字和格式
workbook.write(fileOutputStream);// 讲数据写出去
String sql = "insert into excel_info(package_name,excel_path,excel_name,detect_time) values(?,?,?,?)";
Db.update(sql, package_name, filepath, fileName,new Date());
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

private static void getExcelWookBook(HSSFWorkbook workbook,
String package_name, String md5, String appName) {
// 创建一表单(1)----监测渠道
HSSFSheet sheet = workbook.createSheet("渠道监测");
setColumnWidth0(sheet);
ChannelSort CS = new ChannelSort();// 排序
List<Channel> total =Channel.channel.getChannelDetail(package_name);
int totalSize = total.size();
List<Channel> genList = CS.sortChannel(Channel.channel
.getChannelGenDetail(package_name));
List<Channel> pirList = CS.sortChannel(Channel.channel
.getChannelPirDetail(package_name));
List<Channel> totalChannelGroup = CS.sortChannel(Channel.channel.getGroup(
package_name, md5));
getHeader0(sheet, package_name, md5, appName, workbook);
getBodyDataSheet1(genList, pirList ,sheet, workbook, totalSize);
getBodyDataSheet2(genList, pirList ,sheet, workbook);
getBodyDataSheet3(genList, pirList ,totalChannelGroup, sheet, workbook);


// 创建一表单(2)----渠道全部版本信息
List<Channel> allChannel = CS.sortChannel(Channel.channel
.getVerdist(package_name));
int totalCount = Channel.channel.getCountLevel(package_name);// 总计apk
HSSFSheet sheet1 = workbook.createSheet("渠道全部版本信息");
setColumnWidth(sheet1);
getHeader(sheet1, package_name, md5, appName, workbook);// excel上部分
getBodyData2(sheet1, allChannel, totalCount, workbook);


// 创建一表单(3)----渠道盗版版本
List<Channel> channlePir = CS.sortChannel(Channel.channel.getPiratic(
package_name, md5));
int countPiratic = Channel.channel.getCount(package_name, md5, false);// 盗版个数
HSSFSheet sheet2 = workbook.createSheet("渠道盗版版本");
setColumnWidth(sheet2);
getHeader(sheet2, package_name, md5, appName, workbook);
getBodyData2(sheet2, channlePir, countPiratic, workbook);


// 创建一表单(4)----版本的正盗版个数
List<Channel> totalChannel = CS.sortChannel(totalChannelGroup);
// int totalCount = Channel.channel.getCountLevel(package_name);//总计apk
int countGen = Channel.channel.getCount(package_name, md5, true);// 正版个数
// int countPiratic = Channel.channel.getCount(package_name, md5,
// false);//盗版个数
HSSFSheet sheet3 = workbook.createSheet("版本的正盗版个数");
setColumnWidth(sheet3);
getHeader(sheet3, package_name, md5, appName, workbook);
getBodyData4(sheet3, totalChannel, totalCount, countGen, countPiratic,
workbook);


// 创建一表单(5)----正版信息详情
List<Channel> Channel1 = CS.sortChannel(Channel.channel
.getChannelDetail(package_name, md5));
HSSFSheet sheet4 = workbook.createSheet("正版信息详情");
setColumnWidth(sheet4);
getHeader(sheet4, package_name, md5, appName, workbook);
getBodyData5(sheet4, Channel1, workbook);


// 创建一表单(6)----盗版信息详情
List<Channel> PriChannel = CS.sortChannel(Channel.channel
.getPirAnalysis(package_name, md5));
HSSFSheet sheet5 = workbook.createSheet("盗版信息详情");
setColumnWidth(sheet5);
getHeader(sheet5, package_name, md5, appName, workbook);
getBodyData6(sheet5, PriChannel, workbook);
}

@SuppressWarnings("deprecation")
private static void getHeader0(HSSFSheet sheet, String package_name,
String md5, String appName, HSSFWorkbook workbook) {
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));// 指定合并区域
sheet.addMergedRegion(new Region(0, (short) 3, 1, (short) 4));// 指定合并区域
sheet.addMergedRegion(new Region(0, (short) 5, 1, (short) 7));// 指定合并区域
// 创建一个样式,黑色加粗
HSSFCellStyle style = getHeadStyle(workbook, true);
// 创建一个样式，黑色
HSSFCellStyle style1 = getHeadStyle(workbook, false);
// 创建一个样式，红色加粗
HSSFCellStyle style2 = getHeadStyle(workbook, true);
HSSFFont font2 = workbook.createFont();
font2.setColor(HSSFColor.RED.index);// 字体颜色
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
style2.setFont(font2);
// 创建表题行
HSSFRow headerRow = sheet.createRow(0);
for (int i = 0; i < 8; i++) {
HSSFCell headerCell = headerRow.createCell(i);
switch (i) {
case 0:
headerCell.setCellStyle(style1);
break;
case 1:
headerCell.setCellValue("应用名称");
headerCell.setCellStyle(style);
break;
case 2:
headerCell.setCellValue(String.valueOf(appName));
headerCell.setCellStyle(style2);
break;
case 3:
headerCell.setCellValue("证书");
headerCell.setCellStyle(style);
break;
case 4:
headerCell.setCellStyle(style1);
break;
case 5:
headerCell.setCellStyle(style2);
headerCell.setCellValue(md5);
break;
case 6:
headerCell.setCellStyle(style1);
break;
case 7:
headerCell.setCellStyle(style1);
break;
}
}
// 创建表题行
HSSFRow headerRow1 = sheet.createRow(1);
for (int i = 0; i < 8; i++) {
HSSFCell headerCell = headerRow1.createCell(i);
switch (i) {
case 0:
break;
case 1:
headerCell.setCellValue("包名");
headerCell.setCellStyle(style);
break;
case 2:
headerCell.setCellValue(package_name);
headerCell.setCellStyle(style2);
break;
case 3:
headerCell.setCellStyle(style1);
break;
case 4:
headerCell.setCellStyle(style1);
break;
case 5:
headerCell.setCellStyle(style1);
break;
case 6:
headerCell.setCellStyle(style1);
break;
case 7:
headerCell.setCellStyle(style1);
break;
}
}
// 创建表题行
HSSFRow headerRow2 = sheet.createRow(2);
// sheet.addMergedRegion(new Region(2, (short) 4, 2, (short) 5));// 指定合并区域
sheet.addMergedRegion(new Region(2, (short) 5, 2, (short) 7));// 指定合并区域
for (int i = 0; i < 8; i++) {
HSSFCell headerCell = headerRow2.createCell(i);
headerCell.setCellStyle(style1);
switch (i) {
case 0:
headerCell.setCellValue("渠道监测");
break;
case 1:
headerCell.setCellValue("渠道全部版本信息");
break;
case 2:
headerCell.setCellValue("渠道盗版版本");
break;
case 3:
headerCell.setCellValue("盗本的正盗版个数");
break;
case 4:
headerCell.setCellValue("正版信息详情");
break;
case 5:
headerCell.setCellValue("盗版信息详情");
break;
case 6:
headerCell.setCellValue("");
break;
case 7:
headerCell.setCellValue("");
break;
}
}
}


@SuppressWarnings("deprecation")
private static void getHeader(HSSFSheet sheet, String package_name,
String md5, String appName, HSSFWorkbook workbook) {
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));// 指定合并区域
sheet.addMergedRegion(new Region(0, (short) 3, 1, (short) 3));// 指定合并区域
sheet.addMergedRegion(new Region(0, (short) 4, 1, (short) 5));// 指定合并区域
// 创建一个样式,黑色加粗
HSSFCellStyle style = getHeadStyle(workbook, true);
// 创建一个样式，黑色
HSSFCellStyle style1 = getHeadStyle(workbook, false);
// 创建一个样式，红色加粗
HSSFCellStyle style2 = getHeadStyle(workbook, true);
HSSFFont font2 = workbook.createFont();
font2.setColor(HSSFColor.RED.index);// 字体颜色
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
style2.setFont(font2);
// 创建表题行
HSSFRow headerRow = sheet.createRow(0);
for (int i = 0; i < 6; i++) {
HSSFCell headerCell = headerRow.createCell(i);
switch (i) {
case 0:
break;
case 1:
headerCell.setCellValue("应用名称");
headerCell.setCellStyle(style);
break;
case 2:
headerCell.setCellValue(String.valueOf(appName));
headerCell.setCellStyle(style2);
break;
case 3:
headerCell.setCellValue("证书");
headerCell.setCellStyle(style);
break;
case 4:
headerCell.setCellStyle(style2);
headerCell.setCellValue(md5);
break;
case 5:
headerCell.setCellStyle(style1);
break;
}
}
// 创建表题行
HSSFRow headerRow1 = sheet.createRow(1);
for (int i = 0; i < 6; i++) {
HSSFCell headerCell = headerRow1.createCell(i);
switch (i) {
case 0:
break;
case 1:
headerCell.setCellValue("包名");
headerCell.setCellStyle(style);
break;
case 2:
headerCell.setCellValue(package_name);
headerCell.setCellStyle(style2);
break;
case 3:
headerCell.setCellStyle(style1);
break;
case 4:
break;
case 5:
headerCell.setCellStyle(style1);
break;
}
}
// 创建表题行
HSSFRow headerRow2 = sheet.createRow(2);
for (int i = 0; i < 6; i++) {
HSSFCell headerCell = headerRow2.createCell(i);
headerCell.setCellStyle(style1);
switch (i) {
case 0:
headerCell.setCellValue("渠道监测");
break;
case 1:
headerCell.setCellValue("渠道全部版本信息");
break;
case 2:
headerCell.setCellValue("渠道盗版版本");
break;
case 3:
headerCell.setCellValue("盗本的正盗版个数");
break;
case 4:
headerCell.setCellValue("正版信息详情");
break;
case 5:
headerCell.setCellValue("盗版信息详情");
break;
}
}
}


@SuppressWarnings("deprecation")
private static void getBodyDataSheet1(List<Channel> genList, List<Channel> pirList,HSSFSheet sheet,
HSSFWorkbook workbook ,int total){
sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 7));// 指定合并区域
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
// 渠道发现此应用
HSSFRow headerRow4 = sheet.createRow(3);
HSSFCell headerCell3 = headerRow4.createCell(0);
String time = getTimeStamp(genList);
headerCell3.setCellValue("正版信息汇总：" + time + "日，共检测到 "
+ total + " 个渠道,其中  " + genList.size()
+ " 渠道监测到正版，渠道信息如下：");
headerCell3.setCellStyle(style);
HSSFCell headerCell5 = headerRow4.createCell(7);
headerCell5.setCellStyle(style);
getBodyData1(genList, sheet, workbook);
}

@SuppressWarnings("deprecation")
private static void getBodyDataSheet2(List<Channel> genList, List<Channel> pirList,HSSFSheet sheet,
HSSFWorkbook workbook){
int lineSize = genList.size();
sheet.addMergedRegion(new Region(lineSize + 5, (short) 0, lineSize + 5, (short) 7));// 指定合并区域
HSSFCellStyle style = getHeadStyle(workbook, false);// 创建一个样式，黑色
// 渠道发现此应用
HSSFRow headerRow4 = sheet.createRow(lineSize + 5);
String time = getTimeStamp(genList);
HSSFCell headerCell3 = headerRow4.createCell(0);
headerCell3.setCellValue("盗版信息汇总：" + time + "日，共检测到 "
+ pirList.size() + " 个渠道,其中  " + pirList.size()
+ " 渠道监测到盗版，渠道信息如下：");
headerCell3.setCellStyle(style);
HSSFCell headerCell5 = headerRow4.createCell(7);
headerCell5.setCellStyle(style);
getBodySheetData2(genList,pirList, sheet, workbook);
}

/**
* 得到第4个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyDataSheet3(List<Channel> genList, List<Channel> pirList,List<Channel> groupList,HSSFSheet sheet,
HSSFWorkbook workbook) {
int size = genList.size() + pirList.size() + 7;
sheet.addMergedRegion(new Region(size, (short) 0, size, (short) 1));// 指定合并区域
sheet.addMergedRegion(new Region(size, (short) 2, size, (short) 3));
sheet.addMergedRegion(new Region(size, (short) 4, size, (short) 5));
sheet.addMergedRegion(new Region(size, (short) 6, size, (short) 7));
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
HSSFRow bodyRowLit1 = sheet.createRow(size);
HSSFCell bodyCellList0 = bodyRowLit1.createCell(0);
bodyCellList0.setCellValue("版本");
bodyCellList0.setCellStyle(style1);
HSSFCell bodyCellList1 = bodyRowLit1.createCell(1);
bodyCellList1.setCellStyle(style1);
HSSFCell bodyCellList2 = bodyRowLit1.createCell(2);
bodyCellList2.setCellValue("版本个数");
bodyCellList2.setCellStyle(style1);
HSSFCell bodyCellList3 = bodyRowLit1.createCell(3);
bodyCellList3.setCellStyle(style1);
HSSFCell bodyCellList4 = bodyRowLit1.createCell(4);
bodyCellList4.setCellValue("正版个数");
bodyCellList4.setCellStyle(style1);
HSSFCell bodyCellList5 = bodyRowLit1.createCell(5);
bodyCellList5.setCellStyle(style1);
HSSFCell bodyCellList6 = bodyRowLit1.createCell(6);
bodyCellList6.setCellValue("盗版个数");
bodyCellList6.setCellStyle(style1);
HSSFCell bodyCellList7 = bodyRowLit1.createCell(7);
bodyCellList7.setCellStyle(style1);
for (int i = 0; i < groupList.size(); i++) {
sheet.addMergedRegion(new Region(size + 1 + i, (short) 0, size + 1 + i,(short) 1));// 指定合并区域
sheet.addMergedRegion(new Region(size + 1 + i, (short) 2, size + 1 + i,(short) 3));
sheet.addMergedRegion(new Region(size + 1 + i, (short) 4, size + 1 + i,(short) 5));
sheet.addMergedRegion(new Region(size + 1 + i, (short) 6, size + 1 + i,(short) 7));
HSSFRow bodyListRow = sheet.createRow(size + 1 + i);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) groupList.get(i).get("ver"));
empNvcAppNameCell0.setCellStyle(style);
//
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellStyle(style);

HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2
.setCellValue(groupList.get(i).get("total").toString());
empNvcAppNameCell2.setCellStyle(style);

HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellStyle(style);
//
HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellValue(groupList.get(i).get("zhengban")
.toString());
empNvcAppNameCell4.setCellStyle(style);

HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellStyle(style);
//
HSSFCell empNvcAppNameCell6 = bodyListRow.createCell(6);
empNvcAppNameCell6.setCellValue(groupList.get(i).get("daoban")
.toString());
empNvcAppNameCell6.setCellStyle(style);

HSSFCell empNvcAppNameCell7 = bodyListRow.createCell(7);
empNvcAppNameCell7.setCellStyle(style);
}
}



/**
* 得到第一个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyData1(List<Channel> list, HSSFSheet sheet,
HSSFWorkbook workbook) {
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
// 展示渠道应用
HSSFRow bodyRow = sheet.createRow(4);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("渠道名称");
bodyCell0.setCellStyle(style1);
HSSFCell bodyCell1 = bodyRow.createCell(1);
bodyCell1.setCellValue("是否检测到");
bodyCell1.setCellStyle(style1);
HSSFCell bodyCell2 = bodyRow.createCell(2);
bodyCell2.setCellValue("应用名称");
bodyCell2.setCellStyle(style1);
HSSFCell bodyCell3 = bodyRow.createCell(3);
bodyCell3.setCellValue("包名");
bodyCell3.setCellStyle(style1);
HSSFCell bodyCell4 = bodyRow.createCell(4);
bodyCell4.setCellValue("版本个数");
bodyCell4.setCellStyle(style1);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellValue("版本号");
bodyCell5.setCellStyle(style1);
HSSFCell bodyCell6 = bodyRow.createCell(6);
bodyCell6.setCellValue("下载次数");
bodyCell6.setCellStyle(style1);
HSSFCell bodyCell7 = bodyRow.createCell(7);
bodyCell7.setCellValue("下载地址");
bodyCell7.setCellStyle(style1);
for (int i = 0; i < list.size(); i++) {
// sheet.addMergedRegion(new Region(5 + i, (short) 4, 5 + i, (short) 5));// 指定合并区域
// sheet.addMergedRegion(new Region(5 + i, (short) 6, 5 + i, (short) 7));
// sheet.addMergedRegion(new Region(5 + i, (short) 4, 5 + i, (short) 5));
HSSFRow bodyListRow = sheet.createRow(i + 5);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) list.get(i).get("name"));
empNvcAppNameCell0.setCellStyle(style);
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellStyle(style);
if (null == list.get(i).get("flag")) {
empNvcAppNameCell1.setCellValue("是");
} else if ("0".equals(list.get(i).get("flag"))) {
empNvcAppNameCell1.setCellValue("是");
} else {
empNvcAppNameCell1.setCellValue("否");
}
HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2.setCellValue(String.valueOf(list.get(i).get("app_name")));
empNvcAppNameCell2.setCellStyle(style);

HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellValue(String.valueOf(list.get(i).get("package_name")));
empNvcAppNameCell3.setCellStyle(style);

HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellStyle(style);
if(null!=list.get(i).get("versionNum")){
empNvcAppNameCell4.setCellValue(String.valueOf(list.get(i).get("versionNum")));
}else{
empNvcAppNameCell4.setCellValue("");
}

HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellValue(String.valueOf(list.get(i).get("app_version")));
empNvcAppNameCell5.setCellStyle(style);

HSSFCell empNvcAppNameCell6 = bodyListRow.createCell(6);
empNvcAppNameCell6.setCellValue(String.valueOf(list.get(i).get("down_num")));
empNvcAppNameCell6.setCellStyle(style);

HSSFCell empNvcAppNameCell7 = bodyListRow.createCell(7);
empNvcAppNameCell7.setCellValue(String.valueOf(list.get(i).get("down_url")));
empNvcAppNameCell7.setCellStyle(style);
}
/*
* int rowNum = 0;// 行号 int number = 0;// 6列数据 HSSFRow row =
* sheet.createRow(rowNum + 4); for (int i = 0; i < list.size(); i++) {
* // 每一行只显示6列数据 if (number > 5) { rowNum += 1; number = 0; row =
* sheet.createRow(rowNum + 4); } HSSFCell empNvcAppNameCell =
* row.createCell(number); empNvcAppNameCell.setCellValue((String)
* list.get(i).get("name")); number++; }
*/
}



/**
* 得到第一个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodySheetData2(List<Channel> genList, List<Channel> pirList,HSSFSheet sheet,
HSSFWorkbook workbook) {
int genListSize = genList.size();
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
// 展示渠道应用
HSSFRow bodyRow = sheet.createRow(genListSize + 6);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("渠道名称");
bodyCell0.setCellStyle(style1);
HSSFCell bodyCell1 = bodyRow.createCell(1);
bodyCell1.setCellValue("是否检测到");
bodyCell1.setCellStyle(style1);
HSSFCell bodyCell2 = bodyRow.createCell(2);
bodyCell2.setCellValue("应用名称");
bodyCell2.setCellStyle(style1);
HSSFCell bodyCell3 = bodyRow.createCell(3);
bodyCell3.setCellValue("包名");
bodyCell3.setCellStyle(style1);
HSSFCell bodyCell4 = bodyRow.createCell(4);
bodyCell4.setCellValue("版本个数");
bodyCell4.setCellStyle(style1);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellValue("版本号");
bodyCell5.setCellStyle(style1);
HSSFCell bodyCell6 = bodyRow.createCell(6);
bodyCell6.setCellValue("下载次数");
bodyCell6.setCellStyle(style1);
HSSFCell bodyCell7 = bodyRow.createCell(7);
bodyCell7.setCellValue("下载地址");
bodyCell7.setCellStyle(style1);
for (int i = 0 ; i < pirList.size(); i++) {
// sheet.addMergedRegion(new Region(i + 7 + genListSize, (short) 4, i + 7 + genListSize, (short) 5));// 指定合并区域
// sheet.addMergedRegion(new Region(i + 7 + genListSize, (short) 6, i + 7 + genListSize, (short) 7));
// sheet.addMergedRegion(new Region(5 + i, (short) 4, 5 + i, (short) 5));
HSSFRow bodyListRow = sheet.createRow(i + 7 + genListSize);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue(String.valueOf(pirList.get(i).get("name")));
empNvcAppNameCell0.setCellStyle(style);
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellStyle(style);
if (null == pirList.get(i).get("flag")) {
empNvcAppNameCell1.setCellValue("是");
} else if ("0".equals(pirList.get(i).get("flag"))) {
empNvcAppNameCell1.setCellValue("是");
} else {
empNvcAppNameCell1.setCellValue("否");
}

HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2.setCellStyle(style);
if (null != pirList.get(i).get("app_name")) {
empNvcAppNameCell2.setCellValue(String.valueOf(pirList.get(i).get("app_name")));
} else {
empNvcAppNameCell2.setCellValue("");
}

HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellValue(String.valueOf(pirList.get(i).get("package_name")));
empNvcAppNameCell3.setCellStyle(style);

HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellStyle(style);
if(null!=pirList.get(i).get("versionNum")){
empNvcAppNameCell4.setCellValue(String.valueOf(pirList.get(i).get("versionNum")));
}else{
empNvcAppNameCell4.setCellValue("");
}

HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellStyle(style);
if (null != pirList.get(i).get("app_version")) {
empNvcAppNameCell5.setCellValue(String.valueOf(pirList.get(i).get("app_version")));
} else {
empNvcAppNameCell2.setCellValue("");
}

HSSFCell empNvcAppNameCell6 = bodyListRow.createCell(6);
empNvcAppNameCell6.setCellStyle(style);
if (null != pirList.get(i).get("down_num")) {
empNvcAppNameCell6.setCellValue(String.valueOf(pirList.get(i).get("down_num")));
} else {
empNvcAppNameCell2.setCellValue("");
}

HSSFCell empNvcAppNameCell7 = bodyListRow.createCell(7);
empNvcAppNameCell7.setCellStyle(style);
if (null != pirList.get(i).get("down_url")) {
empNvcAppNameCell7.setCellValue(String.valueOf(pirList.get(i).get("down_url")));
} else {
empNvcAppNameCell7.setCellValue("");
}
}
}
/**
* 得到第二个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyData2(HSSFSheet sheet, List<Channel> list,
int totalCount, HSSFWorkbook workbook) {
sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 5));// 指定合并区域
sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) 1));// 指定合并区域
sheet.addMergedRegion(new Region(4, (short) 2, 4, (short) 3));
sheet.addMergedRegion(new Region(4, (short) 4, 4, (short) 5));
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);
// 渠道发现此应用
HSSFRow headerRow = sheet.createRow(3);
HSSFCell headerCell = headerRow.createCell(0);
headerCell.setCellStyle(style);
headerCell.setCellValue("版本个数 ：" + totalCount);
HSSFCell headerCell5 = headerRow.createCell(5);
headerCell5.setCellStyle(style);


HSSFRow bodyRow = sheet.createRow(4);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("渠道");
bodyCell0.setCellStyle(style1);
HSSFCell bodyCell2 = bodyRow.createCell(2);
bodyCell2.setCellValue("版本个数");
bodyCell2.setCellStyle(style1);
HSSFCell bodyCell4 = bodyRow.createCell(4);
bodyCell4.setCellValue("版本");
bodyCell4.setCellStyle(style1);
HSSFCell bodyCell1 = bodyRow.createCell(1);
bodyCell1.setCellStyle(style);
HSSFCell bodyCell3 = bodyRow.createCell(3);
bodyCell3.setCellStyle(style);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellStyle(style);


for (int i = 0; i < list.size(); i++) {
sheet.addMergedRegion(new Region(5 + i, (short) 0, 5 + i, (short) 1));// 指定合并区域
sheet.addMergedRegion(new Region(5 + i, (short) 2, 5 + i, (short) 3));
sheet.addMergedRegion(new Region(5 + i, (short) 4, 5 + i, (short) 5));
HSSFRow bodyListRow = sheet.createRow(i + 5);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) list.get(i).get("name"));
empNvcAppNameCell0.setCellStyle(style);
//
HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2.setCellValue(list.get(i).get("num").toString());
empNvcAppNameCell2.setCellStyle(style);
//
HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellValue((String) list.get(i).get("ver"));
empNvcAppNameCell4.setCellStyle(style);
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell1.setCellStyle(style);
empNvcAppNameCell3.setCellStyle(style);
empNvcAppNameCell5.setCellStyle(style);
}
}


/**
* 得到第4个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyData4(HSSFSheet sheet3, List<Channel> list,
int totalCount, int countGen, int countPiratic,
HSSFWorkbook workbook) {
sheet3.addMergedRegion(new Region(3, (short) 0, 3, (short) 5));// 指定合并区域
sheet3.addMergedRegion(new Region(4, (short) 0, 4, (short) 1));// 指定合并区域
sheet3.addMergedRegion(new Region(4, (short) 4, 4, (short) 5));
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
HSSFRow bodyRow = sheet3.createRow(3);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("总计apk ：" + totalCount + " , " + "正版 ："
+ countGen + " , " + "盗版 ：" + countPiratic);
bodyCell0.setCellStyle(style);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellStyle(style);


HSSFRow bodyRowLit1 = sheet3.createRow(4);
HSSFCell bodyCellList0 = bodyRowLit1.createCell(0);
bodyCellList0.setCellValue("版本");
bodyCellList0.setCellStyle(style1);
HSSFCell bodyCellList2 = bodyRowLit1.createCell(2);
bodyCellList2.setCellValue("版本个数");
bodyCellList2.setCellStyle(style1);
HSSFCell bodyCellList3 = bodyRowLit1.createCell(3);
bodyCellList3.setCellValue("正版个数");
bodyCellList3.setCellStyle(style1);
HSSFCell bodyCellList4 = bodyRowLit1.createCell(4);
bodyCellList4.setCellValue("盗版个数");
bodyCellList4.setCellStyle(style1);
HSSFCell bodyCellList1 = bodyRowLit1.createCell(1);
bodyCellList1.setCellStyle(style1);
HSSFCell bodyCellList5 = bodyRowLit1.createCell(5);
bodyCellList5.setCellStyle(style1);
for (int i = 0; i < list.size(); i++) {
sheet3.addMergedRegion(new Region(5 + i, (short) 0, 5 + i,
(short) 1));// 指定合并区域
sheet3.addMergedRegion(new Region(5 + i, (short) 4, 5 + i,
(short) 5));
HSSFRow bodyListRow = sheet3.createRow(i + 5);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) list.get(i).get("ver"));
empNvcAppNameCell0.setCellStyle(style);
//
HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2
.setCellValue(list.get(i).get("total").toString());
empNvcAppNameCell2.setCellStyle(style);
//
HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellValue(list.get(i).get("zhengban")
.toString());
empNvcAppNameCell3.setCellStyle(style);
//
HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellValue(list.get(i).get("daoban")
.toString());
empNvcAppNameCell4.setCellStyle(style);
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellStyle(style);
HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellStyle(style);
}
}


/**
* 得到第5个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyData5(HSSFSheet sheet, List<Channel> list,
HSSFWorkbook workbook) {
sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 5));// 指定合并区域
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
HSSFRow bodyRow = sheet.createRow(3);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("总计apk ：" + list.size());
bodyCell0.setCellStyle(style);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellStyle(style);


HSSFRow bodyRowLit1 = sheet.createRow(4);
HSSFCell bodyCellList0 = bodyRowLit1.createCell(0);
bodyCellList0.setCellValue("应用名称");
bodyCellList0.setCellStyle(style1);
HSSFCell bodyCellList1 = bodyRowLit1.createCell(1);
bodyCellList1.setCellValue("包名");
bodyCellList1.setCellStyle(style1);
HSSFCell bodyCellList2 = bodyRowLit1.createCell(2);
bodyCellList2.setCellValue("版本");
bodyCellList2.setCellStyle(style1);
HSSFCell bodyCellList3 = bodyRowLit1.createCell(3);
bodyCellList3.setCellValue("渠道");
bodyCellList3.setCellStyle(style1);
HSSFCell bodyCellList4 = bodyRowLit1.createCell(4);
bodyCellList4.setCellValue("下载次数");
bodyCellList4.setCellStyle(style1);
HSSFCell bodyCellList5 = bodyRowLit1.createCell(5);
bodyCellList5.setCellValue("下载地址");
bodyCellList5.setCellStyle(style1);
for (int i = 0; i < list.size(); i++) {
HSSFRow bodyListRow = sheet.createRow(i + 5);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) list.get(i)
.get("app_name"));
empNvcAppNameCell0.setCellStyle(style);
//
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellValue((String) list.get(i).get(
"real_package_name"));
empNvcAppNameCell1.setCellStyle(style);
//
HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2.setCellValue((String) list.get(i).get(
"app_version"));
empNvcAppNameCell2.setCellStyle(style);
//
HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellValue((String) list.get(i).get("name"));
empNvcAppNameCell3.setCellStyle(style);
//
HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellValue(list.get(i).get("down_num")
.toString());
empNvcAppNameCell4.setCellStyle(style);
//
HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellValue((String) list.get(i)
.get("down_url"));
empNvcAppNameCell5.setCellStyle(style);
}
}


/**
* 得到第6个excel的sheet的body展示数据
* 
* @param list
* @param sheet
*/
@SuppressWarnings("deprecation")
private static void getBodyData6(HSSFSheet sheet, List<Channel> list,
HSSFWorkbook workbook) {
sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 5));// 指定合并区域
// 创建一个样式，黑色
HSSFCellStyle style = getHeadStyle(workbook, false);
HSSFCellStyle style1 = getHeadStyle(workbook, true);// 创建一个样式，黑色加粗
HSSFRow bodyRow = sheet.createRow(3);
HSSFCell bodyCell0 = bodyRow.createCell(0);
bodyCell0.setCellValue("总计apk ：" + list.size());
bodyCell0.setCellStyle(style);
HSSFCell bodyCell5 = bodyRow.createCell(5);
bodyCell5.setCellStyle(style);


HSSFRow bodyRowLit1 = sheet.createRow(4);
HSSFCell bodyCellList0 = bodyRowLit1.createCell(0);
bodyCellList0.setCellValue("应用名称");
bodyCellList0.setCellStyle(style1);
HSSFCell bodyCellList1 = bodyRowLit1.createCell(1);
bodyCellList1.setCellValue("包名");
bodyCellList1.setCellStyle(style1);
HSSFCell bodyCellList2 = bodyRowLit1.createCell(2);
bodyCellList2.setCellValue("版本");
bodyCellList2.setCellStyle(style1);
HSSFCell bodyCellList3 = bodyRowLit1.createCell(3);
bodyCellList3.setCellValue("渠道");
bodyCellList3.setCellStyle(style1);
HSSFCell bodyCellList4 = bodyRowLit1.createCell(4);
bodyCellList4.setCellValue("下载次数");
bodyCellList4.setCellStyle(style1);
HSSFCell bodyCellList5 = bodyRowLit1.createCell(5);
bodyCellList5.setCellValue("下载地址");
bodyCellList5.setCellStyle(style1);
for (int i = 0; i < list.size(); i++) {
HSSFRow bodyListRow = sheet.createRow(i + 5);
//
HSSFCell empNvcAppNameCell0 = bodyListRow.createCell(0);
empNvcAppNameCell0.setCellValue((String) list.get(i)
.get("app_name"));
empNvcAppNameCell0.setCellStyle(style);
//
HSSFCell empNvcAppNameCell1 = bodyListRow.createCell(1);
empNvcAppNameCell1.setCellValue((String) list.get(i).get(
"real_package_name"));
empNvcAppNameCell1.setCellStyle(style);
//
HSSFCell empNvcAppNameCell2 = bodyListRow.createCell(2);
empNvcAppNameCell2.setCellValue((String) list.get(i).get(
"app_version"));
empNvcAppNameCell2.setCellStyle(style);
//
HSSFCell empNvcAppNameCell3 = bodyListRow.createCell(3);
empNvcAppNameCell3.setCellValue((String) list.get(i).get("name"));
empNvcAppNameCell3.setCellStyle(style);
//
HSSFCell empNvcAppNameCell4 = bodyListRow.createCell(4);
empNvcAppNameCell4.setCellValue(list.get(i).get("down_num")
.toString());
empNvcAppNameCell4.setCellStyle(style);
//
HSSFCell empNvcAppNameCell5 = bodyListRow.createCell(5);
empNvcAppNameCell5.setCellValue((String) list.get(i)
.get("down_url"));
empNvcAppNameCell5.setCellStyle(style);
}
}


/**
* 创建导出excle样式边框
* 
* @param workbook
* @param flag
*            字体加粗标识
* @return
*/
private static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook,
boolean flag) {
// 创建一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置边框样式
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
HSSFFont font = workbook.createFont();// 生成一个字体
font.setColor(HSSFColor.BLACK.index);// 字体颜色
if (flag) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
}
style.setFont(font);// 把字体应用到当前的样式
style.setWrapText(true);// 设置text自动换行
return style;
}
/**
* 获得导出时间
* @param genList
* @return
*/
private static String getTimeStamp(List<Channel> genList) {
/*if (null != genList && genList.size() > 0) {
Date timeStamp = genList.get(0).get("batch_timestamp");// 2015-09-06
if (null != timeStamp) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HHmmss");
time =format.format(timeStamp);
time = time.substring(5, 10).replaceAll("-", "月");
}
}*/
Date timestamp = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HHmmss");
String time =format.format(timestamp);
time = time.substring(5, 10).replaceAll("-", "月");
return time;
} 
/**
* 设置excle列宽度
* 
* @param sheet
*/
private static void setColumnWidth0(HSSFSheet sheet) {
for (int i = 0; i < 8; i++) {
sheet.setColumnWidth(i, 4500);
}
}
/**
* 设置excle列宽度
* 
* @param sheet
*/
private static void setColumnWidth(HSSFSheet sheet) {
for (int i = 0; i < 6; i++) {
sheet.setColumnWidth(i, 6000);
}
}
}