public ExcelXmlModel GetUploadExcelXMLConfig(string xmlFilePath, string menuid)
{
ExcelXmlModel output = new ExcelXmlModel();
string excel_menu = "..excel_" + menuid;
JObject jObject = BI_Project.Helpers.Utility.JTokenHelper.GetXML2Jobject(xmlFilePath);
output.TemplateContents = BI_Project.Helpers.Utility.JTokenHelper.GetActiveJObject(jObject, excel_menu);
//Get Common Info
output.ExcelXmlCommon = new ExcelXmlCommon();
output.ExcelXmlCommon.ExcelFileName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject,
excel_menu + ".common.filename");
output.ExcelXmlCommon.SheetName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject,
excel_menu + ".common.sheet");
output.ExcelXmlCommon.StartRow = int.Parse(BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject,
excel_menu + ".common.datarowstart"));
output.ExcelXmlCommon.ReportName = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject,
excel_menu + ".common.reportname");
output.ExcelXmlCommon.exportDirectory = BI_Project.Helpers.Utility.JTokenHelper.GetElementLanguage(jObject,
excel_menu + ".common.exportDirectory");
//GET PARAMETERS
IEnumerable <JToken> paras = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, excel_menu + "..paras.para");
int index = 0;
foreach (JToken para in paras)
{
//XMLParaModel
ExcelXMLPara paraXMLModel = new ExcelXMLPara();
paraXMLModel.Name = para.SelectToken("..name").Value <string>();
paraXMLModel.Row = para.SelectToken("..row").Value <Int32>();
paraXMLModel.Column = para.SelectToken("..column").Value <Int32>();
paraXMLModel.Colspan = para.SelectToken("..colspan").Value <Int32>();
paraXMLModel.Title = para.SelectToken("..title").Value <string>();
output.LstParas.Add(paraXMLModel);
}
//GET COLUMNS CONFIG
IEnumerable <JToken> columns = BI_Project.Helpers.Utility.JTokenHelper.GetList(jObject, excel_menu + "..columns.column");
foreach (JToken column in columns)
{
ExcelXMLColumn columnXML = new ExcelXMLColumn();
columnXML.Name = column.SelectToken("..name").Value <string>();
columnXML.Title = column.SelectToken("..title").Value <string>();
columnXML.DataType = column.SelectToken("..datatype").Value <string>();
columnXML.IsGetData = column.SelectToken("..isgetdata").Value <bool>();
columnXML.ColumnWidth = column.SelectToken("..width").Value <int>();
output.LstColumn.Add(columnXML);
}
return(output);
}
public MemoryStream ExportExcel(ExcelXmlModel model, IEnumerable <object> data, ref string fileName)
{
var p = new ExcelPackage();
fileName = model.ExcelXmlCommon.ExcelFileName;
//WRITE PARAMETERS TO EXCEL FILE
ExcelWorksheet ws = p.Workbook.Worksheets.Add(model.ExcelXmlCommon.SheetName);
foreach (ExcelXMLPara para in model.LstParas)
{
writePara(ws, para, true);
}
//WRITE HEADER OF DATA RANGE
int indexColumn = 0;
int indexDataRow = model.ExcelXmlCommon.StartRow;
foreach (ExcelXMLColumn column in model.LstColumn)
{
indexColumn++;
writeHeaderColumn(ws, indexDataRow, indexColumn, 1, column.Title, true, column.ColumnWidth);
}
foreach (Dictionary <string, object> row in data)
{
indexDataRow++;
indexColumn = 0;
foreach (ExcelXMLColumn column in model.LstColumn)
{
indexColumn++;
if (column.IsGetData)
{
writeHeaderColumn(ws, indexDataRow, indexColumn, 1, row[column.Name], false, column.ColumnWidth);
//ws.Column(indexColumn).BestFit = true;
//ws.Column(indexColumn).AutoFit(column.ColumnWidth);
}
}
}
//ws.Cells[1, 1, indexDataRow, indexColumn].AutoFitColumns();
//ws.Column(2).AutoFit(150);
return(new MemoryStream(p.GetAsByteArray()));
}
