private static IEnumerable <InfoObject> ParseSheet(ExcelWorksheet worksheet) { int row = 1; string agencyType = string.Empty; Dictionary <int, string> columnNames = new Dictionary <int, string>(); while (row <= worksheet.Dimension.End.Row) { //header row if (worksheet.Cells[row, 1].Style.Fill.BackgroundColor.Rgb == "FFFFFF00") { agencyType = worksheet.Cells[row, 1].GetValue <string>(); for (int i = 2; i < worksheet.Dimension.End.Column; i++) { var columnName = worksheet.Cells[row, i].GetValue <string>(); if (!string.IsNullOrEmpty(columnName)) { if (columnNames.ContainsKey(i)) { columnNames[i] = columnName; } else { columnNames.Add(i, columnName); } } } } else { string name = worksheet.Cells[row, 1].GetValue <string>(); if (string.IsNullOrEmpty(name) || name.StartsWith("Note:", StringComparison.InvariantCultureIgnoreCase) || worksheet.Cells[row, 1].Style.Font.Italic) { //skip these rows } else { for (int i = 2; i <= worksheet.Dimension.End.Column; i++) { var columnName = string.Empty; columnNames.TryGetValue(i, out columnName); if (string.IsNullOrEmpty(columnName)) { columnName = "Unknown Column"; } var obj = new InfoObject() { Row = row, Column = i, State = worksheet.Name.Trim(), AgencyType = agencyType, Name = name.Trim(), ColumnName = columnName, Value = worksheet.Cells[row, i].GetValue <string>() }; if (!string.IsNullOrEmpty(obj.Value)) { obj.ValueType = GetValueType(obj.Value); yield return(obj); } } } } row++; } }
private static IEnumerable<InfoObject> ParseSheet(ExcelWorksheet worksheet) { int row = 1; string agencyType = string.Empty; Dictionary<int, string> columnNames = new Dictionary<int, string>(); while (row <= worksheet.Dimension.End.Row) { //header row if (worksheet.Cells[row, 1].Style.Fill.BackgroundColor.Rgb == "FFFFFF00") { agencyType = worksheet.Cells[row, 1].GetValue<string>(); for (int i = 2; i <= worksheet.Dimension.End.Column; i++) { var columnName = worksheet.Cells[row, i].GetValue<string>(); if (!string.IsNullOrEmpty(columnName)) { if (columnNames.ContainsKey(i)) columnNames[i] = columnName; else columnNames.Add(i, columnName); } } } else { string name = worksheet.Cells[row, 1].GetValue<string>(); if (string.IsNullOrEmpty(name) || name.StartsWith("Note:", StringComparison.InvariantCultureIgnoreCase) || worksheet.Cells[row, 1].Style.Font.Italic) { //skip these rows } else { for (int i = 2; i <= worksheet.Dimension.End.Column; i++) { var columnName = string.Empty; columnNames.TryGetValue(i, out columnName); if (string.IsNullOrEmpty(columnName)) columnName = "Unknown Column"; var obj = new InfoObject() { Row = row, Column = i, State = worksheet.Name.Trim(), AgencyType = agencyType, Name = name.Trim(), ColumnName = columnName, Value = worksheet.Cells[row, i].GetValue<string>() }; if (!string.IsNullOrEmpty(obj.Value)) { obj.ValueType = GetValueType(obj.Value); yield return obj; } } } } row++; } }