//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // Use English (US) culture to apply styles workbook.Culture = CultureInfo.GetCultureInfo("en-US"); var sheet1 = workbook.Worksheets["Sheet1"]; sheet1.Name = "ImportDataTable"; sheet1.Range["A1"].Value = "Tour budget table"; // Create a DataTable and define its columns var dataTable = new DataTable(); dataTable.Columns.Add("EmployeeID", typeof(int)).AllowDBNull = false; dataTable.Columns.Add("Adults", typeof(int)).AllowDBNull = false; dataTable.Columns.Add("Children", typeof(int)).AllowDBNull = false; dataTable.Columns.Add("Rooms", typeof(int)).AllowDBNull = false; dataTable.Columns.Add("Spa", typeof(double)).AllowDBNull = false; dataTable.Columns.Add("Hotpot", typeof(int)).AllowDBNull = false; dataTable.Columns.Add("Budget", typeof(double)).AllowDBNull = false; // Generate test data. To get the same data, you can set the seed value of the random number generator. var rnd = new Random(1234); const int rowCount = 20; for (int i = 0; i < rowCount; i++) { int employeeId = rnd.Next(10000, 99999); int adults = rnd.Next(1, 4); int children = rnd.Next(0, 3); int rooms = (int)Math.Floor((adults + children) / 2.0); double spa = Math.Ceiling((adults + children) * rnd.NextDouble()); int hotpot = adults + children; double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750; // Add the row to the DataTable dataTable.Rows.Add(employeeId, adults, children, rooms, spa, hotpot, budget); } // Import the DataTable into the worksheet var result = sheet1.Range["A2"].ImportData(dataTable); sheet1.Range[1, 0, 1, result.ColumnsImported].Style = workbook.Styles["Heading 3"]; sheet1.Range[2, 0, result.RowsImported - 1, result.ColumnsImported].Style = workbook.Styles["20% - Accent1"]; sheet1.Range[1, 0, result.RowsImported, result.ColumnsImported].EntireColumn.AutoFit(); sheet1.Range["A1"].Style = workbook.Styles["Heading 1"]; sheet1.Range["1:2"].AutoFit(); sheet1.Range[2, 6, result.RowsImported, 1].NumberFormat = "$#,##0.00"; sheet1.Range[2, 6, result.RowsImported, 1].EntireColumn.ColumnWidth = 10; // Save to an excel file workbook.Save("ImportDataTable.xlsx");
' Create a new Workbook Dim workbook As New Workbook ' Use English (US) culture to apply styles workbook.Culture = CultureInfo.GetCultureInfo("en-US") Dim sheet1 = workbook.Worksheets!Sheet1 sheet1.Name = "ImportDataTable" sheet1.Range!A1.Value = "Tour budget table" ' Create a DataTable and define its columns Dim dataTable As New DataTable With dataTable.Columns .Add("EmployeeID", GetType(Integer)).AllowDBNull = False .Add("Adults", GetType(Integer)).AllowDBNull = False .Add("Children", GetType(Integer)).AllowDBNull = False .Add("Rooms", GetType(Integer)).AllowDBNull = False .Add("Spa", GetType(Double)).AllowDBNull = False .Add("Hotpot", GetType(Integer)).AllowDBNull = False .Add("Budget", GetType(Double)).AllowDBNull = False End With ' Generate test data. To get the same data, you can set the seed value of the random number generator. Dim rnd As New Random(Seed:=1234) Const rowCount = 20 For i = 0 To rowCount - 1 Dim employeeId = rnd.Next(10000, 99999) Dim adults = rnd.Next(1, 4) Dim children = rnd.Next(0, 3) Dim rooms = Math.Floor((adults + children) / 2) Dim spa = Math.Ceiling((adults + children) * rnd.NextDouble) Dim hotpot = adults + children Dim budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750 ' Add the row to the DataTable dataTable.Rows.Add(employeeId, adults, children, rooms, spa, hotpot, budget) Next ' Import the DataTable into the worksheet Dim result = sheet1.Range("A2").ImportData(dataTable) sheet1.Range(1, 0, 1, result.ColumnsImported).Style = workbook.Styles("Heading 3") sheet1.Range(2, 0, result.RowsImported - 1, result.ColumnsImported).Style = workbook.Styles("20% - Accent1") sheet1.Range(1, 0, result.RowsImported, result.ColumnsImported).EntireColumn.AutoFit() sheet1.Range!A1.Style = workbook.Styles("Heading 1") sheet1.Range("1:2").AutoFit() sheet1.Range(2, 6, result.RowsImported, 1).NumberFormat = "$#,##0.00" sheet1.Range(2, 6, result.RowsImported, 1).EntireColumn.ColumnWidth = 10 ' save to an excel file workbook.Save("ImportDataTable.xlsx")