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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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