Export Data From Database to Excel in C#

Introduction

No one can deny that "Export to Excel" will forever be a hot topic for both programmers and developers. There are even many articles related to it but we are still often frustrated when we encounter problems. In this article, I will introduce a few solutions based on special Export to Excel questions that are frequently asked by people in forums.

Content Table

• Export One Datatable to One Excel Sheet
• Export multiple Datatables to multiple Excel Sheets
• Export multiple Datatables to One Excel Sheet
• Export Specific Rows and Columns to Excel Sheet

Export One Datatable from Database to One Excel Sheet

Although exporting one datatable to one Excel sheet does not deserve a special topic, it is the most common one. After viewing all the following solutions, you may find how helpful this solution is. In this solution, I use this .NET Excel component Spire.XLS for .NET which is a third party library. Using it, we do not need to install Microsoft Excel.  Now let us start our task.

Connect with Database

We all know that the first step to export a database table to Excel is to connect with the database (I use a Microsoft Access database). At the very beginning, we need to represent an open connection to the data source by initializing a new instance of this class System.Data.OleDb.OleDbConnection. Then, open the database by the string OldBdConnection.ConnectionString. After that, we can use the CommandText property of the class System.Data.OleDb.OleDbCommand to state a certain datatable. To get the datatable data and fill the data in the dataset table, we need to call the method System.Data.OleDb.OleDbDataAdapter.Fill(DataSet dataSet). Here you can see the code:

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";
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection))
{
    DataTable t = new DataTable();
    dataAdapter.Fill(t);
}

If you use SQL database, you need connect database with following code:

SqlConnection connection = new SqlConnection();
connection.ConnectionString = @"Data Source=server;Initial Catalog=db;User ID=test;Password=test;";
SqlCommand command = new SqlCommand();
command.CommandText = "select * from parts";
 using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, connection))
{
    DataTable t = new DataTable();
    dataAdapter.Fill(t);
}

Export to Excel

When using Spire.XLS, we can export a datatable to Excel by only one method, which is:

Spire.Xls.Worksheet.InsertDataColumn(DataColumn dataColumn, bool columnHeaders, int firstRow, int firstColumn);

There are four parameters passed. The first one is the data column to import. The second indicates whether to import field names. The last two decide the start row and column in Excel when the datatable data is exported to the worksheet.

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("ToExcel.xls");

Export multiple Datatables to multiple Excel Sheets from Database

After knowing how to export one datatable to an Excel sheet, I can say that exporting multiple datatables to Excel sheets is just a piece of cake. The main method is the same as the method called in the solution above; it is:

Spire.Xls.Worksheet.InsertDataColumn(DataColumn dataColumn, bool columnHeaders, int firstRow, int firstColumn);

The only difference is that in this solution we need to export more than one datatable to Excel sheets. Please see the detailed code:

static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    DataTable dt = GetDataTableFromDB("select * from country");
    workbook.Worksheets[0].InsertDataTable(dt, true, 1, 1);
    dt = GetDataTableFromDB("select * from items");
    workbook.Worksheets[1].InsertDataTable(dt, true, 1, 1);
    dt = GetDataTableFromDB("select * from parts");
    workbook.Worksheets[2].InsertDataTable(dt, true, 1, 1);
    workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
    System.Diagnostics.Process.Start("sample.xlsx");
}
static DataTable GetDataTableFromDB(string cmdText)
{
    DataTable dt = new DataTable();
    string connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb";
    using (OleDbConnection conn = new OleDbConnection(connStr))
    {
        OleDbCommand commd = new OleDbCommand(cmdText,conn);
        using (OleDbDataAdapter da = new OleDbDataAdapter(commd))
        {          
            da.Fill(dt);
        }
    }
    return dt;
}

Export multiple Datatables to One Excel Sheet from a Database

When we export multiple datatables to one Excel sheet, we should return to the following method again:

Spire.Xls.Worksheet.InsertDataColumn(DataColumn dataColumn, bool columnHeaders, int firstRow, int firstColumn);

As I said above, the last two parameters specify the start row and column in Excel when we export the datatable data to a worksheet. By specifying the two parameters, we can export multiple datatables to one Excel sheet. Of course, we should be very clear about which cell is the start cell and which is the end cell of each datatable when exporting to Excel.

Export Specific Rows and Columns from Datatable to Excel Sheet

In this solution, I would like to create a Windows Forms project. Thus, we can read the datatable in DataGridView clearly and quickly decide which rows and columns should be exported to Excel. The following is a picture of my datatable data which is completely shown in a DataGridView:

Now, I want to export data that is less than 1000 in the "Cost" column and the data that is greater than 500 in the "ListPrice" column to Excel.  Let us use this CommandText property of the class System.Data.OleDb.OleDbCommand to directly state certain conditions that the data must satisfy from a specific datatable. Then, export this data to a DataSet table and DataGridView. Finally export the DataGridView data to Excel. Please see the entire code below:

private void button1_Click(object sender, EventArgs e)
{
    //connect with 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 where Cost<1000 and ListPrice>500";
    DataSet dataSet = new System.Data.DataSet();
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
    dataAdapter.Fill(dataSet);
    DataTable dt = dataSet.Tables[0];
    this.dataGridView1.DataSource = dt;
    //export specific data to Excel
    Workbook book = new Workbook();
    Worksheet sheet = book.Worksheets[0];
    book.Worksheets[0].InsertDataTable(this.dataGridView1.DataSource as DataTable, true, 1, 1);
    book.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
    System.Diagnostics.Process.Start("sample.xlsx");
}

Here you can see the effect in the DataGridView that is the same with the target Excel file:

Conclusion

After reading all the solutions, we can easily find that these solutions are very similar to the others except for a little difference. Most people know how to export data from a database to Excel, while how many people have tried exporting multiple datatables or specific rows and columns to Excel? Sometimes, the answer is around us but we do not find them. If we can draw differences about other cases from one basic instance, things will be much easier.