/// <summary> /// Convert a system color to Excel color /// </summary> /// <param name="color">The system color to convert</param> /// <returns>Excel color</returns> public static XlRgbColor ConvertSytemColorToXlColor(Color color) { int rgbColor = VB.Information.RGB(color.R, color.G, color.B); XlRgbColor xlColor = (XlRgbColor)rgbColor; return(xlColor); }
public static XlRgbColor GetExcelColor(ProductMasterItem item) { XlRgbColor color = XlRgbColor.rgbWhite; if (MasterBrushDictionary.ContainsKey(item)) { Brush brush = MasterBrushDictionary[item]; if (Equals(brush, Brushes.Aquamarine)) { color = XlRgbColor.rgbAquamarine; } else if (Equals(brush, Brushes.LightGreen)) { color = XlRgbColor.rgbLightGreen; } else if (Equals(brush, Brushes.DarkSalmon)) { color = XlRgbColor.rgbDarkSalmon; } else if (Equals(brush, Brushes.CadetBlue)) { color = XlRgbColor.rgbCadetBlue; } else if (Equals(brush, Brushes.BurlyWood)) { color = XlRgbColor.rgbBurlyWood; } } return(color); }
public void changeColor(int Row, int Line, XlRgbColor color) { Row++; Line++; Range range = ws.Range[ws.Cells[Row, Line], ws.Cells[Row, Line]]; range.Interior.Color = color; }
private void ApplyRowStyle(int rowIndex, int columnsCount, XlRgbColor bgColor, bool needBold) { Range range = InvoicesSheet.Rows.Range[InvoicesSheet.Rows.Cells[rowIndex, 1], InvoicesSheet.Rows.Cells[rowIndex, columnsCount]]; range.Font.Bold = needBold; range.Interior.Color = bgColor; range.Borders.LineStyle = XlLineStyle.xlContinuous; }
private static void SchoolInfo(Dictionary <string, School> schools, _Worksheet resultSheet) { double[] mon; int totalWrite = 2; int moneyWrite = 2; int itemWrite = 2; int itemTot; int orderTotalsStart = 1; int totalsColStart = 8; int itemSchoolStart = 14; string itemType = "Small"; XlRgbColor[] Colours = new XlRgbColor[] { XlRgbColor.rgbLightGreen, XlRgbColor.rgbLightYellow, XlRgbColor.rgbLavender, XlRgbColor.rgbLightBlue, XlRgbColor.rgbLightSalmon }; int schoolColourIndex = 0; resultSheet.Cells[1, orderTotalsStart] = "School Name"; resultSheet.Cells[1, orderTotalsStart + 1] = "Order Number"; resultSheet.Cells[1, orderTotalsStart + 2] = "Net"; resultSheet.Cells[1, orderTotalsStart + 3] = "Fee"; resultSheet.Cells[1, orderTotalsStart + 4] = "Amount"; resultSheet.Cells[1, totalsColStart] = "School Name"; resultSheet.Cells[1, totalsColStart + 1] = "Total Net"; resultSheet.Cells[1, totalsColStart + 2] = "Total Fee"; resultSheet.Cells[1, totalsColStart + 3] = "Total Amount"; resultSheet.Cells[1, totalsColStart + 4] = "Number of Orders"; resultSheet.Cells[1, itemSchoolStart] = "School Name"; resultSheet.Cells[1, itemSchoolStart + 1] = "Item Name"; resultSheet.Cells[1, itemSchoolStart + 2] = "Number of Items"; resultSheet.Cells[1, itemSchoolStart + 3] = "SKU"; resultSheet.Cells[1, itemSchoolStart + 4] = "Items per type"; foreach (string schoolName in schools.Keys) { mon = schools[schoolName].GetAllTotals(); resultSheet.Cells[totalWrite, totalsColStart] = schoolName; resultSheet.Cells[totalWrite, totalsColStart + 1] = mon[0]; resultSheet.Cells[totalWrite, totalsColStart + 2] = mon[1]; resultSheet.Cells[totalWrite, totalsColStart + 3] = mon[2]; resultSheet.Cells[totalWrite, totalsColStart + 4] = mon[3]; resultSheet.Range[resultSheet.Cells[totalWrite, totalsColStart], resultSheet.Cells[totalWrite, totalsColStart + 4]].Interior.Color = Colours[schoolColourIndex]; totalWrite++; resultSheet.Cells[moneyWrite, orderTotalsStart] = schoolName; Dictionary <int, double[]> money = schools[schoolName].GetMoney(); foreach (int orderNum in money.Keys) { mon = money[orderNum]; resultSheet.Cells[moneyWrite, orderTotalsStart + 1] = orderNum; resultSheet.Cells[moneyWrite, orderTotalsStart + 2] = mon[0]; resultSheet.Cells[moneyWrite, orderTotalsStart + 3] = mon[1]; resultSheet.Cells[moneyWrite, orderTotalsStart + 4] = mon[2]; resultSheet.Range[resultSheet.Cells[moneyWrite, orderTotalsStart], resultSheet.Cells[moneyWrite, orderTotalsStart + 4]].Interior.Color = Colours[schoolColourIndex]; moneyWrite++; } resultSheet.Cells[itemWrite, itemSchoolStart] = schoolName; itemType = "Small"; itemTot = 0; foreach (string[] item in schools[schoolName].GetSortItems()) { resultSheet.Cells[itemWrite, itemSchoolStart + 1] = item[0]; resultSheet.Cells[itemWrite, itemSchoolStart + 2] = item[1]; resultSheet.Cells[itemWrite, itemSchoolStart + 3] = item[2]; resultSheet.Range[resultSheet.Cells[itemWrite, itemSchoolStart], resultSheet.Cells[itemWrite, itemSchoolStart + 4]].Interior.Color = Colours[schoolColourIndex]; if (itemType.Equals(item[0].Substring(item[0].LastIndexOf(" ") + 1))) { itemTot += Convert.ToInt32(item[1]); } else { resultSheet.Cells[itemWrite - 1, itemSchoolStart + 4] = itemTot.ToString(); itemType = item[0].Substring(item[0].LastIndexOf(" ") + 1); itemTot = Convert.ToInt32(item[1]); } itemWrite++; } resultSheet.Cells[itemWrite - 1, itemSchoolStart + 4] = itemTot.ToString(); schoolColourIndex = (schoolColourIndex + 1) % Colours.Length; } }
public static void SetCellBackground(Worksheet worksheet, CellIndex from, CellIndex to, XlRgbColor color) { worksheet.Range[worksheet.Cells[from.x, from.y], worksheet.Cells[to.x, to.y]].Interior.Color = color; }
/// <summary> /// Writes the text into the given range. Does not add a row. /// </summary> /// <param name="text">Text to be written out</param> /// <param name="startColumn">Zero indexed column</param> /// <param name="columns">Total number of columns the text takes</param> /// <param name="row"></param> /// <param name="worksheet"></param> private static void OutputText(string text, int startColumn, int columns, int row, Worksheet worksheet, XlRgbColor color = XlRgbColor.rgbBlack, XlRgbColor bg = XlRgbColor.rgbWhite) { columns--; Range rng = GetRange(_minCol + startColumn, row, _minCol + startColumn + columns, row, worksheet); rng.UnMerge(); rng.Cells.Font.Size = TextFontSize; rng.Merge(); rng.WrapText = true; rng.Value = text; rng.Font.Color = color; // text color rng.Interior.Color = bg; }
void SelectionLocationReceived(object sender, EventArgs e) { RangePacket packet = (RangePacket)sender; Range selection = ActiveWorksheet.Range[packet.RangeInfo]; ExcelUser user = packet.User; if (lastRange != null) { Debug.WriteLine(lastLeftColor); lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Color = lastLeftColor; lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight = lastLeftWeight; lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle = lastLeftStyle; lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Color = lastTopColor; lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Weight = lastTopWeight; lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle = lastTopStyle; lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Color = lastRightColor; lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Weight = lastRightWeight; lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle = lastRightStyle; lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Color = lastBottomColor; lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = lastBottomWeight; lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = lastBottomStyle; lastRange.ClearComments(); if (lastComments.Count >= 0) { if (lastComments[0] != null) { lastRange.AddComment(lastComments[0]); } } //all you have to do is .copy!!!!!!!!!!!!!!!!!!!!!! //selection.Copy(lastRange); //ActiveWorksheet.Range["A1"].Value = "test"; } lastRange = selection; Debug.WriteLine("got selection change"); ActiveRange = selection; lastComments = new List <string>(); lastLeftColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color; lastLeftWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight; lastLeftStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle; lastTopColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color; lastTopWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight; lastTopStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle; lastRightColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color; lastRightWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight; lastRightStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle; lastBottomColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color; lastBottomWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight; lastBottomStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle; selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color = XlRgbColor.rgbBlue; selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium; selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color = XlRgbColor.rgbBlue; selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium; selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color = XlRgbColor.rgbBlue; selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium; selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color = XlRgbColor.rgbBlue; selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium; lastComments.Add(selection.Comment?.Text()); selection.ClearComments(); Debug.WriteLine("Made it to comment adding"); if (selection.Count < 2) { selection.AddComment(user.ToString() + ": Updating this cell at the moment"); } else { selection[selection.Columns.Count]?.AddComment(user.ToString() + ": Updating these cells at the moment"); } }