//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["C4"].Value = "FY 2019"; worksheet.Range["C5"].Value = "Sales"; worksheet.Range["C6"].Value = "Monthly"; string[] months = { "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar" }; int[] monthlySales = { 188897, 208146, 226196, 277318, 263273, 259845, 241047, 256306, 195845, 204934, 257852, 227779 }; for (int i = 0; i < months.Length; i++) { worksheet.Range[$"B{7 + i}"].Value = months[i]; worksheet.Range[$"C{7 + i}"].Value = monthlySales[i]; } // Set color using string worksheet.Range["B4:C6"].Interior.Color = Color.FromArgb(173, 216, 230); worksheet.Range["C4:C6"].Borders.Color = Color.FromArgb(0, 0, 0); worksheet.Range["B7:B18"].Interior.Color = Color.FromArgb(211, 211, 211); worksheet.Range["B7:C18"].Borders.Color = Color.FromArgb(0, 0, 0); worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin; worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin; worksheet.Range["B4:B6"].Merge(); worksheet.Range["C2:D18"].ColumnWidth = 15; worksheet.Range["B2:D18"].HorizontalAlignment = HorizontalAlignment.Center; worksheet.Range["B2:C18"].VerticalAlignment = VerticalAlignment.Center; worksheet.Range["B4:C6"].Font.Bold = true; worksheet.Range["C7:C18"].NumberFormat = "#,##0"; worksheet.Range["C2"].HorizontalAlignment = HorizontalAlignment.Right; // Use cell decoration to hightlight the highest sales ICornerFold cornerFold1 = new CornerFold(Color.Red, CornerPosition.LeftTop, 8); ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon( "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K", 12, 12, IconPosition.OutsideRight); worksheet.Range["C10"].Decoration = new Excel.CellDecoration(cornerFold1, new List<ICellDecorationIcon>() { cellDecorationIcon1 }); worksheet.Range["D10"].Value = "Highest"; // Use cell decoration to hightlight the lowest sales ICornerFold cornerFold2 = new CornerFold(Color.Green, CornerPosition.LeftTop, 8); ICellDecorationIcon cellDecorationIcon2 = new CellDecorationIcon( "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=", 12, 12, IconPosition.OutsideRight); worksheet.Range["C7"].Decoration = new Excel.CellDecoration(cornerFold2, new List<ICellDecorationIcon>() { cellDecorationIcon2 }); worksheet.Range["D7"].Value = "Lowest"; // Save to a .sjs file workbook.Save("CellDecoration.sjs");
' Create a new Workbook Dim workbook As New Workbook Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Range("C4").Value = "FY 2019" worksheet.Range("C5").Value = "Sales" worksheet.Range("C6").Value = "Monthly" Dim months As String() = {"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"} Dim monthlySales As Integer() = {188897, 208146, 226196, 277318, 263273, 259845, 241047, 256306, 195845, 204934, 257852, 227779} For i As Integer = 0 To months.Length - 1 worksheet.Range($"B{7 + i}").Value = months(i) worksheet.Range($"C{7 + i}").Value = monthlySales(i) Next ' Set color using string worksheet.Range("B4:C6").Interior.Color = Color.FromArgb(173, 216, 230) worksheet.Range("C4:C6").Borders.Color = Color.FromArgb(0, 0, 0) worksheet.Range("B7:B18").Interior.Color = Color.FromArgb(211, 211, 211) worksheet.Range("B7:C18").Borders.Color = Color.FromArgb(0, 0, 0) worksheet.Range("B4:B6").Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.Thin worksheet.Range("B4:B6").Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.Thin worksheet.Range("B4:B6").Merge() worksheet.Range("C2:D18").ColumnWidth = 15 worksheet.Range("B2:D18").HorizontalAlignment = HorizontalAlignment.Center worksheet.Range("B2:C18").VerticalAlignment = VerticalAlignment.Center worksheet.Range("B4:C6").Font.Bold = True worksheet.Range("C7:C18").NumberFormat = "#,##0" worksheet.Range("C2").HorizontalAlignment = HorizontalAlignment.Right ' Use cell decoration to highlight the highest sales Dim cornerFold1 As ICornerFold = New CornerFold(Color.Red, CornerPosition.LeftTop, 8) Dim cellDecorationIcon1 As ICellDecorationIcon = New CellDecorationIcon( "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K", 12, 12, IconPosition.OutsideRight) worksheet.Range("C10").Decoration = New Excel.CellDecoration(cornerFold1, New List(Of ICellDecorationIcon)() From {cellDecorationIcon1}) worksheet.Range("D10").Value = "Highest" ' Use cell decoration to highlight the lowest sales Dim cornerFold2 As ICornerFold = New CornerFold(Color.Green, CornerPosition.LeftTop, 8) Dim cellDecorationIcon2 As ICellDecorationIcon = New CellDecorationIcon( "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=", 12, 12, IconPosition.OutsideRight) worksheet.Range("C7").Decoration = New Excel.CellDecoration(cornerFold2, New List(Of ICellDecorationIcon)() From {cellDecorationIcon2}) worksheet.Range("D7").Value = "Lowest" ' Save to a .sjs file workbook.Save("CellDecoration.sjs")