How to create excel sheet with multi sheet based on Modules

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

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 .

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

What I have tried:

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;
}

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 ?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

sam k6y ago
create Excel file (workbook)
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) ...
save Excel file. Permalink. Posted 13hrs ago. Maciej Los. Comments.