private static int getborder(Excel.Border border) { var style = border.LineStyle; var weight = border.Weight; if (style == Excel.XlLineStyle.xlLineStyleNone.GetHashCode()) { return(0); } else if (style == Excel.XlLineStyle.xlContinuous.GetHashCode()) { if (weight == Excel.XlBorderWeight.xlHairline.GetHashCode()) { return(1); } else if (weight == Excel.XlBorderWeight.xlThin.GetHashCode()) { return(1); } else if (weight == Excel.XlBorderWeight.xlThick.GetHashCode()) { return(4); } else if (weight == Excel.XlBorderWeight.xlMedium.GetHashCode()) { return(2); } } else if (style == Excel.XlLineStyle.xlDouble.GetHashCode()) { return(20); } return(0); }
public void SetBorder(BorderThickness leftBorderThickness, BorderThickness topBorderThickness, BorderThickness rightBorderThickness, BorderThickness bottomBorderThickness) { AssertNotDisposed(); Excel.Range _range = _GetRange(); var blackColor = ColorTranslator.ToOle(Color.Black); Excel.Borders _borders = _range.Borders; Excel.Border _leftBorder = _borders[Excel.XlBordersIndex.xlEdgeLeft]; Excel.Border _topBorder = _borders[Excel.XlBordersIndex.xlEdgeTop]; Excel.Border _rightBorder = _borders[Excel.XlBordersIndex.xlEdgeRight]; Excel.Border _bottomBorder = _borders[Excel.XlBordersIndex.xlEdgeBottom]; _leftBorder.LineStyle = leftBorderThickness == BorderThickness.None ? Excel.XlLineStyle.xlLineStyleNone : Excel.XlLineStyle.xlContinuous; _topBorder.LineStyle = topBorderThickness == BorderThickness.None ? Excel.XlLineStyle.xlLineStyleNone : Excel.XlLineStyle.xlContinuous; _rightBorder.LineStyle = rightBorderThickness == BorderThickness.None ? Excel.XlLineStyle.xlLineStyleNone : Excel.XlLineStyle.xlContinuous; _bottomBorder.LineStyle = bottomBorderThickness == BorderThickness.None ? Excel.XlLineStyle.xlLineStyleNone : Excel.XlLineStyle.xlContinuous; _leftBorder.Weight = EnumConvert.ConvertBorderThickness(leftBorderThickness); _leftBorder.Color = blackColor; _topBorder.Weight = EnumConvert.ConvertBorderThickness(topBorderThickness); _topBorder.Color = blackColor; _rightBorder.Weight = EnumConvert.ConvertBorderThickness(rightBorderThickness); _rightBorder.Color = blackColor; _bottomBorder.Weight = EnumConvert.ConvertBorderThickness(bottomBorderThickness); _bottomBorder.Color = blackColor; Marshal.ReleaseComObject(_bottomBorder); Marshal.ReleaseComObject(_rightBorder); Marshal.ReleaseComObject(_topBorder); Marshal.ReleaseComObject(_leftBorder); Marshal.ReleaseComObject(_borders); Marshal.ReleaseComObject(_range); }
/// <summary> /// 设置连续多单元格块范围 /// </summary> /// <param name="startRow"></param> /// <param name="startCol"></param> /// <param name="endRow"></param> /// <param name="endCol"></param> public void SetRange(int startRow, int startCol, int endRow, int endCol) { this._range = this._worksheet.Range[this._worksheet.Cells[startRow, startCol], this._worksheet.Cells[endRow, endCol]]; this._font = this._range.Font; this._borders = this._range.Borders; this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft]; this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop]; this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight]; this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom]; }
/// <summary> /// 设置单元格范围 /// </summary> /// <param name="row"></param> /// <param name="col"></param> public void SetRange(int row, int col) { this._range = this._worksheet.Cells[row, col]; this._font = this._range.Font; this._borders = this._range.Borders; this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft]; this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop]; this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight]; this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom]; }
public void SetHorizontalBorderColor(Color color) { Excel.Range _range = _GetRange(); Excel.Borders _borders = _range.Borders; Excel.Border _border = _borders[Excel.XlBordersIndex.xlInsideHorizontal]; var oleColor = ColorTranslator.ToOle(color); _border.Color = oleColor; Marshal.ReleaseComObject(_border); Marshal.ReleaseComObject(_borders); Marshal.ReleaseComObject(_range); }
/// <summary> /// 设置单元格边框(只能设置单个单元格的边框) /// </summary> /// <param name="range">Range对象</param> /// <param name="bordersEdge">单元格边框范围</param> /// <param name="ExcelBordersEdge">单元格边线</param> /// <param name="bordersLineStyle">边框线条分隔</param> /// <param name="bordersWeight">边框线粗细</param> public void SetCellsBorder(Excel.Range range, ExcelBordersEdge bordersEdge, ExcelBordersLineStyle bordersLineStyle, ExcelBorderWeight bordersWeight) { //range.Select(); Excel.Border border = null; switch (bordersEdge) { //左右顶底的线 case ExcelBordersEdge.EdgeLeft: border = range.Borders[Excel.XlBordersIndex.xlEdgeLeft]; break; case ExcelBordersEdge.EdgeRight: border = range.Borders[Excel.XlBordersIndex.xlEdgeRight]; break; case ExcelBordersEdge.EdgeTop: border = range.Borders[Excel.XlBordersIndex.xlEdgeTop]; break; case ExcelBordersEdge.EdgeBottom: border = range.Borders[Excel.XlBordersIndex.xlEdgeBottom]; break; //对角线 case ExcelBordersEdge.DiagonalDown: border = range.Borders[Excel.XlBordersIndex.xlDiagonalDown]; break; case ExcelBordersEdge.DiagonUp: border = range.Borders[Excel.XlBordersIndex.xlDiagonalUp]; break; //边框内部是横竖线(不包括边框) case ExcelBordersEdge.InsideHorizontal: border = range.Borders[Excel.XlBordersIndex.xlInsideHorizontal]; break; case ExcelBordersEdge.InsideVertical: border = range.Borders[Excel.XlBordersIndex.xlInsideVertical]; break; case ExcelBordersEdge.LineStyleNone: //所先范围内所有线都没有 range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone; //xlNone range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone; break; } if (border != null) { //XlLineStyle Excel.XlLineStyle mXlLineStyle = Excel.XlLineStyle.xlContinuous; switch (bordersLineStyle) { case ExcelBordersLineStyle.Continious: mXlLineStyle = Excel.XlLineStyle.xlContinuous; break; case ExcelBordersLineStyle.Dash: mXlLineStyle = Excel.XlLineStyle.xlDash; break; case ExcelBordersLineStyle.DashDot: mXlLineStyle = Excel.XlLineStyle.xlDashDot; break; case ExcelBordersLineStyle.DashDotDot: mXlLineStyle = Excel.XlLineStyle.xlDashDotDot; break; case ExcelBordersLineStyle.Dot: mXlLineStyle = Excel.XlLineStyle.xlDot; break; case ExcelBordersLineStyle.Double: mXlLineStyle = Excel.XlLineStyle.xlDouble; break; case ExcelBordersLineStyle.StyleNone: mXlLineStyle = Excel.XlLineStyle.xlLineStyleNone; break; case ExcelBordersLineStyle.SlantDashDot: mXlLineStyle = Excel.XlLineStyle.xlSlantDashDot; break; } border.LineStyle = mXlLineStyle; //XlBorderWeight Excel.XlBorderWeight mXlBorderWeight = Excel.XlBorderWeight.xlThin; switch (bordersWeight) { case ExcelBorderWeight.Hairline: mXlBorderWeight = Excel.XlBorderWeight.xlHairline; break; case ExcelBorderWeight.Medium: mXlBorderWeight = Excel.XlBorderWeight.xlMedium; break; case ExcelBorderWeight.Thick: mXlBorderWeight = Excel.XlBorderWeight.xlThick; break; case ExcelBorderWeight.Thin: mXlBorderWeight = Excel.XlBorderWeight.xlThin; break; } border.Weight = mXlBorderWeight; }//end if }
/// <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); }