public string ExportFeeApportion() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<Queryfeeapportion>(inputValue); List<FeeApportionListClass> dtRef = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IFee_ApportionService>("Fee_ApportionService").GetFeeApportDataList(query); try { // if ((dtRef != null) && (dtRef.Count > 0)) { DataTable dtReport = TableView.CreateFee_ApportionDataTable(); for (var r = 0; r < dtRef.Count; r++) { DataRow dr = dtReport.NewRow(); dr[1] = dtRef[r].Tm.ToString(); dr[2] = dtRef[r].Obj.ToString(); dr[3] = Math.Round(dtRef[r].BeforeVal, 2); dr[4] = Math.Round(dtRef[r].ApportionVal, 2); dr[5] = Math.Round(dtRef[r].TotalVal, 2); dtReport.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\费用分摊表.xls"; TemplateParam param = new TemplateParam("费用分摊表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0)); //TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0)); param.DataColumn = new[] { 0, 1, 2, 3, 4, 5 }; param.ItemUnit = "(单位:元 )"; param.ItemUnitCell = new CellParam(3, 5); dtReport.TableName = "费用分摊表"; ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportLoadForecast() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryLoadForecast>(inputValue); ResultLoadForecastMap dtRef = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.ILoadForecastService>("LoadForecastService").GetLoadForecastChart(query); try { if ((dtRef != null) && (dtRef.LoadForecast.Count > 0)) { DataTable dtReport = TableView.CreateFee_ForecastDataTable(); List<ResultLoadForecastList> listNew = dtRef.LoadForecast; for (var r = 0; r < listNew.Count; r++) { DataRow dr = dtReport.NewRow(); dr[1] = listNew[r].TimeArea.ToString(); dr[2] = listNew[r].ForeCast.ToString(); if (listNew[r].History==-9999) { dr[3] = "--"; dr[4] = "--"; dr[5] = "--"; } else { dr[3] = listNew[r].History.ToString(); dr[4] = listNew[r].Deviation.ToString(); dr[5] = listNew[r].Pecent.ToString(); } dtReport.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\负荷预测表.xls"; TemplateParam param = new TemplateParam("负荷预测表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0)); //TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0)); param.DataColumn = new[] { 0, 1, 2, 3, 4, 5 }; //param.ItemUnit = "(单位:元"; //param.ItemUnitCell = new CellParam(3, 5); dtReport.TableName = "负荷预测表"; ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportSysLogExcel() { try { #region 获取数据 var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QuerySysLogContact>(inputValue); query.PageCurrent = 1; query.PageSize = 100000000; var result = new NTS.EMS.Config.BLL.QuerySysLogBll().GetSysLog(query); #endregion #region 组织数据 #endregion if (result.SysLogList.Count > 0) { DataTable dt = TableView.CreateSysLogDataTable(); for (var i = 0; i < result.SysLogList.Count; i++) { DataRow dr = dt.NewRow(); dr[1] = result.SysLogList[i].SysNo; dr[2] = result.SysLogList[i].ModelName; dr[3] = result.SysLogList[i].LogContent; dr[4] = result.SysLogList[i].LogTime; dr[5] = result.SysLogList[i].OpType == OpType.Operate ? "操作" : "配置"; dr[6] = result.SysLogList[i].UserName; dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\日志查询表.xls"; string suTitle = ""; string startTime = query.StartTime.ToString("yyyy-MM-dd"); string endTime = query.EndTime.ToString("yyyy-MM-dd"); if (query.StartTime.ToShortDateString() == "1900-1-1") { startTime = result.SysLogList.Min(p => p.LogTime).ToString("yyyy-MM-dd"); } if (query.EndTime.ToShortDateString() == "1900-1-1") { endTime = result.SysLogList.Max(p => p.LogTime).ToString("yyyy-MM-dd"); } suTitle = startTime + "~" + endTime; TemplateParam param = new TemplateParam("日志查询", new CellParam(0, 0), suTitle, new CellParam(3, 0), false, new CellParam(4, 0)); param.DataColumn = new[] { 0, 6, 4, 5, 2, 3 }; param.SortColumn = 0; dt.TableName = "日志查询"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
/// <summary> /// 从模版中导出Excel文件 /// </summary> /// <param name="dt">数据源DataTable</param> /// <param name="path">导出文件路径</param> /// <param name="templatePath">模版路径</param> /// <param name="param"></param> public static void ExportExcel(DataTable dt, string path, string templatePath, TemplateParam param) { FileStream templateFile = new FileStream(templatePath, FileMode.Open, FileAccess.Read);//读取模版文件 hssfworkbook = new HSSFWorkbook(templateFile); ISheet sheet1 = hssfworkbook.GetSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);//获取第一张工作表 ICellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; //插入标题 if (param.TitleCell != null) { IRow rowTitle = sheet1.GetRow(param.TitleCell.Row); ICell cellTitle = rowTitle.GetCell(param.TitleCell.Cell); if (!string.IsNullOrEmpty(param.Title)) cellTitle.SetCellValue(param.Title+cellTitle.StringCellValue); } //插入副标题 if (param.SubTitleCell != null) { IRow rowSubTitle = sheet1.GetRow(param.SubTitleCell.Row); ICell cellSubTitle = rowSubTitle.GetCell(param.SubTitleCell.Cell); if (!string.IsNullOrEmpty(param.SubTitle)) cellSubTitle.SetCellValue(param.SubTitle); } //插入单位 if (param.ItemUnitCell != null) { IRow rowItemUnit = sheet1.GetRow(param.ItemUnitCell.Row); ICell cellItemUnit = rowItemUnit.GetCell(param.ItemUnitCell.Cell); if (!string.IsNullOrEmpty(param.ItemUnit)) cellItemUnit.SetCellValue(param.ItemUnit); } int columnCount = param.DataColumn.Length > 0 ? param.DataColumn.Length : dt.Columns.Count; //插入表格内容 if (param.DataCell != null) { if (param.DataTitle) { IRow rowDataTitle = sheet1.GetRow(param.DataCell.Row); ICell cellDataTitle; for (int m = 0; m < columnCount; m++) { cellDataTitle = rowDataTitle.GetCell(m); cellDataTitle.CellStyle = style; string title = dt.Columns[param.DataColumn[m]].ColumnName; if (m == param.SortColumn) title = "排序"; cellDataTitle.SetCellValue(title); } } IRow row; ICell cell; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { row = sheet1.GetRow(i + param.DataCell.Row + 1) ?? sheet1.CreateRow(i + param.DataCell.Row + 1); for (int j = 0; j < columnCount; j++) { cell = row.GetCell(j) ?? row.CreateCell(j); cell.CellStyle = style; object obj = dt.Rows[i][param.DataColumn[j]]; if (j == param.SortColumn) obj = i + 1; if(param.SpecialColumn.Count>0) { if (param.SpecialColumn.ContainsKey(j)) { obj = param.SpecialColumn[j]; } } SetCellValue(cell, obj, dt.Columns[param.DataColumn[j]]); } } } } sheet1.ForceFormulaRecalculation = true; FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
/// <summary> /// 从模版中导出Excel文件 /// </summary> /// <param name="dt">数据源DataTable</param> /// <param name="path">导出文件路径</param> /// <param name="templatePath">模版路径</param> /// <param name="param"></param> public static void ExportExcel(DataTable dt, string path, string templatePath, TemplateParam param) { FileStream templateFile = new FileStream(templatePath, FileMode.Open, FileAccess.Read);//读取模版文件 hssfworkbook = new HSSFWorkbook(templateFile); ISheet sheet1 = hssfworkbook.GetSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);//获取第一张工作表 ICellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; //插入标题 if (param.TitleCell != null) { IRow rowTitle = sheet1.GetRow(param.TitleCell.Row); ICell cellTitle = rowTitle.GetCell(param.TitleCell.Cell); if (!string.IsNullOrEmpty(param.Title)) { cellTitle.SetCellValue(param.Title + cellTitle.StringCellValue); } } //插入副标题 if (param.SubTitleCell != null) { IRow rowSubTitle = sheet1.GetRow(param.SubTitleCell.Row); ICell cellSubTitle = rowSubTitle.GetCell(param.SubTitleCell.Cell); if (!string.IsNullOrEmpty(param.SubTitle)) { cellSubTitle.SetCellValue(param.SubTitle); } } //插入单位 if (param.ItemUnitCell != null) { IRow rowItemUnit = sheet1.GetRow(param.ItemUnitCell.Row); ICell cellItemUnit = rowItemUnit.GetCell(param.ItemUnitCell.Cell); if (!string.IsNullOrEmpty(param.ItemUnit)) { cellItemUnit.SetCellValue(param.ItemUnit); } } int columnCount = param.DataColumn.Length > 0 ? param.DataColumn.Length : dt.Columns.Count; //插入表格内容 if (param.DataCell != null) { if (param.DataTitle) { IRow rowDataTitle = sheet1.GetRow(param.DataCell.Row); ICell cellDataTitle; for (int m = 0; m < columnCount; m++) { cellDataTitle = rowDataTitle.GetCell(m); cellDataTitle.CellStyle = style; string title = dt.Columns[param.DataColumn[m]].ColumnName; if (m == param.SortColumn) { title = "排序"; } cellDataTitle.SetCellValue(title); } } IRow row; ICell cell; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { row = sheet1.GetRow(i + param.DataCell.Row + 1) ?? sheet1.CreateRow(i + param.DataCell.Row + 1); for (int j = 0; j < columnCount; j++) { cell = row.GetCell(j) ?? row.CreateCell(j); cell.CellStyle = style; object obj = dt.Rows[i][param.DataColumn[j]]; if (j == param.SortColumn) { obj = i + 1; } if (param.SpecialColumn.Count > 0) { if (param.SpecialColumn.ContainsKey(j)) { obj = param.SpecialColumn[j]; } } SetCellValue(cell, obj, dt.Columns[param.DataColumn[j]]); } } } } sheet1.ForceFormulaRecalculation = true; FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
public string ExportExcelCostQuery() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryCost>(inputValue); var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.ICostQueryService>("CostQueryService").GetCostQuery(query); string objectName = ""; switch(query.ObjType) { case AreaType.Area: objectName = new BLL.BaseLayerObject().GetBaseLayerObjectList( string.Format(" and layerobjectid={0}", query.ObjectId), " order by LayerObjectID")[0].LayerObjectName; break; case AreaType.Liquid: objectName = new BLL.BaseLayerObject().GetBaseFuncLayerObjectList( string.Format(" and layerobjectid={0}", query.ObjectId), " order by LayerObjectID")[0].LayerObjectName; break; } var dept = string.Empty; List<Model.Itemcode> itList = null; string unit = ""; string itemCodeName = ""; itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); if (itList.Count > 0) { dept = itList[0].Unit; } unit = query.ItemCode == "00000" ? "T" : dept; itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName; try { if (result.FeeTbl != null) { if (result.FeeTbl.FeeList.Count > 0) { DataTable dt = TableView.CreateCostQueryDataTable(result); for (var r = 0; r < result.FeeTbl.FeeList.Count; r++) { DataRow dr = dt.NewRow(); for (int j = 1; j <= dt.Columns.Count - 1; j++) { dr[j] = result.FeeTbl.FeeList[r][j - 1]; } dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\费用查询.xls"; string time = ""; switch (query.Particle) { case Particle.Month: time = query.StartTime.Year + "-" + query.StartTime.Month + "月"; break; case Particle.Year: time = query.StartTime.Year + "年"; break; } TemplateParam param = new TemplateParam(objectName + " 费用查询---" + itemCodeName + "能耗", new CellParam(0, 0), time, new CellParam(3, 0), true, new CellParam(4, 0)); List<int> columnIndex = new List<int>(); for (int i = 0; i <= dt.Columns.Count - 1; i++) { columnIndex.Add(i); } param.DataColumn = columnIndex.ToArray(); param.ItemUnit = "(单位:" + unit + ")"; param.ItemUnitCell = new CellParam(3, 5); param.SortColumn = 0; dt.TableName = "费用查询"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportQueryLineChart() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<BasicQuery>(inputValue); var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart") .GetQueryLineChart(query); try { string name = new BLL.BaseLayerObject().GetBaseLayerObjectList( string.Format(" and layerobjectid={0}", query.ObjectNum), " order by LayerObjectID")[0].LayerObjectName; string itemUnit = result.Dept[0]; var dept = string.Empty; var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); if (itList.Count > 0) { dept = itList[0].Unit; } string itemName = query.ItemCode == "00000" ? "总能耗" : dept; var ItemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); string ItemName = query.ItemCode == "00000" ? "总能耗" : ItemList[0].ItemCodeName; DataTable dt = TableView.CreateBaseDataTable(); if (query.ItemCode != "00000") { for (var r = 0; r < result.Enery[itList[0].ItemCodeName].Count; r++) { DataRow dr = dt.NewRow(); dr[1] = result.ObjectName[r]; dr[2] = query.ObjectNum; dr[3] = name; dr[4] = result.ObjectName[r]; dr[5] = result.ObjectName[r]; dr[6] = itemName; dr[7] = decimal.Round(decimal.Parse(result.Enery[itList[0].ItemCodeName][r].ToString()), 2).ToString(); dt.Rows.Add(dr); } } else { foreach (var i in result.Enery) { DataRow drs = dt.NewRow(); drs[1] = i.Key; drs[2] = "-"; drs[3] = "-"; drs[4] = "-"; drs[5] = "-"; drs[6] = "-"; drs[7] = "-"; dt.Rows.Add(drs); for (var r = 0; r < result.Enery[i.Key].Count; r++) { DataRow dr = dt.NewRow(); dr[1] = result.ObjectName[r]; dr[2] = query.ObjectNum; dr[3] = name; dr[4] = result.ObjectName[r]; dr[5] = result.ObjectName[r]; dr[6] = itemName; dr[7] = decimal.Round(decimal.Parse(result.Enery[i.Key][r].ToString()), 2); dt.Rows.Add(dr); } } } if (dt != null) { string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗查询.xls"; TemplateParam param = new TemplateParam(name + " " + ItemName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0)); param.DataColumn = new[] { 0, 3, 1, 7 }; param.ItemUnit = "(单位:" + itemUnit + ")"; param.ItemUnitCell = new CellParam(3, 4); dt.TableName = "能耗查询统计"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportExcelDataRankingNew() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryOrderObjects>(inputValue); string icode = query.ItemCode; query.QueryType = EnergyAnalyseQueryType.Default; ResultOrder resultAll = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery") .GetShopOrderNew(query); query.QueryType = EnergyAnalyseQueryType.UnitArea; ResultOrder resultArea = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery") .GetShopOrderNew(query); query.QueryType = EnergyAnalyseQueryType.UnitPerson; ResultOrder resultPerson = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery") .GetShopOrderNew(query); var dept = string.Empty; var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); if (itList.Count > 0) { dept = itList[0].Unit; } string unit = icode == "00000" ? "T" : dept; //switch (query.QueryType) //{ // case EnergyAnalyseQueryType.Convert2Co2: // case EnergyAnalyseQueryType.Convert2Coal: // unit = "T";//标准煤单位 // break; // case EnergyAnalyseQueryType.Convert2Money: // unit = "元";//标准煤单位 // break; //} string itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName; try { if (query.AreaIdLst.Count > 0) { DataTable dt = TableView.CreateOrderBaseDataTable(); for (var r = 0; r < resultAll.OrderLst.Count; r++) { DataRow dr = dt.NewRow(); dr[1] = resultAll.OrderLst[r].Obj; dr[2] = query.AreaIdLst; dr[3] = resultAll.OrderLst[r].Obj; dr[4] = resultAll.OrderLst[r].Obj; dr[5] = resultAll.OrderLst[r].Obj; dr[6] = itemCodeName; dr[7] = decimal.Round(decimal.Parse(resultAll.OrderLst[r].Val.ToString()), 2).ToString(); dr[8] = resultArea.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultArea.OrderLst[r].Val.ToString()), 2).ToString(); dr[9] = resultPerson.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultPerson.OrderLst[r].Val.ToString()), 2).ToString(); dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\新能耗排名表.xls"; if (!File.Exists(templatePath)) { return "{\"status\":\"error\",\"msg\":\"未发现Excel模板文件\"}"; } TemplateParam param = new TemplateParam(itemCodeName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0)); param.DataColumn = new[] { 0, 3, 7, 8, 9 }; param.ItemUnit = "(单位:" + unit + ")"; param.ItemUnitCell = new CellParam(3, 4); param.SortColumn = 0; dt.TableName = "能耗排名表"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportExcelEnergyAnalyse() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryAnalyse>(inputValue); string icode = query.ItemCode; EnergyAnalyseQueryType tempQueryType = query.QueryType; query.QueryType = EnergyAnalyseQueryType.Default;//总能耗 ResultEnergyAnalyse resultAll = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart").GetEnergyAnalyseLineChart(query); ResultEnergyAnalyse resultArea = null; ResultEnergyAnalyse resultPerson = null; if (query.IsDevice == 0) {// query.QueryType = EnergyAnalyseQueryType.UnitArea;//单位面积 resultArea = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart") .GetEnergyAnalyseLineChart(query); query.QueryType = EnergyAnalyseQueryType.UnitPerson;//人均 resultPerson = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart") .GetEnergyAnalyseLineChart(query); } var dept = string.Empty; List<Model.Itemcode> itList = null; string unit = ""; string itemCodeName = ""; if (query.IsDevice == 1) { var deviceList = new NTS.WEB.BLL.BaseLayerObject().GetDeviceObjectList(string.Format(" and deviceid={0}", query.ObjectId), " order by deviceid"); itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + deviceList[0].ItemCodeID + "'", " order by ItemcodeID"); if (itList.Count > 0) { unit = itList[0].Unit;//单个分类分项单位 itemCodeName = itList[0].ItemCodeName; } if (query.ItemCode == "00000") {//总能耗 unit = "T";//标准煤单位 itemCodeName = "总能耗"; } } else { itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); if (itList.Count > 0) { dept = itList[0].Unit; } unit = icode == "00000" ? "T" : dept; itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName; } //query.QueryType = tempQueryType; //switch (query.QueryType) //{ // case EnergyAnalyseQueryType.Convert2Co2: // case EnergyAnalyseQueryType.Convert2Coal: // unit = "T";//标准煤单位 // break; // case EnergyAnalyseQueryType.Convert2Money: // unit = "元";//标准煤单位 // break; //} try { if (resultAll.OrderLst.Count > 0) { DataTable dt = TableView.CreateOrderBaseDataTable(); for (var r = 0; r < resultAll.OrderLst.Count; r++) { DataRow dr = dt.NewRow(); dr[1] = resultAll.OrderLst[r].Tm; dr[2] = query.ObjectId; dr[3] = resultAll.OrderLst[r].Obj; dr[4] = resultAll.OrderLst[r].Tm; dr[5] = resultAll.OrderLst[r].Tm; dr[6] = itemCodeName; dr[7] = decimal.Round(decimal.Parse(resultAll.OrderLst[r].Val.ToString()), 2).ToString(); if (query.IsDevice == 0) { dr[8] = resultArea.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultArea.OrderLst[r].Val.ToString()), 2).ToString(); dr[9] = resultPerson.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultPerson.OrderLst[r].Val.ToString()), 2).ToString(); } dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗分析表.xls"; if (query.IsDevice == 1) { templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗分析表_设备.xls"; } TemplateParam param = new TemplateParam(resultAll.OrderLst[0].Obj + " " + itemCodeName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0)); param.DataColumn = new[] { 0, 3, 1, 7, 8, 9 }; if (query.IsDevice == 1) { param.DataColumn = new[] { 0, 3, 1, 7 }; } param.ItemUnit = "(单位:" + unit + ")"; param.ItemUnitCell = new CellParam(3, 5); param.SortColumn = 0; dt.TableName = "能耗分析表"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportExcelDataRanking() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<NTS.WEB.DataContact.QueryOrder>(inputValue); query.PageCurrent = 1; query.PageSize = 10000; string icode = query.ItemCode; var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery") .GetShopOrder(query); query.Particle = "area"; var result2 = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery") .GetShopOrder(query); var dept = string.Empty; var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); if (itList.Count > 0) { dept = itList[0].Unit; } string itemName = icode == "00000" ? "T" : dept; var ItemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID"); string ItemName = query.ItemCode == "00000" ? "总能耗" : ItemList[0].ItemCodeName; try { string unittype = string.IsNullOrEmpty(HttpContext.Current.Request["unittype"]) ? "" : HttpContext.Current.Request["unittype"];// string type = "0"; string name = ""; switch (type) { case "0": name = "总能耗"; break; case "1": name = "面积能耗"; break; case "2": name = "人均能耗"; break; } if (query.ObjectNum.Count > 0) { DataTable dt = TableView.CreateOrderBaseDataTable(); for (var r = 0; r < result.OrderList.Count; r++) { DataRow dr = dt.NewRow(); dr[1] = result.OrderList[r].Title; dr[2] = query.ObjectNum; dr[3] = result.OrderList[r].Title; dr[4] = result.OrderList[r].Title; dr[5] = result.OrderList[r].Title; dr[6] = itemName; dr[7] = decimal.Round(decimal.Parse(result.OrderList[r].Energy.ToString()), 2).ToString(); dr[8] = decimal.Round(decimal.Parse(result2.OrderList[r].Energy.ToString()), 2).ToString(); dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗排名表.xls"; TemplateParam param = new TemplateParam(ItemName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0)); param.DataColumn = new[] { 0, 3, 7, 8 }; param.ItemUnit = "(单位:" + dept + ")"; param.ItemUnitCell = new CellParam(3, 4); param.SortColumn = 0; dt.TableName = "能耗排名表"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
/// <summary> /// 导出所有能源信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public string ExportItemEnergy(string starttime, string endtime, string objectid, string timeunit, string counttype, string SavePath) { try { Itemcode itemcodeBll = new Itemcode(); ReportQueryModelNew model = new ReportQueryModelNew(); model.startime = DateTime.Parse(starttime); model.endtime = DateTime.Parse(endtime); model.objectid = objectid; model.timeunit = (ReportStyleNew)(int.Parse(timeunit)); model.objecttype = 0; model.itemcode = counttype; string strPath = SavePath; // AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; string strTemPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate\\项目能耗查询.xls"; IList<Model.Itemcode> itemcode = itemcodeBll.GetItemcodeList( " and itemcodenumber = '" + counttype + "'", ""); string strUnit = "mch"; if(itemcode.Count>0) { strUnit = itemcode[0].Unit; } #region 第2种方法 DataTable dtlist = GetItemcodeData(model); string name = GetBaseLayerObjectName(model.objectid); #endregion if (dtlist != null) { string temp_path = strPath; //AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = strTemPath; // AppDomain.CurrentDomain.BaseDirectory + "template\\区域能耗查询.xls"; string datetime = model.startime.ToString("yyyy-MM-dd") + "~" + (model.timeunit == ReportStyleNew.DayStyle ? model.endtime.ToString("yyyy-MM-dd HH:59:59") : model.endtime.ToString("yyyy-MM-dd")); TemplateParam param = new TemplateParam(name, new CellParam(0, 0), datetime, new CellParam(3, 0), false, new CellParam(4, 0)); //param.DataColumn = new[] { 0, 3, 6, 7, 8, 9}; param.DataColumn = new[] { 0, 1, 3, 6, 7 }; param.ItemUnit = "能耗值:(" + strUnit + ")"; param.ItemUnitCell = new CellParam(4, 4); dtlist.TableName = "能耗查询统计"; string strDownFile = temp_path + save_path; //return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; ExportHelper.ExportExcel(dtlist, strDownFile, strTemPath, param); //return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:没有数据!\"}"; } }
/// <summary> /// 导出所有能源信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public string ExportAllEnergy(string starttime, string endtime, string objectid, string timeunit, string SavePath) { try { ReportQueryModelNew model = new ReportQueryModelNew(); model.startime = DateTime.Parse(starttime); model.endtime = DateTime.Parse(endtime); model.objectid = objectid; model.timeunit = (ReportStyleNew)(int.Parse(timeunit)); model.objecttype = 0; string strPath = SavePath; // AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; string strTemPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate\\区域能耗查询.xls"; #region 第2种方法 DataTable dtItemCode = GetItemCode(" and itemcodenumber in ('01000','02000','03000','04000')"); DataTable dtlist = GetItemcodeDataAll(model, dtItemCode); string name = GetBaseLayerObjectName(model.objectid); #endregion if (dtlist != null) { string temp_path = strPath; //AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = strTemPath; // AppDomain.CurrentDomain.BaseDirectory + "template\\区域能耗查询.xls"; string datetime = model.startime.ToString("yyyy-MM-dd") + "~" + (model.timeunit == ReportStyleNew.DayStyle ? model.endtime.ToString("yyyy-MM-dd HH:59:59") : model.endtime.ToString("yyyy-MM-dd")); TemplateParam param = new TemplateParam(name, new CellParam(0, 0), datetime, new CellParam(3, 0), false, new CellParam(4, 0)); //param.DataColumn = new[] { 0, 3, 6, 7, 8, 9}; param.DataColumn = new[] { 0, 3, 1, 6, 7, 8, 9, 10 }; dtlist.TableName = "能耗查询统计"; string strDownFile = temp_path + save_path; ExportHelper.ExportExcel(dtlist, strDownFile, strTemPath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; //return "导出失败:当前无任何数据!"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:没有数据\"}"; } }
public string ExportAlarm() { var inputValue = _ntsPage.Request.Form["Inputs"]; var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryAlarmNew>(inputValue); var res = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IAlarmService>("AlarmService").GetAlarmList(query, ""); List<NTS.WEB.ResultView.AlarmNewList> dtRef = res.data; try { if ((dtRef != null) && (dtRef.Count > 0)) { // DataTable dtReport = TableView.CreateAlarmDataTable(); DataTable dtReport = new DataTable(); for (var r = 0; r < dtRef.Count; r++) { DataRow dr = dtReport.NewRow(); dr[1] = dtRef[r].Time.ToString(); dr[2] = dtRef[r].Object.ToString(); dr[3] = dtRef[r].Position; dr[4] = dtRef[r].Info; dr[5] = dtRef[r].AlarmItem; dr[6] = dtRef[r].Class; dr[7] = dtRef[r].AlarmStatus; dtReport.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\告警管理表.xls"; TemplateParam param = new TemplateParam("告警管理表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0)); //TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0)); param.DataColumn = new[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //param.ItemUnit = "(单位:元"; //param.ItemUnitCell = new CellParam(3, 5); dtReport.TableName = "告警管理表"; ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }
public string ExportQueryObjs() { var inputValue = _ntsPage.Request["inputs"]; ; var oderObject = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryOrderObjects>(inputValue); //var inputValue = _ntsPage.Request.Form["Inputs"]; //var tabId = int.Parse(_ntsPage.Request.Form["TabId"]); //var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryCompare>(inputValue); //var result = new ResultCompare(); QueryCompare query = new QueryCompare(); query.StartTime = oderObject.StartTime; query.EndTime = oderObject.EndTime; query.ObjectNum = oderObject.AreaIdLst; query.ItemCode = oderObject.ItemCode; query.ObjType = oderObject.ObjType; query.Unit = oderObject.Particle; var result = new ResultCompare(); switch (oderObject.QueryType) { case EnergyAnalyseQueryType.Default: result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService") .GetCompareChart(oderObject); break; case EnergyAnalyseQueryType.UnitArea: result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService") .GetAreaCompareChart(oderObject); break; case EnergyAnalyseQueryType.UnitPerson: result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService") .GetPersonNumCompareChart(oderObject); ; break; default: result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService") .GetCompareChart(oderObject); break; } string strDepName = ""; if (result.Dept.Count > 0) { strDepName = result.Dept[0].ToString(); } else { throw new Exception("没有数据信息"); } switch (oderObject.QueryType) { case EnergyAnalyseQueryType.Convert2Co2: strDepName = "T"; break; case EnergyAnalyseQueryType.Convert2Coal: strDepName = "T"; break; case EnergyAnalyseQueryType.Convert2Money: strDepName = "元"; break; } ResultContrast resultCon = JsonForHightChartsNew.IndexContrastObjsChart(query, "对比柱状图", result.ObjectName.ToArray(), strDepName, result.Enery); try { if (resultCon.ContrastLst.Count > 0) { DataTable dt = TableView.CreateContrastDataTable(); for (var r = 0; r < resultCon.ContrastLst.Count; r++) { DataRow dr = dt.NewRow(); dr[1] = resultCon.ContrastLst[r].Tm.ToString(); dr[2] = resultCon.ContrastLst[r].Obj; dr[3] = resultCon.ContrastLst[r].EneType; dr[4] = resultCon.ContrastLst[r].Val; dt.Rows.Add(dr); } string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\"; if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); string[] files = Directory.GetFiles(temp_path); foreach (string fn in files) { File.Delete(temp_path + fn); } } string save_path = DateTime.Now.Ticks + ".xls"; string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗对比表.xls"; TemplateParam param = new TemplateParam("能耗对比表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0)); //TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0)); param.DataColumn = new[] { 0, 1, 2, 3, 4 }; param.ItemUnit = "(单位:" + result.Dept[0].ToString() + ")"; param.ItemUnitCell = new CellParam(3, 4); param.SortColumn = 0; dt.TableName = "能耗对比表"; ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param); return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}"; } else { return "{\"status\":\"error\",\"msg\":\"导出失败:当前无任何数据\"}"; } } catch (Exception ex) { return "{\"status\":\"error\",\"msg\":\"导出失败:由于当前无数据或其他原因导致" + ex.Message + "\"}"; } }