private string GetYdCollectOnExport(string jsonDataTable)
{
string fn = "/XTemp/采集历史报表.xls";
string filePath = System.Web.HttpContext.Current.Server.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.HttpContext.Current.Server.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
DataTable dtSource = new DataTable();
if (jsonDataTable.Length > 10)
{
dtSource = JsonHelper.ToDataTable(jsonDataTable);
}
string funType = dtSource.Rows.Count == 0 ? "" : funType = CommFunc.ConvertDBNullToString(dtSource.Rows[0]["FunType"]);
string content = "";
System.Reflection.FieldInfo info = typeof(V0Fun).GetField(funType);
if (info != null)
{
var obj = info.GetCustomAttributes(typeof(Describe), false);
if (obj != null)
{
foreach (Describe md in obj)
{
content = md.describe;
}
}
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "RowId", 5)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间", "CoName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表地址", "ModuleAddr", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("采集时间", "TagTime", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("读数", "LastVal", 15)
{
IsSetWith = true
});
Excel.ExcelOparete excel = new Excel.ExcelOparete("采集历史报表");
excel.SetObjectValue("采集历史报表" + (content == "" ? "" : ":" + content), 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
private string GetYdSsrOfExport(DataTable dtSource)
{
string fn = "/XTemp/拉合闸报表.xls";
string filePath = System.Web.HttpContext.Current.Server.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.HttpContext.Current.Server.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "RowId", 15)
{
IsSetWith = true
});
//columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15) { IsSetWith = true });
//columns.Add(new Excel.ExcelColumn("房间", "CoName", 15) { IsSetWith = true });
columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间", "CoName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表", "ModuleAddr", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("拉合闸状态", "FunTypeS", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("操作人", "Create_by", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("操作时间", "Create_dt", 15)
{
IsSetWith = true, DefaultExcelCellStyle = columnCellStyle1
});
//columns.Add(new Excel.ExcelColumn("E", "E", 15) { IsSetWith = true });
Excel.ExcelOparete excel = new Excel.ExcelOparete("拉合闸报表");
excel.SetObjectValue("拉合闸报表", 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
private string GetYdMonitorOnExport(DataTable dtSource)
{
string fn = "/XTemp/监视数据报表.xls";
string filePath = System.Web.Hosting.HostingEnvironment.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.Hosting.HostingEnvironment.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
dtSource.Columns.Add("OnLineS", typeof(System.String));
foreach (DataRow dr in dtSource.Rows)
{
dr["OnLineS"] = CommFunc.ConvertDBNullToInt32(dr["OnLine"]) == 1 ? "正常" : "异常";
}
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "RowId", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间", "CoName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("通信方式", "HandledBY", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表地址", "ModuleAddr", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("表码值", "CurVal", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("表剩余电量", "RdVal", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("在线状态", "OnLineS", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("最后通信时间", "LastTime", 15)
{
IsSetWith = true, DefaultExcelCellStyle = columnCellStyle1
});
//columns.Add(new Excel.ExcelColumn("E", "E", 15) { IsSetWith = true });
Excel.ExcelOparete excel = new Excel.ExcelOparete("监视数据报表");
excel.SetObjectValue("监视数据报表", 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
private string GetYdMontionOnExport(DataTable dtSource)
{
string fn = "/XTemp/运行历史报表.xls";
string filePath = System.Web.HttpContext.Current.Server.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.HttpContext.Current.Server.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
int cnt = CommFunc.ConvertDBNullToInt32(dtSource.Rows[0]["Cnt"]);
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "RowId", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间", "CoName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表地址", "ModuleAddr", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("日期", "TagTimeS", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "01月" : cnt == 24 ? "00:00" : "01号", "h00", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "02月" : cnt == 24 ? "01:00" : "02号", "h01", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "03月" : cnt == 24 ? "02:00" : "03号", "h02", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "04月" : cnt == 24 ? "03:00" : "04号", "h03", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "05月" : cnt == 24 ? "04:00" : "05号", "h04", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "06月" : cnt == 24 ? "05:00" : "06号", "h05", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "07月" : cnt == 24 ? "06:00" : "07号", "h06", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "08月" : cnt == 24 ? "07:00" : "08号", "h07", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "09月" : cnt == 24 ? "08:00" : "09号", "h08", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "10月" : cnt == 24 ? "09:00" : "10号", "h09", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "11月" : cnt == 24 ? "10:00" : "11号", "h10", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "12月" : cnt == 24 ? "11:00" : "12号", "h11", 15)
{
IsSetWith = true
});
if (cnt > 13)
{
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "12:00" : "13号", "h12", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "13:00" : "14号", "h13", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "14:00" : "15号", "h14", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "15:00" : "16号", "h15", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "16:00" : "17号", "h16", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "17:00" : "18号", "h17", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "18:00" : "19号", "h18", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "19:00" : "20号", "h19", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "20:00" : "21号", "h20", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "21:00" : "22号", "h21", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "22:00" : "23号", "h22", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "23:00" : "24号", "h23", 15)
{
IsSetWith = true
});
}
if (cnt > 24)
{
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "25号", "h24", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "26号", "h25", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "27号", "h26", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "28号", "h27", 15)
{
IsSetWith = true
});
if (cnt >= 29)
{
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "29号", "h28", 15)
{
IsSetWith = true
});
}
if (cnt >= 30)
{
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "30号", "h29", 15)
{
IsSetWith = true
});
}
if (cnt >= 31)
{
columns.Add(new Excel.ExcelColumn(cnt == 12 ? "00月" : cnt == 24 ? "00:00" : "31号", "h30", 15)
{
IsSetWith = true
});
}
}
//columns.Add(new Excel.ExcelColumn("E", "E", 15) { IsSetWith = true });
Excel.ExcelOparete excel = new Excel.ExcelOparete("运行历史报表");
excel.SetObjectValue("运行历史报表", 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
private string GetYdCustomerOnExport(DataTable dtSource)
{
string fn = "/XTemp/客户列表.xls";
string filePath = System.Web.HttpContext.Current.Server.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.HttpContext.Current.Server.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
if (dtSource == null || dtSource.Rows.Count == 0)
{
throw new Exception("没有数据");
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "RowId", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("合同号", "Contract", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电户号", "ModuleName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("租客姓名", "CrmName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("租客联系电话", "MPhone", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("租客身份证号码/护照", "CrmNo", 15)
{
IsSetWith = true
});
//columns.Add(new Excel.ExcelColumn("固定电话", "Phone", 15) { IsSetWith = true });
//columns.Add(new Excel.ExcelColumn("邮件地址", "Email", 15) { IsSetWith = true });
columns.Add(new Excel.ExcelColumn("入住地址", "CoFullName", 15)
{
IsSetWith = true
});
//columns.Add(new Excel.ExcelColumn("备注", "Remark", 15) { IsSetWith = true });
Excel.ExcelOparete excel = new Excel.ExcelOparete("客户列表");
excel.SetObjectValue("客户列表", 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}
private string GetYdRepHisOnExport(string jsonDataTable)
{
string fn = "/XTemp/能耗历史报表.xls";
string filePath = System.Web.HttpContext.Current.Server.MapPath(@"/XTemp");
if (System.IO.Directory.Exists(filePath) == false)
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = System.Web.HttpContext.Current.Server.MapPath(fn);
if (System.IO.File.Exists(filename))/*先删除已存在的文件，再汇出Excel*/
{
System.IO.File.Delete(filename);
}
DataTable dtSource = new DataTable();
if (jsonDataTable.Length > 10)
{
dtSource = JsonHelper.ToDataTable(jsonDataTable);
}
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
columnCellStyle1 = new Excel.ExcelCellStyle()
{
DataFormart = "yyyy-MM-dd HH:mm:ss",
};
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("序号", "ID", 15)
{
IsSetWith = true
});
//columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15) { IsSetWith = true });
//columns.Add(new Excel.ExcelColumn("房间", "CoName", 15) { IsSetWith = true });
columns.Add(new Excel.ExcelColumn("建筑", "CoStrcName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间", "CoName", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表地址", "ModuleAddr", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("日期范围", "Date", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("倍率", "Multiply", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("上期读数", "FirstVal", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("本期读数", "LastVal", 15)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("用电量", "UseVal", 15)
{
IsSetWith = true
});
//columns.Add(new Excel.ExcelColumn("E", "E", 15) { IsSetWith = true });
Excel.ExcelOparete excel = new Excel.ExcelOparete("能耗历史报表");
excel.SetObjectValue("能耗历史报表", 0, 0, 3);
excel.SetColumnName(columns, 1, 0);
excel.SetColumnValue(columns, dtSource.Select(), 2, 0);
excel.SaveExcelByFullFileName(filename);
return(fn);
}