Multiple Table Data want to Export in Excell Separate Sheets

Dear Vijay,

I am facing below issue once running the code.

You can achieve this by using the EPPlus library, which provides functionality to work with Excel files. First, you'll need to install the EPPlus package via NuGet Package Manager.

class Program
{
    static void Main(string[] args)
    {
        DataSet dataSet = new DataSet("SampleDataSet");

        // Create the first DataTable
        DataTable table1 = new DataTable("Table1");
        table1.Columns.Add("ID", typeof(int));
        table1.Columns.Add("Name", typeof(string));
        table1.Columns.Add("Age", typeof(int));
        table1.Rows.Add(1, "John", 25);
        table1.Rows.Add(2, "Emily", 30);
        table1.Rows.Add(3, "Michael", 28);
        dataSet.Tables.Add(table1);

        // Create the second DataTable
        DataTable table2 = new DataTable("Table2");
        table2.Columns.Add("ID", typeof(int));
        table2.Columns.Add("Department", typeof(string));
        table2.Columns.Add("Salary", typeof(double));
        table2.Rows.Add(1, "IT", 55000.50);
        table2.Rows.Add(2, "Marketing", 60000.75);
        table2.Rows.Add(3, "Finance", 65000.25);
        dataSet.Tables.Add(table2);

        string excelFilePath = @"D:\output_file.xlsx";

        // Set the LicenseContext
        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial; // Set to NonCommercial or Commercial

        using (ExcelPackage excelPackage = new ExcelPackage())
        {
            foreach (DataTable table in dataSet.Tables)
            {
                // Add each DataTable to a separate sheet in the Excel file
                AddDataTableToSheet(excelPackage, table, table.TableName);
            }

            FileInfo excelFile = new FileInfo(excelFilePath);
            excelPackage.SaveAs(excelFile);
        }
        Console.WriteLine("Data exported to Excel file successfully.");
        Console.ReadLine();
    }

    static void AddDataTableToSheet(ExcelPackage excelPackage, DataTable dataTable, string sheetName)
    {
        OfficeOpenXml.ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add(sheetName);
        worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
    }
}

// Create a new Workbook.
excelworkBook = excel.Workbooks.Add(Type.Missing);

// Assuming you have a DataTable for each table (e.g., dtTable1, dtTable2)
ExportDataTableToExcel(cs.dt, "SAIB", dateTimePicker1.Text, dateTimePicker2.Text);

// Create a second sheet for the second table
ExportDataTableToExcel(dtTable2, "SecondSheetName", dateTimePicker1.Text, dateTimePicker2.Text);

// Save the Excel file
excelworkBook.SaveAs("YourFilePath.xlsx");
excelworkBook.Close();
excel.Quit();

// Release COM objects
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelworkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

// Define a method to export a DataTable to a specific sheet
private void ExportDataTableToExcel(DataTable dt, string sheetName, string fromDate, string toDate)
{
    // Create a Worksheet.
    Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    excelSheet.Name = sheetName;

    excelSheet.Cells[1, 1] = sheetName;
    excelSheet.Cells[1, 2] = "Date : From: " + fromDate + " and To: " + toDate;

    int rowcount = 2;

    foreach (DataRow datarow in dt.Rows)
    {
        rowcount += 1;
        for (int i = 1; i <= dt.Columns.Count; i++)
        {
            // on the first iteration we add the column headers
            if (rowcount == 3)
            {
                excelSheet.Cells[2, i] = dt.Columns[i - 1].ColumnName;
                excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
            }

            excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

            //for alternate rows
            if (rowcount > 3)
            {
                if (i == dt.Columns.Count)
                {
                    if (rowcount % 2 == 0)
                    {
                        Microsoft.Office.Interop.Excel.Range excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dt.Columns.Count]];
                        FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
                    }
                }
            }
        }
    }

    // now we resize the columns
    Microsoft.Office.Interop.Excel.Range excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dt.Columns.Count]];
    excelCellrange.EntireColumn.AutoFit();
    Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
    border.Weight = 2d;

    excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dt.Columns.Count]];
    FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
}

// Define a method for formatting cells
private void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string interiorColor, System.Drawing.Color fontColor, bool isBold)
{
    range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml(interiorColor));
    range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
    range.Font.Bold = isBold;
}

To export data from multiple tables to an Excel file with each table in a separate sheet, you need to create a new worksheet for each table. See the above example of how you can modify your code to achieve this: