-
Jun 4th, 2020, 03:19 AM
#1
Thread Starter
Lively Member
How to create excel file with multiple sheet name
I work on c# desktop app I Can't export data to excel sheet with multiple tab(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
Code:
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 exist on data table.
so if I have two modules meaning I have two sheet .
What I have tried:
Code:
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.RightToLeft = true;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
}
else
{
ExcelWorksheetView wv = ws.View;
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 problem code above create one files with one sheet only
so How to create files with multi sheet based on module ?
-
Jun 4th, 2020, 02:48 PM
#2
Re: How to create excel file with multiple sheet name
-
Jun 4th, 2020, 11:41 PM
#3
Lively Member
Re: How to create excel file with multiple sheet name
Hello,@engbarbary
Please try this code,To How to create excel file with multiple sheet name
Here is a simple C# class that programatically creates an Excel WorkBook and adds two sheets to it, and then populates both sheets. Finally, it saves the WorkBook to a file in the application root directory so that you can inspect the results...
Code:
public class Tyburn1
{
object missing = Type.Missing;
public Tyburn1()
{
Excel.Application oXL = new Excel.Application();
oXL.Visible = false;
Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
oSheet.Name = "The first sheet";
oSheet.Cells[1, 1] = "Something";
Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
as Excel.Worksheet;
oSheet2.Name = "The second sheet";
oSheet2.Cells[1, 1] = "Something completely different";
string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
+ "\\SoSample.xlsx";
oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
}
}
To do this, you would need to add a reference to Microsoft.Office.Interop.Excel to your project (you may have done this already since you are creating one sheet).
The statement that adds the second sheet is...
Code:
Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
as Excel.Worksheet;
the '1' argument specifies a single sheet, and it can be more if you want to add several sheets at once.
Final note: the statement oXL.Visible = false; tells Excel to start in silent mode.
I hope this code will be useful.
Thank You.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|