//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; // Change column width and row height int[] columnWidths = { 24, 13, 14, 17 }; int[] rowHeights = { 15, 9, 19, 17, 17, 35, 32, 7, 17, 15, 15, 15, 15, 15, 15, 15, 15, 17 }; for (int i = 0; i < columnWidths.Length; i++) { worksheet.Columns[i].ColumnWidth = columnWidths[i]; } for (int i = 0; i < rowHeights.Length; i++) { worksheet.Rows[i].RowHeight = rowHeights[i]; } // Set data and style worksheet.Range["A3"].Value = "Invoice"; worksheet.Range["A3"].Font.Size = 14; worksheet.Range["A3"].Font.Bold = true; worksheet.Range["C3"].Value = "Date:"; worksheet.Range["C3"].HorizontalAlignment = HorizontalAlignment.Right; worksheet.Range["D3"].Formula2 = "=NOW()"; worksheet.Range["D3"].NumberFormat = "m/d/yyyy"; worksheet.Range["D3"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Medium; worksheet.Range["A5"].Value = "Shipping Address:"; worksheet.Range["C5"].Value = "Billing Address:"; worksheet.Range["A6"].Value = "150 Wayback Lane,\nNew York,\nNew York"; worksheet.Range["C6"].Value = "414 Rocket Drive,\nWesterville,\nOhio"; // Set table data object[,] data = new object[,] { {"Product", "Unit Price", "Quantity", "Total Price" }, {"Bike", 125, 1, "" }, {"Helmet", 35, 1, "" }, {"Gloves", 15.4, 1, "" }, {"Accessories", 20, 3, "" }, }; worksheet.Range["A9:D13"].Value = data; // Add table. ITable table = worksheet.Tables.Add(worksheet.Range["A9:D13"], true); // Set table Style worksheet.Range["B10:B13"].NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)"; worksheet.Range["D10:D13"].FormulaR1C1 = "=[@Quantity]*[@Unit Price]"; worksheet.Range["D10:D13"].NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)"; table.Columns[0].TotalsCalculation = TotalsCalculation.None; table.Columns[1].TotalsCalculation = TotalsCalculation.None; table.Columns[2].TotalsCalculation = TotalsCalculation.None; table.Columns[3].TotalsCalculation = TotalsCalculation.Sum; table.ShowTotals = true; // Merge cells worksheet.Range["A3:B3"].Merge(); worksheet.Range["A5:B5"].Merge(); worksheet.Range["A5:B5"].Borders.LineStyle = BorderLineStyle.Medium; worksheet.Range["C5:D5"].Merge(); worksheet.Range["C5:D5"].Borders.LineStyle = BorderLineStyle.Medium; worksheet.Range["A6:B7"].Merge(); worksheet.Range["A6:B7"].WrapText = true; worksheet.Range["A6:B7"].VerticalAlignment = VerticalAlignment.Center; worksheet.Range["A6:B7"].Borders.LineStyle = BorderLineStyle.Medium; worksheet.Range["C6:D7"].Merge(); worksheet.Range["C6:D7"].WrapText = true; worksheet.Range["C6:D7"].VerticalAlignment = VerticalAlignment.Center; worksheet.Range["C6:D7"].Borders.LineStyle = BorderLineStyle.Medium; worksheet.SheetView.DisplayGridlines = false; // Convert table to range. table.ConvertToRange(); // Save to an excel file workbook.Save("ConvertToRange.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim worksheet As IWorksheet = workbook.Worksheets(0) ' Change column width and row height Dim columnWidths() = {24, 13, 14, 17} Dim rowHeights() = {15, 9, 19, 17, 17, 35, 32, 7, 17, 15, 15, 15, 15, 15, 15, 15, 15, 17} For i = 0 To columnWidths.Length - 1 worksheet.Columns(i).ColumnWidth = columnWidths(i) Next For i = 0 To rowHeights.Length - 1 worksheet.Rows(i).RowHeight = rowHeights(i) Next ' Set data And style worksheet.Range("A3").Value = "Invoice" worksheet.Range("A3").Font.Size = 14 worksheet.Range("A3").Font.Bold = True worksheet.Range("C3").Value = "Date:" worksheet.Range("C3").HorizontalAlignment = HorizontalAlignment.Right worksheet.Range("D3").Formula2 = "=NOW()" worksheet.Range("D3").NumberFormat = "m/d/yyyy" worksheet.Range("D3").Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.Medium worksheet.Range("A5").Value = "Shipping Address:" worksheet.Range("C5").Value = "Billing Address:" worksheet.Range("A6").Value = "150 Wayback Lane," & vbCrLf & "New York," & vbCrLf & "New York" worksheet.Range("C6").Value = "414 Rocket Drive," & vbCrLf & "Westerville," & vbCrLf & "Ohio" ' Set table data Dim data(,) As Object = { {"Product", "Unit Price", "Quantity", "Total Price"}, {"Bike", 125, 1, ""}, {"Helmet", 35, 1, ""}, {"Gloves", 15.4, 1, ""}, {"Accessories", 20, 3, ""} } worksheet.Range("A9:D13").Value = data ' Add table. Dim table As ITable = worksheet.Tables.Add(worksheet.Range("A9:D13"), True) ' Set table Style worksheet.Range("B10:B13").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)" worksheet.Range("D10:D13").FormulaR1C1 = "=[@Quantity]*[@Unit Price]" worksheet.Range("D10:D13").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)" table.Columns(0).TotalsCalculation = TotalsCalculation.None table.Columns(1).TotalsCalculation = TotalsCalculation.None table.Columns(2).TotalsCalculation = TotalsCalculation.None table.Columns(3).TotalsCalculation = TotalsCalculation.Sum table.ShowTotals = True ' Merge cells worksheet.Range("A3:B3").Merge() worksheet.Range("A5:B5").Merge() worksheet.Range("A5:B5").Borders.LineStyle = BorderLineStyle.Medium worksheet.Range("C5:D5").Merge() worksheet.Range("C5:D5").Borders.LineStyle = BorderLineStyle.Medium worksheet.Range("A6:B7").Merge() worksheet.Range("A6:B7").WrapText = True worksheet.Range("A6:B7").VerticalAlignment = VerticalAlignment.Center worksheet.Range("A6:B7").Borders.LineStyle = BorderLineStyle.Medium worksheet.Range("C6:D7").Merge() worksheet.Range("C6:D7").WrapText = True worksheet.Range("C6:D7").VerticalAlignment = VerticalAlignment.Center worksheet.Range("C6:D7").Borders.LineStyle = BorderLineStyle.Medium worksheet.SheetView.DisplayGridlines = False ' Convert table to range. table.ConvertToRange() ' save to an excel file workbook.Save("ConvertToRange.xlsx")