public ActionResult ExportFile(String fileType, ContentIdentifier cid, User user) { var helper = GetHelper(cid.PortletId.Value); List <String> allowedTypes = AllowedExports(helper); if (allowedTypes.Contains(fileType)) { DataTable dt; try { dt = GetData(helper); } catch (Exception ex) { return(View("Error", new Error { ErrorMessage = "An error occurred while querying the database.", Exception = _userFacade.FindByUsername(user.Username).IsSiteAdmin ? ex.ToString().Replace("\n", "<br />") : String.Empty })); } var strContentType = "text/plain"; // these defaults will be overwritten if we're successful var strFilename = "ErrorOutput.txt"; byte[] byteArray; using (var mstream = new MemoryStream()) { using (var sw = new StreamWriter(mstream)) { string fileName; if (helper.GetSetting("QueryTitle").Value.Trim().Length > 0) { fileName = Regex.Replace(helper.GetSetting("QueryTitle").Value.Trim(), @"\W", ""); } //remove non-alphanumeric characters from filename else { fileName = "ExportedData"; } switch (fileType) { case "Xls": var dgResults = OutputHelper.CreateDataGrid(); OutputHelper.ConfigureDataGrid(ref dgResults, dt, helper.GetSetting("GOGridShowColumnHeadings", false). BoolValue, helper.GetSetting("GOGridAltRowColors", false).BoolValue, helper.GetSetting("GOGridShowGridlines", false).BoolValue, Convert.ToInt16( helper.GetSetting("GOGridCellPadding", 5).Value), helper.GetSetting("ColumnLabels").Value); dgResults.DataSource = dt; dgResults.DataBind(); var stringWrite = new StringWriter(); var htmlWrite = new HtmlTextWriter(stringWrite); dgResults.RenderControl(htmlWrite); htmlWrite.Flush(); sw.WriteLine(stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", "")); strContentType = "application/vnd.ms-excel"; strFilename = fileName + ".xls"; break; case "Xml": sw.WriteLine(OutputHelper.RenderXml(dt)); strContentType = "text/xml"; strFilename = fileName + ".xml"; break; case "Csv": sw.WriteLine(OutputHelper.RenderCsv(dt, helper.GetSetting("GOGridShowColumnHeadings", false) . BoolValue, helper.GetSetting("ColumnLabels").Value)); strContentType = "text/csv"; strFilename = fileName + ".csv"; break; case "Literal": sw.WriteLine(OutputHelper.RenderLiteral(dt, helper.GetSetting("ExportLiteralPattern", "{0}") . Value)); strContentType = "text/plain"; strFilename = fileName + ".txt"; break; } sw.Flush(); sw.Close(); byteArray = mstream.ToArray(); mstream.Flush(); mstream.Close(); } } return(File(byteArray, strContentType, strFilename)); } return(View("Error", new Error { ErrorMessage = "File Export Type Not Allowed" })); }
public object RunQueryHTML(Guid _portletID) { Portlet portlet = ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID); if (portlet.ParentPage.CanView(PortalUser.Current)) { try { var mapper = new NHSimpleQuerySettingsMapper(); var settings = mapper.GetSettings(_portletID).ToList(); var _helper = new SettingsHelper(settings, _portletID, mapper); try { DataTable dt = GetData(_portletID); String html = string.Empty; if (dt != null && dt.Rows.Count > 0) { if (_helper.GetSetting("JICSAllowExports").BoolValue) { HttpContext.Current.Session["sqhtml+" + PortalUser.Current.ID.AsGuid + _portletID] = dt; } if (Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value) > 0) { dt = dt.AsEnumerable().Take(Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value)).CopyToDataTable(); } switch (_helper.GetSetting("JICSOutput", "grid").Value) { case "grid": var dgResults = OutputHelper.CreateDataGrid(); OutputHelper.ConfigureDataGrid(ref dgResults, dt, _helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue, _helper.GetSetting("JICSGridAltRowColors", false).BoolValue, _helper.GetSetting("JICSGridShowGridlines", false).BoolValue, Convert.ToInt16(_helper.GetSetting("JICSGridCellPadding", 5).Value), _helper.GetSetting("ColumnLabels").Value); dgResults.DataSource = dt; dgResults.DataBind(); var stringWrite = new StringWriter(); var htmlWrite = new HtmlTextWriter(stringWrite); dgResults.RenderControl(htmlWrite); htmlWrite.Flush(); html = stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", ""); break; case "xml": html = "<pre>" + HttpUtility.HtmlEncode(OutputHelper.RenderXml(dt)) + "</pre>"; break; case "csv": html = "<pre>" + OutputHelper.RenderCsv(dt, _helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue, _helper.GetSetting("ColumnLabels").Value) + "</pre>"; break; case "literal": html = OutputHelper.RenderLiteral(dt, _helper.GetSetting("ExportLiteralPattern", "{0}").Value); break; } return(new { success = true, resultFormat = _helper.GetSetting("JICSOutput", "grid").Value, html }); } return(new { success = true, resultFormat = _helper.GetSetting("JICSOutput", "grid").Value, html, query = (PortalUser.Current.IsSiteAdmin ? _helper.GetSetting("QueryText").Value : "") }); } catch (Exception ex) { return(new { success = false, message = "Query Failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.StackTrace : "") }); } } catch (Exception ex) { return(new { success = false, message = "Connection failed before query was executed." + (PortalUser.Current.IsSiteAdmin ? ": " + ex.StackTrace : "") }); } } return(new { success = false, message = "You do not have permissions to view this portlet" }); }
private void Page_Load(object sender, System.EventArgs e) { var strContentType = "text/plain"; // these defaults will be overwritten if we're successful var strFilename = "ErrorOutput.txt"; var mstream = new MemoryStream(); var sw = new StreamWriter(mstream); if (Request.QueryString["sqkey"] != null && IsGuidFormat(Request.QueryString["sqkey"].ToString())) { var strKey = PortalUser.Current.ID.AsGuid + Request.QueryString["sqkey"]; var strFormat = Request.QueryString["format"]; if (HttpContext.Current.Session["sqhtml+" + strKey] != null && strFormat != null) { try { DataTable dt = (DataTable)HttpContext.Current.Session["sqhtml+" + strKey]; var mapper = new NHSimpleQuerySettingsMapper(); var settings = mapper.GetSettings(new Guid(Request.QueryString["sqkey"])).ToList(); var _helper = new SettingsHelper(settings, new Guid(Request.QueryString["sqkey"]), mapper); string fileName; if (_helper.GetSetting("QueryTitle").Value.Trim().Length > 0) { fileName = Regex.Replace(_helper.GetSetting("QueryTitle").Value.Trim(), @"\W", "");//remove non-alphanumeric characters from filename } else { fileName = "ExportedData"; } switch (strFormat) { case "xls": var dgResults = OutputHelper.CreateDataGrid(); OutputHelper.ConfigureDataGrid(ref dgResults, dt, _helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue, _helper.GetSetting("JICSGridAltRowColors", false).BoolValue, _helper.GetSetting("JICSGridShowGridlines", false).BoolValue, Convert.ToInt16(_helper.GetSetting("JICSGridCellPadding", 5).Value), _helper.GetSetting("ColumnLabels").Value); dgResults.DataSource = dt; dgResults.DataBind(); var stringWrite = new StringWriter(); var htmlWrite = new HtmlTextWriter(stringWrite); dgResults.RenderControl(htmlWrite); htmlWrite.Flush(); sw.WriteLine(stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", "")); strContentType = "application/vnd.ms-excel"; strFilename = fileName + ".xls"; break; case "xml": sw.WriteLine(OutputHelper.RenderXml(dt)); strContentType = "text/xml"; strFilename = fileName + ".xml"; break; case "csv": sw.WriteLine(OutputHelper.RenderCsv(dt, _helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue, _helper.GetSetting("ColumnLabels").Value)); strContentType = "text/csv"; strFilename = fileName + ".csv"; break; case "literal": sw.WriteLine(OutputHelper.RenderLiteral(dt, _helper.GetSetting("ExportLiteralPattern", "{0}").Value)); strContentType = "text/plain"; strFilename = fileName + ".txt"; break; } } catch (Exception ex) { sw.WriteLine("Export failed. Please contact site adminstrator. (cache corrupted)" + (PortalUser.Current.IsSiteAdmin ? ex.ToString() : "")); } } else { sw.WriteLine("Export failed. Please contact site adminstrator. (cache empty)"); } } else { sw.WriteLine("Export failed. Please contact site adminstrator. (bad key)"); } sw.Flush(); sw.Close(); byte[] byteArray = mstream.ToArray(); mstream.Flush(); mstream.Close(); Response.Clear(); Response.AddHeader("Content-Type", strContentType); Response.AddHeader("Content-Disposition", "attachment; filename=" + strFilename); Response.AddHeader("Content-Length", byteArray.Length.ToString()); Response.ContentType = "application/octet-stream"; Response.BinaryWrite(byteArray); Response.End(); }