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