CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2019
    Posts
    6

    How to create excel sheet with multiple sheet name based on modules ?

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

    only that i can do create excel file with only 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

    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");
    
    
    
    
                          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 file with multi sheet based on module ?

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: How to create excel sheet with multiple sheet name based on modules ?

    Pseudo code
    Code:
    void FillExcelFileWithModuleData()
    {
        using (var pck = new ExcelPackage(pathName))
        {
            // note: your code didn't have any examples of getting modules
            // this is an example of a list of modules where each item contains
            // a module name and data table
             foreach(var moduleData in modules)
             {
                var ws = ep.Workbook.Worksheets.Add(moduleData.Name); 
                ws.Cells[1, 1].LoadFromDataTable(moduleData.Data, true, OfficeOpenXml.Table.TableStyles.Light8);
             }
             ep.Save();
        }
    }

  3. #3
    Join Date
    Nov 2018
    Posts
    2

    Re: How to create excel sheet with multiple sheet name based on modules ?

    You can create multi sheet using Epplus like this

    Code:
    using(var package = new ExcelPackage())
    {
        package.Workbook.Worksheets.Add("First worksheet 1", ws1);
        package.Workbook.Worksheets.Add("First worksheet 2", ws2);
        // Do something with the package
    }
    Read more : How to write to excel in C#? ( Text, adding images / styling in Excel)

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

Featured