public byte[] GetExcelTemplate(string excelkey) { IExcelMetadataStorage storage = new DefaultExcelMetadataStorage(); var metadata = storage.GetMetadataByKey(excelkey); IExporter exporter = new AsposeExcelExporter(); var dt = new DataTable(); var buffer = exporter.Export(dt, metadata.FileBuffer); return(buffer); }
public virtual ActionResult ExportExcel(string jsonColumns, string reportSettings, QueryBuilder qb) { var columns = JsonConvert.DeserializeObject <List <ColumnInfo> >(jsonColumns); HttpContext.Items["__ColumnInfo"] = columns; var exporter = new AsposeExcelExporter(); byte[] templateBuffer = exporter.ParseTemplate(columns, mainTable, title); qb.PageSize = int.MaxValue; var dt = getReportData(qb); dt.TableName = mainTable; var buffer = exporter.Export(dt, templateBuffer); return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls")); }
private void btnDownTemplate_Click(object sender, EventArgs e) { string templatePath = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel.xlsx"; if (!File.Exists(templatePath)) { MessageBox.Show("没有找到Excel模板", "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error); } SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel表格(*.xls)|*.xls"; sfd.FilterIndex = 1; sfd.RestoreDirectory = true; if (sfd.ShowDialog() == DialogResult.OK) { var exporter = new AsposeExcelExporter(); var dt = new DataTable(); byte[] buffer = null; using (var fileStream = new FileStream(templatePath, FileMode.Open)) { var fileSize = fileStream.Length; buffer = new byte[fileSize]; fileStream.Read(buffer, 0, (int)fileSize); fileStream.Close(); } var excelBuffer = exporter.Export(dt, buffer); var ocalFilePath = sfd.FileName.ToString(); using (FileStream fs = new FileStream(ocalFilePath, FileMode.Create)) { BinaryWriter bw = new BinaryWriter(fs); bw.Write(excelBuffer, 0, excelBuffer.Length); bw.Close(); fs.Close(); } } }
// Post ExportISO 导出ISO表单 public ActionResult ExportForm(string Id, string tmplKey, string title, string typeName) { Formula.LogWriter.Info(string.Format("ExportISO - 导出ISO单的Key:{0} - 开始", tmplKey)); // 获取导出Excel的数据源 var dic = new Dictionary <string, object>(); string mapPath = Server.MapPath("..") + "\\bin\\{0}.dll"; string root = Request.ApplicationPath.Split('/')[1]; Type t = null; if (System.IO.File.Exists(string.Format(mapPath, root + ".Logic"))) { Assembly assLogic = Assembly.LoadFrom(string.Format(mapPath, root + ".Logic")); t = assLogic.GetType(typeName); } if (t == null) { if (System.IO.File.Exists(string.Format(mapPath, root))) { Assembly assWeb = Assembly.LoadFrom(string.Format(mapPath, root)); t = assWeb.GetType(typeName); } if (t == null) { throw new Exception("不存在对应的类名(包括命名空间)"); } } if (Array.IndexOf(t.GetInterfaces(), typeof(IExporterForm)) == -1) { throw new Exception(typeName + "没有继承" + typeof(IExporterForm).Name); } IExporterForm exporterForm = (IExporterForm)t.Assembly.CreateInstance(t.FullName); var row = exporterForm.GetMasterData(tmplKey, title, Id); foreach (DataColumn c in row.Table.Columns) { dic.Add(c.ColumnName, row[c.ColumnName]); } var ds = exporterForm.GetDetailData(tmplKey, title, Id); // 查找模板 byte[] templateBuffer = null; var path = System.Configuration.ConfigurationManager.AppSettings["ExcelTemplatePath"]; var templatePath = path.EndsWith("\\") ? string.Format("{0}{1}.xls", path, tmplKey) : string.Format("{0}\\{1}.xls", path, tmplKey); if (System.IO.File.Exists(templatePath)) { templateBuffer = FileHelper.GetFileBuffer(templatePath); } else { throw new Exception("没有找到对应的模板,ISO表单名称为:{0}"); } // 导出Excel文件 var exporter = new AsposeExcelExporter(); var buffer = exporter.Export(dic, ds, templateBuffer); Formula.LogWriter.Info(string.Format("ExportISO - 导出ISO单的Key:{0} - 结束", tmplKey)); if (buffer != null) { return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls")); } return(Content("导出数据失败,请检查相关配置!")); }
public ActionResult ExportInlineExcel(string jsonColumns, string excelKey, string title, string masterDataUrl, string masterColumn, string queryFormData, string sortField, string sortOrder, string detailDataUrl, string relateColumn) { LogWriter.Info(string.Format("ExportExcel - Excel导出的Key:{0} - 开始", excelKey)); #region 收集自动生成模板的列信息 var columns = JsonConvert.DeserializeObject <List <ColumnInfo> >(jsonColumns); // 清空中文名称为空的列 if (columns != null) { for (var i = columns.Count - 1; i >= 0; i--) { if (string.IsNullOrWhiteSpace(columns[i].ChineseName)) { columns.RemoveAt(i); } } HttpContext.Items["__ColumnInfo"] = columns; } #endregion // 导出到Excel的数据源, DataTable dtMaster = null; if (masterDataUrl.IndexOf("[]") >= 0 || masterDataUrl.IndexOf("{") >= 0) // 前台传入Data数据 { dtMaster = JsonConvert.DeserializeObject <DataTable>(masterDataUrl); } else // 前台传入请求数据的地址 { var dic = new Dictionary <string, object>(); if (!string.IsNullOrWhiteSpace(queryFormData)) { dic.Add("queryFormData", queryFormData); } if (!string.IsNullOrWhiteSpace(sortField)) { dic.Add("sortField", sortField); } if (!string.IsNullOrWhiteSpace(sortOrder)) { dic.Add("sortOrder", sortOrder); } var serverUrl = string.Format("{0}://{1}", Request.Url.Scheme, Request.Url.Authority); dtMaster = Get <DataTable>(serverUrl, masterDataUrl, dic) ?? new DataTable(); } DataTable dtDetail = null; if (detailDataUrl.IndexOf("[]") >= 0 || detailDataUrl.IndexOf("{") >= 0) // 前台传入Data数据 { dtDetail = JsonConvert.DeserializeObject <DataTable>(detailDataUrl); } else // 前台传入请求数据的地址 { var dic = new Dictionary <string, object>(); string[] arrID = new string[dtMaster.Rows.Count]; for (int i = 0; i < dtMaster.Rows.Count; i++) { arrID[i] = Convert.ToString(dtMaster.Rows[i][masterColumn]); } string ids = string.Join(",", arrID); dic.Add("queryFormData", "{\"$IN$" + relateColumn + "\": \"" + ids + "\"}"); var serverUrl = string.Format("{0}://{1}", Request.Url.Scheme, Request.Url.Authority); dtDetail = Get <DataTable>(serverUrl, detailDataUrl, dic) ?? new DataTable(); } DataTable dt = new DataTable(); //增加从表列到主表中 foreach (DataColumn dc in dtMaster.Columns) { if (!dt.Columns.Contains(dc.ColumnName)) { dt.Columns.Add(dc.ColumnName, dc.DataType); } } foreach (DataColumn dc in dtDetail.Columns) { if (!dt.Columns.Contains(dc.ColumnName)) { dt.Columns.Add(dc.ColumnName, dc.DataType); } } //合并数据 foreach (DataRow dr in dtMaster.Rows) { DataRow[] details = dtDetail.Select(relateColumn + " = '" + Convert.ToString(dr[masterColumn]) + "'"); if (details.Count() > 0) { foreach (DataRow detail in details) { DataRow drNew = dt.NewRow(); foreach (DataColumn dc in dt.Columns) { if (dr.Table.Columns.Contains(dc.ColumnName)) { drNew[dc.ColumnName] = dr[dc.ColumnName]; } else if (detail.Table.Columns.Contains(dc.ColumnName)) { drNew[dc.ColumnName] = detail[dc.ColumnName]; } } dt.Rows.Add(drNew); } } else { DataRow drNew = dt.NewRow(); foreach (DataColumn dc in dr.Table.Columns) { drNew[dc.ColumnName] = dr[dc.ColumnName]; } dt.Rows.Add(drNew); } } dt.TableName = excelKey; var exporter = new AsposeExcelExporter(); byte[] templateBuffer = null; var path = System.Configuration.ConfigurationManager.AppSettings["ExcelTemplatePath"]; var templatePath = path.EndsWith("\\") ? string.Format("{0}{1}_New.xls", path, excelKey) : string.Format("{0}\\{1}_New.xls", path, excelKey); if (System.IO.File.Exists(templatePath)) { LogWriter.Info(string.Format("ExportExcel - 采用自定义模板,模板路径为:{0}", templatePath)); templateBuffer = FileHelper.GetFileBuffer(templatePath); } else { templateBuffer = exporter.ParseTemplate(columns, excelKey, title); } var buffer = exporter.Export(dt, templateBuffer); LogWriter.Info(string.Format("ExportExcel - Excel导出的Key:{0} - 结束", excelKey)); if (buffer != null) { return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls")); } LogWriter.Info(string.Format("ExportExcel - 导出数据失败,参数: masterDataUrl={0}<br>queryFormData={1}<br>jsonColumns={2}<br>excelKey={3}<br>title={4}<br> ", masterDataUrl, queryFormData, jsonColumns, excelKey, title)); return(Content("导出数据失败,请检查相关配置!")); }
public ActionResult ExportExcel(string dataUrl, string queryFormData, string sortField, string sortOrder, string jsonColumns, string excelKey, string title = "export") { LogWriter.Info(string.Format("ExportExcel - Excel导出的Key:{0} - 开始", excelKey)); #region 收集自动生成模板的列信息 var columns = JsonConvert.DeserializeObject <List <ColumnInfo> >(jsonColumns); // 清空中文名称为空的列 if (columns != null) { for (var i = columns.Count - 1; i >= 0; i--) { if (string.IsNullOrWhiteSpace(columns[i].ChineseName)) { columns.RemoveAt(i); } } HttpContext.Items["__ColumnInfo"] = columns; } #endregion // 导出到Excel的数据源, DataTable dt = null; if (dataUrl.IndexOf("[]") >= 0 || dataUrl.IndexOf("{") >= 0) // 前台传入Data数据 { dt = JsonConvert.DeserializeObject <DataTable>(dataUrl); } else // 前台传入请求数据的地址 { var dic = new Dictionary <string, object>(); if (!string.IsNullOrWhiteSpace(queryFormData)) { dic.Add("queryFormData", queryFormData); } if (!string.IsNullOrWhiteSpace(sortField)) { dic.Add("sortField", sortField); } if (!string.IsNullOrWhiteSpace(sortOrder)) { dic.Add("sortOrder", sortOrder); } dic.Add("pageSize", "0"); var serverUrl = string.Format("{0}://{1}", Request.Url.Scheme, Request.Url.Authority); dt = Get <DataTable>(serverUrl, dataUrl, dic) ?? new DataTable(); } dt.TableName = excelKey; var exporter = new AsposeExcelExporter(); byte[] templateBuffer = null; var path = System.Configuration.ConfigurationManager.AppSettings["ExcelTemplatePath"]; var templatePath = path.EndsWith("\\") ? string.Format("{0}{1}_New.xls", path, excelKey) : string.Format("{0}\\{1}_New.xls", path, excelKey); if (System.IO.File.Exists(templatePath)) { LogWriter.Info(string.Format("ExportExcel - 采用自定义模板,模板路径为:{0}", templatePath)); templateBuffer = FileHelper.GetFileBuffer(templatePath); } else { templateBuffer = exporter.ParseTemplate(columns, excelKey, title); } var buffer = exporter.Export(dt, templateBuffer); LogWriter.Info(string.Format("ExportExcel - Excel导出的Key:{0} - 结束", excelKey)); if (buffer != null) { return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls")); } LogWriter.Info(string.Format("ExportExcel - 导出数据失败,参数: dataUrl={0}<br>queryFormData={1}<br>jsonColumns={2}<br>excelKey={3}<br>title={4}<br> ", dataUrl, queryFormData, jsonColumns, excelKey, title)); return(Content("导出数据失败,请检查相关配置!")); }