How to write data to an excel sheet using asp.net C# code

How to write data to an excel sheet using asp.net C# code

In our last post, we discussed how we can read the data from excel using c# code.

In this post we will discuss how one can create an excel workbook and write data  in it using ASP.Net C# code.

Let's say we want to write below data to an excel workbook named StudentDetails:

Name                       Age

Tony                        25

Robert                     34

Harry                      18

Tom                        21

Below is the step by step procedure to do the same:

Step 1: Create a basic .Net console application in Visual Studio.

Step 2: In the solution explorer in right hand side,right click on reference and click add reference:

Step 3: Select "Microsoft Excel 16.0 Object Library" and click on add:

Step 4: Include "using Excel = Microsoft.Office.Interop.Excel;" :

Step 5:  Add below function in you code to read the excel sheet:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

public static void WriteExcel()

        {

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

            if (xlApp != null)

            {

                Excel.Workbook excelWorkbook;

                Excel.Worksheet excelSheet;

                object misValue = System.Reflection.Missing.Value;

                excelWorkbook = xlApp.Workbooks.Add(misValue);

                excelSheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);

                excelSheet.Cells[1, 1] = "Name";

                excelSheet.Cells[1, 2] = "Age";

                excelSheet.Cells[2, 1] = "Tony";

                excelSheet.Cells[2, 2] = 25;

                excelSheet.Cells[3, 1] = "Robert";

                excelSheet.Cells[3, 2] = 34;

                excelSheet.Cells[4, 1] = "Harry";

                excelSheet.Cells[4, 2] = 18;

                excelSheet.Cells[5, 1] = "Tom";

                excelSheet.Cells[5, 2] = 21;

                excelWorkbook.SaveAs("d:\\StudentsDetails.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

                excelWorkbook.Close(true, misValue, misValue);

                xlApp.Quit();

            }

        }

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////   

As the above code executes, excel sheet named StudentsDeatails get created in d drive having data as shown below:

Now you can manipulate the above code to write down your data into excel sheet.

Hope it was helpful.