Server side Export to Excel

[spiritParticipant]

Hello,

I am trying to have a button at the statusbar of a grid that will make an ajax call to the server which will construct an excel file with the data of the grid. If the user has applied any filters i want the filters to be in the ajax call in order to make the excel with the filtered data.

I know how to make an excel on the server. What i dont know is how to take all filters and the parameter of the grid and put them in the ajax call.

Can you please give me any suggestions?

thank you!!!!

[Peter StoevKeymaster]

Hi spirit,

The built-in Export to Excel functionality already supports that. In exports all rows loaded in the Grid. If the Grid is filtered, it exports the filtered rows.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[spiritParticipant]

Hi,

I understand that and i already use it but in this case the grid is paginated. the pages, filtering and sorting is handled by the server. I am trying to export a grid with all the filtered rows not just the page the the user is seeing.

Is that possible?

[Peter StoevKeymaster]

There’s no such built-in functionality.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[onefortypoint6Participant]

spirit, I had the same need as well – here is how I handled it (because we are using ASP.NET as a web service and no PHP).

add a hidden field to your page to save the filters/etc.

then, when your data grid building the query, save the parameters to the hidden field
$(‘#hidGridFormatedData’).val(formatedData);

then, when the user clicks the export button, use your ajax call to pass the hidden parameters back to the server (which will persist your filters, etc.) – server-side you can ignore the paging, etc.

For us, we then pass back a message to the user, which they use the link to download their file.

Here is the server side code if it helps.

[WebMethod]
        [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
        public string ExportData()
        {
            var queryString = this.Context.Request.QueryString;
            var query = "select * FROM " + this.BuildQuery(queryString);
           
            string strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            MySqlConnection con = new MySqlConnection(strConnString);
            MySqlCommand cmd = new MySqlCommand(query, con);
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            StringBuilder sb = new StringBuilder();
            IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName);
            sb.AppendLine(string.Join(",", columnNames));
            foreach (DataRow row in dt.Rows)
            {
                IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
                sb.AppendLine(string.Join(",", fields));
            }
            string strFilename = Guid.NewGuid().ToString() + ".csv";
            string strUploadPath = Server.MapPath("userexports").ToString();
            File.WriteAllText(strUploadPath + "\\" + strFilename, sb.ToString());
            return strFilename;
        }

[cpcodeMember]

To onefortypoint6 or to the jqWidgets team:

is there a way to intercept the queryString that is being passed to the server when I call updatebounddata – or better yet, whenever a data request is sent to the server? I would like to use onefortypoint6’s solution for this problem, so I need a way to remember the queryString for the last request sent. I only need filter and sort info, but the whole Request.queryString is fine as well. This would be a big help, as it would be in the same format as the string that my server code (.Net) already receives and knows how to parse.

Is this possible?
Thank you

[RedantJParticipant]

I found an way to accomplish the same thing using ASP Classic and VBScript:

Code for export.asp

<!--#include virtual="connection_string.asp"-->

<%
Response.ContentType = "application/vnd.ms-excel"
Response.addHeader "content-disposition", "attachment; filename=FooBar.xls"

strWHERE = Request.QueryString("queryString")

strSQL = "SELECT * FROM fooGrid " & strWHERE
Set rs = conn.Execute(strSQL)
%>
<HTML>
<BODY>
<TABLE BORDER=1 BORDERCOLOR="#CCCCCC">
<TR>
<% For each fField in rs.Fields %>
<TD style="background-color:black; color: white;"><%=fField.Name%></TD>
<% Next %>
</TR>
<% do while not rs.eof %>
<TR>
<% For i = 0 to 9 ' Assume there are 10 columns in the table  %>
<TD><%=rs(i)%></TD>
<% Next %>
</TR>
<% rs.movenext
loop %>
</TABLE>
</BODY>
</HTML>

[Author]

Posts