private RowColIndexes GetTableRowColIndexesForExactMatch(System.Data.DataTable workSheetCoverLocInfo, string searchText)
{
RowColIndexes returnRCIndexes = new RowColIndexes();
int rowIndex = -1; //return -1 if no match found
var rowIndexArray = workSheetCoverLocInfo
.Rows
.Cast <DataRow>()
.Where(r => r.ItemArray.Any(c => Regex.IsMatch(c.ToString().Trim(), searchText, RegexOptions.IgnoreCase)))
.Select(r => r.Table.Rows.IndexOf(r)).ToArray();
if (rowIndexArray.Length > 0)
{
//return the row index
var rowCol = rowIndexArray[0];
rowIndex = rowCol;
}
int colIndex = 0;
if (rowIndex >= 0)
{
//loop through row until column index is found
foreach (var dc in workSheetCoverLocInfo.Rows[rowIndex].ItemArray)
{
if (dc != DBNull.Value)
{
//if (dc.ToString() == searchText)
if (Regex.IsMatch(dc.ToString().Trim(), searchText, RegexOptions.IgnoreCase))
{
break;
}
}
colIndex++;
}
}
else
{
colIndex = -1; //return -1 if no match found
}
return(new RowColIndexes {
rowIndex = rowIndex, colIndex = colIndex
});
}
private RowColIndexes GetTableRowColIndexesForExactMatch(DataTable workSheetCoverLocInfo, string searchText)
{
RowColIndexes returnRCIndexes = new RowColIndexes();
int rowIndex = -1;
//LINQ answer on Stack Overflow
var rowIndexArray = workSheetCoverLocInfo
.Rows
.Cast <DataRow>()
.Where(r => r.ItemArray.Any(c => Regex.IsMatch(c.ToString().Trim(), searchText, RegexOptions.IgnoreCase)))
.Select(r => r.Table.Rows.IndexOf(r)).ToArray();
if (rowIndexArray.Length > 0)
{
var rowCol = rowIndexArray[0];
rowIndex = rowCol;
}
int colIndex = 0;
if (rowIndex >= 0)
{
foreach (var dc in workSheetCoverLocInfo.Rows[rowIndex].ItemArray)
{
if (dc != DBNull.Value)
{
if (Regex.IsMatch(dc.ToString().Trim(), searchText, RegexOptions.IgnoreCase))
{
break;
}
}
colIndex++;
}
}
else
{
colIndex = -1;
}
return(new RowColIndexes {
RowIndex = rowIndex, ColIndex = colIndex
});
}
public LocationInfo ReadInfoFromExcel(string fileInPath)
{
LocationInfo locInfo = new LocationInfo();
using (var stream = File.Open(fileInPath, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx, *.xlsm)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var dataSet = reader.AsDataSet();
DataTable workSheetCoverLocInfo = dataSet.Tables[1];
RowColIndexes propertyIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Property Description:");
RowColIndexes bedRoomIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Bedrooms:");
RowColIndexes bathRoomIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Bathrooms:");
RowColIndexes dateOnMktIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Days on the Market:");
RowColIndexes addressIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Address:");
RowColIndexes cityStateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "City, State:");
locInfo = workSheetCoverLocInfo.AsEnumerable().Select(x => new LocationInfo
{
property = x.Table.Rows[propertyIndexes.RowIndex + 1][propertyIndexes.ColIndex + 2].ToString().Trim() /* + x.Table.Rows[propertyIndexes.rowIndex + 1][propertyIndexes.colIndex + 1].ToString().Trim()*/,
bedrooms = x.Table.Rows[bedRoomIndexes.RowIndex + 1][bedRoomIndexes.ColIndex].ToString().Trim(),
bathrooms = x.Table.Rows[bathRoomIndexes.RowIndex + 1][bathRoomIndexes.ColIndex].ToString().Trim(),
dateOnMkt = x.Table.Rows[dateOnMktIndexes.RowIndex + 1][dateOnMktIndexes.ColIndex + 3].ToString().Trim() + x.Table.Rows[dateOnMktIndexes.RowIndex][dateOnMktIndexes.ColIndex + 2].ToString().Trim(),
address = x.Table.Rows[addressIndexes.RowIndex][addressIndexes.ColIndex].ToString().Trim(),
cityState = x.Table.Rows[cityStateIndexes.RowIndex + 1][cityStateIndexes.ColIndex].ToString().Trim(),
}).First();
}
}
return(locInfo);
}
public LocationInfo ReadInfoFromExcel(string fileInPath)
{
LocationInfo locInfo = new LocationInfo();
using (var stream = File.Open(fileInPath, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx, *.xlsm)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
System.Data.DataSet dataSet = reader.AsDataSet();
// The result of each spreadsheet is in result.Tables
//in xlsm sheet[0] is macro, so the first sheet is index 1
System.Data.DataTable workSheetCoverLocInfo = dataSet.Tables[1];
//Selection.Range("J8,J10,B41,C41,D41,E41,B45,D45,B50,C50,D50,E50,B53,C53,D53,B55,C55,B57,C57");
//"J8,J10,B41,C41,D41,E41,B45,D45,B50,C50,D50,E50,B53,C53,D53,B55,C55,B57,C57"
//rename columns for clarity
//workSheetCoverLocInfo.Columns["Column1"].ColumnName = "B";
//workSheetCoverLocInfo.Columns["Column2"].ColumnName = "C";
//workSheetCoverLocInfo.Columns["Column3"].ColumnName = "D";
//workSheetCoverLocInfo.Columns["Column4"].ColumnName = "E";
//workSheetCoverLocInfo.Columns["Column9"].ColumnName = "J";
//List<MapVal> input = new List<MapVal>();
//var spliceMap = new MapVal();
//spliceMap.fieldName = "SplicePoint";
//spliceMap.fieldLabel = "Splice Point #:";
//spliceMap.relativePos = 1;
//spliceMap.offset = 1;
//input.Add(spliceMap);
//List<string> output = new List<string>();
//foreach (var field in input)
//{
// output.Add(GetFieldValue(workSheetCoverLocInfo, field.fieldName, field.fieldLabel, field.relativePos, field.offset));
//}
//var splice = GetFieldValue(workSheetCoverLocInfo, "SplicePoint", "Splice Point #:", 1, 1);
RowColIndexes spliceIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Splice Point #:");
RowColIndexes releaseIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Release #:");
RowColIndexes addressIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Address");
RowColIndexes cityStateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "City, State:");
RowColIndexes floorNumberIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Floor Number");
RowColIndexes roomCageIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Room/Cage");
RowColIndexes buildingCodeIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Building CLLI Code");
RowColIndexes buildingNameIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Building Name \\\\ Code");
RowColIndexes enclosureIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Rack \\\\ Enclosure");
RowColIndexes makeModelIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Enclosure Make/Model:");
RowColIndexes ospCablesIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "OSP Cables in FDP");
RowColIndexes fibEngineerIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Fiber Engineer");
RowColIndexes feRelDateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Release Date");
RowColIndexes ospPmIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "OSP PM");
RowColIndexes releaseNoIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Release #:");
RowColIndexes subfloorIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Sub Floor / Parking");
RowColIndexes splicePointIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Splice Point #:");
RowColIndexes floorCLLIIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Floor CLLI Output");
RowColIndexes manholeIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Manhole #");
RowColIndexes manhandHoleNumberIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Manhole / Handhole#:");
RowColIndexes ownerIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Owner");
RowColIndexes mhhhOwnerIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "MH / HH Owned By:");
RowColIndexes mfnBackboneIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "MFN Backbone Cables(s) in Enclosure");
RowColIndexes facEngineerIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Facilities Engineer:");
RowColIndexes otherSpliceIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Other Splice Points in Node:");
RowColIndexes dirFacMgmtIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Dir. Facilities Management");
RowColIndexes dirFacMgmtDateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Dir. Facilities Management Date");
RowColIndexes dirNetEngIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Dir. Network Engineering");
RowColIndexes dirNetEngDateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Dir. Network Engineering Date");
RowColIndexes vpNetEngIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "VP Network Engineering");
RowColIndexes vpNetEngDateIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "VP Network Engineering Date");
RowColIndexes floorIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Floor:");
RowColIndexes rackIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Racks:");
RowColIndexes otherSplicesNodeIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Other Splice Points in Node");
RowColIndexes notesIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Notes");
RowColIndexes customerInfoIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Customer Information");
RowColIndexes hhPoleNumberIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Handhole/Pole #:");
RowColIndexes mhPoleOwnedByIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "MH/Pole Owned By:");
RowColIndexes otherSpliceMHIndexes = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, "Other Splice Points in Manhole:");
//create object using linq
locInfo = workSheetCoverLocInfo.AsEnumerable().Select(x => new LocationInfo
{
splicePoint = x.Table.Rows[spliceIndexes.rowIndex][spliceIndexes.colIndex + 1].ToString().Trim() + x.Table.Rows[spliceIndexes.rowIndex][spliceIndexes.colIndex + 2].ToString().Trim(),
release = x.Table.Rows[releaseIndexes.rowIndex][releaseIndexes.colIndex + 1].ToString().Trim() + x.Table.Rows[releaseIndexes.rowIndex][releaseIndexes.colIndex + 2].ToString().Trim(),
address = x.Table.Rows[addressIndexes.rowIndex + 1][addressIndexes.colIndex].ToString().Trim(),
city = x.Table.Rows[cityStateIndexes.rowIndex + 1][cityStateIndexes.colIndex].ToString().Trim(),
floorNo = x.Table.Rows[floorNumberIndexes.rowIndex + 1][floorNumberIndexes.colIndex].ToString().Trim(),
room = x.Table.Rows[roomCageIndexes.rowIndex + 1][roomCageIndexes.colIndex].ToString().Trim(),
buildingCode = x.Table.Rows[buildingCodeIndexes.rowIndex + 1][buildingCodeIndexes.colIndex].ToString().Trim(),
buildingName = x.Table.Rows[buildingNameIndexes.rowIndex + 1][buildingNameIndexes.colIndex].ToString().Trim(),
enclosure = x.Table.Rows[enclosureIndexes.rowIndex + 1][enclosureIndexes.colIndex].ToString().Trim(),
makeModel = x.Table.Rows[makeModelIndexes.rowIndex + 1][makeModelIndexes.colIndex].ToString().Trim(),
ospCables = x.Table.Rows[ospCablesIndexes.rowIndex + 1][ospCablesIndexes.colIndex].ToString().Trim(),
fiberEngineer = x.Table.Rows[fibEngineerIndexes.rowIndex + 1][fibEngineerIndexes.colIndex].ToString().Trim(),
releaseDate = x.Table.Rows[feRelDateIndexes.rowIndex + 1][feRelDateIndexes.colIndex].ToString().Trim(),
ospPM = x.Table.Rows[ospPmIndexes.rowIndex + 1][ospPmIndexes.colIndex].ToString().Trim(),
releaseNo = x.Table.Rows[releaseNoIndexes.rowIndex + 1][releaseNoIndexes.colIndex].ToString().Trim(),
subFloor = x.Table.Rows[subfloorIndexes.rowIndex + 1][subfloorIndexes.colIndex].ToString().Trim(),
splicePointNo = x.Table.Rows[splicePointIndexes.rowIndex + 1][splicePointIndexes.colIndex].ToString().Trim(),
floorCLLI = x.Table.Rows[floorCLLIIndexes.rowIndex + 1][floorCLLIIndexes.colIndex].ToString().Trim(),
manhole = x.Table.Rows[manholeIndexes.rowIndex + 1][manholeIndexes.colIndex].ToString().Trim(),
manholeHandholeNo = x.Table.Rows[manhandHoleNumberIndexes.rowIndex + 1][manhandHoleNumberIndexes.colIndex].ToString().Trim(),
owner = x.Table.Rows[ownerIndexes.rowIndex + 1][ownerIndexes.colIndex].ToString().Trim(),
manholeHandholeOwnedBy = x.Table.Rows[mhhhOwnerIndexes.rowIndex + 1][mhhhOwnerIndexes.colIndex].ToString().Trim(),
mfnBackbone = x.Table.Rows[mfnBackboneIndexes.rowIndex + 1][mfnBackboneIndexes.colIndex].ToString().Trim(),
facilitiesEngineer = x.Table.Rows[facEngineerIndexes.rowIndex + 1][facEngineerIndexes.colIndex].ToString().Trim(),
otherSplicePointsInEnclosure = x.Table.Rows[otherSpliceIndexes.rowIndex + 1][otherSpliceIndexes.colIndex].ToString().Trim(),
dirFacilitiesMgmt = x.Table.Rows[dirFacMgmtIndexes.rowIndex + 1][dirFacMgmtIndexes.colIndex].ToString().Trim(),
dirFacilitiesMgmtDate = x.Table.Rows[dirFacMgmtDateIndexes.rowIndex + 1][dirFacMgmtDateIndexes.colIndex].ToString().Trim(),
dirNetworkEngineering = x.Table.Rows[dirNetEngIndexes.rowIndex + 1][dirNetEngIndexes.colIndex].ToString().Trim(),
dirNetworkEngineeringDate = x.Table.Rows[dirNetEngDateIndexes.rowIndex + 1][dirNetEngDateIndexes.colIndex].ToString().Trim(),
vpNetworkEngineering = x.Table.Rows[vpNetEngIndexes.rowIndex + 1][floorCLLIIndexes.colIndex].ToString().Trim(),
vpNetworkEngineeringDate = x.Table.Rows[vpNetEngDateIndexes.rowIndex + 1][vpNetEngDateIndexes.colIndex].ToString().Trim(),
floor = x.Table.Rows[floorIndexes.rowIndex + 1][floorIndexes.colIndex].ToString().Trim(),
racks = x.Table.Rows[rackIndexes.rowIndex + 1][rackIndexes.colIndex].ToString().Trim(),
otherSplicePointsInNode = x.Table.Rows[otherSplicesNodeIndexes.rowIndex + 1][otherSplicesNodeIndexes.colIndex].ToString().Trim(),
notes = x.Table.Rows[notesIndexes.rowIndex + 1][notesIndexes.colIndex].ToString().Trim(),
customerInfo = x.Table.Rows[customerInfoIndexes.rowIndex + 1][customerInfoIndexes.colIndex].ToString().Trim(),
handholeOrPoleNo = x.Table.Rows[hhPoleNumberIndexes.rowIndex + 1][hhPoleNumberIndexes.colIndex].ToString().Trim(),
manholeOrPoleNo = x.Table.Rows[mhPoleOwnedByIndexes.rowIndex + 1][mhPoleOwnedByIndexes.colIndex].ToString().Trim(),
otherSplicePointsInManhole = x.Table.Rows[otherSpliceMHIndexes.rowIndex + 1][otherSpliceMHIndexes.colIndex].ToString().Trim(),
}).First();
}
}
return(locInfo);
}
