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);
}
}
}
