Data Import/Export (2)
This section will show you an easy solution to quickly export datatable from database to Excel via an Excel .NET component in C#, VB.NET.
Spire.XLS for .NET enables you to both export datatable to excel and import excel to datatable. This solution shows you two lines of key souce code for exporting data from datatable to Excel. One is XlsWorksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn) which is responsible for importing the data into worksheet. The other is Workbook.SaveToFile(string fileName) that is called to save the workbook to Excel file.
Here you can download Spire.XLS for .NET and start to perform the datatable to Excel task by below code.
Sample code:
private void button1_Click(object sender, EventArgs e)
{
//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("insertTableToExcel.xls");
}
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
//connect database
Dim connection As OleDbConnection = New OleDbConnection
connection.ConnectionString = “Provider=””Microsoft.Jet.OLEDB.4.0””;
Data Source=""demo.mdb""; User Id=;Password="
Dim command As OleDbCommand = New OleDbCommand
command.CommandText = "select * from parts"
Dim dataSet As DataSet = New System.Data.DataSet
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(command.CommandText, connection)
dataAdapter.Fill(dataSet)
Dim t As DataTable = dataSet.Tables(0)
//export datatable to excel
Dim book As Workbook = New Workbook
Dim sheet As Worksheet = book.Worksheets(0)
sheet.InsertDataTable(t, True, 1, 1)
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start("insertTableToExcel.xls")
End Sub
End Class
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.
