public List <SpreadsheetColumn> GetColumns(SpreadsheetTable table)
        {
            if (table.Columns == null)
            {
                WorksheetPart worksheetPart = GetWorksheetPart(table);

                List <SpreadsheetColumn> columns = new List <SpreadsheetColumn>();

                Row row = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex == 1).First();

                foreach (Cell cell in row.ChildElements)
                {
                    string value = GetValue(cell);

                    if (table.HeaderRow == 0)
                    {
                        value = SpreadsheetReference.GetColumnName(cell.CellReference);
                    }

                    SpreadsheetColumn column = new SpreadsheetColumn
                    {
                        Name       = value,
                        Label      = value,
                        DataType   = GetDataType(cell.DataType),
                        ColumnIdx  = SpreadsheetReference.GetColumnName(cell.CellReference),
                        DataLength = GetDataLength(GetDataType(cell.DataType))
                    };

                    columns.Add(column);
                }

                return(columns);
            }
            else
            {
                return(table.Columns);
            }
        }
        public WorksheetPart GetWorksheetPart(SpreadsheetTable table)
        {
            string sheetName = SpreadsheetReference.GetSheetName(table.Reference);

            return(GetWorksheetPart(sheetName));
        }
        private IList <IDataObject> LoadDataObjects(string objectType, DataFilter filter)
        {
            try
            {
                bool addDataObject = true, containFilterProperty = false;
                IList <IDataObject> dataObjects     = new List <IDataObject>();
                int index = 0;

                SpreadsheetTable     cfTable        = _provider.GetConfigurationTable(objectType);
                SpreadsheetReference tableReference = cfTable.GetReference();

                WorksheetPart worksheetPart = _provider.GetWorksheetPart(tableReference.SheetName);

                IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex > tableReference.StartRow && r.RowIndex <= tableReference.EndRow);

                foreach (Row row in rows)
                {
                    index                 = 0;
                    addDataObject         = true;
                    containFilterProperty = false;
                    IDataObject dataObject = new GenericDataObject
                    {
                        ObjectType = objectType,
                    };

                    foreach (Cell col in row.ChildElements)
                    {
                        index++;
                        string            columnIdx = SpreadsheetReference.GetColumnName(col.CellReference);
                        SpreadsheetColumn column    = cfTable.Columns.First <SpreadsheetColumn>(c => columnIdx.Equals(c.ColumnIdx));

                        if (column != null)
                        {
                            if (index == 1)
                            {
                                if (_provider.GetValue(col) == null)
                                {
                                    addDataObject = false;
                                    break;
                                }
                            }

                            if (filter != null)
                            {
                                foreach (Expression expression in filter.Expressions)
                                {
                                    if (expression.PropertyName.ToLower().Equals(column.Name.ToLower()))
                                    {
                                        containFilterProperty = true;
                                        if (_provider.GetValue(col) == null)
                                        {
                                            addDataObject = false;
                                            break;
                                        }
                                    }
                                }

                                if (filter.Expressions.Count == 0)
                                {
                                    addDataObject = true;
                                }
                            }

                            if (addDataObject)
                            {
                                dataObject.SetPropertyValue(column.Name, _provider.GetValue(col));
                            }
                        }
                    }

                    if (!containFilterProperty && filter != null)
                    {
                        if (filter.Expressions.Count > 0)
                        {
                            addDataObject = false;
                        }
                    }

                    if (addDataObject)
                    {
                        dataObjects.Add(dataObject);
                    }

                    //foreach (var col in cfTable.Columns)
                    //{
                    //    if (!((GenericDataObject)dataObject).Dictionary.ContainsKey(col.Name))
                    //        dataObject.SetPropertyValue(col.Name, null);
                    //}
                }

                return(dataObjects);
            }
            catch (Exception ex)
            {
                _logger.Error("Error in LoadDataObjects: " + ex);
                throw new Exception("Error while loading data objects of type [" + objectType + "].", ex);
            }
            finally
            {
                //  _provider.Dispose();
            }
        }
        private Response SaveDataObjects(string objectType, IList <IDataObject> dataObjects)
        {
            try
            {
                Response response = new Response();

                SpreadsheetTable     cfTable        = _provider.GetConfigurationTable(objectType);
                SpreadsheetReference tableReference = cfTable.GetReference();
                WorksheetPart        worksheetPart  = _provider.GetWorksheetPart(cfTable);

                foreach (IDataObject dataObject in dataObjects)
                {
                    Status status = new Status();

                    try
                    {
                        string identifier = GetIdentifier(dataObject);
                        status.Identifier = identifier;

                        SpreadsheetColumn column = cfTable.Columns.First <SpreadsheetColumn>(c => c.Name.Equals(cfTable.Identifier));
                        Cell cell = worksheetPart.Worksheet.Descendants <Cell>().FirstOrDefault(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(column.ColumnIdx) && _provider.GetValue(c).Equals(identifier));

                        if (cell != null)
                        {
                            Row existingRow = cell.Ancestors <Row>().First();

                            foreach (SpreadsheetColumn col in cfTable.Columns)
                            {
                                Cell existingCell = existingRow.Descendants <Cell>().First(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(col.ColumnIdx));
                                existingCell.DataType = SpreadsheetProvider.GetCellValue(col.DataType);
                                if (!string.IsNullOrEmpty(Convert.ToString(dataObject.GetPropertyValue(col.Name))))
                                {
                                    existingCell.CellValue.Text = Convert.ToString(dataObject.GetPropertyValue(col.Name));
                                }
                                else if (existingCell.CellValue == null)
                                {
                                    existingCell.CellValue = new CellValue(Convert.ToString(dataObject.GetPropertyValue(col.Name)));
                                }
                            }
                        }
                        else
                        {
                            tableReference.EndRow++;

                            Row newRow = new Row
                            {
                                RowIndex = (UInt32Value)tableReference.EndRow,
                                Spans    = new ListValue <StringValue>
                                {
                                    InnerText = string.Format("1:{0}", cfTable.Columns.Count)
                                }
                            };

                            foreach (SpreadsheetColumn col in cfTable.Columns)
                            {
                                Cell newCell = new Cell
                                {
                                    CellReference = string.Format("{0}{1}", col.ColumnIdx, newRow.RowIndex),
                                    DataType      = SpreadsheetProvider.GetCellValue(col.DataType),
                                    CellValue     = new CellValue(Convert.ToString(dataObject.GetPropertyValue(col.Name)))
                                };

                                newRow.Append(newCell);
                            }

                            SheetData sheetData = (SheetData)worksheetPart.Worksheet.Descendants <SheetData>().First();
                            sheetData.Append(newRow);
                        }

                        worksheetPart.Worksheet.SheetDimension.Reference = tableReference.GetReference(false);
                        cfTable.Reference = tableReference.GetReference(true);

                        worksheetPart.Worksheet.Save();

                        status.Messages.Add("Record [" + identifier + "] has been saved successfully.");
                    }
                    catch (Exception ex)
                    {
                        status.Level = StatusLevel.Error;

                        string message = String.Format(
                            "Error while posting dataObject [{0}]. {1}",
                            dataObject.GetPropertyValue("Tag"),
                            ex.ToString()
                            );

                        status.Messages.Add(message);
                    }

                    response.Append(status);
                }

                return(response);
            }
            catch (Exception ex)
            {
                _logger.Error("Error in LoadDataObjects: " + ex);
                throw new Exception("Error while loading data objects of type [" + objectType + "].", ex);
            }
            finally
            {
                _provider.Dispose();
            }
        }
        public override Response Delete(string objectType, IList <string> identifiers)
        {
            Response response = new Response();

            try
            {
                if (identifiers == null || identifiers.Count == 0)
                {
                    Status status = new Status();
                    status.Level = StatusLevel.Warning;
                    status.Messages.Add("Nothing to delete.");
                    response.Append(status);
                    return(response);
                }

                SpreadsheetTable     cftable        = _provider.GetConfigurationTable(objectType);
                SpreadsheetReference tableReference = cftable.GetReference();

                WorksheetPart     worksheetPart = _provider.GetWorksheetPart(tableReference.SheetName);
                SpreadsheetColumn column        = cftable.Columns.First <SpreadsheetColumn>(c => cftable.Identifier.Equals(c.Name));

                IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>();

                foreach (string identifier in identifiers)
                {
                    Status status = new Status();
                    status.Identifier = identifier;
                    try
                    {
                        foreach (Row row in rows)
                        {
                            Cell cell = row.Descendants <Cell>().First(c => SpreadsheetReference.GetColumnName(c.CellReference).Equals(column.ColumnIdx));

                            if (_provider.GetValue(cell).Equals(identifier))
                            {
                                row.Remove();
                                string message = String.Format(
                                    "DataObject [{0}] deleted successfully.",
                                    identifier
                                    );
                                status.Messages.Add(message);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.Error("Error in Delete: " + ex);
                        status.Level = StatusLevel.Error;
                        string message = String.Format(
                            "Error while deleting dataObject [{0}]. {1}",
                            identifier,
                            ex
                            );
                        status.Messages.Add(message);
                    }
                    response.Append(status);
                    rows = worksheetPart.Worksheet.Descendants <Row>().OrderBy(r => r.RowIndex.Value);
                    uint i = 1;
                    foreach (Row row in rows)
                    {
                        row.RowIndex.Value = i++;
                    }
                    tableReference.EndRow = --i;
                    worksheetPart.Worksheet.SheetDimension.Reference = tableReference.GetReference(false);
                    cftable.Reference = tableReference.GetReference(true);
                    worksheetPart.Worksheet.Save();
                }
            }
            finally
            {
                _provider.Dispose();
            }
            return(response);
        }