[RESOLVED]Issue with exporting jquery grid data to excel

hi,

 i am using filter tool bar on export to excel

$("#list").jqGrid(‘filterToolbar’,
{ stringResult:
true, searchOnEnter:
true, defaultSearch:
"cn" })
.navButtonAdd(‘#pager’,
{

caption:"Export to Excel",

buttonicon:"ui-icon-disk",

onClickButton:function()
{
ExportDataToExcel("#list");},

position:"last"});

 

function
ExportDataToExcel(tableCtrl) {

ExportJQGridDataToExcel(tableCtrl,

"sample.xlsx");

}

 

This is my ExportJQGridDataToExcel function.

 

function

ExportJQGridDataToExcel(tableCtrl, excelFilename) {

var allJQGridData = $(tableCtrl).jqGrid(‘getRowData’);

var jqgridRowIDs = $(tableCtrl).getDataIDs();

var headerData = $(tableCtrl).getRowData(jqgridRowIDs[0]);

 

when i use this

var allJQGridData = $(tableCtrl).jqGrid(‘getGridParam’, ‘data’);  it will give all the records in the grid

when i use this

var allJQGridData = $(tableCtrl).jqGrid(‘getRowData’);
it will give the filtered records and only first page is capturing.

 

Can any one plz help me out how to solve this issue,How can i get all the records in the grid as well as when i filter i need to get selected records.

Hi mcfarlandparkway,

For your issue is related with jqGrid which belongs to the third party control ,I suggest that you try to post your issue to their forum for a professional solution:

http://www.trirand.com/blog/?page_id=393

Best Regards,

Kevin Shen.

No one is responding to that site,if any one knows how to approach plz help me…

Hey,

I’m getting the same, exactly issue!! I’m banging my head against the wall all day!

Did you find a solution yet?

Thank you in advance

Hi diruga,

                   yes,I found a solution Plz check it..

In the jqGridExportToExcel.js file add the searchdata (postdata filter) in post and redirect.

postAndRedirect("/–/ExportGridToExcel.ashx?filename="
+ excelFilename, { excelData: excelData, searchData: $(tableCtrl).jqGrid(
‘getGridParam’,
‘postData’).filters});

 

And in ExportGridToExcel.ashx file

using ExportToExcel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Text.RegularExpressions;

namespace ExportToExcel
{
    
    public class ExportGridToExcel : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            string tabData = context.Request["excelData"]; 

            DataTable dt = ConvertCsvData(tabData);
            if (dt == null)
            {
                //  Add some error-catching here...
                return;
            }

            string excelFilename = context.Request["filename"];  

            if (File.Exists(excelFilename))
                File.Delete(excelFilename);

            // Search data to retreive filter records

            string seachFilter = getfilterdata(context.Request["searchData"]);
            if (!string.IsNullOrEmpty(seachFilter))
            {                            
                DataTable dtFormated = dt.Clone();
                foreach (DataRow dr in dt.Rows)
                {
//In the dr["Name']  you can use your column names --basically in my grid i am filtering like only first 2 columns.where i kept dr["---"] you can add another column

                    if (dr != null && dr["Name"] != null && dr["Name"].ToString().ToLower().Contains(seachFilter.ToLower()) || dr["---"].ToString().ToLower().Contains(seachFilter.ToLower()))
                    {
                        dtFormated.ImportRow(dr);
                    }
                }
                CreateExcelFile.CreateExcelDocument(dtFormated, excelFilename, context.Response);
            }
            else
            {
                CreateExcelFile.CreateExcelDocument(dt, excelFilename, context.Response);
            }
        }

        // Search data to retreive filter records
        private string getfilterdata(string filter)
        {
            string retval = string.Empty;
            if (!string.IsNullOrEmpty(filter))
            {
                string[] retval2 = filter.Split(new string[] { "data" }, StringSplitOptions.RemoveEmptyEntries);
                if (retval2 != null && retval2.Count() > 1 && !string.IsNullOrEmpty(retval2[1]))
                    retval = RemoveSpecialCharacters(retval2[1]);
            }
            return retval;
        }

        //Added Search data to retreive filter records
        public static string RemoveSpecialCharacters(string str)
        {
            return Regex.Replace(str, "[^a-zA-Z0-9_.]+", "", RegexOptions.Compiled);
        }

        private DataTable ConvertCsvData(string CSVdata)
        {
            //  Convert a tab-separated set of data into a DataTable, ready for our C# CreateExcelFile libraries
            //  to turn into an Excel file.
            //
            DataTable dt = new DataTable();
            try
            {
                System.Diagnostics.Trace.WriteLine(CSVdata);

                string[] Lines = CSVdata.Split(new char[] { 'r', 'n' });
                if (Lines == null)
                    return dt;
                if (Lines.GetLength(0) == 0)
                    return dt;

                string[] HeaderText = Lines[0].Split('t');

                int numOfColumns = HeaderText.Count();

                
                foreach (string header in HeaderText)
                    dt.Columns.Add(header, typeof(string));

                DataRow Row;
                for (int i = 1; i < Lines.GetLength(0); i++)
                {
                    string[] Fields = Lines[i].Split('t');
                    if (Fields.GetLength(0) == numOfColumns)
                    {
                        Row = dt.NewRow();
                        for (int f = 0; f < numOfColumns; f++)
                            Row[f] = Fields[f];
                        dt.Rows.Add(Row);
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("An exception occurred: " + ex.Message);
                return null;
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

 

Leave a Reply