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); } }
protected virtual HSSFWorkbook NoTemplateExport(HttpContext context, DataTable table) { YZRequest request = new YZRequest(context); string strGridColumns = request.GetString("columns", null); //获得列定义信息 YZExcelGenerate.ColumnDefineCollection columnDefines = new YZExcelGenerate.ColumnDefineCollection(); if (!String.IsNullOrEmpty(strGridColumns)) { JArray jColumns = JArray.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(strGridColumns))); foreach (JObject jColumn in jColumns) { YZExcelGenerate.ColumnDefine columnDefine = new YZExcelGenerate.ColumnDefine(); columnDefine.Text = (string)jColumn["text"]; columnDefine.ColumnName = (string)jColumn["dataIndex"]; columnDefine.Width = (int)jColumn["width"]; switch (((string)jColumn["align"]).ToLower()) { case "center": columnDefine.Align = YZNPOI.SS.UserModel.HorizontalAlignment.CENTER; break; case "right": columnDefine.Align = YZNPOI.SS.UserModel.HorizontalAlignment.RIGHT; break; default: columnDefine.Align = YZNPOI.SS.UserModel.HorizontalAlignment.LEFT; break; } if (table.Columns.Contains(columnDefine.ColumnName)) { columnDefines.Add(columnDefine); } } } else { foreach (DataColumn column in table.Columns) { YZExcelGenerate.ColumnDefine columnDefine = new YZExcelGenerate.ColumnDefine(); if (String.Compare(column.ColumnName, "ROWSTAT", true) == 0) { continue; } columnDefine.Text = column.ColumnName; columnDefine.ColumnName = column.ColumnName; columnDefine.Width = 100; columnDefines.Add(columnDefine); } } return(YZExcelGenerate.NoTemplateExport(columnDefines, table)); }
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 AttachmentInfo GenerateFileReport(HttpContext context, GetFileReportDataHandler handler) { YZRequest request = new YZRequest(context); string fileid = request.GetString("fileid"); string template = request.GetString("template"); int tagfolderid = request.GetInt32("tagfolderid"); string name = request.GetString("name"); string method = request.GetString("method"); string reportType = method.Substring(8, method.Length - 14); JObject jPost = request.GetPostData <JObject>(); JObject jProcess = (JObject)jPost["process"]; JObject jChart = (JObject)jPost["chart"]; Image chart = this.GetImage(jChart); string templatePath = context.Server.MapPath(String.Format("~/YZSoft/BPA/processreport/templates/{0}/{1}", reportType, template)); TemplateFileType templeteFileType = this.GetTemplateFileType(template); string ext = System.IO.Path.GetExtension(template); AttachmentInfo attachment = new AttachmentInfo(); attachment.Name = name + ext; attachment.Ext = ext; DataSet dataset = handler.Invoke(fileid, tagfolderid, jProcess, chart); switch (templeteFileType) { case TemplateFileType.Excel: HSSFWorkbook book; using (System.IO.FileStream stream = new System.IO.FileStream(templatePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite)) { book = new HSSFWorkbook(stream); } YZExcelGenerate.Fill(book, null, dataset); YZExcelGenerate.PrepareForOutput(book); attachment = AttachmentManager.SaveAsAttachment(book, attachment); break; default: XWPFDocument doc; using (System.IO.FileStream stream = new System.IO.FileStream(templatePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite)) { doc = new XWPFDocument(stream); } WordGenerator.Fill(doc, dataset); attachment = AttachmentManager.SaveAsAttachment(doc, attachment); break; } YZSoft.FileSystem.File file = new YZSoft.FileSystem.File(); file.FolderID = tagfolderid; file.FileID = attachment.FileID; file.AddBy = YZAuthHelper.LoginUserAccount; file.AddAt = DateTime.Now; file.Flag = "Generate"; using (IYZDbProvider provider = YZDbProviderManager.DefaultProvider) { using (IDbConnection cn = provider.OpenConnection()) { file.OrderIndex = YZSoft.FileSystem.DirectoryManager.GetFileNextOrderIndex(provider, cn, tagfolderid); FileSystem.DirectoryManager.Insert(provider, cn, file); } } return(attachment); }
public virtual JObject GenExcelReport(HttpContext context) { YZRequest request = new YZRequest(context); string excelTemplate = request.GetString("ExcelFile"); Dictionary <string, string> reportParams = new Dictionary <string, string>(); //获得查询参数 string strUserParamNames = request.GetString("UserParamNames", null); if (!String.IsNullOrEmpty(strUserParamNames)) { string[] paramNames = strUserParamNames.Split(','); foreach (string paramName in paramNames) { reportParams.Add(paramName, request.GetString(paramName, null)); } } //传递页信息 reportParams.Add("RowNumStart", request.RowNumStart.ToString()); reportParams.Add("RowNumEnd", request.RowNumEnd.ToString()); //打开文件 HSSFWorkbook book; using (FileStream file = new FileStream(context.Server.MapPath(excelTemplate), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { book = new HSSFWorkbook(file); } //填充数据 DataSet dataset = YZExcelGenerate.Fill(book, reportParams, null); YZExcelGenerate.PrepareForOutput(book); //调试输出 //using (FileStream fs = new FileStream(@"e:\abc.xls", FileMode.Create)) //{ // book.Write(fs); // fs.Close(); //} string outputType = context.Request.Params["outputType"]; if (outputType == "Export") //导出 { //Excel文件保存到流 byte[] bytes; using (MemoryStream ms = new MemoryStream()) { book.Write(ms); bytes = ms.ToArray(); } //设置Response头 string fileName = String.Format("{0}-{1}{2}", Path.GetFileNameWithoutExtension(excelTemplate), YZStringHelper.DateToString(DateTime.Now), Path.GetExtension(excelTemplate)); 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(); return(null); } else { JObject rv = new JObject(); 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); //模拟输出一个store rv[YZJsonProperty.total] = 0; JArray children = new JArray(); rv["children"] = children; foreach (DataTable table in dataset.Tables) { if (table.Columns.Contains("TotalRows")) { int totalRows = 0; if (table.Rows.Count == 0) { totalRows = 0; } else { totalRows = Convert.ToInt32(table.Rows[0]["TotalRows"]); } rv[YZJsonProperty.total] = totalRows; children = new JArray(); rv["children"] = children; JObject item = new JObject(); children.Add(item); for (int i = 0; i < table.Rows.Count; i++) { item["id"] = i; } break; } } return(rv); } } }