public List <GISRecord> Read(string fileName)
{
var rslts = new List <GISRecord>();
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
var numberOfSheets = xlWorkbook.Worksheets.Count;
for (int s = 1; s <= numberOfSheets; s++)
{
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[s];
var sheetName = xlWorksheet.Name;
Excel.Range xlRange = xlWorksheet.UsedRange;
var numberofcolumns = xlRange.Columns.Count;
var numberofRowss = xlRange.Rows.Count;
for (int r = 2; r <= numberofRowss; r++)
{
var newRecord = new GISRecord();
newRecord.Source = sheetName;
for (int c = 1; c <= numberofcolumns; c++)
{
var key = xlRange.Cells[1, c].Text;
var value = xlRange.Cells[r, c].Text;
newRecord.Information[key] = value;
if (key == "Latitude")
{
double lat;
if (double.TryParse(value, out lat))
{
newRecord.Location.Lat = lat;
}
}
if (key == "Longitude")
{
double lng;
if (double.TryParse(value, out lng))
{
newRecord.Location.Lng = lng;
}
}
}
rslts.Add(newRecord);
}
}
//foreach (var item in xlRange.Columns)
//{
// Console.WriteLine(item.ToString());
//}
return(rslts);
}
public List <GISRecord> Read(string filename, string source)
{
var rslts = new List <GISRecord>();
using (TextFieldParser parser = new TextFieldParser(filename))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
string[] headers = parser.ReadFields();
char[] charsToTrim = { '*', '#', '\'', ' ', '+', '\"' };
while (!parser.EndOfData)
{
//Processing row
var newRecord = new GISRecord();
newRecord.Source = source;
string[] fields = parser.ReadFields();
for (int i = 0; i < fields.Count(); i++)
{
newRecord.Information[headers[i]] = fields[i].Contains(",") ? "\"" + fields[i] + "\"" : fields[i];
if (headers[i] == "Latitude")
{
double lat;
if (double.TryParse(fields[i], out lat))
{
newRecord.Location.Lat = lat;
}
}
if (headers[i] == "Longitude")
{
double lng;
if (double.TryParse(fields[i], out lng))
{
newRecord.Location.Lng = lng;
}
}
if (headers[i] == "BUS-NO" || headers[i] == "busnum") //wecc || energyanalyics from wecc?
{
newRecord.BusNumber = fields[i];
}
if (headers[i] == "NAME" || headers[i] == "Name") //wecc || energyanalyics
{
var trimmedName = fields[i].ToLower().Trim(charsToTrim).Replace('_', ' ');
if (trimmedName.EndsWith(" (tap)"))
{
trimmedName = trimmedName.Substring(0, trimmedName.LastIndexOf(" (tap)"));
}
if (trimmedName.EndsWith(" tp"))
{
trimmedName = trimmedName.Substring(0, trimmedName.LastIndexOf(" tp"));
}
newRecord.BusName = trimmedName.Contains(",") ? "\"" + trimmedName + "\"" : trimmedName;
newRecord.Information["Trimmed Bus Name"] = newRecord.BusName;
}
if (headers[i] == "SUBSTATION NUMBER" || headers[i] == "busnum") //wecc || energyanalyics from wecc?
{
newRecord.SubstationNumber = fields[i];
}
if (headers[i] == "SUBSTATION NAME" || headers[i] == "SUB_NAME") //wecc || Platts
{
var trimmedName = fields[i].ToLower().Trim(charsToTrim).Replace('_', ' ');
if (trimmedName.EndsWith(" (tap)"))
{
trimmedName = trimmedName.Substring(0, trimmedName.LastIndexOf(" (tap)"));
}
if (trimmedName.EndsWith(" tp"))
{
trimmedName = trimmedName.Substring(0, trimmedName.LastIndexOf(" tp"));
}
newRecord.SubstationName = trimmedName.Contains(",") ? "\"" + trimmedName + "\"" : trimmedName;
newRecord.Information["Trimmed Substation Name"] = newRecord.SubstationName;
}
}
rslts.Add(newRecord);
}
}
return(rslts);
}
