public static void Export(TECBid bid, string path) { File.Copy(@"C:\Users\dtaylor\Dropbox (TEC Systems)\Sales\Estimating tools\REV.5.Estimate 2017.01.xlsx", path); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true)) { WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Points List"); if (worksheetPart != null) { int x = 5; foreach (TECSystem system in bid.Systems) { var currentSystem = system; foreach (TECEquipment equipment in system.Equipment) { var currentEquipment = equipment; foreach (TECSubScope subScope in equipment.SubScope) { worksheetPart.Worksheet.InsertAt(new Row(), x); var subScopeInsert = new SubScopeRow(); subScopeInsert.System = currentSystem; subScopeInsert.Equipment = currentEquipment; subScopeInsert.SubScope = subScope; insertSubScopeIntoSheet(subScopeInsert, x, worksheetPart.Worksheet); x++; } } } Cell cell = GetCell(worksheetPart.Worksheet, "C", 5); cell.CellValue = new CellValue("test"); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // Save the worksheet. worksheetPart.Worksheet.Save(); } } }
private static void insertSubScopeIntoSheet(SubScopeRow subScope, int rowIndex, Worksheet workSheet) { Cell cell = GetCell(workSheet, "A", rowIndex); if (subScope.SubScope.Connection.ParentController != null) { cell.CellValue = new CellValue(subScope.SubScope.Connection.ParentController.Name.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.String); } cell = GetCell(workSheet, "B", rowIndex); cell.CellValue = new CellValue(subScope.System.Name.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = GetCell(workSheet, "C", rowIndex); cell.CellValue = new CellValue(subScope.SubScope.Name.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = GetCell(workSheet, "D", rowIndex); string deviceString = ""; foreach (TECDevice device in subScope.SubScope.Devices) { deviceString += "("; deviceString += device.Name; deviceString += ") "; } cell.CellValue = new CellValue(deviceString.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = GetCell(workSheet, "E", rowIndex); int num = 0; foreach (TECPoint point in subScope.SubScope.Points) { if (point.Type == IOType.AI) { num += point.Quantity; } } cell.CellValue = new CellValue(num.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = GetCell(workSheet, "F", rowIndex); num = 0; foreach (TECPoint point in subScope.SubScope.Points) { if (point.Type == IOType.DI) { num += point.Quantity; } } cell.CellValue = new CellValue(num.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = GetCell(workSheet, "G", rowIndex); num = 0; foreach (TECPoint point in subScope.SubScope.Points) { if (point.Type == IOType.AO) { num += point.Quantity; } } cell.CellValue = new CellValue(num.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = GetCell(workSheet, "H", rowIndex); num = 0; foreach (TECPoint point in subScope.SubScope.Points) { if (point.Type == IOType.DO) { num += point.Quantity; } } cell.CellValue = new CellValue(num.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = GetCell(workSheet, "I", rowIndex); num = 0; foreach (TECPoint point in subScope.SubScope.Points) { if (point.Type == IOType.Protocol) { num += point.Quantity; } } cell.CellValue = new CellValue(num.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); //cell = GetCell(workSheet, "J", rowIndex); //cell.CellValue = new CellValue(subScope.SubScope.Connection.Terminations.ToString()); //cell.DataType = new EnumValue<CellValues>(CellValues.Number); var conduitString = ""; var flexNum = 0; if (subScope.SubScope.Connection.ConduitType != null) { conduitString = subScope.SubScope.Connection.ConduitType.Name; foreach (ICost cost in subScope.SubScope.Connection.ConduitType.AssociatedCosts) { if (cost.Name.ToUpper().Contains("FLEX")) { flexNum++; } } } cell = GetCell(workSheet, "K", rowIndex); cell.CellValue = new CellValue(flexNum.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); if (conduitString.Contains("3/4")) { cell = GetCell(workSheet, "L", rowIndex); cell.CellValue = new CellValue(subScope.SubScope.Connection.ConduitLength.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } if (conduitString.Contains("1\"")) { cell = GetCell(workSheet, "M", rowIndex); cell.CellValue = new CellValue(subScope.SubScope.Connection.ConduitLength.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } if (conduitString.Contains("1.5")) { cell = GetCell(workSheet, "N", rowIndex); cell.CellValue = new CellValue(subScope.SubScope.Connection.ConduitLength.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } if (conduitString.Contains("2")) { cell = GetCell(workSheet, "O", rowIndex); cell.CellValue = new CellValue(subScope.SubScope.Connection.ConduitLength.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } cell = GetCell(workSheet, "P", rowIndex); if (conduitString.ToUpper().Contains("EMT")) { cell.CellValue = new CellValue("EMT"); cell.DataType = new EnumValue <CellValues>(CellValues.String); } else if (conduitString.ToUpper().Contains("RIG")) { cell.CellValue = new CellValue("RGS"); cell.DataType = new EnumValue <CellValues>(CellValues.String); } cell = GetCell(workSheet, "Q", rowIndex); cell.CellValue = new CellValue("instr"); cell.DataType = new EnumValue <CellValues>(CellValues.String); double length = 0; foreach (TECElectricalMaterial type in subScope.SubScope.Connection.Protocol.ConnectionTypes) { length += subScope.SubScope.Connection.Length; } cell = GetCell(workSheet, "R", rowIndex); cell.CellValue = new CellValue(length.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.Number); }