//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["C2"].Value = "Unit:"; worksheet.Range["D2"].Value = "USD"; worksheet.Range["C4:D4"].Merge(); worksheet.Range["C4:D4"].Value = "FY 2019"; worksheet.Range["C5:D5"].Merge(); worksheet.Range["C5:D5"].Value = "Sales"; worksheet.Range["C6"].Value = "Monthly"; worksheet.Range["D6"].Value = "Cumulative"; 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]; worksheet.Range[$"D{7 + i}"].Formula = $"=SUM($C$7:C{7 + i})"; } // Set color using string worksheet.Range["D2"].Interior.Color = ColorUtilities.StringToColor("yellow"); worksheet.Range["D2"].Borders.Color = ColorUtilities.StringToColor("black"); worksheet.Range["B4:D6"].Interior.Color = ColorUtilities.StringToColor("#ADD8E6"); worksheet.Range["C4:D6"].Borders.Color = ColorUtilities.StringToColor("#000000FF"); worksheet.Range["B7:B18"].Interior.Color = ColorUtilities.StringToColor("rgb(211,211,211)"); worksheet.Range["B7:D18"].Borders.Color = ColorUtilities.StringToColor("rgba(0,0,0,1)"); 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:D18"].VerticalAlignment = VerticalAlignment.Center; worksheet.Range["B4:D6"].Font.Bold = true; worksheet.Range["C7:D18"].NumberFormat = "#,##0"; worksheet.Range["C2"].HorizontalAlignment = HorizontalAlignment.Right; // Save to an excel file workbook.Save("SetColorUsingString.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Range("C2").Value = "Unit:" worksheet.Range("D2").Value = "USD" worksheet.Range("C4:D4").Merge() worksheet.Range("C4:D4").Value = "FY 2019" worksheet.Range("C5:D5").Merge() worksheet.Range("C5:D5").Value = "Sales" worksheet.Range("C6").Value = "Monthly" worksheet.Range("D6").Value = "Cumulative" 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) worksheet.Range($"D{7 + i}").Formula = $"=SUM($C$7:C{7 + i})" Next ' Set color using string worksheet.Range("D2").Interior.Color = ColorUtilities.StringToColor("yellow") worksheet.Range("D2").Borders.Color = ColorUtilities.StringToColor("black") worksheet.Range("B4:D6").Interior.Color = ColorUtilities.StringToColor("#ADD8E6") worksheet.Range("C4:D6").Borders.Color = ColorUtilities.StringToColor("#000000FF") worksheet.Range("B7:B18").Interior.Color = ColorUtilities.StringToColor("rgb(211,211,211)") worksheet.Range("B7:D18").Borders.Color = ColorUtilities.StringToColor("rgba(0,0,0,1)") 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:D18").VerticalAlignment = VerticalAlignment.Center worksheet.Range("B4:D6").Font.Bold = True worksheet.Range("C7:D18").NumberFormat = "#,##0" worksheet.Range("C2").HorizontalAlignment = HorizontalAlignment.Right ' save to an excel file workbook.Save("SetColorUsingString.xlsx")