예제 #1
0
        // 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);
        }
예제 #2
0
        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);
                            }
                        }
                    }
                }
            }
        }
예제 #3
0
        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;
                }
            });
        }
예제 #4
0
        // 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);
        }
예제 #5
0
        // 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);
        }