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

Question

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

I have successfully created an excel file with single sheet. But I am not able to do that for multiple sheets.

I guess it depends on how you are looking at creating the excel file. Are you looking @ creating a page per table in the dataset? — KreepN
– KreepN, Commented Nov 16, 2011 at 18:33
You probably changed the default Excel behavior. See stackoverflow.com/questions/8066189/… — JMax
– JMax, Commented Nov 16, 2011 at 19:10
Which technology or component you're using for the single sheet you're already creating? — Shahzad Latif
– Shahzad Latif, Commented Nov 18, 2011 at 13:41

Gayot Fow · Accepted Answer · 2011-12-29 23:59:39Z

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

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

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 get an error on oXL.Quit(); everytime. Excel Undantagskod: xc0000005 Felförskjutning: 0x000000000000f993. The error gets put in the eventlog of the machine. Also do you need the missing arumgents in C# 4? Is it not enough with dynamic oWB= excelApplication.Workbooks.Add();

Theo · Accepted Answer · 2019-06-21 13:00:22Z

1

var groupedSheetList = UserData
    .GroupBy (u => u.date)
    .Select (grp => grp.ToList ())
    .ToList ();

You can try this

using (var package = new ExcelPackage ()) 
    {
        foreach (var item in groupedSheetList) {
            var workSheet = package.Workbook.Worksheets.Add (item[0].date);
    }
}

edited Jun 21, 2019 at 13:00

Theo

61.9k8 gold badges28 silver badges48 bronze badges

answered Jun 21, 2019 at 12:46

Deepika chalana

337 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related