Transferring Data Between DataTable and Excel Using C#

In modern applications, table data processing is a fundamental feature. With C# and the Spire.XLS for .NET library, we can easily implement data conversion between DataTable and Excel. This article will explain how to write DataTable data to an Excel spreadsheet and how to read Excel data into a DataTable.

What is Spire.XLS for .NET?

Spire.XLS for .NET is a powerful Excel file processing component that supports the creation, reading, editing, and conversion of Excel files in .NET applications. It allows developers to easily perform various operations on Excel files without reliance on the installation of Microsoft Excel. The library supports file formats for Excel versions 2003, 2007, 2010, 2013, 2016, etc., and offers high performance and rich functionality, making it suitable for developing reports, data analysis, and data import/export applications.

Key Features of Spire.XLS

• Create and Edit Excel Files : Dynamically generate Excel files and modify their contents.
• Read Excel Data : Quickly read data from Excel files and import it into DataTable or other data structures.
• Formatting : Supports cell styles, font colors, table styles, and more.
• Charts and Images : Can insert charts and images into Excel files.
• Supports Multiple Formats : Includes various file formats like .xlsx, .xls, .xlsm, .csv, etc.

How to Install Spire.XLS for .NET

Installing Spire.XLS for .NET is very simple. Here are several common installation methods:

1. Using NuGet Package Manager

This is the most common method. Just run the following command in your project:

Install-Package Spire.XLS

2. Using Visual Studio

1. Open Visual Studio and navigate to your project.
2. Right-click on the project name in the Solution Explorer, and select "Manage NuGet Packages" .
3. In the "Browse" tab, search for "Spire.XLS" .
4. Select Spire.XLS and click "Install" .

3. Manual Download

If you prefer manual installation, you can visit the Spire.XLS official website to download the corresponding DLL files. Once downloaded, add the DLL files to your project references.

After installation, you can start using Spire.XLS in your C# project to process Excel files.

1. Writing DataTable to Excel

Below are the steps and sample code for writing data from a DataTable to an Excel file.

Sample Code

using System.Data;
using Spire.Xls;

namespace WriteDataTableToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create Workbook instance
            Workbook workbook = new Workbook();

            // Remove the default worksheet
            workbook.Worksheets.Clear();

            // Add a worksheet and name it
            Worksheet worksheet = workbook.Worksheets.Add("InsertDataTable");

            // Create DataTable object
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("No", typeof(Int32));
            dataTable.Columns.Add("Name", typeof(String));
            dataTable.Columns.Add("City", typeof(String));

            // Add rows and data
            DataRow dr = dataTable.NewRow();
            dr[0] = 1; dr[1] = "Tom"; dr[2] = "New York"; dataTable.Rows.Add(dr);
            dr = dataTable.NewRow();
            dr[0] = 2; dr[1] = "Jerry"; dr[2] = "Houston"; dataTable.Rows.Add(dr);
            dr = dataTable.NewRow();
            dr[0] = 3; dr[1] = "Dave"; dr[2] = "Florida"; dataTable.Rows.Add(dr);

            // Write DataTable to worksheet
            worksheet.InsertDataTable(dataTable, true, 1, 1, true);

            // Save to Excel file
            workbook.SaveToFile("InsertDataTable.xlsx", ExcelVersion.Version2016);

            // Release resources
            workbook.Dispose();
        }
    }
}

Code Explanation

1. Create a Workbook instance and clear the default worksheet.
2. Create a DataTable, define columns, and insert data.
3. Use the InsertDataTable method to write the DataTable to Excel.
4. Finally, call the SaveToFile method to save the data in the Excel file.

2. Reading Excel Data into DataTable

Next, we will explain how to read data from an Excel file into a DataTable.

Sample Code

using Spire.Xls;
using System.Data;
using System.Windows.Forms;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create Workbook object
            Workbook wb = new Workbook();

            // Load the existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export worksheet data to DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Release resources
            wb.Dispose();
        }
    }
}

Code Explanation

1. Create a Workbook instance and load the specified Excel file.
2. Get the first worksheet and call the ExportDataTable method to export data.
3. Bind the exported DataTable to the DataGridView control for display.
4. Finally, release the Workbook resources.

Summary

By using the Spire.XLS for .NET library, we can easily convert data between DataTable and Excel. Whether in data analysis, report generation, or data processing, this method greatly enhances development efficiency. We hope the sample code in this article helps you better understand and apply this technology.