C# - How to add an Excel Worksheet programmatically - Office XP / 2003
Solution 1
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.
Solution 2
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.
Solution 3
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.
Solution 4
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;
}
Related videos on Youtube
Jon
C# Developer in the London. Loves C# and being challenged with new and interesting projects. My github account is: https://github.com/twistedtwig
Updated on July 09, 2022Comments
-
Jon almost 2 years
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.
-
Jon over 15 yearsThe exception is: Exception from HRESULT: 0x800A03EC Source is: "Interop.Excel" Thanks for all the help
-
-
David Thielen almost 6 yearsI got a COMException on this on the Add() call if it is a XLSM file.