Generating Excel file using .NET

Given below is an example of how to create excel file using .NET (C#)

Excel has to be installed on the machine for the below example to work, since EXCEL COM object is used to generate excel file

---

1. Create a (C#) console application in Visual Studio

2. Add reference to “Microsoft.Office.Interop.Excel”

3. import the required namespaces

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;

4. The function to create EXCEL is given below

public void WriteToExcel(System.Data.DataTable dtResult, string strFileName, string strDirectoryName)
{
   
    Application xlApp = default(Application);
    Workbook xlWorkBook = default(Workbook);
    Worksheet xlWorkSheet = default(Worksheet);

    string fName = strFileName = strDirectoryName + strFileName;

    try
    {
        object misValue = Missing.Value;
        xlApp = new ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        int iRow = 1;
        int iColumn = 1;

        /*Add header to excel File*/
        StringBuilder oBuilder;
        oBuilder = new StringBuilder();
        foreach (DataColumn dc in dtResult.Columns)
        {
            if (dc.ColumnName != null)
            {
                xlWorkSheet.Cells[iRow, iColumn] = dc.ColumnName;
            }
            else
            {
                xlWorkSheet.Cells[iRow, iColumn] = "";
            }

            iColumn = iColumn + 1;
        }


        /*Add data to excel File*/
        foreach (DataRow dr in dtResult.Rows)
        {
            iColumn = 1;
            iRow = iRow + 1;
            oBuilder = new StringBuilder();
            foreach (DataColumn dc in dtResult.Columns)
            {
                if (dr[dc.ColumnName] == null || dr[dc.ColumnName] == DBNull.Value)
                {
                    xlWorkSheet.Cells[iRow, iColumn] = "";
                }
                else
                {
                    xlWorkSheet.Cells[iRow, iColumn] = dr[dc.ColumnName].ToString();
                }
                iColumn = iColumn + 1;
            }
        }

        
        xlWorkBook.SaveAs(fName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }

    }
    finally
    {
        if (xlApp != null)
            Marshal.ReleaseComObject(xlApp);
        if (xlWorkBook != null)
            Marshal.ReleaseComObject(xlWorkBook);
        if (xlWorkSheet != null)
            Marshal.ReleaseComObject(xlWorkSheet);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }
    }
}

5. Now call the function by passing in the datatable ,excel name and folder path

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");

DataRow dr = dt.NewRow();
dr[0] ="Name1";
dr[1] = "12";
   
dt.Rows.Add(dr);

WriteToExcel(dt, "test.xls", @"Path");