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 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 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 ExportInfo(DataTable dtSource, ref string rootPath)
{
string msg = "";
#region 汇出上载错误信息
try
{
var fileName = HttpContext.Current.Server.MapPath(rootPath);
if (!System.IO.Directory.Exists(fileName))
{
System.IO.Directory.CreateDirectory(fileName);
}
//var fileName = HttpContext.Current.Server.MapPath("~/files/");
int Total = dtSource.Rows.Count;
//string sPath = fileName;
//if (!System.IO.Directory.Exists(sPath))
// System.IO.Directory.CreateDirectory(sPath);
//
DataRow[] errArr = dtSource.Select("isnull(ErrCode,0)<0");
int eNum = errArr.Count();
msg = "总记录:" + Total + ",成功上载:" + (Total - eNum);
if (eNum > 0)
{
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
string fn = "执行错误信息" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filePath = fileName + fn;
rootPath = rootPath + fn;
//fileName = fn;
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("房间号", "CoName", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("上期计数", "FirstVal", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("本期计数", "LastVal", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("上期时间", "FirstTime", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("本期时间", "LastTime", 40)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("水费单价", "Price", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("物业费", "ChargVal", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("错误提示", "ErrTxt", 30)
{
IsSetWith = true
});
Excel.ExcelOparete excel = new Excel.ExcelOparete("执行结果");
excel.SetColumnName(columns, 0, 0);
excel.SetColumnValue(columns, errArr, 1, 0);
excel.SaveExcelByFullFileName(filePath);
msg = msg + ",失败上载:" + eNum;
}
else
{
rootPath = "";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
return(msg);
}
private string ExportInfo(DataTable dtSource, ref string fileName)
{
string msg = "";
#region 汇出上载错误信息
try
{
//
int Total = dtSource.Rows.Count;
//string sPath = @"c:\temp";
string sPath = fileName;
if (!System.IO.Directory.Exists(sPath))
{
System.IO.Directory.CreateDirectory(sPath);
}
//
DataRow[] errArr = dtSource.Select("isnull(ErrCode,0)<0");
int eNum = errArr.Count();
msg = "总记录:" + Total + ",成功上载:" + (Total - eNum);
if (eNum > 0)
{
Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
columnCellStyle0 = new Excel.ExcelCellStyle()
{
DataFormart = "0.00",
HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
};
string fn = "执行错误信息" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filePath = fileName + fn;
fileName = fn;
Excel.ExcelColumnCollection columns = new Excel.ExcelColumnCollection();
columns.Add(new Excel.ExcelColumn("集中器名称", "GwName", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("集中器IP地址", "GwIp", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("采集器名称", "EspName", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("采集器地址", "EspAddr", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("通讯方式（0:COM;1:TCP/Client;3:TCP/Server;4:IOServer）", "TransferType", 40)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("TCP端口", "EspPort", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("COM口", "ComPort", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("波特率", "Baud", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("数据位", "DataBit", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("停止位", "StopBit", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("校验方式（0 无/1 奇/2 偶/3标志/4 空格）", "Parity", 35)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("设备地址", "MeterAddr", 17)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("倍率", "Multiply", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表型号(如DDS3366L)", "ModuleType", 25)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("回路地址", "ModuleAddr", 17)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间(约定定义)（如男生宿舍->南苑1栋->南苑1层->101房间）", "CoFullName", 30)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("错误提示", "ErrTxt", 30)
{
IsSetWith = true
});
Excel.ExcelOparete excel = new Excel.ExcelOparete("执行结果");
excel.SetColumnName(columns, 0, 0);
excel.SetColumnValue(columns, errArr, 1, 0);
excel.SaveExcelByFullFileName(filePath);
msg = msg + ",失败上载:" + eNum;
}
else
{
fileName = "";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
return(msg);
}
/// <summary>
/// 导出采集器模板(导出Excel模板)
/// </summary>
/// <returns></returns>
public bool ExportBuildingBatch(string filename, DataTable dtSource)
{
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("集中器名称", "GwName", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("集中器IP地址", "GwIp", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("采集器名称", "EspName", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("采集器地址", "EspAddr", 20)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("通讯方式（0:COM;1:TCP/Client;3:TCP/Server;4:IOServer）", "TransferType", 40)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("TCP端口", "EspPort", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("COM口", "ComPort", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("波特率", "Baud", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("数据位", "DataBit", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("停止位", "StopBit", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("校验方式（0 无/1 奇/2 偶/3标志/4 空格）", "Parity", 35)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("设备地址", "MeterAddr", 17)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("倍率", "Multiply", 10)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("电表型号(如DDS3366L)", "ModuleType", 25)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("回路地址", "ModuleAddr", 17)
{
IsSetWith = true
});
columns.Add(new Excel.ExcelColumn("房间(约定定义)（如男生宿舍->南苑1栋->南苑1层->101房间）", "CoFullName", 30)
{
IsSetWith = true
});
Excel.ExcelOparete excel = new Excel.ExcelOparete("Excel模板");
excel.SetColumnName(columns, 0, 0);
excel.SetColumnValue(columns, dtSource.Select(), 1, 0);
excel.SaveExcelByFullFileName(filename);
return(true);
}
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);
}
