Generate and write to excel file in C#

This article provides a very simple method to generate excel and write data to spreed sheet.

Below is a sample code snippet showing a method to generate a excel and write data to spreed sheet.I have used Microsoft.Office.Interop.Excel to do it using C#

public void GenerateExcel()

        {

            try

            {

                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

                if (app == null)

                {

                    MessageBox.Show("EXCEL could not be started. Check that your office installation.");

                    return;

                }

                // creating new WorkBook within Excel application

                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

                // creating new Excelsheet in workbook

                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                // see the excel sheet behind the program

                // get the reference of first sheet. By default its name is Sheet1.

                // store its reference to worksheet

                worksheet = workbook.Sheets["Sheet1"];

                worksheet = workbook.ActiveSheet;

                if (worksheet == null)

                {

                    MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");

                }

                // changing the name of active sheet

                worksheet.Name = "Test sheet";

                DataTable dtTest = new DataTable();

                // Add column headings...

                int iCol = 0;

                foreach (DataColumn c in dtTest.Columns)

                {

                    iCol++;

                    worksheet.Cells[1, iCol] = c.ColumnName;

                    // Adding Borders

                    worksheet.Cells[1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

                    //adding Interior color

                    worksheet.Cells[1, iCol].Interior.Color = Color.Yellow;

                }

                // for each row of data...

                int iRow = 0;

                foreach (DataRow r in dtTest.Rows)

                {

                    iRow++;

                    // add each row's cell data...

                    iCol = 0;

                    foreach (DataColumn c in dtTest.Columns)

                    {

                        iCol++;

                        worksheet.Cells[iRow + 1, iCol] = r[c.ColumnName];

                        // Adding Borders

                        worksheet.Cells[iRow + 1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

                        //Change the number format

                        worksheet.Cells[iRow + 1, iCol].NumberFormat = "0.00";

                    }

                }

                app.Visible = true;

                MessageBox.Show("Completed!", "Export");

            }

            catch (Exception ex )

            {

                

                MessageBox.Show(ex.Message, "Error");

            }