Click here to Skip to main content
15,562,015 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 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 related to every sheet .

because every module have different sheet .

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

What I have tried:

C#
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.ZoomScale = 100;
                          wv.RightToLeft = true;
                          ws.PrinterSettings.Orientation = eOrientation.Landscape;

                      }
                      else
                      {
                          ExcelWorksheetView wv = ws.View;
                          wv.ZoomScale = 100;
                          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;
      }
Posted
Updated 3-Jun-20 22:33pm
v3

1 solution

Quote:
I use open XML library

Sorry, but you're wrong. You're using EPPlus, because OpenXml does not have built-in LoadFromDataTable method.

All you have to do is:
1. create Excel file (workbook)
2. loop through the DataRows collection of DataTable
  a) check if sheet with name corresponding to the name of Module exists:
    - NO - create new one
    - YES - refer to existsing one
  b) find first empty cell(row)
  c) insert data into that row
3. save Excel file
 
Share this answer
 
Comments
ahmed_sa 4-Jun-20 6:46am    
if possible can you help me by showing code
Maciej Los 4-Jun-20 6:56am    
It's your job, not mine. I provided detailed instruction. Read it and try to write code by your own.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900