/// <summary>
/// 创建 财务附表表Excel
/// </summary>
private bool CreateCWExcel(string year, string yue, HttpContext context, CountCWGas Cw)
{
string template = 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 viewPaht = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue);//报表文件文件目录
if (System.IO.Directory.Exists(viewPaht) == false)
{
FileManager.CreateFolder(viewPaht);
}
bool ok = FileManager.CopyFile(template, newFile);
if (ok == false)
{
return false;
}
MyExcel ME = new MyExcel();
ME.Open(newFile);
#region 第一步 IC卡充值,消费 写入
ME.ActivateSheet("IC卡消费、充值统计");
string txt = string.Format("系统统计时间：{0} 当班(白班)接班 至 {1} 当班(夜班)交班", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 3, 1);//时间范围
txt = string.Format("系统统计时间：{0} 至 {1} ", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 33, 1);//时间范围
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
string sheetName = "";
int row = 0;//储存底数报表对应行号
ME.ActivateSheet("IC卡消费、充值统计");
if (my.Zd == 2)
{
sheetName = "月度汇总(南高)";
row = 10;
ME.WriteStr(my.充值金额.ToString(), 36, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 36, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 36, 4);//结束时间
}
if (my.Zd == 3)
{
row = 17;
sheetName = "月度汇总(西路)";
ME.WriteStr(my.充值金额.ToString(), 37, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 37, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 37, 4);//结束时间
}
if (my.Zd == 4)
{
row = 5;
sheetName = "月度汇总(麻柳沱)";
ME.WriteStr(my.充值金额.ToString(), 35, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 35, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 35, 4);//结束时间
}
//每枪 预购金额
for (int i = 0; i < my.Guns; i++)
{
ME.WriteStr(my.GunYuGou[i].GunStr, row + i, 3);//起始时间
ME.WriteStr(my.GunYuGou[i].GunEnd, row + i, 4);//结束时间
ME.WriteStr(my.GunYuGou[i].预购金额.ToString(), row + i, 5);//预购金额
}
}
#endregion
#region 第二部 公交车数据写入
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
ME.ActivateSheet("公交月度");
if (my.Zd == 2)
{
continue;
}
if (my.Zd == 3 || my.Zd == 4)
{
for (int i = 0; i < my.GongJiao.Length; i++)
{
int col = 0;
if (my.Zd == 3)
{
ME.WriteStr(my.GongJiao[i].日期, 5 + i, 1);//写入日期
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 2);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 3);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 5);//写入手工记账
}
if (my.Zd == 4)
{
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 6);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 7);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 9);//写入手工记账
}
}
}
}
ME.WriteStr(Cw.Days.ToString(), 43, 2);//写入天数
#endregion
#region 第三步 记账单位合计数写入 , 记账单位 分站合计数
string sheet = "";
CW_月度统计Mod d = null;
CW_JiZhangDanWen[] jz = null;
for (int i = 0; i < 4; i++)
{
switch (i)
{
case 0: { sheet = "月度汇总"; d = Cw.月度汇总; jz = Cw.记账单位合计; } break;//总表合计
case 1: { sheet = "月度汇总(南高)"; d = Cw.myData[0]; jz = Cw.myGunYuGou[0].JiZhang; } break;//南高
case 2: { sheet = "月度汇总(西路)"; d = Cw.myData[1]; jz = Cw.myGunYuGou[1].JiZhang; } break;//西路
case 3: { sheet = "月度汇总(麻柳沱)"; d = Cw.myData[2]; jz = Cw.myGunYuGou[2].JiZhang; } break;//麻柳沱
}
ME.ActivateSheet(sheet);
ME.WriteStr(d.预购刷卡气量.ToString(), 4, 4);
ME.WriteStr(d.刷卡金额.ToString(), 4, 5);
ME.WriteStr(d.交款气量.ToString(), 4, 6);
ME.WriteStr(d.交款金额.ToString(), 4, 7);
for (int j = 0; j < jz.Length; j++)
{
ME.WriteStr(jz[j].手工.ToString(), 4 + (j + 1) * 2, 3);
ME.WriteStr(jz[j].刷卡.ToString(), 5 + (j + 1) * 2, 3);
if (jz[j].单位名称 == "公交公司")
{
continue;
}
ME.WriteStr(jz[j].价格.ToString(), 4 + (j + 1) * 2, 5);
}
}
#endregion
#region 第四步 记账单位表统计
string SQL = @"SELECT a.[日期]
,a.[记账单位名称]
,sum(a.[刷卡记账气量]) as 刷卡
,sum(a.[手工记账气量]) as 手工
,b.编号
FROM [CNGQT].[dbo].[SC_记账明细] a LEFT JOIN cngqt.dbo.SC_记账单位名称 b
on b.记账单位名称=a.[记账单位名称]
where 日期 between '@起始日期@' and '@结束日期@'
group by a.日期,a.记账单位名称,b.编号 order by 日期";
SQL = SQL.Replace("@起始日期@", Cw.Str.ToShortDateString());
SQL = SQL.Replace("@结束日期@", Cw.End.ToShortDateString());
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
ME.ActivateSheet("记账单位统计表");
// ME.WriteStr(Cw.Days.ToString(), 33, 5); //插入统计天数
for (int i = 0; i < Cw.Days; i++)//写入日期标题)
{
string dtext = Cw.Str.AddDays(i).ToString("MM月dd日");
ME.WriteStr(dtext, 1, 3 + i);
}
foreach (DataRow row in dt.Rows)
{
DateTime rq = DateTime.Parse(row["日期"].ToString());
int col = (rq - Cw.Str).Days;//取日期间隔 确定列数
int r = int.Parse(row["编号"].ToString());
double sk = double.Parse(row["刷卡"].ToString());
double sg = double.Parse(row["手工"].ToString());
ME.WriteStr(sk.ToString(), r * 2 + 1, 3 + col);
ME.WriteStr(sg.ToString(), r * 2, 3 + col);
}
ME.HideSheet("记账单位统计表");
#endregion
#region 最后一步:生成 excel,pdf,Swf 文档
ME.Save();
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
return true;
}
/// <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;
}
/// <summary>
/// 生成考勤数据
/// </summary>
/// <param name="zd">站点</param>
/// <param name="time1">起始日期</param>
/// <param name="time2">结束日期</param>
/// <param name="FileID">文件ID</param>
private void KQ_Export(int zd, string names, DateTime time1, DateTime time2, string FileID)
{
string SQL = @"SELECT [日期],[姓名]
,isnull(convert(varchar(20),[上班签到],120),'X') as [上班签到]
,isnull(convert(varchar(20),[下班签退],120),'X') as [下班签退]
,datediff(d,'@起始时间@',日期) as 日
,[出勤情况]
FROM [CNGTZ].[dbo].[TZ_人员_考勤表]
where 姓名 ='@姓名@' AND 日期 BETWEEN '@起始时间@' AND '@结束时间@' order by 日期";
SQL = SQL.Replace("@起始时间@", time1.ToShortDateString());
SQL = SQL.Replace("@结束时间@", time2.ToShortDateString());
TimeSpan sp = time2 - time1;//相隔天数
int days = sp.Days + 1;
string nowFile = HttpContext.Current.Server.MapPath("~/Public/ExcelTemplate/DownTemp/替换文本.xls");
nowFile = nowFile.Replace("替换文本", FileID);
MyExcel ME = new MyExcel();
ME.Open(nowFile);//打开副本文件
for (int i = 0; i < 31; i++)
{//插入 抬头 日期行数据
if (i >= days)
{
ME.HideCol(3 + i, true);
continue;
}
DateTime o = time1.AddDays(i);
ME.WriteStr(o.ToString("dd"), 3, 3 + i);
}
ME.WriteStr("起始时间:" + time1.ToShortDateString() + " 至 " + time2.ToShortDateString() + " ", 2, 1);
DAL dal = new DAL();
string[] name = names.Split(',');//生成姓名数组
for (int i = 0; i < name.Length - 1; i++)
{//插入姓名行数
ME.InsertRow(6);
ME.InsertRow(6);
ME.CellsCopy("A4:AG5", "A6");
}
for (int i = 0; i < name.Length; i++)
{//生成每人考勤数据
ME.WriteStr(name[i], 4 + i * 2, 1);
string zxSQL = SQL.Replace("@姓名@", name[i]);
Sys.DebugMes(zxSQL);
DataTable dt = dal.RunSqlDataTable(zxSQL);
if (dt == null)
{
continue;
}
foreach (DataRow row in dt.Rows)
{
int n = int.Parse(row["日"].ToString());
string x1 = row["上班签到"].ToString();
string x2 = row["下班签退"].ToString();
string cq = row["出勤情况"].ToString();
if (zd == 2 || zd == 3)
{//西路 和 南高 签到对应上班, 签退对应下班
if (cq != "异常" && cq != "正常")
{
System.Drawing.Color mycolor = System.Drawing.Color.Red;
switch (cq)
{
case "事假":
case "病假":
case "其他":
case "工伤":
mycolor = System.Drawing.Color.Red;
break;
case "婚假":
case "年休":
mycolor =Color.Green;
break;
}
ME.WriteStr(cq.Substring(0, 1), 4 + i * 2, 3 + n);
ME.WriteStr(cq.Substring(0, 1), 5 + i * 2, 3 + n);
ME.SetFont(mycolor, 4 + i * 2, 3 + n);
ME.SetFont(mycolor, 5 + i * 2, 3 + n);
}
if (x1 != "X")
{
ME.WriteStr("○", 4 + i * 2, 3 + n);
ME.SetFont(System.Drawing.Color.Black, 4 + i * 2, 3 + n);
}
if (x2 != "X")
{
ME.WriteStr("○", 5 + i * 2, 3 + n);
ME.SetFont(System.Drawing.Color.Black, 5 + i * 2, 3 + n);
}
}
if (zd == 4)
{//麻柳沱 签到对应第一天,签退对应第二天
if (x1 != "X")
{
ME.WriteStr("○", 4 + i * 2, 3 + n);
ME.WriteStr("○", 5 + i * 2, 3 + n);
}
if (x2 != "X")
{
ME.WriteStr("○", 4 + i * 2, 4 + n);
ME.WriteStr("○", 5 + i * 2, 4 + n);
}
}
}
}
ME.Save();
ME.Close();
}