C# code of the LoadGetDataTable demo
using System;
using System.Data;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <summary>
/// Moves data between worksheets and .NET DataTable objects: reads the used range of an existing workbook into a
/// DataTable (the column names come from the first row) and loads a DataTable into a new worksheet.
/// </summary>
public static class LoadGetDataTableDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "LoadGetDataTable";
/// <summary>
/// Reads the employees table of Data\awemployees.xls into a DataTable.
/// </summary>
/// <param name="inputFolder">The folder that contains the Data folder of the sample.</param>
public static DataTable GetEmployees(string inputFolder)
{
// a workbook can be opened from a file or from a stream
using (FileStream stream = new FileStream(Path.Combine(inputFolder, "Data", "awemployees.xls"), FileMode.Open, FileAccess.Read))
{
ExcelWorkbook sourceWorkbook = new ExcelWorkbook(stream);
try
{
ExcelWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];
// true: the first row of the range gives the names of the DataTable columns
return sourceWorksheet.GetDataTable(sourceWorksheet.UsedRange, true);
}
finally
{
sourceWorkbook.Close();
}
}
}
/// <summary>
/// Creates the workbook in the given format and saves it to the output stream.
/// </summary>
/// <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
/// <param name="inputFolder">The folder that contains the Data and Images folders of the sample.</param>
/// <param name="output">The stream that receives the workbook.</param>
public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
// 1. GET: an existing worksheet to a DataTable
DataTable employees = GetEmployees(inputFolder);
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Load data from a DataTable and get data to a DataTable";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Employees";
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape);
DemoLayout.WriteTitle(worksheet, "A2:N3", "Data Loaded from a .NET DataTable", titleStyle);
// 2. LOAD: the DataTable to the worksheet, starting with row 5, column 1; true writes the column
// names in the first row
worksheet.LoadDataTable(employees, 5, 1, true);
// style the header row, keep it visible when scrolling and fit the columns to the data
ExcelRange headerRange = worksheet[5, 1, 5, employees.Columns.Count];
headerRange.RowHeightInPoints = 20;
headerRange.Style.Font.Bold = true;
headerRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
headerRange.Style.Fill.SolidFillOptions.BackColor = Color.LightBlue;
headerRange.Style.Borders.LineStyle = ExcelCellLineStyle.Thin;
worksheet["A6"].FreezePanes();
worksheet.AutofitColumns();
// a summary below the data
int summaryRow = 5 + employees.Rows.Count + 2;
worksheet[summaryRow, 1].Value = String.Format("{0} rows and {1} columns loaded from the DataTable",
employees.Rows.Count, employees.Columns.Count);
worksheet[summaryRow, 1].Style.Font.Bold = true;
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
}
}
Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.