Export Asp.Net Gridview to Excel (OpenXml)

Here is an example of exporting ASP.NET GridView data into an Excel workbook in OpenXml format. For this job you need to use ClosedXml.dll. This is the method which gets the gridview row and a reference to an excel worksheet and adds the row to that worksheet.

private void AddCellsFromGridViewRow(GridViewRow row, IXLWorksheet wsh, int rowNum)
{
    IXLCell currentCell;
    int cellNumber = 0;
    int colNum = 1;
    int columnSpan;
    foreach (TableCell cell in row.Cells)
    {
        currentCell = wsh.Cell(rowNum, colNum);
        // Use this condition if you have template cells with textboxes.
        if (string.IsNullOrEmpty(cell.Text) && cell.HasControls())
        {
            TextBox tb = cell.Controls.OfType<TextBox>().ElementAt(0);
            currentCell.SetValue(tb.Text);
        }
        else
        {
            currentCell.SetValue(row.Cells[cellNumber].Text);
        }
        columnSpan = cell.ColumnSpan;
        if (columnSpan > 1)
        {
            wsh.Range(wsh.Cell(rowNum, colNum), wsh.Cell(rowNum, colNum + columnSpan - 1)).Merge();
        }
        cellNumber++;
        colNum += (columnSpan == 0) ? 1 : columnSpan;
    }
}

This is the method that creates the workbook, calls the previous one and downloads the file.

private void ExportToExcelAndDownload()
{
    XLWorkbook wb = new XLWorkbook();
    IXLWorksheet wsh = wb.Worksheets.Add("sheet1");

    int rowNum = 1;
    
    // Do this if you need to export only visible rows.
    List<GridViewRow> visibleRows = (from x in grd.Rows.OfType<GridViewRow>()
                                     where x.Visible
                                     select x).ToList();
    foreach (GridViewRow row in visibleRows)
    {
        AddCellsFromGridViewRow(row, wsh, rowNum);
        rowNum++;
    }
    wsh.ColumnsUsed().AdjustToContents();

    //Create file in memorystream and download.
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=filename.xlsx");

    byte[] buffer;
    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
    {
        wb.SaveAs(ms);
        ms.Flush();
        buffer = ms.ToArray();
    }
    Response.BinaryWrite(buffer);
    Response.End();
}