Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

How to create Excel file in C#



The following C# code example shows how to use COM interop to create an Excel file. Before going to create a new Excel file programmatically in C#, you must have Excel installed on your system for this code to run properly.

Excel Library

To access the object model from Visual C# .NET, you have to add the Microsoft Excel 15.0 Object Library to your project.

Create a new project in your Visual Studio and add a Command Button to your C# Form.

How to use COM Interop to Create an Excel Spreadsheet

Form the following pictures you can find how to add an Excel reference library in your project.

Select Add Reference dialogue from Project menu of your Visual Studio.

Select Microsoft Excel 15.0 Object Library of COM left side menu and click OK button.

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, you should check whether Excel is installed in your system.

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

Then create new Workbook

xlWorkBook = xlApp.Workbooks.Add(misValue);

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

xlWorkSheet = (Excel.Worksheet)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 you want to write data in sheet 2 then you should code like this.

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

Save Excel file (SaveAs() method)t

After writing the content to the cell, the next step is to save the excel file in your system.

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

How to properly clean up Excel interop objects

Interop marshalling governs how data is passed in method arguments and return values between managed and unmanaged memory during calls. Most data types have common representations in both managed and unmanaged memory. The interop marshaler handles these types for you. Other types can be ambiguous or not represented at all in managed memory.

 
Marshal.ReleaseComObject (excelWB);
Marshal.ReleaseComObject (excelApp);

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.

The Marshal class is in the System.Runtime.InteropServices namespace, so you should import the following namespace.

 
using System.Runtime.InteropServices;

Creating an Excel Spreadsheet Programmatically

Copy and paste the following source code in your C# project file

using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

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


            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)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("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

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

            MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls");
        }

    }
}

Note: You have to add Microsoft.Office.Interop.Excel to your source code.

<br>

<br>
using Excel = Microsoft.Office.Interop.Excel;
<br>

<br>

When you execute this program, it will create an excel file in your D:


Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More