Click here to Skip to main content
15,569,774 members
Please Sign up or sign in to vote.
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
Updated 30-Nov-21 6:49am
v2

1 solution

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);
}
 
Share this answer
 
v3
Comments
[no name] 1-Dec-21 4:28am    
hello thanks for the code.I tried this one but excel header name is missing and the tab order in excel coming like C,B,A.And my last tabel has only one data row and one header row.this table not giving headers.Loop not working for that table only for (int i = 0; i < table.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = table.Columns[i].ColumnName;
}
Richard Deeming 1-Dec-21 4:33am    
I've updated the AddDataTowWorksheet function to use the rowNumber for the Excel row instead of the loop variable.

Looking at the documentation[^], new sheets get inserted before the active sheet by default. You need to pass the after parameter to insert them at the end instead.
[no name] 1-Dec-21 5:25am    
yup npw its fine
[no name] 1-Dec-21 5:28am    
i couldnt understand how to order the tabs looking this doc and our code
Richard Deeming 1-Dec-21 5:30am    
So long as you've added the After: parameter to the xlWorkBook.Sheets.Add call, as shown in my updated answer, the sheets will be in the same order as the tables in your DataSet.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900