none
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

    Tuesday, December 11, 2012 9:35 PM

Answers

  • 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

    Wednesday, December 12, 2012 11:57 PM

All replies

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

    Tuesday, December 11, 2012 9:41 PM
  • 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

    Tuesday, December 11, 2012 9:51 PM
  • "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.

    Tuesday, December 11, 2012 10:11 PM
  • 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


    • Edited by Arne at ATK Tuesday, December 11, 2012 10:19 PM
    Tuesday, December 11, 2012 10:18 PM
  • 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

    Wednesday, December 12, 2012 5:21 AM
  • 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

    Wednesday, December 12, 2012 1:10 PM
  • 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

    Wednesday, December 12, 2012 11:57 PM
  • Thanks for your reply.
    I have decided to go another route where every dataset will be in a separate workbook.

    Certified Geek

    Thursday, December 13, 2012 1:28 PM
  • Thanks, it was really helpful
    Thursday, May 08, 2014 10:54 AM