Convert DataTable to Gembox Table

Question

So , now I have the data I need in a System.Data.DataTable . How can I convert it in a Table from Gembox.Spreadsheet.Tables?

Answer 1

You need to first import the DataTable into the cells and then create a Table from those cells.

For example, like this:

// Sample DataTable.
var dataTable = new DataTable();

dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("LastName", typeof(string));

dataTable.Rows.Add(new object[] { 100, "John", "Doe" });
dataTable.Rows.Add(new object[] { 101, "Fred", "Nurk" });
dataTable.Rows.Add(new object[] { 103, "Hans", "Meier" });
dataTable.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
dataTable.Rows.Add(new object[] { 105, "Jean", "Dupont" });
dataTable.Rows.Add(new object[] { 106, "Mario", "Rossi" });

// New ExcelFile or use ExcelFile.Load for existing file.
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");

// Insert DataTable to an Excel worksheet.
var options = new InsertDataTableOptions() { ColumnHeaders = true };
worksheet.InsertDataTable(dataTable, options);

// Create an Excel table.
string range = $"A1:{CellRange.RowColumnToPosition(dataTable.Rows.Count, dataTable.Columns.Count - 1)}";
var table = worksheet.Tables.Add("Table1", range, true);
table.BuiltInStyle = BuiltInTableStyleName.TableStyleLight10;

// Save as XLSX file.
workbook.Save("output.xlsx");