C#/VB.NET - How to Export Data from Database to Excel

Excel has the advantage over most databases in that it provides tools to analyze data in a more effective way. There might be a situation where your department or workgroup uses Access to work with data, and you use Excel to analyze the data and to distribute the results of your analysis, you have to export data from Access to Excel first. This article introduces how to programmatically accomplish this task in C# and VB.NET using Spire.XLS for .NET library.

Install Spire.XLS for .NET

Method #1: Download Spire.XLS and unzip the package somewhere on your disk to find the “BIN” folder. Spire.XLS has the DLLs compiled for multiple versions of .NET Framework as well as for .NET Core and other platforms. Choose the DLLs from the folder that you exactly need and add them all as dependencies in your project.

Method #2: Create a Windows Form application in you Visual Studio, and install Spire.XLS directly through NuGet. NuGet package manager will automatically install the correct version that fits in with your application.

Export Data from Database to Excel

The following are the steps to export data from database to Excel using Spire.XLS for .NET.

• Create a Workbook object.
• Get the first worksheet through Workbook.Worksheets[index] property.\
• Create a DataTable object.
• Connect to the database and export data from database to the datatable.
• Write datatable to the worksheet using Worksheet.InsertDataTable() method.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

[C#]

using System.Data;
using System.Data.OleDb;
using Spire.Xls;
namespace ExportDataToExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
private void button1_Click(object sender, EventArgs e)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Create a DataTable object
            DataTable dataTable = new DataTable();
            //Specify connnect string and command text
            string connectionString = "Provider= Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:/Users/Administrator/Desktop/data.mdb";
            string commandString = "select * from vendors";
            //Connect to database
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand command = new OleDbCommand(commandString);
                command.Connection = connection;
                DataSet dataSet = new System.Data.DataSet();
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
                dataAdapter.Fill(dataSet);
                //Get table from dataset
                dataTable = dataSet.Tables[0];
            }
            //Write datatable to excel
            sheet.InsertDataTable(dataTable, true, 1, 1);
            //Auto fit column width
            sheet.AllocatedRange.AutoFitColumns();
            //Save the workbook to an Excel file
            workbook.SaveToFile("ExprotToExcel.xlsx", ExcelVersion.Version2016);
        }
    }
}

[VB.NET]

Imports System.Data
Imports System.Data.OleDb
Imports Spire.Xls
 
Namespace ExportDataToExcel
    Public partial Class Form1
  Inherits Form
        Public  Sub New()
            InitializeComponent()
        End Sub
 
        Private  Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
            'Create a Workbook object
            Dim workbook As Workbook =  New Workbook() 
 
            'Get the first worksheet
            Dim sheet As Worksheet =  workbook.Worksheets(0) 
 
            'Create a DataTable object
            Dim dataTable As DataTable =  New DataTable() 
 
            'Specify connnect string and command text
            Dim connectionString As String =  "Provider= Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:/Users/Administrator/Desktop/data.mdb" 
            Dim commandString As String =  "select * from vendors" 
 
            'Connect to database
            Imports (OleDbConnection connection = New OleDbConnection(connectionString))
            {
                Dim command As OleDbCommand =  New OleDbCommand(commandString) 
                command.Connection = connection
                Dim dataSet As DataSet =  New System.Data.DataSet() 
                Dim dataAdapter As OleDbDataAdapter =  New OleDbDataAdapter(command.CommandText,connection) 
                dataAdapter.Fill(dataSet)
 
                'Get table from dataset
                dataTable = dataSet.Tables(0)
            }
 
            'Write datatable to excel
            sheet.InsertDataTable(dataTable, True, 1, 1)
 
            'Auto fit column width
            sheet.AllocatedRange.AutoFitColumns()
 
            'Save the workbook to an Excel file
            workbook.SaveToFile("ExprotToExcel.xlsx", ExcelVersion.Version2016)
        End Sub
    End Class
End Namespace