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())); }