Пример #1
0
        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);
            }
        }
Пример #2
0
        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));
        }
Пример #3
0
        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();
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
                }
            }
        }