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("导出数据失败，请检查相关配置！"));
}
