C - How to add an Excel Worksheet programmatically - Office XP 2003

Question

I am just starting to fiddle with Excel via C  to be able to automate the creation  and addition to an Excel file   I can open the file and update its data and move through the existing worksheets  My problem is how can I add new sheets   I tried   Excel Worksheet newWorksheet  newWorksheet    Excel Worksheet excelApp ThisWorkbook Worksheets Add                  Type Missing  Type Missing  Type Missing  Type Missing     But I get below COM Exception and my googling has not given me any answer      Exception from HRESULT  0x800A03EC Source is   Interop Excel    I am hoping someone maybe able to put me out of my misery

User · Answer

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Excel;

namespace testExcelconsoleApp
{
    class Program
    {
        private String fileLoc = @"C:\temp\test.xls";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.createExcel();
        }

        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                        false, XlPlatform.xlWindows, "",
                                                        true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
    }
}

Thank you for all your help.

User · Answer

You can use OLEDB to create and manipulate Excel files  See this question for links and samples

User · Answer

Here are a couple things I figured  out     You can t open more than one instance of the same object at the same time  For Example if you instanciate a new excel sheet object called xlsheet1 you have to release it before creating another excel sheet object ex xlsheet2  It seem as COM looses track of the object and leaves a zombie process on the server  Using the open method associated with excel workbooks also becomes difficult to close if you have multiple users accessing the same file  Use the Add method instead  it works just as good without locking the file  eg  xlBook   xlBooks Add  C  location XlTemplate xls   Place your garbage collection in a separate block or method after releasing the COM objects

User · Answer

Another  Up Tick  for AR     but if you don t have to use interop I would avoid it altogether   This product is actually quite interesting  http   www clearoffice com  and it provides a very intuitive  fully managed  api for manipulation excel files and seems to be free   at least for the time being   SpreadSheetGear is also excellent but pricey     my two cents

User · Answer

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Excel;

namespace testExcelconsoleApp
{
    class Program
    {
        private String fileLoc = @"C:\temp\test.xls";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.createExcel();
        }

        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                        false, XlPlatform.xlWindows, "",
                                                        true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
    }
}

Thank you for all your help.

User · Answer

I had a similar problem application-level add-in in VSTO  the exception HRESULT  0x800A03EC when adding new sheet      The error code 0x800A03EC  or -2146827284  means NAME NOT FOUND  in   other words  you ve asked for something  and Excel can t find it    Dominic Zukiewicz   Excel error HRESULT  0x800A03EC while trying to get range with cell  39 s name  Then I finally realized ThisWorkbook triggered the exception  ActiveWorkbook went OK   Excel Worksheet newSheetException   Globals ThisAddIn Application ThisWorkbook Worksheets Add Type Missing  sheet  Type Missing  Type Missing   Excel Worksheet newSheetNoException   Globals ThisAddIn Application ActiveWorkbook Worksheets Add Type Missing  sheet  Type Missing  Type Missing

User · Answer

This is what i used to add addtional worksheet  Workbook workbook   null  Worksheet worksheet   null   workbook   app Workbooks Add 1   workbook Sheets Add     Worksheet additionalWorksheet   workbook ActiveSheet

User · Answer

You can use OLEDB to create and manipulate Excel files  See this question for links and samples

User · Answer

You need to add a COM reference in your project to the  Microsoft Excel 11 0 Object Library  - or whatever version is appropriate   This code works for me   private void AddWorksheetToExcelWorkbook string fullFilename string worksheetName        Microsoft Office Interop Excel Application xlApp   null      Workbook xlWorkbook   null      Sheets xlSheets   null      Worksheet xlNewSheet   null       try           xlApp   new Microsoft Office Interop Excel Application             if  xlApp    null              return              Uncomment the line below if you want to see what s happening in Excel            xlApp Visible   true           xlWorkbook   xlApp Workbooks Open fullFilename  0  false  5                          false  XlPlatform xlWindows                      true  false  0  true  false  false            xlSheets   xlWorkbook Sheets as Sheets              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   worksheetName           xlWorkbook Save            xlWorkbook Close Type Missing Type Missing Type Missing           xlApp Quit              finally           Marshal ReleaseComObject xlNewSheet           Marshal ReleaseComObject xlSheets           Marshal ReleaseComObject xlWorkbook           Marshal ReleaseComObject xlApp           xlApp   null               Note that you want to be very careful about properly cleaning up and releasing your COM object references  Included in that StackOverflow question is a useful rule of thumb   Never use 2 dots with COM objects   In your code  you re going to have real trouble with that  My demo code above does NOT properly clean up the Excel app  but it s a start    Some other links that I found useful when looking into this question    Opening and Navigating Excel with C  How to  Use COM Interop to Create an Excel Spreadsheet  C  Programming Guide  How to  Add New Worksheets to Workbooks   According to MSDN      To use COM interop  you must have   administrator or Power User security   permissions    Hope that helps

User · Answer

COM is definitely not a good way to go  More specifically  it s a no go if you re dealing with web environment     I ve used with success the following open source projects    ExcelPackage for OOXML formats  Office 2007  NPOI for  XLS format  Office 2003    Take a look at these blog posts   Creating Excel spreadsheets  XLS and  XLSX in C   NPOI with Excel Table and dynamic Chart

User · Answer

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Excel;

namespace testExcelconsoleApp
{
    class Program
    {
        private String fileLoc = @"C:\temp\test.xls";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.createExcel();
        }

        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                        false, XlPlatform.xlWindows, "",
                                                        true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
    }
}

Thank you for all your help.

User · Answer

Another  Up Tick  for AR     but if you don t have to use interop I would avoid it altogether   This product is actually quite interesting  http   www clearoffice com  and it provides a very intuitive  fully managed  api for manipulation excel files and seems to be free   at least for the time being   SpreadSheetGear is also excellent but pricey     my two cents

User · Answer

Here are a couple things I figured  out     You can t open more than one instance of the same object at the same time  For Example if you instanciate a new excel sheet object called xlsheet1 you have to release it before creating another excel sheet object ex xlsheet2  It seem as COM looses track of the object and leaves a zombie process on the server  Using the open method associated with excel workbooks also becomes difficult to close if you have multiple users accessing the same file  Use the Add method instead  it works just as good without locking the file  eg  xlBook   xlBooks Add  C  location XlTemplate xls   Place your garbage collection in a separate block or method after releasing the COM objects

User · Answer

You need to add a COM reference in your project to the  Microsoft Excel 11 0 Object Library  - or whatever version is appropriate   This code works for me   private void AddWorksheetToExcelWorkbook string fullFilename string worksheetName        Microsoft Office Interop Excel Application xlApp   null      Workbook xlWorkbook   null      Sheets xlSheets   null      Worksheet xlNewSheet   null       try           xlApp   new Microsoft Office Interop Excel Application             if  xlApp    null              return              Uncomment the line below if you want to see what s happening in Excel            xlApp Visible   true           xlWorkbook   xlApp Workbooks Open fullFilename  0  false  5                          false  XlPlatform xlWindows                      true  false  0  true  false  false            xlSheets   xlWorkbook Sheets as Sheets              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   worksheetName           xlWorkbook Save            xlWorkbook Close Type Missing Type Missing Type Missing           xlApp Quit              finally           Marshal ReleaseComObject xlNewSheet           Marshal ReleaseComObject xlSheets           Marshal ReleaseComObject xlWorkbook           Marshal ReleaseComObject xlApp           xlApp   null               Note that you want to be very careful about properly cleaning up and releasing your COM object references  Included in that StackOverflow question is a useful rule of thumb   Never use 2 dots with COM objects   In your code  you re going to have real trouble with that  My demo code above does NOT properly clean up the Excel app  but it s a start    Some other links that I found useful when looking into this question    Opening and Navigating Excel with C  How to  Use COM Interop to Create an Excel Spreadsheet  C  Programming Guide  How to  Add New Worksheets to Workbooks   According to MSDN      To use COM interop  you must have   administrator or Power User security   permissions    Hope that helps

User · Answer

I had a similar problem application-level add-in in VSTO  the exception HRESULT  0x800A03EC when adding new sheet      The error code 0x800A03EC  or -2146827284  means NAME NOT FOUND  in   other words  you ve asked for something  and Excel can t find it    Dominic Zukiewicz   Excel error HRESULT  0x800A03EC while trying to get range with cell  39 s name  Then I finally realized ThisWorkbook triggered the exception  ActiveWorkbook went OK   Excel Worksheet newSheetException   Globals ThisAddIn Application ThisWorkbook Worksheets Add Type Missing  sheet  Type Missing  Type Missing   Excel Worksheet newSheetNoException   Globals ThisAddIn Application ActiveWorkbook Worksheets Add Type Missing  sheet  Type Missing  Type Missing

User · Answer

Do not forget to include Reference to Microsoft Excel 12 0 11 0 object Library   using Excel   Microsoft Office Interop Excel     Include this Namespace     Microsoft Office Interop Excel Application xlApp   null  Excel Workbook xlWorkbook   null  Excel Sheets xlSheets   null  Excel Worksheet xlNewSheet   null  string worksheetName   Sheet Name   object readOnly1   false   object isVisible   true   object missing   System Reflection Missing Value   try       xlApp   new Microsoft Office Interop Excel Application         if  xlApp    null          return          Uncomment the line below if you want to see what s happening in Excel        xlApp Visible   true       xlWorkbook   xlApp Workbooks Open   C  Book1 xls   missing  readOnly1  missing  missing  missing  missing  missing  missing  missing  missing  isVisible  missing  missing  missing        xlSheets    Excel Sheets xlWorkbook Sheets          The first argument below inserts the new worksheet as the first one     xlNewSheet    Excel Worksheet xlSheets Add xlSheets 1   Type Missing  Type Missing  Type Missing       xlNewSheet Name   worksheetName       xlWorkbook Save        xlWorkbook Close Type Missing  Type Missing  Type Missing       xlApp Quit      finally       System Runtime InteropServices Marshal ReleaseComObject xlNewSheet       System Runtime InteropServices Marshal ReleaseComObject xlSheets       System Runtime InteropServices Marshal ReleaseComObject xlWorkbook       System Runtime InteropServices Marshal ReleaseComObject xlApp         xlApp   null

User · Answer

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using Excel;

namespace testExcelconsoleApp
{
    class Program
    {
        private String fileLoc = @"C:\temp\test.xls";

        static void Main(string[] args)
        {
            Program p = new Program();
            p.createExcel();
        }

        private void createExcel()
        {
            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet newSheet = null;

            try
            {
                FileInfo file = new FileInfo(fileLoc);
                if (file.Exists)
                {
                    excelApp = new Excel.Application();
                    workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
                                                        false, XlPlatform.xlWindows, "",
                                                        true, false, 0, true, false, false);

                    sheets = workbook.Sheets;

                    //check columns exist
                    foreach (Excel.Worksheet sheet in sheets)
                    {
                        Console.WriteLine(sheet.Name);
                        sheet.Select(Type.Missing);

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    }

                    newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    newSheet.Name = "My New Sheet";
                    newSheet.Cells[1, 1] = "BOO!";

                    workbook.Save();
                    workbook.Close(null, null, null);
                    excelApp.Quit();
                }
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                newSheet = null;
                sheets = null;
                workbook = null;
                excelApp = null;

                GC.Collect();
            }
        }
    }
}

Thank you for all your help.

User · Answer

Another  Up Tick  for AR     but if you don t have to use interop I would avoid it altogether   This product is actually quite interesting  http   www clearoffice com  and it provides a very intuitive  fully managed  api for manipulation excel files and seems to be free   at least for the time being   SpreadSheetGear is also excellent but pricey     my two cents

User · Answer

You need to add a COM reference in your project to the  Microsoft Excel 11 0 Object Library  - or whatever version is appropriate   This code works for me   private void AddWorksheetToExcelWorkbook string fullFilename string worksheetName        Microsoft Office Interop Excel Application xlApp   null      Workbook xlWorkbook   null      Sheets xlSheets   null      Worksheet xlNewSheet   null       try           xlApp   new Microsoft Office Interop Excel Application             if  xlApp    null              return              Uncomment the line below if you want to see what s happening in Excel            xlApp Visible   true           xlWorkbook   xlApp Workbooks Open fullFilename  0  false  5                          false  XlPlatform xlWindows                      true  false  0  true  false  false            xlSheets   xlWorkbook Sheets as Sheets              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   worksheetName           xlWorkbook Save            xlWorkbook Close Type Missing Type Missing Type Missing           xlApp Quit              finally           Marshal ReleaseComObject xlNewSheet           Marshal ReleaseComObject xlSheets           Marshal ReleaseComObject xlWorkbook           Marshal ReleaseComObject xlApp           xlApp   null               Note that you want to be very careful about properly cleaning up and releasing your COM object references  Included in that StackOverflow question is a useful rule of thumb   Never use 2 dots with COM objects   In your code  you re going to have real trouble with that  My demo code above does NOT properly clean up the Excel app  but it s a start    Some other links that I found useful when looking into this question    Opening and Navigating Excel with C  How to  Use COM Interop to Create an Excel Spreadsheet  C  Programming Guide  How to  Add New Worksheets to Workbooks   According to MSDN      To use COM interop  you must have   administrator or Power User security   permissions    Hope that helps

User · Answer

You can use OLEDB to create and manipulate Excel files  See this question for links and samples

User · Answer

You can use OLEDB to create and manipulate Excel files  See this question for links and samples

User · Answer

Do not forget to include Reference to Microsoft Excel 12 0 11 0 object Library   using Excel   Microsoft Office Interop Excel     Include this Namespace     Microsoft Office Interop Excel Application xlApp   null  Excel Workbook xlWorkbook   null  Excel Sheets xlSheets   null  Excel Worksheet xlNewSheet   null  string worksheetName   Sheet Name   object readOnly1   false   object isVisible   true   object missing   System Reflection Missing Value   try       xlApp   new Microsoft Office Interop Excel Application         if  xlApp    null          return          Uncomment the line below if you want to see what s happening in Excel        xlApp Visible   true       xlWorkbook   xlApp Workbooks Open   C  Book1 xls   missing  readOnly1  missing  missing  missing  missing  missing  missing  missing  missing  isVisible  missing  missing  missing        xlSheets    Excel Sheets xlWorkbook Sheets          The first argument below inserts the new worksheet as the first one     xlNewSheet    Excel Worksheet xlSheets Add xlSheets 1   Type Missing  Type Missing  Type Missing       xlNewSheet Name   worksheetName       xlWorkbook Save        xlWorkbook Close Type Missing  Type Missing  Type Missing       xlApp Quit      finally       System Runtime InteropServices Marshal ReleaseComObject xlNewSheet       System Runtime InteropServices Marshal ReleaseComObject xlSheets       System Runtime InteropServices Marshal ReleaseComObject xlWorkbook       System Runtime InteropServices Marshal ReleaseComObject xlApp         xlApp   null

User · Answer

COM is definitely not a good way to go  More specifically  it s a no go if you re dealing with web environment     I ve used with success the following open source projects    ExcelPackage for OOXML formats  Office 2007  NPOI for  XLS format  Office 2003    Take a look at these blog posts   Creating Excel spreadsheets  XLS and  XLSX in C   NPOI with Excel Table and dynamic Chart

User · Answer

Another  Up Tick  for AR     but if you don t have to use interop I would avoid it altogether   This product is actually quite interesting  http   www clearoffice com  and it provides a very intuitive  fully managed  api for manipulation excel files and seems to be free   at least for the time being   SpreadSheetGear is also excellent but pricey     my two cents

User · Answer

This is what i used to add addtional worksheet  Workbook workbook   null  Worksheet worksheet   null   workbook   app Workbooks Add 1   workbook Sheets Add     Worksheet additionalWorksheet   workbook ActiveSheet

User · Answer

You need to add a COM reference in your project to the  Microsoft Excel 11 0 Object Library  - or whatever version is appropriate   This code works for me   private void AddWorksheetToExcelWorkbook string fullFilename string worksheetName        Microsoft Office Interop Excel Application xlApp   null      Workbook xlWorkbook   null      Sheets xlSheets   null      Worksheet xlNewSheet   null       try           xlApp   new Microsoft Office Interop Excel Application             if  xlApp    null              return              Uncomment the line below if you want to see what s happening in Excel            xlApp Visible   true           xlWorkbook   xlApp Workbooks Open fullFilename  0  false  5                          false  XlPlatform xlWindows                      true  false  0  true  false  false            xlSheets   xlWorkbook Sheets as Sheets              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   worksheetName           xlWorkbook Save            xlWorkbook Close Type Missing Type Missing Type Missing           xlApp Quit              finally           Marshal ReleaseComObject xlNewSheet           Marshal ReleaseComObject xlSheets           Marshal ReleaseComObject xlWorkbook           Marshal ReleaseComObject xlApp           xlApp   null               Note that you want to be very careful about properly cleaning up and releasing your COM object references  Included in that StackOverflow question is a useful rule of thumb   Never use 2 dots with COM objects   In your code  you re going to have real trouble with that  My demo code above does NOT properly clean up the Excel app  but it s a start    Some other links that I found useful when looking into this question    Opening and Navigating Excel with C  How to  Use COM Interop to Create an Excel Spreadsheet  C  Programming Guide  How to  Add New Worksheets to Workbooks   According to MSDN      To use COM interop  you must have   administrator or Power User security   permissions    Hope that helps

[c#] C# - How to add an Excel Worksheet programmatically - Office XP / 2003

Examples related to c#

Examples related to excel

Examples related to com

Examples related to office-interop

Examples related to worksheet