How to create Excel file in C#

 How to create Excel file in C# 

In this post, we will see how to read and write excel file in c#.You can read & write excel file from c# mainly in 2 ways.
  1) Excel Object Model
  2) Microsoft Jet Engine.
Using above microsft library we can creating a new workbook, adding data to a workbook, creating charts etc.There are Open source libery like ClosedXML,EasyXML etc. also availabe which help to write excel file with less effort.

In this post shows how to create an excel file using COM interop. To create new excel file programmatically in c#, we must have MS Excel installed on our system for run this code properly.

 Excel Library:  

  To used the Excel Object class in our project. we have to add the Microsoft Excel Object Library to you project.Form the following pictures you can find how to add Excel reference library in your project.

  How to create an Excel Document Programmatically:  

  First we have to initialize the Excel application Object.
   

Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

 Before creating new Excel Workbook, we should check whether Excel is installed in our system.

if (xlApp == null)
{
    MessageBox.Show("Excel is not properly installed!!");
    return;
}

 Now create new Workbook.

xlWorkBook = xlApp.Workbooks.Add(misValue);

 After creating the new Workbook, next step is to write content to worksheet.

xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "ID";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[2, 1] = "1";
xlWorkSheet.Cells[2, 2] = "One";
xlWorkSheet.Cells[3, 1] = "2";
xlWorkSheet.Cells[3, 2] = "Two";

  In the above code we write the data in the Sheet1, If we want to write data in sheet 2 then we should code like this. The Cells function used to write data cell by cell.1 st number for row and 2nd for column.

xlWorkSheet = xlWorkBook.Worksheets.get_Item(2);
xlWorkSheet.Cells[1, 1] = "Sheet 2 Data";

 Save Excel file (SaveAs() method):  

After write the content to the cell, next step is to save the excel file in our system.

xlWorkBook.SaveAs("your-file-name.xls");

  Properly clean up Excel interop objects:  

To properly clean up Excel interop objects used Marshal class.The Marshal class is in the System.Runtime.InteropServices namespace, so you should import the following namespace.

using System.Runtime.InteropServices;
 
    Marshal.ReleaseComObject(xlWorkBook);
    Marshal.ReleaseComObject(xlApp);

It is important to note that every reference to an Excel COM object had to be set to null when you have finished with it, including Cells, Sheets, everything.   

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace Exam01
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            object misValue = System.Reflection.Missing.Value;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = "ID";
            xlWorkSheet.Cells[1, 2] = "Name";
            xlWorkSheet.Cells[2, 1] = "1";
            xlWorkSheet.Cells[2, 2] = "One";
            xlWorkSheet.Cells[3, 1] = "2";
            xlWorkSheet.Cells[3, 2] = "Two";
            xlWorkBook.SaveAs(@"E:\DotNet\ExelExam\Exam01\Exam01\bin\Debug\your-file-name.xlsx");
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
        }
    }
}