ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 278k

How to create excel sheet with multi sheet based on Modules

Jun 3 2020 9:36 PM
I work on c# desktop app I cannot export data to excel sheet with multiple tab
meaning multi sheet based on data exist on data table module field
I use open XML sdk library
Data table data as below :
Divide Output Excel File To Multi Tab based On Module
  1. PartId         Company    Files     Tab                   Module    
  2. 1222             micro       Abc        source                   1    
  3. 1321             silicon      Abc        source                   1    
  4. 1444             cd2          Abc       types                     2    
  5. 1321             cd3          Abc       types                     2    
  6. 1541             tvs           Abc       types                     2    
Expected Result :
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 related to every sheet .
so if i have two modules meaning I have two sheet .
What I have tried:
 
  1. public Boolean createExcelFile(DataTable Table,String FullFilePathName)  
  2.       {  
  3.           Boolean IsDone = false;  
  4.           try  
  5.           {  
  6.               FileInfo CreatedFile = new FileInfo(FullFilePathName);  
  7.               Boolean ISNew = false;  
  8.               if (!CreatedFile.Exists)  
  9.               {  
  10.   
  11.                   ISNew = true;  
  12.               }  
  13.               using (var pck = new ExcelPackage(CreatedFile))  
  14.               {  
  15.                   ExcelWorksheet ws;  
  16.                   if (ISNew == true)  
  17.                   {  
  18.                       ws = pck.Workbook.Worksheets.Add("Sheet");  
  19.   
  20.   
  21.                       if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft)   // Right to Left for Arabic lang  
  22.                       {  
  23.                           ExcelWorksheetView wv = ws.View;  
  24.                           wv.ZoomScale = 100;  
  25.                           wv.RightToLeft = true;  
  26.                           ws.PrinterSettings.Orientation = eOrientation.Landscape;  
  27.   
  28.                       }  
  29.                       else  
  30.                       {  
  31.                           ExcelWorksheetView wv = ws.View;  
  32.                           wv.ZoomScale = 100;  
  33.                           wv.RightToLeft = false;  
  34.                           ws.PrinterSettings.Orientation = eOrientation.Landscape;  
  35.   
  36.                       }  
  37.                       ws.Cells.AutoFitColumns();  
  38.                       ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);  
  39.                   }  
  40.   
  41.                   else  
  42.                   {  
  43.                        ws = pck.Workbook.Worksheets.FirstOrDefault();  
  44.                        ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);  
  45.                   }  
  46.                   pck.Save();  
  47.                   IsDone = true;  
  48.   
  49.               }  
  50.           }  
  51.           catch (Exception ex)  
  52.           {  
  53.   
  54.               throw ex;  
  55.           }  
  56.           return IsDone;  
  57.       }  
but proplem code above create one files with one sheet only 
so How to create file with multi sheet based on module using open xml sdk ? 

Answers (1)