How to export 3 datatable in to 3 different excel sheets in XL interop C#

Question

1.00/5 (1 vote)

See more:

Hi,

I have 3 data table I wanto export those data tables in to sheet 1 ,sheet 2 ,sheet 3()need to give A,B,C as sheet name also) How we can achive this ? when i was trying i am getting as different seperate excel(by duplicating the code multiple time).but i want to be in same excel with 3 sheets for 3 Data Table.I have lot of code duplication also couldnt do effectively.How i will be giving names for each tabs

What I have Tried:

What I have tried:

C#

public static void CreateDataTable()
{
//tab1-A
    OdbcConnection OConn1 = new OdbcConnection(odbcConnectString1);
    OdbcDataAdapter odbcDataAdapter1 = new OdbcDataAdapter(sQury1, OConn1);
    odbcDataAdapter1 = new OdbcDataAdapter(sQury1, OConn1);
    odbcDataAdapter1.SelectCommand.CommandTimeout = 180;
    System.Data.DataTable dt1 = new System.Data.DataTable();
    odbcDataAdapter1.Fill(dt1);
    DataSet ds1 = new DataSet();
    ds1.Tables.Add(dt1);

//tab2-b
    OdbcConnection OConn2 = new OdbcConnection(odbcConnectString2);
    OdbcDataAdapter odbcDataAdapter2 = new OdbcDataAdapter(sQury2, OConn2);
    odbcDataAdapter2 = new OdbcDataAdapter(sQury2, OConn2);
    odbcDataAdapter2.SelectCommand.CommandTimeout = 180;
    System.Data.DataTable dt2 = new System.Data.DataTable();
    odbcDataAdapter2.Fill(dt2);
    DataSet ds2 = new DataSet();
    ds2.Tables.Add(dt2);

//tab3-c
    OdbcConnection OConn3 = new OdbcConnection(odbcConnectString3);
    OdbcDataAdapter odbcDataAdapter3 = new OdbcDataAdapter(sQury3, OConn3);
    odbcDataAdapter3 = new OdbcDataAdapter(sQury2, OConn2);
    odbcDataAdapter3.SelectCommand.CommandTimeout = 180;
    System.Data.DataTable dt3 = new System.Data.DataTable();
    odbcDataAdapter3.Fill(dt3);
    DataSet ds3 = new DataSet();
    ds2.Tables.Add(dt3);

    CreateExcelSheet1(ds1, excelPath);//i have to send ds1,ds2,ds3?? but cant get in one excel
    CreateExcelSheet1(ds2, excelPath)
    CreateExcelSheet1(ds3, excelPath)
}

//creating excel

public static void CreateExcelSheet1(DataSet ds, string excelPath)
{
    object misValue = System.Reflection.Missing.Value;
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    Excel._Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];
    Excel.Range xlRange = xlWorkSheet.UsedRange;


    // column headings
    for (var i = 0; i < ds.Tables[0].Columns.Count; i++)
    {
        xlWorkSheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
    }

    // data
    for (int i = 1; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
        {
            xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        }
    }

    xlWorkBook.SaveAs(excelPath);

    //cleanup
    GC.Collect();
    GC.WaitForPendingFinalizers();

    //release com objects to fully kill excel process from running in the background
    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorkSheet);

    //close and release
    xlWorkBook.Close();
    Marshal.ReleaseComObject(xlWorkBook);

    //quit and release
    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);
}

Posted 30-Nov-21 4:20am

Manidas Payyoli

Updated 30-Nov-21 5:49am

Richard Deeming

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-11-30T05:49:00

Load the data into a single DataSet:

C#

private static DataTable LoadTable(string connectionString, string query, string tableName)
{
    using (var connection = new OdbcConnection(connectionString))
    using (var command = new OdbcCommand(query, connection))
    using (var adapter = new OdbcDataAdapter(command))
    {
        command.CommandTimeout = 180;
        
        var table = new DataTable(tableName);
        adapter.Fill(table);
        return table;
    }
}

private static DataSet LoadData()
{
    var result = new DataSet();
    result.Tables.Add(LoadTable(odbcConnectString1, sQury1, "A"));
    result.Tables.Add(LoadTable(odbcConnectString2, sQury2, "B"));
    result.Tables.Add(LoadTable(odbcConnectString3, sQury3, "C"));
    return result;
}

public static void CreateDataTable()
{
    DataSet ds = LoadData();
    CreateExcelSheet(ds, excelPath);
}

Then change your CreateExcelSheet method to create a sheet for each table in the DataSet:

C#

private static void AddDataToWorksheet(Excel._Worksheet sheet, DataTable table)
{
    sheet.Name = table.TableName;
    
    for (int i = 0; i < table.Columns.Count; i++)
    {
        sheet.Cells[1, i + 1] = table.Columns[i].ColumnName;
    }
    
    for (int i = 0; i < table.Rows.Count; i++)
    {
        int rowNumber = i + 2;
        DataRow row = table.Rows[i];
        for (int j = 0; j < table.Columns.Count; j++)
        {
            sheet.Cells[rowNumber, j + 1] = Convert.ToString(row[j]);
        }
    }
}

public static void CreateExcelSheet(DataSet ds, string excelPath)
{
    object misValue = System.Reflection.Missing.Value;
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    
    for (int i = 0; i < ds.Tables.Count; i++)
    {
        DataTable table = ds.Tables[i];
        
        Excel._Worksheet xlWorkSheet = xlWorkBook.Sheets.Count <= i
            ? (Excel._Worksheet)xlWorkBook.Sheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count])
            : (Excel._Worksheet)xlWorkBook.Sheets[i + 1];
        
        AddDataToWorksheet(xlWorkSheet, table);
        Marshal.ReleaseComObject(xlWorkSheet);
    }
    
    xlWorkBook.SaveAs(excelPath);

    xlWorkBook.Close();
    xlApp.Quit();
    
    Marshal.ReleaseComObject(xlWorkbook);
    Marshal.ReleaseComObject(xlApp);
}