Excel API Library for .NET - Sample Browser | Document Solutions | Import Data from DataTable
[
フレーム]
Refer to the following example code to import data from DataTable.
//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")