Results 1 to 3 of 3

Thread: How to create excel file with multiple sheet name

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2018
    Posts
    67

    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 ?

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: How to create excel file with multiple sheet name


  3. #3
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Re: How to create excel file with multiple sheet name

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width