× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

How to create excel file with multiple sheets from DataSet using C#

Gayot Fow
Feb 05, 2015
<p>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...</p> <pre><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(); } } </code></pre> <p>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). </p> <p>The statement that adds the second sheet is...</p> <pre><code>Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing) as Excel.Worksheet; </code></pre> <p>the '1' argument specifies a single sheet, and it can be more if you want to add several sheets at once.</p> <p>Final note: the statement oXL.Visible = false; tells Excel to start in silent mode. </p> <p>This tip was originally posted on <a href="http://stackoverflow.com/questions/8156616/How%20to%20create%20excel%20file%20with%20multiple%20sheets%20from%20DataSet%20using%20C#/8674905">Stack Overflow</a>.</p>
comments powered by Disqus