Resources » Code Snippets » C# Syntax

Create Excel file from C#


Posted Date: 24-Mar-2009  Last Updated:   Category: C# Syntax    
Author: Member Level: Gold    Points: 10



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



Did you like this resource? Share it with your friends and show your love!

Responses to "Create Excel file from C#"
Author: Filip    31 Aug 2009Member 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 not    19 Jan 2011Member 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: aronsinoai     19 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: jigu     30 May 2012
good one. Thanks for code.


Guest Author: Bob     04 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: Humaira     10 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: shetty     12 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



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India