public void readProjectFromExcel()
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("C:/Users/32477/Downloads/213032 EQUIPMENT LIST_revF.xls");
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
string activeComponent = ""; //G, S, C, M
//Create project
projectFile = new AssetGroup();
projectFile.assetGroups = new List <AssetGroup>();
projectFile.assetMachines = new List <AssetMachine>();
projectFile.characteristics = new List <Characteristic>();
AssetGroup ag;
AssetMachine am;
AssetScopeOfSupply asos;
Characteristic c;
Statement t;
int rowCount = xlRange.Rows.Count;
int groupCount = 0;
List <string[]> assets = new List <string[]>();
int index = 0;
for (int i = 1; i <= rowCount; i++)
{
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
//First column of row contains indication letter
//Store indication letter to activecomponent
activeComponent = xlRange.Cells[i, 1].Value2.ToString();
switch (activeComponent)
{
case "G":
if (groupCount == 0)
{
projectFile.itemNR = (xlRange.Cells[i, 3].Value2 ?? "").ToString();
projectFile.contractNR = (xlRange.Cells[i, 4].Value2 ?? "").ToString();
projectFile.name = (xlRange.Cells[i, 5].Value2 ?? "").ToString();
}
else
{
ag = new AssetGroup();
ag.name = (xlRange.Cells[i, 5].Value2 ?? "").ToString() + " " + (xlRange.Cells[i, 6].Value2 ?? "").ToString();
ag.itemNR = (xlRange.Cells[i, 3].Value2 ?? "").ToString();
ag.contractNR = (xlRange.Cells[i, 4].Value2 ?? "").ToString();
ag.characteristics = new List <Characteristic>();
ag.assetGroups = new List <AssetGroup>();
ag.assetMachines = new List <AssetMachine>();
string nr = (xlRange.Cells[i, 3].Value2 ?? "").ToString();
index = 4 - nr.Count(x => x == '0');
if (index == 4)
{
index = 3;
}
switch (index)
{
case 1:
projectFile.assetGroups.Add(ag);
break;
case 2:
projectFile.assetGroups.Last().assetGroups.Add(ag);
break;
}
}
groupCount++;
break;
case "M":
string nr2 = (xlRange.Cells[i, 3].Value2 ?? "").ToString();
index = 4 - nr2.Count(x => x == '0');
if (index == 4)
{
index = 3;
}
am = new AssetMachine();
am.itemNR = (xlRange.Cells[i, 3].Value2 ?? "").ToString();
am.contractNR = (xlRange.Cells[i, 4].Value2 ?? "").ToString();
am.name = (xlRange.Cells[i, 6].Value2 ?? "").ToString();
am.characteristics = new List <Characteristic>();
am.assetScopeOfSupply = new List <AssetScopeOfSupply>();
projectFile.assetGroups.Last().assetGroups.Last().assetMachines.Add(am);
break;
case "S":
break;
case "C":
c = new Characteristic();
c.name = (xlRange.Cells[i, 6].Value2 ?? "").ToString();
c.prefix = (xlRange.Cells[i, 8].Value2 ?? "").ToString();
c.value = (xlRange.Cells[i, 9].Value2 ?? "").ToString();
c.unit = (xlRange.Cells[i, 10].Value2 ?? "").ToString();
switch (index)
{
case 0:
projectFile.characteristics.Add(c);
break;
case 1:
projectFile.assetGroups.Last().characteristics.Add(c);
break;
case 2:
projectFile.assetGroups.Last().assetGroups.Last().characteristics.Add(c);
break;
case 3:
projectFile.assetGroups.Last().assetGroups.Last().assetMachines.Last().characteristics.Add(c);
break;
}
//Add Characteristic to parent group, machine or scope
break;
case "T":
t = new Statement();
t.statement = (xlRange.Cells[i, 6].Value2 ?? "").ToString();
//Add Statement to group, machine or scope
break;
}
}
}
}