How to create multiple Excel files with multiple Tab sheet with multiple modules ?

[engahmedbarbary]

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
PartId Company Files Tab name Module
1222 micro Abc source 1
1321 silicon Abc Types 3
1444 cd2 AutoD Rev 10
1321 cd3 AutoD source 11
1541 tvs AutoD Rev 12
9811 tvs2 Mog Dal 5
1901 tvs3 Mog Mondo 6
2111 toyo Mog Pingo 7

what I try

DataSet ds = new DataSet();
var result = from rows in dt.AsEnumerable()
group rows by new { Files = rows["Files"] } into grp
select grp;
foreach (var item in result)
{
ds.Tables.Add(item.CopyToDataTable());
}
Affected = new CExcel().createExcelFileForDs(ds, exportPath);


this create one excel success but more than excel file I dont know How

public Boolean createExcelFileForDs(DataSet ds, 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;
foreach (DataTable Table in ds.Tables)
{
if (ISNew == true)
{
ws = pck.Workbook.Worksheets.Add(Convert.ToString(Table.Rows[0]["Tab"]));
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole 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;
ws.Cells.AutoFitColumns();
}
else
{
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
wv.RightToLeft = false;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.Cells.AutoFitColumns();
}
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;
}

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 sheetes 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 .



Continue reading...