2
Answers

How to create multiple excel file with multiple sheet ?

ahmed elbarbary

ahmed elbarbary
4y
545
1

I work on csharp5.0 app I face Issue Cannot create multiple Excel files With Multiple Excel Sheets With multiple Modules

that mean one file have multiple Tab or sheet and every sheet have multiple modules with data .

suppose I have data as Below :
 
Create Multiple Output Excel File with Multiple Tab with Multiple Module                     
  1. PartId  Company Files   Tab name  Module      
  2. 1222    micro   Abc source      1     
  3. 1321    silicon Abc Types       3     
  4. 1444    cd2        AutoD Rev               10     
  5. 1321    cd3        AutoD source     11    
  6. 1541    tvs        AutoD Rev               12     
  7. 9811    tvs2           Mog  Dal              5    
  8. 1901    tvs3           Mog  Mondo        6    
  9. 2111    toyo           Mog  Pingo        7    
what I tried
  1. DataSet ds = new DataSet();    
  2.  var result = from rows in dt.AsEnumerable()    
  3.                group rows by new { Files = rows["Files"] } into grp    
  4.                select grp;    
  5.  foreach (var item in result)    
  6.  {    
  7.  ds.Tables.Add(item.CopyToDataTable());    
  8.  }    
  9.  Affected = new CExcel().createExcelFileForDs(ds, exportPath);   
this create one excel success but more than excel file I dont know How
  1. public Boolean createExcelFileForDs(DataSet ds, String FullFilePathName) {  
  2.  Boolean IsDone = false;  
  3.  try {  
  4.   FileInfo CreatedFile = new FileInfo(FullFilePathName);  
  5.   Boolean ISNew = false;  
  6.   if (!CreatedFile.Exists) {  
  7.   
  8.    ISNew = true;  
  9.   }  
  10.   using(var pck = new ExcelPackage(CreatedFile)) {  
  11.    ExcelWorksheet ws;  
  12.    foreach(DataTable Table in ds.Tables) {  
  13.     if (ISNew == true) {  
  14.      ws = pck.Workbook.Worksheets.Add(Convert.ToString(Table.Rows[0]["Tab"]));  
  15.      ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet    
  16.      ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet        
  17.      if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft) // Right to Left for Arabic lang    
  18.      {  
  19.       ExcelWorksheetView wv = ws.View;  
  20.       wv.ZoomScale = 100;  
  21.       wv.RightToLeft = true;  
  22.       ws.PrinterSettings.Orientation = eOrientation.Landscape;  
  23.       ws.Cells.AutoFitColumns();  
  24.      } else {  
  25.       ExcelWorksheetView wv = ws.View;  
  26.       wv.ZoomScale = 100;  
  27.       wv.RightToLeft = false;  
  28.       ws.PrinterSettings.Orientation = eOrientation.Landscape;  
  29.       ws.Cells.AutoFitColumns();  
  30.      }  
  31.      ws.Cells.AutoFitColumns();  
  32.      ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);  
  33.     } else {  
  34.      ws = pck.Workbook.Worksheets.FirstOrDefault();  
  35.      ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);  
  36.     }  
  37.    }  
  38.    pck.Save();  
  39.    IsDone = true;  
  40.   
  41.   }  
  42.  } catch (Exception ex) {  
  43.   
  44.   throw ex;  
  45.  }  
  46.  return IsDone;  
  47. } 
Expected Result as Image :

create 3 files Abc,AutoD,Mog

Abc have two sheet source and Types , source sheet name have one row and Types Have one row .

AutoD have two sheets Rev and Source , source sheet name have one row and Rev Have two rows .

Mog have Three sheetes Dal and Mondo and Pingo , Dal sheet name have one row and Mondo Have one row and Pingo have one row .

Answers (2)
Next Recommended Forum