Export Data From Database to Excel in C#

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.