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