How to write an Excel file in C#


In the previous tutorial, we saw how to read an Excel file in C#. In this tutorial, we will see how to write an Excel file in C#. This is part of a series of tutorials on Programming tips using C# and Microsoft Excel. This tutorial will be very useful for developers working on Desktop applications in .NET where we need to do lot of report generation in Microsoft Excel.

At the end of this tutorial, we will see how to open a Microsoft Excel file in C#, write few columns of data into an Excel file. In the end we will how to format the Excel file and finally save the file and close the Excel application. When I mentioned data, it can be anything. It could be a normal string, an integer, an Excel formula, Date etc.

write-excel-1


The figure shows the Excel file that we are going to generate programmatically. We have a header, followed 2 columns of data. And finally we will insert an Excel formula. We will do very basic formatting with changing the column width and changing the color of the header. We can do everything that you can do in Microsoft Excel through C#. However, discussion on formatting is outside the purview of this tutorial.


There are a number of free 3rd party libraries available for handling Microsoft Excel file in C#. However, here we use COM library which is easier compared to other libraries. Also, it is already available in your system if you have Microsoft Office installed.


Steps :


Add reference to Microsoft Excel

Step 1: First, you need to add a reference to the Excel object Library

Create a new C# Windows Forms Project. Open the Solution Explorer pane, Right click on References and click on Add Reference. This will open a new window “Add Reference”. Click on the COM tab, and select “Microsoft Excel Object Library”.

Add-Reference-1

Add-Reference-2


Step 2 :
You need to add the reference in the code, in order to use all the methods of Excel object library. This can be done using the following:

 

Step 3 : Instantiate an Excel Application object using the code:

 

Step 4 : Sometimes, Excel display some warning messages if there any compatibility issues. You can disable those warning messages using the following code.

 

Set the Visibility

Step 5 :
By default, the visibility of the Excel application is set to FALSE. Atleast during development stage, lot of developers would prefer to debug the code and also see the Excel file. You can set the visibility of the Excel file using the code :

 

When you are debugging, the visibility is set to true, so you can see the Excel file when the application is running. When in release mode, the visibility is set to false.

Create a new Excel File


Step 6 :
It is also possible that, you can open an existing Excel file and open a particular sheet and write data into it. I have discussed opening an Excel file here. Please go through it, if it interests you. Here we create a new Excel workbook. You can use the following code:

 

Write Data into Excel File

Step 7 : So far, so good ? In this example, we need to enter the data in 2 columns, column A and column B. Then, we will take the average percentage in Column B of all the people using the Excel formula “AVERAGE()”. Finally, we will save the file and close the Excel application.

The following code will enter the data in the Excel sheet.

 

Assuming that the data for column A and B are in the lists, Name and Percentage, the following code enters the data in the excel cells

 

Inserting the Excel Formula

Step 8 : The idea is simple, create an Excel formula as a string in C# and insert in the particular cell. Here we need to obtain the average of all the percentage data in Column B. Here we need to use the Excel formula “=AVERAGE()”.

The Excel formula will be =AVERAGE(B2:B11)

The following code in C# generates the above Excel formula :

 

Formatting the Excel sheet

Step 9 : Let us discuss little bit on formatting the Excel file, we just created. As I mentioned earlier, we can everything related to formatting in C# that we will do in Microsoft Excel.

You can make the header bold and change the background using the following code:

 

Step 10 : Sometimes, you donot know the complete address range . In such cases, you can obtain the address till the last used row and last used column using the following code:

 

And the rest of the steps remain same as mentioned in Step 9.

Column Number to Column Name conversion

Step 11 : As you know in Microsoft Excel, the cells are indexed by their cell index or address. The columns are indexed in alphabet starting from A, B… Z, AA, AB and so on.

In the example we have considered in this tutorial, we have always assumed to the column address as “A”. In order obtain the column name, sometimes we need to first convert the integer into an Column Name

The following function does just that. I found this beautiful piece of code somewhere in the internet, donot know from where.(If you are reading this, thanks for sharing). It converts the integer into equivalent Column name.

 

So, columnNumberToName(1) would return “A”

columnNumberToName(15) would return “O”

columnNumberToName(27) would return “AA” and so on.

Save the Excel File

Step 12 : The last step is to close the Excel file and the Excel application. The following code closes the Excel file and releases all the memory associated with the Excel application.

 

Download

You can download the complete source code from here and and the Excel file from here.

I hope, you find this tutorial on reading data from Microsoft excel in C# useful. Please leave a comment if you have any doubts or contact me with your comments and feedback. I like reading your feedback.



Kiran Chandrashekhar

Hey, Thanks for dropping by. My name is Kiran Chandrashekhar. I am a full-time software freelancer. I love Maths and Mathematical Shortcuts. Numbers fascinate me. I will be posting articles on Mathematical Shortcuts, Software Tips, Programming Tips in this website. I love teaching students preparing for various competitive examinations. Read my complete story.

4 Comments

  1. Using Microsoft Visual Studio Professional 2012 Version 11.0.603.15 Update 2 and Microsoft Office Professional 2013
    I have tried out your code “How to write an Excel file in C#”
    I have tried to add a reference to “Microsoft Excel Object Library” but there is no reference in the COM tab.
    On the other hand in The Assemblies tab, there are references to Microsoft.Office.Interop.Excel and to Microsoft.Office.Interop.Word
    After adding these two references i receive the following error:
    Warning 1
    The type ‘Microsoft.Office’ in ‘C:\C#-forrit\Projects\MBM-Forrit\MBM-Forrit\Office.cs’ conflicts with the
    imported namespace ‘Microsoft.Office’ in ‘C:\WINDOWS\assembly\GAC_MSIL\Office\15.0.0.0__71e9bce111e9429c\Office.dll’.
    Using the type defined in ‘C:\C#-forrit\Projects\MBM-Forrit\MBM-Forrit\Office.cs’.C:\C#-forrit\Projects\MBM-Forrit\MBM-Forrit\Form1.cs 11 25 MBM-Forrit

    How can i overcome this error ?

    Best regards

    Magnus

    • Hello Magnus,

      Please email your code to I will have a look at it.

      Thanks
      Kiran

  2. Piece of writing writing is also a excitement, if you be acquainted with then you can write or else it is complex to
    write.

Leave a Reply

Your email address will not be published. Required fields are marked *

Protected by WP Anti Spam