In C# applications, efficiently converting data between Excel files and DataTables is essential for enhancing data accessibility, analysis, and processing capabilities. By transferring data from Excel to DataTables, developers can leverage the full power of .NET to analyze, transform, and process data, while converting data back to Excel enables easy sharing, reporting, and integration with other systems. This article demonstrates how to use Spire.XLS for .NET to export data from Excel files DataTable and import Data from Datable to Excel files with C# code.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Export Data from Excel to DataTable with C#
Spire.XLS for .NET offers the Worksheet.ExportDataTable() method to export data from an entire Excel worksheet to a DataTable object. Additionally, the Worksheet.Range[].ExportDataTable() method allows exporting from a specific cell range to a DataTable. Developers can use the ExportTableOptions class to customize options when exporting data from a cell range. The detailed steps for the exporting are as follows:
- Create an instance of the Workbook class and load an Excel file using the Workbook.LoadFromFile() method.
- Access a worksheet in the Excel file via the Workbook.Worksheets[] property.
- Export the data from the entire worksheet to a DataTable object using the Worksheet.ExportDataTable() method.
- Alternatively:
- Create an instance of ExportTableOptions to specify export options.
- Export data from a specified cell range to a DataTable using the Worksheet.ExportDataTable() method with the ExportTableOptions instance as a parameter.
- Output the DataTable.
- C#
using Spire.Xls;
using System.Data;
namespace ExcelToDataTable
{
class Program
{
static void Main(string[] args)
{
// Create an instance of Workbook
Workbook wb = new Workbook();
// Load the Excel file
wb.LoadFromFile("Sample.xlsx");
// Get a worksheet
Worksheet sheet = wb.Worksheets[0];
// Export worksheet data to DataTable
//DataTable dataTable = sheet.ExportDataTable();
// Or export a specified cell range to DataTable
ExportTableOptions options = new ExportTableOptions();
options.ComputedFormulaValue = true;
options.ExportColumnNames = false;
options.KeepDataFormat = false;
options.RenameStrategy = RenameStrategy.Letter;
DataTable dataTable = sheet.Range[5, 1, 7, 6].ExportDataTable(options);
// Output the column names of the DataTable
for (int i = 0; i < dataTable.Columns.Count; i++)
{
Console.Write(dataTable.Columns[i].ColumnName + "\t");
}
Console.WriteLine();
// Output the data rows of the DataTable
foreach (DataRow row in dataTable.Rows)
{
foreach (var item in row.ItemArray)
{
Console.Write(item + "\t");
}
Console.WriteLine();
}
}
}
}
Import Data from DataTable to Excel with C#
Spire.XLS for .NET provides the Worksheet.InsertDataTable(DataTable, colHeaders: bool, firstRow: int, firstColumn: int) method to insert data from a DataTable object into an Excel worksheet. The detailed steps for importing data from a DataTable to Excel are as follows:
- Define the data and create a DataTable object.
- Create a Workbook instance and clear the default worksheets using the Workbook.Worksheets.Clear() method.
- Insert a new worksheet with a specified name using the Workbook.Worksheets.Add(sheetName: string) method.
- Insert the data from the DataTable object to the worksheet using Worksheet.InsertDataTable() method.
- Adjust the formatting as needed.
- Save the workbook using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
using System.Data;
namespace DataTableToExcel
{
class Program
{
static void Main(string[] args)
{
// Define a dataset and create an instance of DataTable
// Redefine data array with new products and quantities
string[,] data = new string[,]
{
{ "Product", "Quantity", "SalesAmount" },
{ "Product X", "75", "4500.0" },
{ "Product Y", "200", "12000.0" },
{ "Product Z", "90", "5400.0" },
{ "Product W", "150", "9000.0" },
{ "Product Q", "130", "7800.0" },
{ "Product R", "85", "5100.0" },
{ "Product S", "160", "9600.0" }
};
DataTable dataTable = new DataTable();
// Get the number of columns
int columnCount = data.GetLength(1);
// Add columns to DataTable
for (int col = 0; col < columnCount; col++)
{
dataTable.Columns.Add(data[0, col]);
}
// Add rows to DataTable
for (int row = 1; row < data.GetLength(0); row++)
{
DataRow dataRow = dataTable.NewRow();
for (int col = 0; col < columnCount; col++)
{
dataRow[col] = data[row, col];
}
dataTable.Rows.Add(dataRow);
}
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Clear default worksheets and add a new worksheet
workbook.Worksheets.Clear();
Worksheet sheet = workbook.Worksheets.Add("SalesReport");
// Import DataTable data into the worksheet
sheet.InsertDataTable(dataTable, true, 1, 1);
// Adjust column widths for better readability
for (int i = 1; i <= sheet.AllocatedRange.ColumnCount; i++)
{
sheet.AutoFitColumn(i);
}
// Save the workbook to a file
workbook.SaveToFile("output/DataTableToExcel.xlsx", FileFormat.Version2016);
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
