예제 #1
0
        public static Movimento TryParse2013(IXLRangeRow row)
        {
            try
            {
                var data = (DateTime)row.Cell(1).Value;
                var categoria = (string)row.Cell(2).Value;
                var descrizione = (string)row.Cell(3).Value;
                var spesa = Convert.ToDecimal(row.Cell(4).Value);

                return new Movimento
                {
                    Data = data,
                    Categoria = categoria,
                    Descrizione = descrizione,
                    Spesa = spesa,
                };
            }
            catch
            {
                return null;
            }
        }
예제 #2
0
 /// <summary>
 /// RenderRow
 /// </summary>
 /// <param name="iXLRangeRow"></param>
 /// <param name="builder"></param>
 /// <param name="columnsCount"></param>
 static void RenderRow(IXLRangeRow iXLRangeRow, ref StringBuilder builder, int columnsCount)
 {
     for (int iRow = 1; iRow <= columnsCount; iRow++)
     {
         String columnValue = iXLRangeRow.Cell(iRow).GetString();
         builder.Append("\"" + columnValue + "\"").Append(",");
     }
 }
예제 #3
0
 /// <summary>
 /// IsBlankRow
 /// </summary>
 /// <param name="iXLRangeRow"></param>
 /// <param name="columnsCount"></param>
 /// <returns></returns>
 static bool IsBlankRow(IXLRangeRow iXLRangeRow, int columnsCount)
 {
     bool isEmpty = true;
     for (int jRow = 1; jRow <= columnsCount; jRow++)
     {
         if (iXLRangeRow.Cell(jRow).IsEmpty() == false)
         {
             isEmpty = false;
             break;
         }
     }
     return isEmpty;
 }
예제 #4
0
 public void Add(IXLRangeRow range)
 {
     _ranges.Add((XLRangeRow)range);
 }
예제 #5
0
        public static List <string> UpdateContentFromExcelRow <T>(T toUpdate, ExcelHeaderRow headerInfo,
                                                                  IXLRangeRow toProcess)
        {
            // ReSharper disable StringLiteralTypo
            var skipColumns = new List <string>
            {
                "contentid",
                "id",
                "contentversion",
                "lastupdatedon",
                "originalfilename",
                "pointdetail"
            };
            // ReSharper restore StringLiteralTypo

            var properties = typeof(T).GetProperties().ToList();

            var propertyNames = properties.Select(x => x.Name.ToLower()).ToList();
            var columnNames   = headerInfo.Columns.Where(x => !string.IsNullOrWhiteSpace(x.ColumnHeader))
                                .Select(x => x.ColumnHeader.TrimNullToEmpty().ToLower()).ToList();
            var namesToProcess = propertyNames.Intersect(columnNames).Where(x => !skipColumns.Any(x.StartsWith))
                                 .ToList();

            var propertiesToUpdate = properties.Where(x => namesToProcess.Contains(x.Name.ToLower())).ToList();

            var returnString = new List <string>();

            foreach (var loopProperties in propertiesToUpdate)
            {
                if (loopProperties.PropertyType == typeof(string))
                {
                    var excelResult = GetStringFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue.TrimNullToEmpty());
                }
                else if (loopProperties.PropertyType == typeof(Guid?))
                {
                    var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(Guid))
                {
                    var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
                        excelResult.ParsedValue == null)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(DateTime?))
                {
                    var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(DateTime))
                {
                    var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
                        excelResult.ParsedValue == null)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(double?))
                {
                    var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(double))
                {
                    var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
                        excelResult.ParsedValue == null)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(int?))
                {
                    var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(int))
                {
                    var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
                        excelResult.ParsedValue == null)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(bool?))
                {
                    var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else if (loopProperties.PropertyType == typeof(bool))
                {
                    var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name);

                    if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value ||
                        excelResult.ParsedValue == null)
                    {
                        returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}");
                    }

                    loopProperties.SetValue(toUpdate, excelResult.ParsedValue);
                }
                else
                {
                    returnString.Add(
                        $"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}, not a recognized type");
                }
            }

            if (toUpdate is PointContentDto pointDto)
            {
                var excelResult = GetPointDetails(headerInfo, toProcess);

                if (excelResult.Any(x => x.ValueParsed == null) || excelResult.Any(x => !x.ValueParsed.Value) ||
                    excelResult.Any(x => x.ParsedValue == null))
                {
                    returnString.Add($"Row {toProcess.RowNumber()} - could not process Point Details");
                }
                else
                {
                    pointDto.PointDetails = excelResult.Select(x => x.ParsedValue).ToList();
                    pointDto.PointDetails.ForEach(x => x.PointContentId = pointDto.ContentId);
                }
            }

            return(returnString);
        }
예제 #6
0
        public static ExcelValueParse <string> GetStringFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess,
                                                                     string columnName)
        {
            var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader,
                                                                               columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase));

            var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value
                              .ToString();

            return(new ExcelValueParse <string>
            {
                ParsedValue = stringValue, StringValue = stringValue, ValueParsed = true
            });
        }
예제 #7
0
        public static List <ExcelValueParse <PointDetail> > GetPointDetails(ExcelHeaderRow headerInfo,
                                                                            IXLRangeRow toProcess)
        {
            var contentColumns = headerInfo.Columns.Where(x => x.ColumnHeader.StartsWith("PointDetail"));

            var returnList = new List <ExcelValueParse <PointDetail> >();

            foreach (var loopColumns in contentColumns)
            {
                var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), loopColumns.ExcelSheetColumn)
                                  .GetString();

                if (string.IsNullOrWhiteSpace(stringValue))
                {
                    continue;
                }

                var toAdd = new ExcelValueParse <PointDetail> {
                    StringValue = stringValue
                };
                returnList.Add(toAdd);


                var splitList = stringValue.RemoveNewLines().TrimNullToEmpty().Split("||")
                                .Select(x => x.TrimNullToEmpty()).ToList();

                if (splitList.Count != 3)
                {
                    toAdd.ParsedValue = null;
                    toAdd.ValueParsed = false;
                    continue;
                }

                PointDetail pointDetail;

                //
                // Content Id - new or db retrieved PointDetail()
                //
                if (splitList[0].Length <= 10 || !splitList[0].StartsWith("ContentId:"))
                {
                    pointDetail = new PointDetail {
                        ContentId = Guid.NewGuid(), CreatedOn = DateTime.Now
                    };
                }
                else
                {
                    var contentIdString = splitList[0].Substring(10, splitList[0].Length - 10).TrimNullToEmpty();

                    if (!Guid.TryParse(contentIdString, out var contentId))
                    {
                        toAdd.ParsedValue = null;
                        toAdd.ValueParsed = false;
                        continue;
                    }

                    var db            = Db.Context().Result;
                    var possiblePoint = db.PointDetails.Single(x => x.ContentId == contentId);

                    //Content Id specified but no db entry - error, exit
                    if (possiblePoint == null)
                    {
                        toAdd.ParsedValue = null;
                        toAdd.ValueParsed = false;
                        continue;
                    }

                    pointDetail = possiblePoint;
                    pointDetail.LastUpdatedOn = DateTime.Now;
                }

                //
                //Get the data type first so it can be used to create a new point if needed
                //
                if (splitList[1].Length <= 5 || !splitList[1].StartsWith("Type:"))
                {
                    toAdd.ParsedValue = null;
                    toAdd.ValueParsed = false;
                    continue;
                }

                var dataTypeString = splitList[1].Substring(5, splitList[1].Length - 5).TrimNullToEmpty();

                if (!Db.PointDetailDataTypeIsValid(dataTypeString))
                {
                    toAdd.ParsedValue = null;
                    toAdd.ValueParsed = false;
                    continue;
                }

                pointDetail.DataType = dataTypeString;


                //
                // Point Detail Data
                //
                if (splitList[2].Length <= 5 || !splitList[2].StartsWith("Data:"))
                {
                    //Empty Data - error
                    toAdd.ParsedValue = null;
                    toAdd.ValueParsed = false;
                    continue;
                }

                try
                {
                    var jsonString       = splitList[2].Substring(5, splitList[2].Length - 5);
                    var detailData       = Db.PointDetailDataFromIdentifierAndJson(dataTypeString, jsonString);
                    var validationResult = detailData.Validate();

                    if (!validationResult.isValid)
                    {
                        toAdd.ParsedValue = null;
                        toAdd.ValueParsed = false;
                        continue;
                    }

                    pointDetail.StructuredDataAsJson = jsonString;
                }
                catch
                {
                    toAdd.ParsedValue = null;
                    toAdd.ValueParsed = false;
                    continue;
                }

                toAdd.ParsedValue = pointDetail;
                toAdd.ValueParsed = true;
            }

            return(returnList);
        }
예제 #8
0
        public static ExcelValueParse <bool?> GetBoolFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess,
                                                                  string columnName)
        {
            var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader,
                                                                               columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase));

            var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value
                              .ToString();

            if (string.IsNullOrWhiteSpace(stringValue))
            {
                return new ExcelValueParse <bool?> {
                           ParsedValue = null, StringValue = stringValue, ValueParsed = true
                }
            }
            ;

            if (bool.TryParse(stringValue, out var parsedValue))
            {
                return new ExcelValueParse <bool?>
                       {
                           ParsedValue = parsedValue, StringValue = stringValue, ValueParsed = true
                       }
            }
            ;

            return(new ExcelValueParse <bool?> {
                ParsedValue = null, StringValue = stringValue, ValueParsed = false
            });
        }
예제 #9
0
 public SubtotalGroup(int level, int column, string groupTitle, IXLRange range, IXLRangeRow summaryRow, bool pageBreaks)
 {
     Column     = column;
     SummaryRow = summaryRow;
     PageBreaks = pageBreaks;
     Level      = level;
     GroupTitle = groupTitle;
     Range      = range;
 }