public ActionResult ExportExcel(string VersionID, string jsonColumns, string title)
{
var columns = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Formula.ImportExport.ColumnInfo> >(jsonColumns);
var exporter = new MvcAdapter.ImportExport.AsposeExcelExporter();
byte[] templateBuffer = null;
var excelKey = "Budget";
var version = this.GetEntityByID <S_I_BudgetInfo>(VersionID);
if (version == null)
{
throw new Formula.Exceptions.BusinessValidationException("未能找到预算信息，无法导出EXCEL");
}
if (string.IsNullOrEmpty(title))
{
title = version.S_I_Engineering.Name + "_预算表";
}
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);
templatePath = Server.MapPath("/") + templatePath;
if (System.IO.File.Exists(templatePath))
{
Formula.LogWriter.Info(string.Format("ExportExcel - 采用自定义模板，模板路径为：{0}", templatePath));
templateBuffer = Formula.ImportExport.FileHelper.GetFileBuffer(templatePath);
}
else
{
templateBuffer = exporter.ParseTemplate(columns, excelKey, title);
}
var tmp = version.S_I_BudgetInfo_Detail.Where(c => c.ModifyState != BomVersionModifyState.Remove.ToString()).
OrderBy(d => d.CBSFullID.Length).ThenBy(c => c.SortIndex).ToList();
#region 重排
Action <List <S_I_BudgetInfo_Detail>, string, List <S_I_BudgetInfo_Detail> > f = null;
f = (res, parentID, source) =>
{
var children = source.Where(a => a.CBSParentID == parentID);
foreach (var child in children)
{
res.Add(child);
f(res, child.CBSID, source);
}
};
List <S_I_BudgetInfo_Detail> data = new List <S_I_BudgetInfo_Detail>();
var topDatas = tmp.Where(a => a.CBSFullID.Length == tmp.Min(b => b.CBSFullID.Length));
foreach (var top in topDatas)
{
data.Add(top);
f(data, top.CBSID, tmp);
}
#endregion
var dt = new DataTable();
dt.Columns.Add("NodeType");
var type = typeof(S_I_BudgetInfo_Detail);
foreach (var item in columns)
{
if (dt.Columns.Contains(item.FieldName))
{
continue;
}
var property = type.GetProperty(item.FieldName);
if (property != null)
{
if (property.PropertyType == typeof(System.Nullable <decimal>) || property.PropertyType == typeof(decimal) ||
property.PropertyType == typeof(System.Nullable <float>) || property.PropertyType == typeof(float) ||
property.PropertyType == typeof(System.Nullable <double>) || property.PropertyType == typeof(double))
{
dt.Columns.Add(item.FieldName, typeof(decimal));
}
else if (property.PropertyType == typeof(System.Nullable <int>) || property.PropertyType == typeof(int))
{
dt.Columns.Add(item.FieldName, typeof(int));
}
else
{
dt.Columns.Add(item.FieldName);
}
}
else
{
dt.Columns.Add(item.FieldName);
}
}
foreach (var item in tmp)
{
var row = dt.NewRow();
var dic = FormulaHelper.ModelToDic <S_I_BudgetInfo_Detail>(item);
foreach (var detailColumn in columns)
{
if (dic.ContainsKey(detailColumn.FieldName) && dic[detailColumn.FieldName] != null)
{
row[detailColumn.FieldName] = dic[detailColumn.FieldName]; //dic.GetValue(detailColumn.FieldName);
}
}
dt.Rows.Add(row);
}
dt.TableName = excelKey;
var buffer = exporter.Export(dt, templateBuffer);
return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls"));
}
public ActionResult ExportExcel(string EngineeringInfoID, string jsonColumns, string title)
{
var columns = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Formula.ImportExport.ColumnInfo> >(jsonColumns);
var exporter = new MvcAdapter.ImportExport.AsposeExcelExporter();
byte[] templateBuffer = null;
var excelKey = "Bom";
var engineeringInfo = this.GetEntityByID <S_I_Engineering>(EngineeringInfoID);
if (string.IsNullOrEmpty(title))
{
title = engineeringInfo.Name + "_设计采购进度";
}
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);
templatePath = Server.MapPath("/") + templatePath;
if (System.IO.File.Exists(templatePath))
{
Formula.LogWriter.Info(string.Format("ExportExcel - 采用自定义模板，模板路径为：{0}", templatePath));
templateBuffer = Formula.ImportExport.FileHelper.GetFileBuffer(templatePath);
}
else
{
templateBuffer = exporter.ParseTemplate(columns, excelKey, title);
}
var versionList = this.entities.Set <S_P_Bom>().Where(c => c.EngineeringInfoID == engineeringInfo.ID).OrderBy(c => c.SortIndex).ToList();
var data = this.entities.Set <S_I_PBS>().Where(d => d.EngineeringInfoID == engineeringInfo.ID).
OrderBy(c => c.SortIndex).ToList();
var pbsNodeTypeEnum = EnumBaseHelper.GetEnumDef("Base.PBSType");
var dt = new DataTable();
dt.Columns.Add("NodeType");
foreach (var item in columns)
{
if (dt.Columns.Contains(item.FieldName))
{
continue;
}
dt.Columns.Add(item.FieldName);
}
foreach (var item in data)
{
var dic = FormulaHelper.ModelToDic <S_I_PBS>(item);
var details = versionList.Where(c => c.PBSNodeID == item.ID).ToList();
//判定是否要过滤掉没有设备材料的PBS节点
if (versionList.Count(c => c.PBSNodeFullID.StartsWith(item.FullID)) == 0)
{
continue;
}
var row = dt.NewRow();
foreach (var column in columns)
{
row[column.FieldName] = dic.GetValue(column.FieldName);
}
var enumItem = pbsNodeTypeEnum.EnumItem.FirstOrDefault(c => c.Code == item.NodeType);
if (enumItem == null)
{
row["NodeType"] = item.NodeType;
}
else
{
row["NodeType"] = enumItem.Name;
}
dt.Rows.Add(row);
foreach (var detail in details)
{
var detailDic = FormulaHelper.ModelToDic <S_P_Bom>(detail);
var detailRow = dt.NewRow();
foreach (var detailColumn in columns)
{
detailRow[detailColumn.FieldName] = detailDic.GetValue(detailColumn.FieldName);
}
detailRow["NodeType"] = "";
dt.Rows.Add(detailRow);
}
}
dt.TableName = excelKey;
var buffer = exporter.Export(dt, templateBuffer);
return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls"));
}
public ActionResult ExportExcel(QueryBuilder qb, string VersionID, string jsonColumns, string title)
{
var columns = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Formula.ImportExport.ColumnInfo> >(jsonColumns);
var exporter = new MvcAdapter.ImportExport.AsposeExcelExporter();
byte[] templateBuffer = null;
var excelKey = "Bid";
var version = this.GetEntityByID <S_E_Bom>(VersionID);
if (version == null)
{
throw new Formula.Exceptions.BusinessValidationException("未能找到设计BOM清单，无法导出EXCEL");
}
if (string.IsNullOrEmpty(title))
{
title = version.S_I_Engineering.Name + "_预算设备清单";
}
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);
templatePath = Server.MapPath("/") + templatePath;
if (System.IO.File.Exists(templatePath))
{
Formula.LogWriter.Info(string.Format("ExportExcel - 采用自定义模板，模板路径为：{0}", templatePath));
templateBuffer = Formula.ImportExport.FileHelper.GetFileBuffer(templatePath);
}
else
{
templateBuffer = exporter.ParseTemplate(columns, excelKey, title);
}
var modifyState = BomVersionModifyState.Remove.ToString();
var versionList = this.entities.Set <S_E_Bom_Detail>().Where(c => c.VersionID == VersionID && c.ModifyState != modifyState).Where(qb).OrderBy(c => c.SortIndex).ToList();
var tmp = this.entities.Set <S_I_PBS>().Where(d => d.EngineeringInfoID == version.EngineeringInfoID && d.NodeType != "Root").OrderBy(c => c.SortIndex).ToList();
var pbsNodeTypeEnum = EnumBaseHelper.GetEnumDef("Base.PBSType");
#region 重排
Action <List <S_I_PBS>, string, List <S_I_PBS> > f = null;
f = (res, parentID, source) =>
{
var children = source.Where(a => a.ParentID == parentID);
foreach (var child in children)
{
res.Add(child);
f(res, child.ID, source);
}
};
List <S_I_PBS> data = new List <S_I_PBS>();
var topDatas = tmp.Where(a => a.FullID.Length == tmp.Min(b => b.FullID.Length));
foreach (var top in topDatas)
{
data.Add(top);
f(data, top.ID, tmp);
}
#endregion
var dt = new DataTable();
dt.Columns.Add("NodeType");
foreach (var item in columns)
{
if (dt.Columns.Contains(item.FieldName))
{
continue;
}
dt.Columns.Add(item.FieldName);
}
foreach (var item in data)
{
var dic = FormulaHelper.ModelToDic <S_I_PBS>(item);
var details = versionList.Where(c => c.PBSNodeID == item.ID).ToList();
//判定是否要过滤掉没有设备材料的PBS节点
if (versionList.Count(c => c.PBSNodeFullID.StartsWith(item.FullID)) == 0)
{
continue;
}
var row = dt.NewRow();
foreach (var column in columns)
{
row[column.FieldName] = dic.GetValue(column.FieldName);
}
var enumItem = pbsNodeTypeEnum.EnumItem.FirstOrDefault(c => c.Code == item.NodeType);
if (enumItem == null)
{
row["NodeType"] = item.NodeType;
}
else
{
row["NodeType"] = enumItem.Name;
}
dt.Rows.Add(row);
foreach (var detail in details)
{
var detailDic = FormulaHelper.ModelToDic <S_E_Bom_Detail>(detail);
var detailRow = dt.NewRow();
foreach (var detailColumn in columns)
{
detailRow[detailColumn.FieldName] = detailDic.GetValue(detailColumn.FieldName);
}
detailRow["NodeType"] = "";
dt.Rows.Add(detailRow);
}
}
dt.TableName = excelKey;
var versionDic = version.ToDic();
exporter.OnExport = (sheet) =>
{
foreach (var item in versionDic)
{
string val = "";
if (item.Value != null)
{
val = item.Value.ToString();
}
SetSignContentToSheet(sheet, "$" + item.Key, val);
}
};
var buffer = exporter.Export(dt, templateBuffer);
return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls"));
}
public ActionResult ExportExcel(string monthColArr, string jsonColumns, string title, string templateCode)
{
var columns = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Formula.ImportExport.ColumnInfo> >(jsonColumns);
var exporter = new MvcAdapter.ImportExport.AsposeExcelExporter();
byte[] templateBuffer = null;
var excelKey = "CapitalCompany";
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);
templatePath = Server.MapPath("/") + templatePath;
if (System.IO.File.Exists(templatePath))
{
Formula.LogWriter.Info(string.Format("ExportExcel - 采用自定义模板，模板路径为：{0}", templatePath));
templateBuffer = Formula.ImportExport.FileHelper.GetFileBuffer(templatePath);
}
else
{
templateBuffer = exporter.ParseTemplate(columns, excelKey, title);
}
var dt = new DataTable();
foreach (var item in columns)
{
if (dt.Columns.Contains(item.FieldName))
{
continue;
}
dt.Columns.Add(item.FieldName);
}
List <Dictionary <string, object> > dicList = GetDetailDicList(monthColArr, templateCode);
foreach (var dic in dicList)
{
var detailRow = dt.NewRow();
foreach (var detailColumn in columns)
{
if (detailColumn.FieldName == "Name")
{
string space = "";
if (!string.IsNullOrEmpty(dic.GetValue("FullID")))
{
int spaceCount = dic.GetValue("FullID").Split(',').Length;
for (int i = 0; i < spaceCount; i++)
{
space += " ";
}
}
detailRow[detailColumn.FieldName] = space + dic.GetValue(detailColumn.FieldName);
}
else
{
detailRow[detailColumn.FieldName] = dic.GetValue(detailColumn.FieldName);
}
}
dt.Rows.Add(detailRow);
}
dt.TableName = "CapitalCompany";
var buffer = exporter.Export(dt, templateBuffer);
return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls"));
}