package com.icss.tools.excel;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.util.Region;
import com.icss.core.db.Record;
import com.icss.core.db.RecordSet;
public class ExportExcel extends MyMethods implements IExcelConstants{
/***
* 导出按地区统计结果的Excel文件
* @param title
* @param dataSet
* @param dataSet2
* @param dataSet3
* @param out
* @param hidAreaId
* @param hidProvinceId
* @param apUUId
* @param exportFirstRow
* @param exportShowCol
*/
public void exportExcel(String title, RecordSet dataSet0, RecordSet dataSet, RecordSet dataSet2, RecordSet dataSet3, OutputStream out,
String hidAreaId, String hidProvinceId, String apUUId, String exportFirstRow, String exportShowCol, String nowTime)
{
int [] colIntNums = this.convertStringToIntArrays(exportShowCol);
int [] rowNums = this.convertStringToIntArrays(exportFirstRow);
String pattern="yyyy-MM-dd";
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为10个字节
sheet.setDefaultColumnWidth((short) 16);
// 生成一个样式
HSSFCellStyle style0 = workbook.createCellStyle();
// 设置表头样式
style0 = ExcelCss.makeStyle(0,workbook) ;
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置表头样式
style = ExcelCss.makeStyle(1,workbook) ;
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2 = ExcelCss.makeStyle(2,workbook) ;
// 生成并设置另一个样式
HSSFCellStyle style3 = workbook.createCellStyle();
style3 = ExcelCss.makeStyle(3,workbook) ;
// 生成并设置另一个样式
HSSFCellStyle style4 = workbook.createCellStyle();
style4 = ExcelCss.makeStyle(4,workbook) ;
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("wll");
//产生表格标题行
HSSFRow row = sheet.createRow(0);
if(rowNums!=null)
{
short hb1=(short)rowNums[0];
short hb2=(short)rowNums[1];
short hb3=(short)rowNums[2];
sheet.addMergedRegion(new Region(0,(short)0,0,(short)(3+ hb1 + hb2 + hb3)));
}else
{
sheet.addMergedRegion(new Region(0,(short)0,0,(short)27));
}
HSSFCell cellHead = row.createCell(0);
cellHead.setCellStyle(style0);
HSSFRichTextString texth = new HSSFRichTextString("按地区机场生产统计"+nowTime);
cellHead.setCellValue(texth);
//列的合并
sheet.addMergedRegion(new Region(1,(short)0,1,(short)2));
sheet.addMergedRegion(new Region(2,(short)0,2,(short)2));
//行的合并
sheet.addMergedRegion(new Region(1,(short)1,2,(short)1));
//行的合并
sheet.addMergedRegion(new Region(1,(short)2,2,(short)2));
//列的合并
short hb1=(short)rowNums[0];
short hb2=(short)rowNums[1];
short hb3=(short)rowNums[2];
//旅客吞吐量
if(rowNums[0]>0 && rowNums[0]<8)
{
sheet.addMergedRegion(new Region(1,(short)4,1,(short)(4 +hb1-1)));
}else if(rowNums[0] == 0)
{
sheet.addMergedRegion(new Region(1,(short)3,1,(short)(4 +hb1-1)));
}else
{
sheet.addMergedRegion(new Region(1,(short)4,1,(short)11));
}
//货邮吞吐量
if( rowNums[0]!=0 && rowNums[1]!=0 )
{
sheet.addMergedRegion(new Region(1,(short)(4 + hb1),1,(short)(4+ hb1 + hb2 -1)));
}else if(rowNums[0] == 0 && rowNums[1] == 0)
{
sheet.addMergedRegion(new Region(1,(short)1,1,(short)1));
}else if(rowNums[0] != 0 && rowNums[1] == 0)
{
sheet.addMergedRegion(new Region(1,(short)1,1,(short)1 ));
}else if(rowNums[0] == 0 && rowNums[1] != 0)
{
sheet.addMergedRegion(new Region(1,(short)4,1,(short)(4 + hb2-1)));
}else
{
sheet.addMergedRegion(new Region(1,(short)12,1,(short)19));
}
//起降次数吞吐量
if(rowNums[0]!=0 && rowNums[1]!=0 && rowNums[2] != 0 )
{
sheet.addMergedRegion(new Region(1,(short)(4+ hb1 + hb2),1,(short)(4+ hb1 + hb2 + hb3 -1)));
}else if(rowNums[0] == 0 && rowNums[1] == 0 && rowNums[2] == 0)
{
sheet.addMergedRegion(new Region(1,(short)(4 - 1),1,(short)(4+hb1+hb2+hb3 - 1 )));
}else if(rowNums[0] != 0 && rowNums[1] == 0 && rowNums[2] != 0)
{
sheet.addMergedRegion(new Region(1,(short)(4 + hb1),1,(short)(4 + hb1 + hb3-1)));
}else if(rowNums[0] != 0 && rowNums[1] == 0 && rowNums[2] == 0)
{
sheet.addMergedRegion(new Region(1,(short)4,1,(short)(4 + hb1-1)));
}else if(rowNums[0] == 0 && rowNums[1] != 0 && rowNums[2] == 0)
{
sheet.addMergedRegion(new Region(1,(short)1,1,(short)1));
}else if(rowNums[0] != 0 && rowNums[1] != 0 && rowNums[2] == 0)
{
sheet.addMergedRegion(new Region(1,(short)(4 + hb1),1,(short)(4+ hb1 + hb2 - 1)));
}else if(rowNums[0] == 0 && rowNums[1] == 0 && rowNums[2] != 0)
{
sheet.addMergedRegion(new Region(1,(short)4,1,(short)(4+ hb3 - 1)));
}else
{
sheet.addMergedRegion(new Region(1,(short)20,1,(short)27));
}
//第一行
HSSFRow row1 = sheet.createRow(1);
List<String> headerList=new ArrayList<String>();
headerList = this.chooseList(this.headersRow1, colIntNums);
for(int e=0; e < headerList.size(); e++)
{
HSSFCell cell = row1.createCell(e);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headerList.get(e));
cell.setCellValue(text);
}
//第二行
HSSFRow row2 = sheet.createRow(2);
List<String> header1tList=new ArrayList<String>();
header1tList = this.chooseList(this.headers, colIntNums);
for (short i = 0; i < header1tList.size(); i++)
{
HSSFCell cell = row2.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(header1tList.get(i));
cell.setCellValue(text);
}
//第三行
HSSFRow row3 = sheet.createRow(3);
List<String> header2tList=new ArrayList<String>();
//取得一个record对象
Record sumRecord=dataSet0.get(0);
header2tList = this.chooseList(this.accountHeaders, colIntNums);
for(int i=0; i < header2tList.size(); i++)
{
if(i<3)
{
HSSFCell cell = row3.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(header2tList.get(i));
cell.setCellValue(text);
}else{
HSSFCell cell = row3.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(sumRecord.getString(header2tList.get(i)));
cell.setCellValue(text);
}
}
int index = 3;
for(int i=0; i<dataSet.size(); i++)
{
if(!isExsist(hidAreaId,dataSet.get(i).getString("AREA_UUID")))
{
continue;
}
//遍历集合数据，产生数据行
index++;
row = sheet.createRow(index);
//取得一个record对象
Record areaRecord=dataSet.get(i);
List<String> areaList=new ArrayList<String>();
areaList = this.chooseList(areaTableContent, colIntNums);
for(int m=0; m<areaList.size(); m++)
{
HSSFCell cellOne = row.createCell(m);
if(m<=3)
{
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else
{
style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
cellOne.setCellStyle(style2);
cellOne.setCellType(HSSFCell.CELL_TYPE_STRING);
cellOne.setCellValue(areaRecord.getString(areaList.get(m)));
}
for(int j=0; j<dataSet2.size(); j++)
{
Record provinceRecord=dataSet2.get(j);
if(!isExsist(hidProvinceId,provinceRecord.getString("PROVINCE_UUID")))
{
continue;
}
if(!provinceRecord.getString("AREA_UUID").equals(areaRecord.getString("AREA_UUID")))
{
continue;
}else
{
//遍历集合数据，产生数据行
index++;
row = sheet.createRow(index);
}
List<String> provinceList=new ArrayList<String>();
provinceList = this.chooseList(provinceTableContent, colIntNums);
for(int n=0; n<provinceList.size(); n++)
{
HSSFCell cellTwo = row.createCell(n);
if(n<=3)
{
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else
{
style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
cellTwo.setCellStyle(style3);
cellTwo.setCellType(HSSFCell.CELL_TYPE_STRING);
cellTwo.setCellValue(provinceRecord.getString(provinceList.get(n)));
}
for(int k=0; k<dataSet3.size(); k++)
{
Record tjfxRecord=dataSet3.get(k);
if(!isExsist(apUUId,tjfxRecord.getString("AP_UUID")))
{
continue;
}
if(!provinceRecord.getString("PROVINCE_UUID").equals(tjfxRecord.getString("PROVINCE_BM")))
{
continue;
}else
{
//遍历集合数据，产生数据行
index++;
row = sheet.createRow(index);
}
List<String> airportList=new ArrayList<String>();
airportList = this.chooseList(airpotTableContent, colIntNums);
for(int p=0; p<airportList.size(); p++)
{
HSSFCell cellthree = row.createCell(p);
if(p<=3)
{
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else
{
style4.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
cellthree.setCellStyle(style4);
cellthree.setCellType(HSSFCell.CELL_TYPE_STRING);
cellthree.setCellValue(tjfxRecord.getString(airportList.get(p)));
}
}
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 导出按省份查询的统计结果
* @param request
* @param response
*/
public void exportByProvince(HttpServletRequest request,HttpServletResponse response){
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM");//设置日期格式
String today = df1.format(new Date());
String startDate=null;
String endDate=null;
String sortCondition=null;
String sortName=null;
if(request.getParameter("startDate")!=null && !request.getParameter("startDate").equals(""))
{
startDate=request.getParameter("startDate");
}else
{
startDate=today;
}
if(request.getParameter("endDate")!=null && !request.getParameter("endDate").equals(""))
{
endDate=request.getParameter("endDate");
}else
{
endDate=today;
}
if(request.getParameter("sortCondition")!=null && !request.getParameter("sortCondition").equals(""))
{
sortCondition=request.getParameter("sortCondition");
}else
{
sortCondition=sortCondition;
}
if(request.getParameter("sortName")!=null && !request.getParameter("sortName").equals(""))
{
sortName=request.getParameter("sortName");
}else
{
sortName=sortName;
}
String nowTime="(" + startDate + "-" + endDate + ")";
String exportFirstRow=request.getParameter("exportFirstRow");
String exportShowCol=request.getParameter("exportShowCol");
String hidAreaId=request.getParameter("exportAreaId");
String hidProvinceId=request.getParameter("exportProvinceId");
String hidApUUId=request.getParameter("apUUId");
request.setAttribute("startDate", startDate);
request.setAttribute("endDate", endDate);
TjfxHandler tjfxHandler = new TjfxHandler();
try
{
RecordSet provinceRecordSet=tjfxHandler.getProvinceTjfxSumInfo(startDate, endDate, sortCondition, sortName);
RecordSet tjfxRecprd=tjfxHandler.getSfTjfxSumInfo(startDate, endDate, sortCondition, sortName);
RecordSet sumRecordSet = tjfxHandler.getSumTjfxInfo(startDate,endDate);
ExportSfTjfxExcel ex = new ExportSfTjfxExcel();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment; filename=" +new String("报表.xls".getBytes("gb2312"), "ISO8859-1" ) );
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");//设置日期格式
String now = df.format(new Date());
//OutputStream out = new FileOutputStream(address);
OutputStream out=response.getOutputStream();
ex.exportExcel("按省份机场生产统计", sumRecordSet, provinceRecordSet, tjfxRecprd, out, hidAreaId, hidProvinceId, hidApUUId, exportFirstRow, exportShowCol, nowTime);
out.flush();
out.close();
}
catch (Exception e)
{
log.error("系统首页：" + e);
throw new BaseException(ErrorCode.IO_EXCEPTION, e);
}
}
/**
* 功能说明：处理管理局列表详细信息显示
*
* @param request
* @param response
*/
public void handleGljInfo(HttpServletRequest request,
HttpServletResponse response)
{
RequestKit requestKit = (RequestKit) request.getAttribute("RequestKit");
// 获取前台界面传入的参数值
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM");//设置日期格式
String today = df1.format(new Date());
String startDate=null;
String endDate=null;
String sortCondition=null;
String sortName=null;
if(request.getParameter("startDate")!=null && !request.getParameter("startDate").equals(""))
{
startDate=request.getParameter("startDate");
}else
{
startDate=today;
}
if(request.getParameter("endDate")!=null && !request.getParameter("endDate").equals(""))
{
endDate=request.getParameter("endDate");
}else
{
endDate=today;
}
if(request.getParameter("sortCondition")!=null && !request.getParameter("sortCondition").equals(""))
{
sortCondition=request.getParameter("sortCondition");
}else
{
sortCondition=sortCondition;
}
if(request.getParameter("sortName")!=null && !request.getParameter("sortName").equals(""))
{
sortName=request.getParameter("sortName");
}else
{
sortName=sortName;
}
request.setAttribute("startDate", startDate);
request.setAttribute("endDate", endDate);
request.setAttribute("sortCondition",sortCondition);
request.setAttribute("sortName",sortName);
TjfxHandler tjfxHandler = new TjfxHandler();
try
{
RecordSet sumRecordSet = tjfxHandler.getSumTjfxInfo(startDate,endDate);
RecordSet gljSumRecordSet=tjfxHandler.getGljTjfxSumInfo(startDate, endDate, sortCondition, sortName);
RecordSet tjfxGljRecprdSet=tjfxHandler.getGljTjfxInfo(startDate, endDate, sortCondition, sortName);
request.setAttribute("sumRecordSet", sumRecordSet);
request.setAttribute("gljSumRecordSet", gljSumRecordSet);
request.setAttribute("tjfxGljRecprdSet", tjfxGljRecprdSet);
//SortedMap orderMap= tjfxHandler.getOrderMap();
//request.setAttribute("orderMap",orderMap);
request.setAttribute("orderValue",Constants.orderValue);
request.setAttribute("orderName",Constants.orderName);
forward("/jsp/tjfx/gljtj.jsp");
}
catch (ServletException e)
{
log.error("系统首页：" + e);
throw new BaseException(ErrorCode.SERVLET_EXCEPTION, e);
}
catch (Exception e)
{
log.error("系统首页：" + e);
throw new BaseException(ErrorCode.IO_EXCEPTION, e);
}
}
/**
* 导出按省份查询的统计结果
* @param request
* @param response
*/
public void exportByGlj(HttpServletRequest request,HttpServletResponse response){
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM");//设置日期格式
String today = df1.format(new Date());
String startDate=null;
String endDate=null;
String sortCondition=null;
String sortName=null;
if(request.getParameter("startDate")!=null && !request.getParameter("startDate").equals(""))
{
startDate=request.getParameter("startDate");
}else
{
startDate=today;
}
if(request.getParameter("endDate")!=null && !request.getParameter("endDate").equals(""))
{
endDate=request.getParameter("endDate");
}else
{
endDate=today;
}
if(request.getParameter("sortCondition")!=null && !request.getParameter("sortCondition").equals(""))
{
sortCondition=request.getParameter("sortCondition");
}else
{
sortCondition=sortCondition;
}
if(request.getParameter("sortName")!=null && !request.getParameter("sortName").equals(""))
{
sortName=request.getParameter("sortName");
}else
{
sortName=sortName;
}
String nowTime="(" + startDate + "-" + endDate + ")";
String exportFirstRow=request.getParameter("exportFirstRow");
String exportShowCol=request.getParameter("exportShowCol");
String hidAreaId=request.getParameter("exportAreaId");
String hidOfficeId=request.getParameter("exportProvinceId");
String hidApUUId=request.getParameter("apUUId");
request.setAttribute("startDate", startDate);
request.setAttribute("endDate", endDate);
TjfxHandler tjfxHandler = new TjfxHandler();
try
{
RecordSet officeRecordSet=tjfxHandler.getGljTjfxSumInfo(startDate, endDate,sortCondition, sortName);
RecordSet tjfxRecprd=tjfxHandler.getGljTjfxInfo(startDate, endDate, sortCondition, sortName);
RecordSet sumRecordSet = tjfxHandler.getSumTjfxInfo(startDate,endDate);
ExportGljTjfxExcel ex = new ExportGljTjfxExcel();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment; filename=" +new String("报表.xls".getBytes("gb2312"), "ISO8859-1" ) );
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");//设置日期格式
String now = df.format(new Date());
//OutputStream out = new FileOutputStream(address);
OutputStream out=response.getOutputStream();
ex.exportGljExcel("按管理局机场生产统计", sumRecordSet, officeRecordSet, tjfxRecprd, out, hidAreaId, hidOfficeId, hidApUUId, exportFirstRow, exportShowCol,nowTime);
out.flush();
out.close();
}
catch (Exception e)
{
log.error("系统首页：" + e);
throw new BaseException(ErrorCode.IO_EXCEPTION, e);
}
}
/**
*
* @Title: getProvinceTree
* @Description: 获取Province树
*/
public void getProvinceTree(HttpServletRequest request, HttpServletResponse response)
{
log.debug("getTree(request,response) enter");
TjfxHandler handler = new TjfxHandler();
TreeView tree;
String strGoPage = "/jsp/tjfx/sfTree.jsp";
try
{
tree = handler.getProvinceTree();
log.debug("getTree(request,response) : tree = "
+ tree.toJsonString());
request.setAttribute("tree", tree);
log.debug("getTree(request,response) leave");
forwardIgnoreException(strGoPage);
}
catch (Exception e)
{
log.error("获得航空器制造厂商树：" + e);
log.debug("getManufactoryTree(request,response) leave");
throw new BaseException(ErrorCode.EXCEPTION, e);
}
}
/**
*
* @Title: getGljTree
* @Description: 获取Glj树
*/
public void getGljTree(HttpServletRequest request, HttpServletResponse response)
{
log.debug("getTree(request,response) enter");
TjfxHandler handler = new TjfxHandler();
TreeView tree;
String strGoPage = "/jsp/tjfx/gljTree.jsp";
try
{
tree = handler.getGljTreeInfo();
log.debug("getTree(request,response) : tree = "
+ tree.toJsonString());
request.setAttribute("tree", tree);
log.debug("getTree(request,response) leave");
forwardIgnoreException(strGoPage);
}
catch (Exception e)
{
log.error("获得航空器制造厂商树：" + e);
log.debug("getManufactoryTree(request,response) leave");
throw new BaseException(ErrorCode.EXCEPTION, e);
}
}