/// <summary> /// Create a list of ItemBuilder objects and export them in a Excel file /// </summary> /// <param name="itemName">Item name</param> /// <returns>Result of action</returns> public ActionResult ExportHuman(string itemName, string instanceName, string connectionString) { string source = string.Format(CultureInfo.InvariantCulture, @"Excel:List({0})", itemName); this.itemBuilder = new ItemBuilder(itemName, instanceName); var res = ActionResult.NoAction; try { this.workBook = new XSSFWorkbook(); this.sheet = (XSSFSheet)this.workBook.CreateSheet(this.itemBuilder.Definition.Layout.LabelPlural); // HEADER int countCells = 0; this.CreateHeader(); ReadOnlyCollection <ItemBuilder> list = Read.Active(this.itemBuilder.Definition, instanceName); int countRows = 1; foreach (ItemBuilder item in list) { if (item == null) { continue; } ToolsXlsx.CreateRow(this.sheet, countRows); countCells = 0; foreach (ItemField field in this.itemBuilder.HumanFields) { this.sheet.GetRow(countRows).CreateCell(countCells); if (item.ContainsKey(field.Name) && item[field.Name] != null) { if (item[field.Name].GetType().Name.ToUpperInvariant() == "JOBJECT") { string data = item[field.Name].ToString(); JObject x = item[field.Name] as JObject; if (x["Description"] != null) { data = x["Description"].ToString(); } this.sheet.GetRow(countRows).GetCell(countCells).SetCellValue(data); } else { string fieldName = string.Empty; if (item.Definition.Fields.Any(f => f.Name == field.Name)) { fieldName = field.Name; } else { if (item.Definition.Fields.Any(f => f.Name == field.Name + "Id")) { fieldName = string.Format(CultureInfo.InvariantCulture, "{0}Id", field.Name); } fieldName = field.Name; } if (!string.IsNullOrEmpty(fieldName)) { ItemField fieldDefinition = item.Definition.Fields.Where(f => f.Name == fieldName).First(); this.SetCellValue(countRows, countCells, item[fieldName], fieldDefinition.DataType); } } } countCells++; } countRows++; } string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith("\\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}", path); } path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}\Temp\{1}.{2}", path, this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension); string link = string.Format(CultureInfo.GetCultureInfo("en-us"), @"/Temp/{0}.{1}", this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension); using (FileStream fs = new FileStream(path, FileMode.Create)) { this.workBook.Write(fs); } res.SetSuccess(link); } catch (IOException ex) { res.SetFail(ex); ExceptionManager.Trace(ex, source); } catch (NullReferenceException ex) { res.SetFail(ex); ExceptionManager.Trace(ex, source); } catch (NotSupportedException ex) { res.SetFail(ex); ExceptionManager.Trace(ex, source); } return(res); }
public static ReadOnlyCollection <ItemBuilder> GetActive(string itemName, string instanceName) { return(Read.Active(ItemDefinition.Load(itemName, instanceName), instanceName)); }
/// <summary>Continues PageLoad execution if session is alive</summary> private void Go() { var d0 = DateTime.Now; string itemName = this.Request.QueryString["ItemName"]; string instanceName = this.Request.QueryString["InstanceName"]; bool external = false; var instance = CustomerFramework.Load(instanceName); var definition = ItemDefinition.Load(itemName, instanceName); var data = Read.Active(definition, instance.Name); if (this.Request.QueryString.AllKeys.Count() > 3) { var temporalData = data.ToList(); if (this.Request.QueryString.AllKeys.Any(k => k == "InstanceName")) { external = true; foreach (string key in this.Request.QueryString.AllKeys.Where(k => k != "ItemName")) { if (!key.Equals("ItemName", StringComparison.OrdinalIgnoreCase)) { continue; } var localKey = key.Remove('_'); object query = this.Request.QueryString[localKey]; if (query.ToString().Contains("EXCLUDE")) { long val = Convert.ToInt64(query.ToString().Split('/')[1]); temporalData = temporalData.Where(d => Convert.ToInt64(d[localKey]) != val).ToList(); } else if (query.GetType().ToString().ToUpperInvariant() == "SYSTEM.STRING") { if (query.ToString().Trim().ToUpperInvariant().Equals("NULL", StringComparison.OrdinalIgnoreCase) || query.ToString().Trim().ToUpperInvariant().Equals("0", StringComparison.OrdinalIgnoreCase)) { temporalData = temporalData.Where(d => d[localKey] == null || d[localKey].ToString().Trim().Equals("0", StringComparison.OrdinalIgnoreCase)).ToList(); } else { temporalData = temporalData.Where(d => d[localKey] != null && d[localKey].ToString().Equals(query.ToString())).ToList(); } } else { temporalData = temporalData.Where(d => d[localKey] != null && Convert.ToInt64(d[localKey]) == Convert.ToInt64(query)).ToList(); } } data = new ReadOnlyCollection <ItemBuilder>(temporalData); } else { foreach (string key in this.Request.QueryString.AllKeys.Where(k => k != "ItemName")) { var localKey = key; if (localKey == "callback") { external = true; } if (localKey == "format" || localKey == "InstanceName" || localKey == "callback" || localKey == "_" || string.IsNullOrEmpty(localKey)) { continue; } if (key.Equals(itemName + "Id")) { localKey = "Id"; } localKey = localKey.Replace("_", string.Empty); object query = this.Request.QueryString[key]; if (query.ToString().Contains("EXCLUDE")) { long val = Convert.ToInt64(query.ToString().Split('/')[1]); temporalData = temporalData.Where(d => Convert.ToInt64(d[localKey]) != val).ToList(); } else if (query.GetType().ToString().ToUpperInvariant() == "SYSTEM.STRING") { if (query.ToString().Trim().ToUpperInvariant().Equals("NULL", StringComparison.OrdinalIgnoreCase) || query.ToString().Trim().ToUpperInvariant().Equals("0", StringComparison.OrdinalIgnoreCase)) { temporalData = temporalData.Where(d => d[localKey] == null || d[localKey].ToString().Trim().Equals("0", StringComparison.OrdinalIgnoreCase)).ToList(); } else { temporalData = temporalData.Where(d => d[localKey] != null && d[localKey].ToString().Equals(query.ToString())).ToList(); } } else { temporalData = temporalData.Where(d => d[localKey] != null && Convert.ToInt64(d[localKey]) == Convert.ToInt64(query)).ToList(); } } data = new ReadOnlyCollection <ItemBuilder>(temporalData); } } var d1 = DateTime.Now; this.Response.Clear(); this.Response.ContentType = "application/json"; var d2 = DateTime.Now; if (external) { string res = string.Format( CultureInfo.InvariantCulture, @"{0}", ItemBuilder.ListItemJson(data).Replace("\n", string.Empty).Replace("\r", string.Empty).Replace("\\", string.Empty).Replace(@"""""""""", @"""""")); this.Response.Write(res); } else { this.Response.Write(@"{""data"":"); this.Response.Write(ItemBuilder.ListItemJson(data)); this.Response.Write(string.Format(CultureInfo.GetCultureInfo("en-us"), @",""Time"":""{0:#,##0} - {1:#,##0}""}}", (d1 - d0).TotalMilliseconds, (d2 - d1).TotalMilliseconds)); } this.Response.Flush(); this.Response.SuppressContent = true; this.ApplicationInstance.CompleteRequest(); }