public virtual object GetQueryData(HttpContext context) { YZRequest request = new YZRequest(context); String datasourceName = request.GetString("DataSource", null); String query = request.GetString("Query"); QueryParameterCollection queryParams = JArray.Parse(request.GetString("QueryParams")).ToObject <QueryParameterCollection>(); YZClientParamCollection @params = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(request.GetString("params", YZJsonHelper.Base64EmptyJArray)))).ToObject <YZClientParamCollection>(); bool clientCursor = request.GetBool("clientCursor", false); //应用查询条件 BPMDBParameterCollection finallyParams = queryParams.CreateNullDBParameters(); foreach (BPMDBParameter @param in finallyParams) { YZClientParam clientParam = @params.TryGetItem(@param.Name); if (clientParam != null && clientParam.value != null) { @param.Value = clientParam.value; } } FlowDataTable table = new FlowDataTable(); int rowcount; using (BPMConnection cn = new BPMConnection()) { cn.WebOpen(); table.Load(cn, BPMCommandType.Query, query, finallyParams, clientCursor, request.Start, request.Limit, out rowcount); } return(new { total = rowcount, children = table.ToDataTable() }); }
public virtual void ExportGrid2Excel(HttpContext context) { YZRequest request = new YZRequest(context); bool dynamicParams = request.GetBool("dynamicParams", false); //获得数据 - jsonResponse string jsonResponse; JObject jRequest = JObject.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(context.Request.Params["request"]))); string url = (string)jRequest["url"]; url = "~/" + url; JToken jToken; using (StringWriter sw = new StringWriter()) { HtmlTextWriter writer = new HtmlTextWriter(sw); HttpResponse response = new HttpResponse(writer); List <string> queryParams = new List <string>(); queryParams.Add("DateFormat=text"); foreach (KeyValuePair <string, JToken> property in (jRequest["params"] as JObject)) { queryParams.Add(property.Key + "=" + HttpUtility.UrlEncode((string)property.Value, Encoding.Default)); } HttpRequest callrequest = new HttpRequest(null, context.Request.Url.ToString(), String.Join("&", queryParams.ToArray())); HttpContext callcontext = new HttpContext(callrequest, response); IHttpHandler handler = PageParser.GetCompiledPageInstance(url, context.Server.MapPath(url), context); handler.ProcessRequest(callcontext); jsonResponse = sw.ToString(); jToken = JToken.Parse(jsonResponse); if (jToken is JObject) { JObject jObject = jToken as JObject; if (jObject["success"] != null && jObject["success"].Type == JTokenType.Boolean && (bool)jObject["success"] == false) { throw new Exception((string)jObject["errorMessage"]); } } } //将数据转化为Table DataTable table; string rootProperty = request.GetString("rootProperty", null); JArray jTable; if (String.IsNullOrEmpty(rootProperty)) { jTable = jToken as JArray; } else { jTable = (jToken as JObject)[rootProperty] as JArray; } foreach (JObject jRow in jTable) { foreach (KeyValuePair <string, JToken> jProperty in jRow) { if (jProperty.Value is JArray) { jRow[jProperty.Key] = Convert.ToString(jProperty.Value); } if (jProperty.Value is JObject) { jRow[jProperty.Key] = Convert.ToString(jProperty.Value); } } } table = jTable.ToObject <DataTable>(); table.TableName = "GridStore"; //SQL Server数据库中monery4位小数点处理 foreach (DataRow row in table.Rows) { foreach (DataColumn column in table.Columns) { object value = row[column]; if (value is decimal) { value = (decimal)Decimal.ToDouble((decimal)value); } row[column] = value; } } //生成Excel string templateExcel = request.GetString("templateExcel", String.Empty); HSSFWorkbook book = null; if (String.IsNullOrEmpty(templateExcel)) { book = this.NoTemplateExport(context, table); } else { if (!templateExcel.StartsWith("~/")) { templateExcel = "~/" + templateExcel; } Dictionary <string, string> reportParams = new Dictionary <string, string>(); //获得查询参数 foreach (KeyValuePair <string, JToken> property in (jRequest["params"] as JObject)) { reportParams.Add(property.Key, (string)property.Value); } if (!reportParams.ContainsKey("Kwd")) { reportParams.Add("Kwd", ""); } if (dynamicParams) { string strRuntimeParams = reportParams["params"]; if (!String.IsNullOrEmpty(strRuntimeParams)) { YZClientParamCollection runtimeParams = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(strRuntimeParams))).ToObject <YZClientParamCollection>(); foreach (YZClientParam clientParams in runtimeParams) { reportParams[clientParams.name] = Convert.ToString(clientParams.value); } } } //打开文件 using (FileStream file = new FileStream(context.Server.MapPath(templateExcel), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { book = new HSSFWorkbook(file); } DataSet dataset = new DataSet(); dataset.Tables.Add(table); YZExcelGenerate.Fill(book, reportParams, dataset); YZExcelGenerate.PrepareForOutput(book); } //Excel文件保存到流 byte[] bytes; using (MemoryStream ms = new MemoryStream()) { book.Write(ms); bytes = ms.ToArray(); } //导出文件名 string fileName = context.Request.Params["fileName"]; if (String.IsNullOrEmpty(fileName)) { fileName = "Export"; } fileName += YZStringHelper.DateToString(DateTime.Now) + ".xls"; this.ProcessResponseHeader(context, fileName, true); context.Response.BinaryWrite(bytes); //this.OnExported(context, table); //设置Response头 //context.Response.Clear(); //context.Response.ContentType = "application/vnd.ms-excel"; //context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); //context.Response.AppendHeader("Content-Length", bytes.Length.ToString()); //context.Response.BinaryWrite(bytes); //context.Response.End(); }
protected virtual void ApplyExcelData(YZRequest request, JObject rv, ReportExcelView view, DataTable dataTable, YZClientParamCollection queryParams) { string excelTemplate = request.Context.Server.MapPath("~/YZSoft/Report/Templates/" + view.TemplateFile); Dictionary <string, string> reportParams = new Dictionary <string, string>(); reportParams.Add("ReportDate", YZStringHelper.DateToStringL(DateTime.Today)); foreach (YZClientParam queryParam in queryParams) { reportParams.Add(queryParam.name, Convert.ToString(queryParam.value)); } dataTable.TableName = "GridStore"; //打开文件 HSSFWorkbook book; using (FileStream file = new FileStream(excelTemplate, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { book = new HSSFWorkbook(file); } //填充数据 DataSet dataset = new DataSet(); dataset.Tables.Add(dataTable); YZExcelGenerate.Fill(book, reportParams, dataset); YZExcelGenerate.PrepareForOutput(book); //调试输出 //using (FileStream fs = new FileStream(@"e:\abc.xls", FileMode.Create)) //{ // book.Write(fs); // fs.Close(); //} book.GetSheetAt(0).DisplayGridlines = false; using (MemoryStream stream = new MemoryStream()) { book.Write(stream); string htmlFile = FileConvert.Excel2Html(stream, Path.GetExtension(excelTemplate)); rv["htmlFile"] = Path.GetFileName(htmlFile); } }
public virtual JObject GetReportData(HttpContext context) { YZRequest request = new YZRequest(context); string path = request.GetString("path"); string viewName = request.GetString("viewName", null); string outputType = request.GetString("outputType", ""); YZClientParamCollection runtimeParams = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(request.GetString("params", YZJsonHelper.Base64EmptyJArray)))).ToObject <YZClientParamCollection>(); //获得数据 Report report; ReportView view; DataTable dataTable; int rowcount; using (BPMConnection cn = new BPMConnection()) { cn.WebOpen(); //获得报表定义 report = Report.Open(cn, path); if (String.IsNullOrEmpty(viewName)) { view = report.DefaultView; } else { view = report.Views.TryGetItem(viewName); } string srcdata = request.GetString("srcdata", null); if (String.IsNullOrEmpty(srcdata)) { BPMDBParameterCollection selectParameters = report.QueryParameters.CreateNullDBParameters(); //应用查询条件 foreach (BPMDBParameter selectParam in selectParameters) { YZClientParam clientParam = runtimeParams.TryGetItem(selectParam.Name); if (clientParam != null && clientParam.value != null) { selectParam.Value = clientParam.value; } } cn.RequestParams["sortstring"] = request.GetSortString(""); cn.UpdateRequestParams(); //获得数据 FlowDataTable ftable = new FlowDataTable(); ftable.Load(cn, BPMCommandType.Report, path, selectParameters, report.ClientCursor, request.Start, request.Limit, out rowcount); dataTable = ftable.ToDataTable(); } else { dataTable = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(srcdata))).ToObject <DataTable>(); rowcount = request.GetInt32("total"); } } //将数据转化为Json集合 JObject rv = new JObject(); rv[YZJsonProperty.total] = rowcount; rv["srcdata"] = Convert.ToBase64String(Encoding.UTF8.GetBytes(JArray.FromObject(dataTable).ToString(Formatting.None, YZJsonHelper.Converters))); JArray children = new JArray(); rv.Add("children", children); //不管是什么view都要给数据 foreach (DataRow row in dataTable.Rows) { JObject item = new JObject(); children.Add(item); foreach (DataColumn column in dataTable.Columns) { object value = row[column.ColumnName]; item[column.ColumnName] = JToken.FromObject(value); if (NameCompare.EquName(column.ColumnName, "TaskID") && (value is int)) { item["Token"] = YZSecurityHelper.GenTaskAccessToken((int)value); } //为任务链接生成Token ReportColumnInfo colInfo = report.ReportColumnInfos.TryGetItem(column.ColumnName); if (colInfo != null && colInfo.LinkType == ReportLinkType.Task) { ParameterFill paramFill = colInfo.ParametersFill.TryGetItem("@TaskID"); if (paramFill != null) { int linktoTaskID; if (Int32.TryParse(Convert.ToString(row[paramFill.FillWith]), out linktoTaskID)) { item[column.ColumnName + "Token"] = YZSecurityHelper.GenTaskAccessToken((int)linktoTaskID); } } } } } if (String.Compare(outputType, "Export", true) != 0) { if (view is ReportMSChartView) { this.ApplyMSChartData(request, rv, view as ReportMSChartView, dataTable); } if (view is ReportExcelView) { this.ApplyExcelData(request, rv, view as ReportExcelView, dataTable, runtimeParams); } } return(rv); }