Start Building Professional
Web Apps Today

Excel Export ASP.NET (using C#)

Actually this is not a question. I just want to help other asp.net users.
This solution tested with dhtmlxgrid ver 1.6 professional. (IE and FF)

Make sure you've backup your orginal files before modification.




Step 1
##############################################################################

Firstly, You have to edit dhtmlxgrid_nxml.js file for remove extra \n lines from data.

Replace----------------------------------------------##########################

dhtmlXGridObject.prototype.serializeToCSV = function(labels){

this.editStop()
if (labels)
    this._agetm="getTitle";
else if (this._mathSerialization)
this._agetm="getMathValue";
else this._agetm="getValue";

var out=[];
if (this._csvHdr){
    var a=[]; var b=this.hdr.rows[1].cells;
    for (var i=0; i<b.length; i++)
            if ((!this._srClmn)||(this._srClmn[i]))
            {
             var cellContent = _isIE?b[i].innerText:b[i].textContent;
             cellContent = replace(cellContent,"\n"," ");
                a.push(cellContent);
         }
    out.push(a.join(this.csv.cell));
}

//rows collection
var i=0;
var leni=this.rowsBuffer.length;

for(i; i<leni; i++){
        var temp=this._serializeRowToCVS(null,i);
        temp = replace(temp,"\n"," ");
        if (temp!="") out.push(temp);
}

return out.join(this.csv.row);
}


with----------------------------------------------##########################



function replace(string,text,by) {
var strLength = string.length, txtLength = text.length;
if ((strLength == 0) || (txtLength == 0)) return string;
var i = string.indexOf(text);
if ((!i) && (text != string.substring(0,txtLength))) return string;
if (i == -1) return string;
var newstr = string.substring(0,i) + by;
if (i+txtLength < strLength)
newstr += replace(string.substring(i+txtLength,strLength),text,by);
return newstr;
}

dhtmlXGridObject.prototype.serializeToCSV = function(labels){

this.editStop()
if (labels)
    this._agetm="getTitle";
else if (this._mathSerialization)
this._agetm="getMathValue";
else this._agetm="getValue";

var out=[];
if (this._csvHdr){
    var a=[]; var b=this.hdr.rows[1].cells;
    for (var i=0; i<b.length; i++)
            if ((!this._srClmn)||(this._srClmn[i]))
            {
             var cellContent = _isIE?b[i].innerText:b[i].textContent;
             cellContent = replace(cellContent,"\n"," ");
                a.push(cellContent);
         }
    out.push(a.join(this.csv.cell));
}

//rows collection
var i=0;
var leni=this.rowsBuffer.length;

for(i; i<leni; i++){
        var temp=this._serializeRowToCVS(null,i);
        temp = replace(temp,"\n"," ");
        if (temp!="") out.push(temp);
}

return out.join(this.csv.row);
}


Step 2
##############################################################################


Add this function to your master js file or add it to your page.

function ExportToExcel(grid){
grid.setCSVDelimiter("|");
grid.enableCSVHeader(true);
return grid.serializeToCSV(true);
}



Step 3
##############################################################################



Add this c# code to your appcode.
It's silly but works :)

public static void ExportToExcel(string excelData, string fileName)
{
DataTable dt = new DataTable();
StringReader tr = new StringReader(excelData);
string line;
while ((line = tr.ReadLine()) != null)
{
string[] items = line.Split('|');
if (dt.Columns.Count == 0)
{
// Create the data columns for the data table based on the number of items
// on the first line of the file
for (int i = 0; i < items.Length; i++)
dt.Columns.Add(new DataColumn("Column" + i, typeof(string)));
}
dt.Rows.Add(items);
}
System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.ShowHeader = false;
grid.DataSource = dt;
grid.DataMember = dt.TableName;
grid.DataBind();


HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
stringWrite.Write("<html><head><meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\"></head><body>");
grid.RenderControl(htmlWrite);
stringWrite.Write("</body></html>");

HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}

Step 4
##############################################################################

Add hidden to your page

<input type="hidden" value="" runat="server" id="hdnExcel" />


Step 5
##############################################################################

this is your server side button html
<asp:Button CssClass="btnExcel" ID="btnExcel" runat="server" Text="Export to Excel" OnClientClick="ToExcel();return true;" UseSubmitBehavior="true" OnClick="btnExcel_Click"></asp:Button>

code:

protected void btnExcel_Click(object sender, EventArgs e)
{
string excelData = hdnExcel.Value;
MyHelperFile.ExportToExcel(excelData, Page.Title.Trim(' '));
}



Finished
##############################################################################

That's it. It's look like complicated but it's very easy.


Mehmet Kut - mehmetkut@gmail.com

The same effect can be achieved without code modification.
You can skip step 1, and modify step 2 as 

function ExportToExcel(grid){ 
       grid.csv.cell="|"; 
       grid.csv.row="\f";
       grid.enableCSVHeader(true); 
       var csv=grid.serializeToCSV(true);
       return csv.replace(/\n/g,"").replace(/\f/g,"\n");  //replace new lines in data, and set new lines as row separators
}