public List <XPDataObject> GetListFromExcelDataSource(XPClassInfo newClass) { var list = new List <XPDataObject>(); var resultView = HS.GetResultView(ExcelDataSource); foreach (DevExpress.DataAccess.Native.Excel.ViewRow row in resultView) { var newObject = newClass.CreateObject(Session) as XPDataObject; for (int i = 0; i < resultView.Columns.Count; i++) { newObject.SetMemberValue(resultView.Columns[i].Name, resultView.Columns[i].GetValue(row)); } list.Add(newObject); } return(list); }
public Workbook ExportFromExcelDataSource(ExcelDataSource excelDataSource, SpreadsheetControl control) { excelDataSource.Fill(); var outDocument = GetOutDocument(); foreach (var item in control.Document.Worksheets) { control.Document.Worksheets.ActiveWorksheet = item; if (control.ActiveWorksheet.DefinedNames.Any()) { ExportByMailMerge(control, outDocument); } else { outDocument.Worksheets.Add(item.Name); outDocument.Worksheets.LastOrDefault().CopyFrom(item); var userdRange = item.GetDataRange().Where(x => x.HasFormula && x.Formula.ToLower().Contains("=field(")); if (userdRange.Any()) { foreach (var rangeItem in userdRange) { if (rangeItem.RowIndex > 0) { var headerCell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex - 1, rangeItem.ColumnIndex]; if (headerCell.Value.IsEmpty) { headerCell.SetValue(rangeItem.DisplayText.Replace("]", "").Replace("[", "")); } } var dataMember = control.Document.MailMergeDataMember; var splitedItem = rangeItem.DisplayText.Split('.'); if (splitedItem.Count() > 1) { dataMember = splitedItem.FirstOrDefault().Replace("[", ""); } //var query = ds.Result.Where(x => x.Name == dataMember).SelectMany(x => x.Columns).Where(x => x.Name == splitedItem.LastOrDefault().Replace("]", "").Replace("[", "")).FirstOrDefault() as DevExpress.DataAccess.Native.Sql.ResultColumn; //for (int i = 0; i < query.Count; i++) //{ // var value = query.Values[i]; // var cell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex + i, rangeItem.ColumnIndex]; // cell.SetValue(value); //} } } else { var resultView = HS.GetResultView(excelDataSource); for (int i = 0; i < resultView.Columns.Count; i++) { var col = resultView.Columns[i]; var doc = outDocument.Worksheets.LastOrDefault(); var headerCell = doc.Cells[0, i]; if (headerCell.Value.IsEmpty) { headerCell.SetValue(col.Name); } foreach (ViewRow row in resultView) { var value = col.GetValue(row); doc.Cells[row.Index + 1, i].SetValue(value); } } } } ReplaceTemplateValues(item, outDocument); } return(outDocument); }