Create Excel file from C#

Create Excel from C# Program

Add reference for

Excel
Microsoft.Office.Core
VBIDE



using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;


namespace CreateExcelSheets
{
class Program
{
[STAThread]
static void Main(string[] args)
{
//string MyFile = Path.GetFullPath(".") + @"\sample.xls";
string MyFile = @"c:\sample.xls";
CreateWorkbook(MyFile);
Console.WriteLine("File Saved to " + MyFile);
Console.ReadLine();
}

#region Create Workbook
private static void CreateWorkbook(string FileName)
{

Excel.Application xl = null;
Excel._Workbook wb = null;
Excel._Worksheet sheet = null;
//VBIDE.VBComponent module = null;
bool SaveChanges = false;
try
{

if (File.Exists(FileName)) { File.Delete(FileName); }

GC.Collect();

// Create a new instance of Excel from scratch

xl = new Excel.Application();
xl.Visible = false;

// Add one workbook to the instance of Excel

wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);



// Get a reference to the one and only worksheet in our workbook

//sheet = (Excel._Worksheet)wb.ActiveSheet;
sheet = (Excel._Worksheet)(wb.Sheets[1]);

// Fill spreadsheet with sample data
//sheet.Name = "Test";

for (int r = 0; r < 20; r++)
{

for (int c = 0; c < 10; c++)
{
sheet.Cells[r + 1, c + 1] = 125;
}
}

// set come column heading names
sheet.Name = "Jan";
sheet.Cells[1, 1] = "Heading";

sheet = (Excel._Worksheet)(wb.Sheets[2]);
sheet.Name = "Feb";
sheet.Cells[1, 1] = "Heading";

sheet = (Excel._Worksheet)(wb.Sheets[3]);
sheet.Name = "Mar";
sheet.Cells[1, 1] = "Heading";

sheet = (Excel._Worksheet)(wb.Sheets[4]);
sheet.Name = "Apr";

sheet.Cells[1, 1] = "Heading";



// Let loose control of the Excel instance

xl.Visible = false;
xl.UserControl = false;

// Set a flag saying that all is well and it is ok to save our changes to a file.

SaveChanges = true;

// Save the file to disk

wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,
null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
false, false, null, null, null);

}
catch (Exception err)
{
String msg;
msg = "Error: ";
msg = String.Concat(msg, err.Message);
msg = String.Concat(msg, " Line: ");
msg = String.Concat(msg, err.Source);
Console.WriteLine(msg);
}
finally
{

try
{
// Repeat xl.Visible and xl.UserControl releases just to be sure
// we didn't error out ahead of time.

xl.Visible = false;
xl.UserControl = false;
// Close the document and avoid user prompts to save if our method failed.
wb.Close(SaveChanges, null, null);
xl.Workbooks.Close();
}
catch { }

// Gracefully exit out and destroy all COM objects to avoid hanging instances
// of Excel.exe whether our method failed or not.

xl.Quit();

//if (module != null) { Marshal.ReleaseComObject(module); }
if (sheet != null) { Marshal.ReleaseComObject(sheet); }
if (wb != null) { Marshal.ReleaseComObject(wb); }
if (xl != null) { Marshal.ReleaseComObject(xl); }

//module = null;
sheet = null;
wb = null;
xl = null;
GC.Collect();
}
}
#endregion
}
}


Comments

Author: Filip31 Aug 2009 Member Level: Silver   Points : 2

Nice code but using Excel Interop is not a good idea to use when working with Excel from C#. It has many issues that can make your program not work, like compatibility with Excel versions etc. I recommend to try GemBox spreadsheet component at http://www.gemboxsoftware.com/GBSpreadsheet.htm.

Author: bug me not19 Jan 2011 Member Level: Bronze   Points : 1

nice, that's what I was looking for, how to create an excel document from scratches, do you know how can I take that Object and export it directly to my computer? If i'm not in the server, I mean. @fd3z. Thanks.

Guest Author: aronsinoai19 Dec 2011

I think the most effictive way to create/edit Excel files is through the open xml office package, which doesn't require Office to be installed on the target machine, thus it is great for server side processing (web applications) and even for desktop applications it consumes less resources, thus it is very fast.
I have wrote small wrapper around it. You can check it here: http://officehelper.codeplex.com.

Guest Author: jigu30 May 2012

good one. Thanks for code.

Guest Author: Bob04 Nov 2012

Wow, I'm almost embarrassed to say how long it took me to figure this out. Great piece of code here!!

Guest Author: Humaira10 Oct 2013

Because it provides .NET code to implement in your .NET Applications to create, read modify and even convert the excel file to many other format.
you will find many codes from that page.

Guest Author: shetty12 Nov 2013

You can create excel files with charts and pivot tables in c# by using Aspose.Cells for .NET:

http://www.aspose.com/.net/excel-component.aspx