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