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++;
}
}
