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();
}

Postback problem after file download

There is a strange problem in Sharepoint pages when dynamically creating and downloading files. After downloading a file page controls lose the ability of making postback. The problem occurs because of a variable named _spFormOnSubmitCalled, which must have value "false" to be able to make postback. This variable has been implemented to prevent multiple submits. So, when downloading a file, the page isn't being fully refreshed, so this variable value changes to "true".
The workaround is to manually set this property value to "false"

This answer provides solution for this situation.

The solution is simple. Just register client side onclick event to the button and change the property value by javascript:

<asp:Button ID="btnDownloadFile" runat="server" Text="Download file" 
OnClick="btnDownloadFile_Click"
OnClientClick="javascript:setFormSubmitToFalse()" />

Setting timeout value 3 seconds is important to be sure Sharepoint has already changed the property value and we are changing it back:

function setFormSubmitToFalse() {
    setTimeout(function () { _spFormOnSubmitCalled = false; }, 3000);
    return true;
}

Signing 3rd party dll

If you have a dll library which doesn't have strong name, but you need to put it into Global Assembly Cache (GAC), you must sign it. For this you must first create a key with public and private keys, get MSIL of the dll then compile it by signing with the key. You need to open Visual Studio Command Prompt (Developer Command Prompt for VS), change directory to the directory where the dll is located, then run the following commands:

sn -k key.snk 
by this command a key is being generated

ildasm.exe MyLib.dll /output:MyLib.il
by this command you get the MSIL of the acssembly.

Now you should rename the dll file you used. Then run this command:

ilasm MyLib.dll /dll /output:MyLib.dll /key:key.snk
this command recompiles the library with key.

After this you can put the assembly into GAC by running this command.
gacutil /i MyLib.dll

Date super regex

The regular expression below checks if date is in dd/mm/yyyy format. It also checks if the inserted year has february 29th or not.

(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))

I don't remember where I have got this regex, but it is extremely useful.

You can check the functionality below.

Date


This is the source code of the example above:

Html:

Date <input id="txtDate" placeholder="dd/mm/yyyy" type="text" />
<input onclick="dateCheck()" type="button" value="Check date" /><br />
<span id="spInfo"></span>

Javascript:

function dateCheck()
{

var text=document.getElementById("txtDate").value;
var patt=/(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))/;
var result = patt.test(text);
var resultSpan=document.getElementById("spInfo");
if(result==true)
{
resultSpan.innerHTML="Date is correct";
}
else
{
resultSpan.innerHTML="Date is incorrect";
}
}

Visual Studio 2010 reports asynchronous loading

Reports in asp.net pages load asynchronously, which may cause some difficulties. It may cause infinite page loading if used incorrectly. The issue occurs because report viewer control makes asynchronous postbacks.
Brian Hartman describes the ReportViewer control in his blog and clarifies how to use it.
You can read it here.

Setting BCS data column value programmatically

When you try to set value to a BCS field programmatically, you must consider its differences from standard field. Rompen Patrick describes the technic in his blog.
The brief description is, that while setting value to BCS field, you must set value to a hidden field, too. This hidden field is created probably for showing BCS field data in Sharepoint UI.

private void SetBscFieldValue(string value)
{
    //Set true AllowUnsafeUpdates keeping its original value.
    bool allowUnsafeUpdatesValue = currentWeb.AllowUnsafeUpdates;
    currentWeb.AllowUnsafeUpdates = true;

    //Get a reference to the field.
    SPField field = item.Fields["BCSField"];

    //Get the field's entity name.  
    XmlDocument xmlData = new XmlDocument();
    xmlData.LoadXml(field.SchemaXml);
    string entityName = xmlData.FirstChild.Attributes["RelatedFieldWssStaticName"].Value;

    //Set the entity instance value.
    item[entityName] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { value });

    //Set the field display value.
    item["BCSField"] = value;

    //Save the list item.
    item.Update();

    //Revert allow unsafe updates to its original value.
    currentWeb.AllowUnsafeUpdates = allowUnsafeUpdatesValue;
}

Sharepoint Field Names With Spaces

If the SharePoint column's name contains spaces, we have to do some tricks to work with that column from code. There are two types of field names: internal name - a static one, and display name, which can be changed by users every time they want.