// exports collection to a new workbook starting at cell A1 on the first worksheet
public static dynamic Export(IVisualCollection collection)
{
dynamic result = null;
try
{
ExcelHelper excel = new ExcelHelper();
excel.CreateWorkbook();
List <string> columnNames = new List <string>();
if (collection.Count > 0)
{
// get column properties
IEnumerable <IEntityProperty> columnProperties = collection.OfType <IEntityObject>().First().Details.Properties.All();
int columnCounter = 1;
int rowCounter = 1;
// add columns names to the list
foreach (IEntityProperty entityProperty in columnProperties)
{
columnNames.Add(entityProperty.Name);
// add column headers to Excel workbook
excel.Cells(rowCounter, columnCounter, entityProperty.DisplayName);
columnCounter += 1;
}
// add values on the row following the headers
rowCounter = 2;
// iterate the collection and extract values by column name
foreach (IEntityObject entityObj in collection)
{
for (int i = 0; i <= columnNames.Count - 1; i++)
{
excel.Cells(rowCounter, i + 1, LightSwitchHelper.GetValue(entityObj, columnNames[i]));
}
rowCounter += 1;
}
}
excel.ShowWorkbook();
result = excel.Workbook;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
private static void AddItemsToCollection()
{
//This should always be called on the logical thread
Debug.Assert(_collection.Screen.Details.Dispatcher.CheckAccess(), "Expected to run on the logical thread");
//Add Items to Collection
dynamic numRows = _excelDocRange.GetLength(0) - 1;
for (var i = 1; i <= numRows; i++)
{
var newRow = _collection.AddNew() as IEntityObject;
Int32 currentRow = i;
Int32 nOfficeColumnIndex = default(Int32);
foreach (ColumnMapping mapping in _columnMappings)
{
if (mapping.TableField != null && mapping.TableField.Name != "<Ignore>")
{
nOfficeColumnIndex = GetColumnIndex(mapping.OfficeColumn);
if (nOfficeColumnIndex >= 0)
{
string value = _excelDocRange[currentRow, nOfficeColumnIndex];
if (string.IsNullOrEmpty(value) && mapping.TableField.IsNullable)
{
newRow.Details.Properties[mapping.TableField.Name].Value = null;
}
else if (mapping.TableField.EntityType != null)
{
//Get cached results
if (navProperties.ContainsKey(String.Format("{0}_{1}", mapping.TableField.Name, value)))
{
newRow.Details.Properties[mapping.TableField.Name].Value = navProperties[String.Format("{0}_{1}", mapping.TableField.Name, value)];
}
}
else
{
var propValue = newRow.Details.Properties[mapping.TableField.Name].Value;
LightSwitchHelper.TryConvertValue(mapping.TableField.TypeName, value, ref propValue);
}
}
}
}
}
}
private static void ValidateData()
{
List <string> errorList = new List <string>();
dynamic numRows = _excelDocRange.GetLength(0) - 1;
//Dispatch to the logical thread
_collection.Screen.Details.Dispatcher.BeginInvoke(() =>
{
try
{
//Work through each row of data
for (var i = 1; i <= numRows; i++)
{
Int32 currentRow = i;
Int32 count = 0;
Int32 nOfficeColumnIndex = default(Int32);
// Work through each mapped column
foreach (ColumnMapping mapping in _columnMappings)
{
// Make sure the current column should be processed
if (mapping.TableField != null && mapping.TableField.Name != "<Ignore>")
{
nOfficeColumnIndex = GetColumnIndex(mapping.OfficeColumn);
if (nOfficeColumnIndex >= 0)
{
// Read in the value for the current row and column
string value = _excelDocRange[currentRow, nOfficeColumnIndex];
// Process values without null or empty string values
if (!(string.IsNullOrEmpty(value)))
{
// Try and validate based on the Entity type
bool isValid = false;
if (mapping.TableField.EntityType != null)
{
isValid = LightSwitchHelper.ValidData(value, currentRow, mapping, _collection, navProperties, errorList);
}
else
{
isValid = LightSwitchHelper.ValidData(value, currentRow, mapping, errorList);
}
}
else if (string.IsNullOrEmpty(value) && mapping.TableField.IsNullable == false && mapping.TableField.EntityType == null && mapping.TableField.TypeName != "String")
{
errorList.Add("Column: " + mapping.OfficeColumn + " Row:" + currentRow.ToString() + " A value must be specified for " + mapping.TableField.DisplayName + ". A default value will be used.");
}
}
else
{
// Couldn't find the column in the Excel range
errorList.Add("Column: " + mapping.OfficeColumn + " Row:" + currentRow.ToString() + " Could not locate column in Office document.");
}
}
count = count + 1;
}
}
if (errorList.Count > 0)
{
DisplayErrors(errorList);
}
else
{
//Add Items to Collection
AddItemsToCollection();
}
}
catch (Exception ex)
{
throw ex;
}
});
}
// exports a collection to a workbook starting at the specified location
public static dynamic ExportEntityCollection(IEntityCollection collection, string Workbook, string Worksheet, string Range, List <ColumnMapping> ColumnNames)
{
ExcelHelper xlProxy = new ExcelHelper();
dynamic wb = null;
dynamic ws = null;
dynamic rg = null;
dynamic result = null;
try
{
wb = xlProxy.Excel.Workbooks.Open(Workbook);
if ((wb != null))
{
ws = wb.Worksheets(Worksheet);
if ((ws != null))
{
rg = ws.Range(Range);
// get column properties
int columnCounter = 0;
int rowCounter = 0;
// add columns names to the list
foreach (ColumnMapping map in ColumnNames)
{
if (map.TableField.DisplayName.Length > 0)
{
rg.Offset(rowCounter, columnCounter).Value = map.TableField.DisplayName;
}
else
{
rg.Offset(rowCounter, columnCounter).Value = map.TableField.Name;
}
columnCounter += 1;
}
// add values on the row following the headers
rowCounter = 1;
// iterate the collection and extract values by column name
foreach (IEntityObject entityObj in collection)
{
for (int i = 0; i <= ColumnNames.Count - 1; i++)
{
try
{
rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetValue(entityObj, ColumnNames[i].TableField.Name);
}
catch (Exception ex) { }
}
rowCounter += 1;
}
result = wb;
xlProxy.ShowWorkbook();
}
}
}
catch (System.Runtime.InteropServices.COMException comException)
{
result = null;
xlProxy.Quit();
switch (comException.ErrorCode)
{
case -2147352565:
// Bad worksheet name
throw new System.ArgumentException("Unknown worksheet", "Worksheet");
case -2146827284:
// Bad path parameter or invalid range reference
if (comException.InnerException == null)
{
throw new System.ArgumentException("Invalid range reference", "Range");
}
else
{
throw new System.ArgumentException("Can't open Excel workbook", comException.InnerException);
}
default:
throw comException;
}
}
return(result);
}
// exports a collection to a workbook starting at the specified location
public static dynamic Export(IVisualCollection collection, string Workbook, string Worksheet, string Range)
{
ExcelHelper xlProxy = new ExcelHelper();
dynamic wb = null;
dynamic ws = null;
dynamic rg = null;
dynamic result = null;
try
{
wb = xlProxy.Excel.Workbooks.Open(Workbook);
if ((wb != null))
{
ws = wb.Worksheets(Worksheet);
if ((ws != null))
{
rg = ws.Range(Range);
List <string> columnNames = new List <string>();
if (collection.Count > 0)
{
// get column properties
IEnumerable <IEntityProperty> columnProperties = collection.OfType <IEntityObject>().First().Details.Properties.All();
int columnCounter = 0;
int rowCounter = 0;
// add columns names to the list
foreach (IEntityProperty entityProperty in columnProperties)
{
columnNames.Add(entityProperty.Name);
rg.Offset(rowCounter, columnCounter).Value = entityProperty.DisplayName;
columnCounter += 1;
}
// add values on the row following the headers
rowCounter = 1;
// iterate the collection and extract values by column name
foreach (IEntityObject entityObj in collection)
{
for (int i = 0; i <= columnNames.Count - 1; i++)
{
rg.Offset(rowCounter, i).Value = LightSwitchHelper.GetValue(entityObj, columnNames[i]);
}
rowCounter += 1;
}
}
result = wb;
xlProxy.ShowWorkbook();
}
}
}
catch (System.Runtime.InteropServices.COMException comException)
{
result = null;
xlProxy.Quit();
switch (comException.ErrorCode)
{
case -2147352565: // Bad worksheet name
throw new System.ArgumentException("Unknown worksheet", "Worksheet");
case -2146827284: // Bad path parameter or invalid range reference
if (comException.InnerException == null)
{
throw new System.ArgumentException("Invalid range reference", "Range");
}
else
{
throw new System.ArgumentException("Can't open Excel workbook", comException.InnerException);
}
default:
throw comException;
}
}
return(result);
}
