How do I add multiple works sheets to an Excel document from C#

Question

I have created five Excel files in temporary files. Now I want to combine them as 5 work sheets in one workbook.
How can I do that?

---

Certified Geek

Answer 1

From Stack Overflow
(http://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba)

Sub CopyWorkbook()

    Dim currentSheet as Worksheet
    Dim sheetIndex as Integer
    sheetIndex = 1

    For Each currentSheet in Worksheets
        Windows("SOURCE WORKBOOK").Activate 
        currenSheet.Select 
        currentSheet.Copy Before:=Workbooks("TARGET WORKBOOK").Sheets(sheetIndex) 

        sheetIndex = sheetIndex + 1
    Next currentSheet
End Sub

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 2

Unfortunately your code is not in C#.
I am looking for the proper way to use named parameters in C# when creating worksheets.
I am looking for code that loads Five Excel files into one Excel file with multiple worksheets.

---

Certified Geek

Answer 3

"Unfortunately your code is not in C#."

I find that statement hilarious, not just funny. For a total of perhaps 15 lines of VBA code.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 4

Your code has named parameters. When I try to use name parameters in C# I get an exception.
excelApp.Worksheets.Add( After: 1); // Throws an exception.
Maybe it is the value of my parameters and the state of my Excel objects that is the problem. I am not able to find a working example that I can use for loading five excel files into one workbook.

I have a working example of loading a single file into Excel.

Normally I figure out how to convert VBA code into C#, but not all of the time. The other problem is that your suggested code does not solve my problem.

---

Certified Geek

Answer 5

Hummmm, interesting.  Maybe something like this will work:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication6
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

        // creating Excel Application
        Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
        // creating new WorkBook within Excel application
        Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);
        // creating new Excelsheet in workbook
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   

        // see the excel sheet behind the program
        app.Visible = true;


        // get the reference of first sheet. By default its name is Sheet1.
        // store its reference to worksheet
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;

        // changing the name of active sheet
        worksheet.Name = "TestingExcelAndCSharp";


        // save the application
        workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        // Exit from the application

        app.Quit();

        }
    }
}

Just set a reference to Excel: COM > Microsoft Excel 14.0 Object Library

---

Ryan Shuell

Answer 6

Ryan,
You have some good ideas, but there is only one worksheet and need five worksheets. Each worksheet needs to be loaded from an external excel file. I know how to load one file into the first worksheet.
excelApp = new Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(FEPFilename,
 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
 true, false, 0, true, false, false);

Each external Excel needs to be load into a specific work sheet.

---

Certified Geek

Answer 7

Sorry for the delay getting back to you.  I don't have .NET installed on my office machine.  It's kind of weird that I work as a 'developer', and I'm not allowed to install any 'developer tools' on my office machine.  I guess they don't trust .NET, or they don't understand it, or some such thing.  Anyway, give this a try and feed back.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication7
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            Sheets xlSheets = null;
            Worksheet xlNewSheet = null;


            xlSheets = workbook.Sheets as Sheets;

            // see the excel sheet behind the program
            app.Visible = true;

            //Select the sheet
            worksheet = workbook.Worksheets[1];
            //Rename the sheet
            worksheet.Name = "MySheet1";

            worksheet = workbook.Worksheets[2];
            worksheet.Name = "MySheet2";

            worksheet = workbook.Worksheets[3];
            worksheet.Name = "MySheet3";

            // The first argument below inserts the new worksheet as the first one
            xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
            xlNewSheet.Name = "MySheet4";

            // The first argument below inserts the new worksheet as the first one
            xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
            xlNewSheet.Name = "MySheet5";

            // save the application
            workbook.SaveAs("c:\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            // Exit from the application

            app.Quit();

        }
    }
}

---

Ryan Shuell

Answer 8

Thanks for your reply.
I have decided to go another route where every dataset will be in a separate workbook.

---

Certified Geek