/// <summary>
/// 创建 月度报表Excel
/// </summary>
private bool CreateYueExcl(string year, string yue, HttpContext context, CountYueGas Tj)
{
string template = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/template/导出月分班统计模板站点.xls");//模板文件
string dishutemplate = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/template/底数模板.xls");//模板文件
string newFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/月报表.xls");//报表文件文件
string pdfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/月报表.pdf");//报表文件文件
string swfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/月报表.swf");//报表文件文件
string dishuFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/底数.xls");//底数文件
string viewPaht = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/html");//HTML报表文件文件目录
if (System.IO.Directory.Exists(viewPaht) == false)
{
FileManager.CreateFolder(viewPaht);
}
bool ok = FileManager.CopyFile(template, newFile);//复制月报表文件
FileManager.CopyFile(dishutemplate, dishuFile);//复制底数文件
if (ok == false)
{
return false;
}
MyExcel ME = new MyExcel();
MyExcel DS = new MyExcel();
ME.Open(newFile);
DS.Open(dishuFile);
#region 第一步：生成每个站点统计数据
for (int i = 0; i < Tj.zds; i++)
{
string sheetName = "";
int row = 0;//储存底数报表对应行号
if (Tj.MyDayGas[i].Zd == 2)
{
sheetName = "南高";
row = 4;
}
if (Tj.MyDayGas[i].Zd == 3)
{
row = 6;
sheetName = "西路";
}
if (Tj.MyDayGas[i].Zd == 4)
{
row = 5;
sheetName = "麻柳沱";
}
//写入站点数据
for (int j = 0; j < Tj.MyDayGas[i].DayGas.Length; j++)
{
ME.ActivateSheet(sheetName);
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].总生产气量.ToString(), 4 + j, 2);//总生产气量
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].IC卡刷卡.ToString(), 4 + j, 4);//IC卡刷卡
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].现金气量.ToString(), 4 + j, 5);//现金气量
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].手工记账.ToString(), 4 + j, 6);//手工记账
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].IC卡记账.ToString(), 4 + j, 7);//IC卡记账
if (Tj.MyDayGas[i].Zd == 3)
{
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].内部用气.ToString(), 4 + j, 8);//内部用气
}
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].维修损耗.ToString(), 4 + j, 9);//维修损耗
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].耗电量.ToString(), 4 + j, 10);//耗电量
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].购进气量.ToString(), 4 + j, 11);//购进气量
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].礶差.ToString(), 4 + j, 12);//礶差
//写入 月度底数
if (j == 0)//第一天数据 写入起数
{
ME.ActivateSheet("流量计底数");
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].进站起.ToString(), row, 2);
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].再生起.ToString(), row, 4);
DS.ActivateSheet("流量计底数");
DS.WriteStr(Tj.MyDayGas[i].DayGas[j].进站起.ToString(), row, 2);
DS.WriteStr(Tj.MyDayGas[i].DayGas[j].再生起.ToString(), row, 4);
}
if (j == Tj.MyDayGas[i].DayGas.Length - 1)//最后一天数据 写入止数
{
ME.ActivateSheet("流量计底数");
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].进站止.ToString(), row, 3);
ME.WriteStr(Tj.MyDayGas[i].DayGas[j].再生止.ToString(), row, 5);
DS.ActivateSheet("流量计底数");
DS.WriteStr(Tj.MyDayGas[i].DayGas[j].进站止.ToString(), row, 3);
DS.WriteStr(Tj.MyDayGas[i].DayGas[j].再生止.ToString(), row, 5);
}
}
// 写入修正值
}
#endregion
#region 第二部：生成日期，天数，制表时间
ME.ActivateSheet("分站售气统计");
ME.WriteStr(Tj.Days.ToString(), 4, 8);//天数
ME.WriteStr(Tj.Str.ToShortDateString(), 2, 7);//起始日期
ME.WriteStr(Tj.End.ToShortDateString(), 2, 12);//结束日期
DS.WriteStr(Tj.Str.ToShortDateString(), 1, 2);//起始日期
DS.WriteStr(Tj.End.ToShortDateString(), 1, 4);//结束日期
ME.ActivateSheet("进销总表");
ME.WriteStr("制表日期：" + DateTime.Now.ToString("yyyy-MM-dd"), 2, 12);//制表时间
#endregion
#region 第三步：报表 数据，停业备注
string temp = "";
double tyTime = 0;
foreach (ZdYueGasMod yd in Tj.MyYueGas)
{
if (yd.Zd == 2)
{
ME.ActivateSheet("分站售气统计");
ME.WriteStr(yd.YueGas.停运时间.ToString(), 5, 11);
ME.ActivateSheet("进销总表");
ME.WriteStr(yd.YueGas.月度计划.ToString(), 6, 2);
ME.WriteStr(yd.YueGas.上年销售.ToString(), 6, 6);
ME.WriteStr(yd.YueGas.累计购进.ToString(), 6, 13);
ME.WriteStr(yd.YueGas.累计销售.ToString(), 6, 14);
ME.ActivateSheet("购进统计");
ME.WriteStr(yd.YueGas.累计修正.ToString(), 4, 4);
DS.WriteStr(yd.YueGas.累计修正.ToString(), 4, 6);
temp += "南高CNG站：" + yd.YueGas.备注 + " 累计停业：" + yd.YueGas.停运时间.ToString() + "小时\r\n";
tyTime += yd.YueGas.停运时间;
}
if (yd.Zd == 3)
{
ME.ActivateSheet("分站售气统计");
ME.WriteStr(yd.YueGas.停运时间.ToString(), 6, 11);
ME.ActivateSheet("进销总表");
ME.WriteStr(yd.YueGas.月度计划.ToString(), 8, 2);
ME.WriteStr(yd.YueGas.上年销售.ToString(), 8, 6);
ME.WriteStr(yd.YueGas.累计购进.ToString(), 8, 13);
ME.WriteStr(yd.YueGas.累计销售.ToString(), 8, 14);
ME.ActivateSheet("购进统计");
ME.WriteStr(yd.YueGas.累计修正.ToString(), 6, 4);
DS.WriteStr(yd.YueGas.累计修正.ToString(), 6, 6);
temp += "江阳西路CNG站：" + yd.YueGas.备注 + " 累计停业：" + yd.YueGas.停运时间.ToString() + "小时\r\n";
tyTime += yd.YueGas.停运时间;
}
if (yd.Zd == 4)
{
ME.ActivateSheet("分站售气统计");
ME.WriteStr(yd.YueGas.停运时间.ToString(), 4, 11);
ME.ActivateSheet("进销总表");
ME.WriteStr(yd.YueGas.月度计划.ToString(), 7, 2);
ME.WriteStr(yd.YueGas.上年销售.ToString(), 7, 6);
ME.WriteStr(yd.YueGas.累计购进.ToString(), 7, 13);
ME.WriteStr(yd.YueGas.累计销售.ToString(), 7, 14);
ME.ActivateSheet("购进统计");
ME.WriteStr(yd.YueGas.累计修正.ToString(), 5, 4);
DS.WriteStr(yd.YueGas.累计修正.ToString(), 5, 6);
temp += "麻柳沱CNG站：" + yd.YueGas.备注 + " 累计停业：" + yd.YueGas.停运时间.ToString() + "小时\r\n";
tyTime += yd.YueGas.停运时间;
}
}
temp += "停业时间共计累计：" + tyTime.ToString() + "小时。";
ME.ActivateSheet("进销总表");
ME.WriteStr(temp, 12, 2);//停业备注
#endregion
#region 第四步：隐藏掉多余的行
int strRow = Tj.Days + 1;//隐藏 起始行
int hideNumRow = 35 - Tj.Days;//需要隐藏的行数
ME.ActivateSheet("合计数据");
ME.HideRow(strRow + 3, hideNumRow, true);
ME.ActivateSheet("南高");
ME.HideRow(strRow + 3, hideNumRow, true);
ME.ActivateSheet("西路");
ME.HideRow(strRow + 3, hideNumRow, true);
ME.ActivateSheet("麻柳沱");
ME.HideRow(strRow + 3, hideNumRow, true);
ME.ActivateSheet("分站售气统计");
ME.HideRow(strRow + 10, hideNumRow, true);
#endregion
#region 第五步：将表单转换HML文档、PDF文档、jpeg图片 可用于预览
DS.Save();
DS.Close();
ME.ActivateSheet("进销总表");
ME.Save();
int gg = ME.Sheetcount;
for (int i = 0; i < ME.Sheetcount; i++)
{//为每张表存为htm文档
ME.SaveAsHtml(viewPaht + "\\" + i.ToString() + ".htm", ME.SheetName[i]);
}
ME.SavePdf(pdfFile);//保存为pdf文档
Sys.Pdf2Swf(pdfFile, swfFile);//生成 swf文件
//转换为图片
//myConvert.ConvertPDF2Image(viewPaht + "\\" + "me.pdf", viewPaht + "\\", "me", 1, gg,
// ImageFormat.Jpeg, myConvert.pdfType.Two);
ME.Save();
ME.Close();
#endregion
#region 第六步： 修改hml文档中的表格样式，为了兼容非IE浏览器
for (int i = 0; i < gg; i++)
{
string file = viewPaht + "\\" + i.ToString() + ".htm";
string str = File.ReadAllText(file, Encoding.Default);
str = str.Replace(":.5pt", ":1px");
File.WriteAllText(file, str, Encoding.Default);
}
#endregion
return true;
}
