/// <summary> /// Formats the schedule with alternating background colors /// </summary> /// <param name="viewSchedule"></param> public void FormatScheduleColumns(ViewSchedule viewSchedule) { ScheduleDefinition definition = viewSchedule.Definition; int index = 0; Color white = new Color(0xFF, 0xFF, 0xFF); Color highlight = new Color(0xd8, 0xd8, 0xd8); bool applyHighlight = false; // Loop over fields in order foreach (ScheduleFieldId id in definition.GetFieldOrder()) { // Index 0, 2, etc use highlight color if (index % 2 == 0) { applyHighlight = true; } // Index 1, 3, etc use no background color else { applyHighlight = false; } // Get the field style ScheduleField field = definition.GetField(id); TableCellStyle style = field.GetStyle(); TableCellStyleOverrideOptions options = style.GetCellStyleOverrideOptions(); // Set override options for background color per requirement options.BackgroundColor = applyHighlight; style.SetCellStyleOverrideOptions(options); // Set background color per requirement style.BackgroundColor = applyHighlight ? highlight : white; field.SetStyle(style); index++; } }
/// <summary> /// /// </summary> /// <param name="filePath"></param> /// <param name="sched"></param> /// <param name="doc"></param> /// <param name="pt"></param> /// <param name="contentOnly"></param> public void AddScheduleData(string filePath, ViewSchedule sched, Document doc, PathType pt, bool contentOnly) { string docPath; if (doc.IsWorkshared) { docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(doc.GetWorksharingCentralModelPath()); } else { docPath = doc.PathName; } string fullPath; if (pt == PathType.Absolute) { fullPath = filePath; } else { fullPath = PathExchange.GetFullPath(filePath, docPath); } // Get the file path excelFilePath = fullPath; if (!File.Exists(excelFilePath)) { return; } // read the Excel file and create the schedule Excel.Application excelApp = new Excel.Application(); Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath); Excel.Sheets worksheets = workbook.Worksheets; worksheet = null; foreach (Excel.Worksheet ws in worksheets) { if (ws.Name.Trim() == sched.Name.Trim()) { worksheet = ws; } } if (worksheet == null) { return; } //TaskDialog.Show("Test", "Worksheet found"); // Find the ThinLine linestyle CategoryNameMap lineSubCats = doc.Settings.Categories.get_Item(BuiltInCategory.OST_Lines).SubCategories; ElementId thinLineStyle = new ElementId(-1); ElementId hairlineStyle = new ElementId(-1); ElementId thinStyle = new ElementId(-1); ElementId mediumStyle = new ElementId(-1); ElementId thickStyle = new ElementId(-1); foreach (Category style in lineSubCats) { if (style.Name == "Thin Lines") { thinLineStyle = style.Id; } if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.hairlineInt) { hairlineStyle = style.Id; } else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thinInt) { thinStyle = style.Id; } else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.mediumInt) { mediumStyle = style.Id; } else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thickInt) { thickStyle = style.Id; } } if (hairlineStyle.IntegerValue == -1) { hairlineStyle = thinLineStyle; } if (thinStyle.IntegerValue == -1) { thinStyle = thinLineStyle; } if (mediumStyle.IntegerValue == -1) { mediumStyle = thinLineStyle; } if (thickStyle.IntegerValue == -1) { thickStyle = thinLineStyle; } // Find out how many rows and columns we need in the schedule Excel.Range rng = ActualUsedRange(worksheet); Excel.Range range = rng; int rowCount = range.Rows.Count; int columnCount = range.Columns.Count; // Get the schedule body to set the overall width TableSectionData bodyData = sched.GetTableData().GetSectionData(SectionType.Body); if (!contentOnly) { double schedWidth = range.Columns.Width; try { bodyData.SetColumnWidth(0, (schedWidth * pointWidthInches) / 12); } catch { } } // Get the header body to create the necessary rows and columns TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header); if (!contentOnly) { //TaskDialog.Show("Test: ", "Row Count: " + rowCount.ToString() + "\nColumn Count: " + columnCount.ToString()); for (int i = 0; i < columnCount - 1; i++) { headerData.InsertColumn(1); } for (int i = 0; i < rowCount - 1; i++) { headerData.InsertRow(1); } for (int i = 1; i <= headerData.NumberOfColumns; i++) { try { Excel.Range cell = worksheet.Cells[1, i]; headerData.SetColumnWidth(i - 1, (cell.Width * pointWidthInches) / 12); } catch { } } for (int i = 1; i <= headerData.NumberOfRows; i++) { try { Excel.Range cell = worksheet.Cells[i, 1]; headerData.SetRowHeight(i - 1, (cell.Height * pointWidthInches) / 12); } catch { } } } List <TableMergedCell> mergedCells = new List <TableMergedCell>(); int errorCount = 0; for (int i = 1; i <= headerData.NumberOfRows; i++) // Iterate through rows of worksheet data { for (int j = 1; j <= headerData.NumberOfColumns; j++) // Iterate through columns of worksheet data { // Get the current cell in the worksheet grid Excel.Range cell = worksheet.Cells[i, j]; // If adjusting the formatting or adding content is not necessary, // just update the text content. This is via a UI switch. if (contentOnly) { try { headerData.SetCellText(i - 1, j - 1, cell.Text); continue; } catch { errorCount++; continue; } } Excel.Font font = cell.Font; Excel.DisplayFormat dispFormat = cell.DisplayFormat; TableCellStyle cellStyle = new TableCellStyle(); TableCellStyleOverrideOptions styleOverride = cellStyle.GetCellStyleOverrideOptions(); Excel.Border topEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeTop]; Excel.Border bottomEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeBottom]; Excel.Border leftEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeLeft]; Excel.Border rightEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeRight]; // Determine Bottom Edge Line Style if (bottomEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone) { cellStyle.BorderBottomLineStyle = new ElementId(-1); } else { switch (bottomEdge.Weight) { case (int)Excel.XlBorderWeight.xlHairline: cellStyle.BorderBottomLineStyle = hairlineStyle; break; case (int)Excel.XlBorderWeight.xlThin: cellStyle.BorderBottomLineStyle = thinStyle; break; case (int)Excel.XlBorderWeight.xlMedium: cellStyle.BorderBottomLineStyle = mediumStyle; break; case (int)Excel.XlBorderWeight.xlThick: cellStyle.BorderBottomLineStyle = thickStyle; break; } } // Determine Top Edge Line Style if (topEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone) { cellStyle.BorderTopLineStyle = new ElementId(-1); } else { switch (topEdge.Weight) { case (int)Excel.XlBorderWeight.xlHairline: cellStyle.BorderTopLineStyle = hairlineStyle; break; case (int)Excel.XlBorderWeight.xlThin: cellStyle.BorderTopLineStyle = thinStyle; break; case (int)Excel.XlBorderWeight.xlMedium: cellStyle.BorderTopLineStyle = mediumStyle; break; case (int)Excel.XlBorderWeight.xlThick: cellStyle.BorderTopLineStyle = thickStyle; break; } } // Determine Left Edge Line Style if (leftEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone) { cellStyle.BorderLeftLineStyle = new ElementId(-1); } else { switch (leftEdge.Weight) { case (int)Excel.XlBorderWeight.xlHairline: cellStyle.BorderLeftLineStyle = hairlineStyle; break; case (int)Excel.XlBorderWeight.xlThin: cellStyle.BorderLeftLineStyle = thinStyle; break; case (int)Excel.XlBorderWeight.xlMedium: cellStyle.BorderLeftLineStyle = mediumStyle; break; case (int)Excel.XlBorderWeight.xlThick: cellStyle.BorderLeftLineStyle = thickStyle; break; } } // Determine Right Edge Line Style if (rightEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone) { cellStyle.BorderRightLineStyle = new ElementId(-1); } else { switch (rightEdge.Weight) { case (int)Excel.XlBorderWeight.xlHairline: cellStyle.BorderRightLineStyle = hairlineStyle; break; case (int)Excel.XlBorderWeight.xlThin: cellStyle.BorderRightLineStyle = thinStyle; break; case (int)Excel.XlBorderWeight.xlMedium: cellStyle.BorderRightLineStyle = mediumStyle; break; case (int)Excel.XlBorderWeight.xlThick: cellStyle.BorderRightLineStyle = thickStyle; break; } } // Border Styles are always overridden styleOverride.BorderBottomLineStyle = true; styleOverride.BorderTopLineStyle = true; styleOverride.BorderLeftLineStyle = true; styleOverride.BorderRightLineStyle = true; if (styleOverride.BorderBottomLineStyle || styleOverride.BorderTopLineStyle || styleOverride.BorderLeftLineStyle || styleOverride.BorderRightLineStyle) { styleOverride.BorderLineStyle = true; } // Get Background color and font name System.Drawing.Color backGroundColor = System.Drawing.ColorTranslator.FromOle((int)cell.Interior.Color); cellStyle.BackgroundColor = new Color(backGroundColor.R, backGroundColor.G, backGroundColor.B); styleOverride.BackgroundColor = true; cellStyle.FontName = cell.Font.Name; styleOverride.Font = true; // Determine Horizontal Alignment // If its not set to left, right or center, do not modify switch (dispFormat.HorizontalAlignment) { case (int)Excel.XlHAlign.xlHAlignLeft: cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left; styleOverride.HorizontalAlignment = true; break; case (int)Excel.XlHAlign.xlHAlignRight: cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right; styleOverride.HorizontalAlignment = true; break; case (int)Excel.XlHAlign.xlHAlignGeneral: // No specific style assigned // Check if it's a number which is typically right aligned if (double.TryParse(cell.Text, out double alignTest)) { cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right; styleOverride.HorizontalAlignment = true; } else // Assume text and left align it { cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left; styleOverride.HorizontalAlignment = true; } break; case (int)Excel.XlHAlign.xlHAlignCenter: cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Center; styleOverride.HorizontalAlignment = true; break; } // Get the vertical alignment of the cell switch (dispFormat.VerticalAlignment) { case (int)Excel.XlVAlign.xlVAlignBottom: cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Bottom; styleOverride.VerticalAlignment = true; break; case (int)Excel.XlVAlign.xlVAlignTop: cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Top; styleOverride.VerticalAlignment = true; break; default: cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Middle; styleOverride.VerticalAlignment = true; break; } switch (dispFormat.Orientation) { case (int)Excel.XlOrientation.xlUpward: cellStyle.TextOrientation = 9; styleOverride.TextOrientation = true; break; case (int)Excel.XlOrientation.xlDownward: cellStyle.TextOrientation = -9; styleOverride.TextOrientation = true; break; case (int)Excel.XlOrientation.xlVertical: cellStyle.TextOrientation = 9; styleOverride.TextOrientation = true; break; default: int rotation = (int)cell.Orientation; if (rotation != (int)Excel.XlOrientation.xlHorizontal) { cellStyle.TextOrientation = rotation; styleOverride.TextOrientation = true; } break; } // Determine Text Size double textSize = Convert.ToDouble(font.Size); //double newTextSize = (textSize / 72) / 12; cellStyle.TextSize = textSize; styleOverride.FontSize = true; // Determine Font Color System.Drawing.Color fontColor = System.Drawing.ColorTranslator.FromOle((int)font.Color); cellStyle.TextColor = new Color(fontColor.R, fontColor.G, fontColor.B); styleOverride.FontColor = true; // NOTES: Bold is a bool // Italic is a bool // Underline is an int cellStyle.IsFontBold = (bool)font.Bold; cellStyle.IsFontItalic = (bool)font.Italic; cellStyle.IsFontUnderline = (int)font.Underline == 2; styleOverride.Bold = true; styleOverride.Italics = true; styleOverride.Underline = true; cellStyle.SetCellStyleOverrideOptions(styleOverride); if (cell.MergeCells == true) { TableMergedCell tmc = new TableMergedCell() { Left = j - 1, Right = cell.MergeArea.Columns.Count - 1, Top = i - 1, Bottom = (i - 1) + cell.MergeArea.Rows.Count - 1 }; // Check to see if the cell is already merged... bool alreadyMerged = false; foreach (TableMergedCell mergedCell in mergedCells) { bool left = false; bool right = false; bool top = false; bool bottom = false; if (i - 1 >= mergedCell.Top) { top = true; } if (i - 1 <= mergedCell.Bottom) { bottom = true; } if (j - 1 >= mergedCell.Left) { left = true; } if (j - 1 <= mergedCell.Right) { right = true; } //TaskDialog.Show("MergedCell", string.Format("Top: {0}\nBottom: {1}\nLeft: {2}\nRight: {3}\ni-1: {4}\nj-1: {5}", mergedCell.Top, mergedCell.Bottom, mergedCell.Left, mergedCell.Right, i - 1, j - 1)); if (top && bottom && left && right) { alreadyMerged = true; break; } } if (!alreadyMerged) { try { headerData.MergeCells(tmc); headerData.SetCellText(i - 1, j - 1, cell.Text); headerData.SetCellStyle(i - 1, j - 1, cellStyle); j += cell.MergeArea.Columns.Count - 1; mergedCells.Add(tmc); // TaskDialog.Show("Test", string.Format("This cell [{0},{1}] is merged.\nMerged Area: [{2},{3}]", cell.Row - 1, cell.Column - 1, cell.MergeArea.Rows.Count.ToString(), cell.MergeArea.Columns.Count.ToString())); } catch { } } } else { //TaskDialog.Show("Non Merged", string.Format("This cell is not merged with any others [{0}, {1}]", i - 1, j - 1)); try { headerData.SetCellText(i - 1, j - 1, cell.Text); headerData.SetCellStyle(i - 1, j - 1, cellStyle); } catch { } } } } if (errorCount > 0) { TaskDialog.Show("Warning", "Error reloading content for " + errorCount.ToString() + " cells.\n\nConsider unchecking the \"Content Only\" checkbox and reloading the schedule to force it to rebuild."); } // Write the Schema to the project Schema schema = null; try { schema = Schema.Lookup(schemaGUID); } catch { } ModifySchemaData(schema, sched.Id); workbook.Close(false); Marshal.ReleaseComObject(worksheets); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); excelApp.Quit(); Marshal.ReleaseComObject(excelApp); }