How to export data to excel?

Recently, I have worked on export data to excel and import data from excel. I have face many problems while working on this. So I have searched for different ways for doing export and import from excel. I would like to share with you guys different export methods so that you can easily try it.

Way 1: using Open XML when working with Office 2007 or later version

protected void ExportToExcel(DataTable dt, List<string> columnNames, string fileName)
    {
        MemoryStream stream = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.Create();
        DocumentFormat.OpenXml.Packaging.SpreadsheetDocument doc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(stream, true);
        DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
        DocumentFormat.OpenXml.Extensions.WorksheetWriter writer = new DocumentFormat.OpenXml.Extensions.WorksheetWriter(doc, worksheetPart);

        //writer.PasteText("B2", "Hello World");
        DataTable dtc = new DataTable();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dtc.Columns.Add(new DataColumn(columnNames[i]));
        }
        DataRow dr = dt.NewRow();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dr[i] = columnNames[i];
        }
        dt.Rows.InsertAt(dr, 0);
        writer.InsertDataTable(dt, "A1", columnNames);
        //writer.PasteDataTable(dtc, "A1");
        //Save to the memory stream
        DocumentFormat.OpenXml.Extensions.SpreadsheetWriter.Save(doc);

        //Write to response stream
        this.Response.Clear();
        this.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", fileName));
        this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.WriteTo(this.Response.OutputStream);
        this.Response.End();
    }

Way 2: working with office automation

protected void Exporttoexcel2(DataTable dt, string fileName)
    {
        off.Application excel = new off.Application();
        off.Workbook workbook = excel.Application.Workbooks.Add(true);
        int iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            excel.Cells[1, iCol] = c.ColumnName;
        }

        int iRow = 0;
        foreach (DataRow r in dt.Rows)
        {
            iRow++;
            iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
            }
        }

        string path = string.Empty;
        path = Server.MapPath("~/TempFiles/");
        path = path + fileName;

        object missing = System.Reflection.Missing.Value;
        workbook.SaveAs(path, off.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, off.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        excel.Visible = true;
        off.Worksheet worksheet = (off.Worksheet)excel.ActiveSheet;
        ((off._Worksheet)worksheet).Activate();
        ((off._Application)excel).Quit();

        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + "");
        this.Response.TransmitFile(path);
        this.Response.End();
    }

Way 3: working with ExcelPackage library

protected void Exporttoexcel3(DataTable dt, string fileName)
    {
        FileInfo newFile = new FileInfo(fileName);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile))
        {
            xlPackage.DebugMode = true;
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(fileName);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = dt.Columns[i].ColumnName;
            }

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    worksheet.Cell(r + 2, i + 1).Value = Convert.ToString(dt.Rows[r][i]);
                }
            }
            xlPackage.Save();
        }
    }