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); }