13

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.

cigien
  • 57,834
  • 11
  • 73
  • 112
HarshJain
  • 327
  • 1
  • 4
  • 12
  • 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 Nov 16 '11 at 18:33
  • You probably changed the default Excel behavior. See http://stackoverflow.com/questions/8066189/c-sharp-create-excel-workbook-with-1-sheet-by-default – JMax Nov 16 '11 at 19:10
  • Which technology or component you're using for the single sheet you're already creating? – Shahzad Latif Nov 18 '11 at 13:41

2 Answers2

25

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.

Gayot Fow
  • 8,710
  • 1
  • 35
  • 48
  • 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(); – Patrik Lindström Feb 12 '12 at 22:34
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);
    }
}
Theo
  • 57,719
  • 8
  • 24
  • 41
Deepika chalana
  • 43
  • 7