How to create excel file with multiple sheet name

I work on c# desktop app I Can't export data to excel sheet with multiple tab(multi sheet)

based on data exist on data table module field

I use open XML library

Data table data as below :

Divide Output Excel File To Multi Tab based On Module

Code:

---

PartId  Company Files  Tab    Module 
1222    micro    Abc    source    1   
1321    silicon    Abc      source    1   
1444    cd2      Abc    types      2   
1321    cd3      Abc    types      2   
1541    tvs      Abc    types        2

---

Expected Result :

Create File ABC.xlsx with two sheet first sheet name source and second sheet name types based on module and load data related to every sheet based on data exist on data table.

so if I have two modules meaning I have two sheet .

What I have tried:

Code:

---

public Boolean createExcelFile(DataTable Table,String FullFilePathName)
      {
          Boolean IsDone = false;
          try
          {
              FileInfo CreatedFile = new FileInfo(FullFilePathName);
              Boolean ISNew = false;
              if (!CreatedFile.Exists)
              {

                  ISNew = true;
              }
              using (var pck = new ExcelPackage(CreatedFile))
              {
                  ExcelWorksheet ws;
                  if (ISNew == true)
                  {
                      ws = pck.Workbook.Worksheets.Add("Sheet");


                      if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft)  // Right to Left for Arabic lang
                      {
                          ExcelWorksheetView wv = ws.View;

                          wv.RightToLeft = true;
                          ws.PrinterSettings.Orientation = eOrientation.Landscape;

                      }
                      else
                      {
                          ExcelWorksheetView wv = ws.View;

                          wv.RightToLeft = false;
                          ws.PrinterSettings.Orientation = eOrientation.Landscape;

                      }
                      ws.Cells.AutoFitColumns();
                      ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);
                  }

                  else
                  {
                      ws = pck.Workbook.Worksheets.FirstOrDefault();
                      ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);
                  }
                  pck.Save();
                  IsDone = true;

              }
          }
          catch (Exception ex)
          {

              throw ex;
          }
          return IsDone;
      }

---

but problem code above create one files with one sheet only

so How to create files with multi sheet based on module ?

Possibly useful links:

https://docs.microsoft.com/en-us/vis...s?view=vs-2019

https://stackoverflow.com/questions/...-using-c-sharp

Hello,@engbarbary

Please try this code,To How to create excel file with multiple sheet name

Here is a simple C# class that programatically creates an Excel WorkBook and adds two sheets to it, and then populates both sheets. Finally, it saves the WorkBook to a file in the application root directory so that you can inspect the results...

Code:

---

public class Tyburn1
{
    object missing = Type.Missing;
    public Tyburn1()
    {
        Excel.Application oXL = new Excel.Application();
        oXL.Visible = false;
        Excel.Workbook oWB = oXL.Workbooks.Add(missing);
        Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
        oSheet.Name = "The first sheet";
        oSheet.Cells[1, 1] = "Something";
        Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
                        as Excel.Worksheet;
        oSheet2.Name = "The second sheet";
        oSheet2.Cells[1, 1] = "Something completely different";
        string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)       
                                + "\\SoSample.xlsx";
        oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
            missing, missing, missing, missing,
            Excel.XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing, missing, missing);
        oWB.Close(missing, missing, missing);
        oXL.UserControl = true;
        oXL.Quit();
    }
}

---

To do this, you would need to add a reference to Microsoft.Office.Interop.Excel to your project (you may have done this already since you are creating one sheet).

The statement that adds the second sheet is...

Code:

---

Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
                            as Excel.Worksheet;

---

the '1' argument specifies a single sheet, and it can be more if you want to add several sheets at once.

Final note: the statement oXL.Visible = false; tells Excel to start in silent mode.

I hope this code will be useful.
Thank You.