We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

grid sumary with dynamic data

Hi,

I fill a grid using a SQLDataSource that calls a store procedure.

This store procedure contains a dynamic pivot, so, I don't know the column names.

The store procedure fill the grid correctly. I like add a summary row, but I don't know the columns names for summarize.

How can I do it?

Also, I like export to excel, but I get an error:

In my code:

ExcelExport exp = new ExcelExport();
string titulo = GetTitulo().Replace("<bold>", "").Replace("</bold>", "");
IEnumerable datos = (IEnumerable)SqlDSResumen.Select(DataSourceSelectArguments.Empty);

IWorkbook workbook = exp.Export(gridDatos.Model, datos, "Export.xlsx", ExcelVersion.Excel2013, false, true, "default-theme", true);

Utils.AñadirTituloExcel(workbook, 0, titulo);
workbook.SaveAs("Informes.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2013);

But "datos" is not enumerable.
          

Thanks!

15 Replies

PK Prasanna Kumar Viswanathan Syncfusion Team December 24, 2015 09:31 AM UTC

Hi Manolo,

Thanks for using Syncfusion controls.

Query : “How to summarize the auto generated columns?

Your requirement can be achieved by the dataBound event of ejGrid.  This event triggers during initial rendering of Grid. Also we can get the column names in gridInstance object. Using that we add the summary rows and enable the summary through setModel.

Please find the code example and sample:


<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">


    <ej:Grid ID="FlatGrid" runat="server" AllowSorting="True" DataSourceID="SqlData" AllowGrouping="true" OnServerWordExporting="FlatGrid_ServerWordExporting" OnServerPdfExporting="FlatGrid_ServerPdfExporting" OnServerExcelExporting="FlatGrid_ServerExcelExporting" AllowPaging="True" AllowFiltering="True" >

        <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport,wordExport,pdfExport"></ToolbarSettings>

        <FilterSettings FilterType="Excel"></FilterSettings>

        <ClientSideEvents DataBound="databound" />

        </ej:Grid>

    <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"

            SelectCommand="SELECT * FROM [Territories]"></asp:SqlDataSource>

  <script type="text/javascript">

    function databound(args) {

        this.model.summaryRows.push({ title: "Sum", summaryColumns: [{ summaryType: ej.Grid.SummaryType.Sum, displayColumn: this.model.columns[2].headerText, dataMember: this.model.columns[2].headerText, format: "{0:C2}", prefix: "Sum =" }] });

        $("#MainContent_FlatGrid").ejGrid("model.showSummary", true);     

    }

</script>
</asp:Content>


Sample: http://www.syncfusion.com/downloads/support/forum/121522/ze/WebApplication21-1223074639

Query : “Export to excel

We have tried with our sample and we did not face any issues while exporting to the Excel. While exporting, we suggest you to convert the datasource to list of data.

Please find the code example :


protected void FlatGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

        {

            ExcelExport exp = new ExcelExport();

            DataView data = (DataView)SqlData.Select(new DataSourceSelectArguments());

            IList data1 = data;

            DataTable dt = data.Table;

            var emp = (from DataRow row in dt.Rows


                   select new Order

                   {

                       TerritoryID = Convert.ToInt32(row["TerritoryID"]),

                       TerritoryDescription = row["TerritoryDescription"].ToString(),

                       RegionID = Convert.ToInt32(row["RegionID"]),

                   }).ToList();

            IWorkbook workbook = exp.Export(FlatGrid.Model, (IEnumerable)emp, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime", true);

            exp.Export(FlatGrid.Model, (IEnumerable)emp, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime", false, workbook, MultipleExportType.AppendToSheet, "Flat Grid");
        }


Please provide the following details us to find out the root cause of the issue.

1.       Share the code example.

2.       Screenshot of an issue.


3.       Essential Studio version details.


Please try the attached sample and still if you face the issue, please reproduce the issue in the sample and send back

Regards,
Prasanna Kumar N.S.V



MA Manolo December 28, 2015 08:49 AM UTC

Hi,

I attach an example that execute an store procedure.

This sp returns a static columns (Area, Provincia) and dynamic columns (WeekX).

I cant set the summarize row on weeks columns and get the exportation



Attachment: SampleButton_a1ae6f65.zip


PK Prasanna Kumar Viswanathan Syncfusion Team December 29, 2015 06:10 AM UTC

Hi Manolo,

By analyzing your code example, missing of $ symbol while using jQuery selectors for getting Grid object to enable the summary in the databound event is the root cause for the issue. So, we suggest you to use the $ symbol or jQuery while using jQuery selector to properly get the Grid object.

Please find the code example, screenshot and sample:


<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">

    <ej:Grid runat="server" ID="gridDatos"

            AllowPaging="true" AllowGrouping="true" AllowScrolling="true" AllowFiltering="true" AllowTextWrap="true" AllowResizing="true" AllowResizeToFit="true" 

            OnServerExcelExporting="gridDatos_ServerExcelExporting"

            DataSourceID="SqlDSResumen">           

            <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport"></ToolbarSettings>

            <GroupSettings GroupedColumns="Area" ShowDropArea="true" />

            <FilterSettings FilterType="Excel"></FilterSettings>

            <ClientSideEvents DataBound="databound" />


        </ej:Grid>


---------------------------


     <script type="text/javascript">


    function databound(args) {


        this.model.summaryRows.push({ title: "Sum", summaryColumns: [{ summaryType: ej.Grid.SummaryType.Sum, displayColumn: this.model.columns[2].headerText, dataMember: this.model.columns[2].headerText, format: "{0:C2}", prefix: "Sum =" }] });


        $("#" + args.model.clientId).ejGrid("model.showSummary", true);


    }


</script>


Screenshot :



Sample: http://www.syncfusion.com/downloads/support/forum/121522/ze/SampleButton-1483815088

Regards,
Prasanna Kumar N.S.V



MA Manolo December 29, 2015 08:40 AM UTC

Ups! ok!

But.... what about the export process?? It doesn't work


PK Prasanna Kumar Viswanathan Syncfusion Team December 30, 2015 10:51 AM UTC

Hi Manolo,

For exporting, we have already suggested you to convert the datasource to list of data.


Please find the code example, screenshot and modified sample:


protected void gridDatos_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

        {

            ExcelExport exp = new ExcelExport();

            DataView datos = (DataView)SqlDSResumen.Select(DataSourceSelectArguments.Empty);

            DataTable dt = datos.Table;

            var emp = (from DataRow row in dt.Rows


                       select new Order

                       {

                           Area = row["Area"].ToString(),

                           Provincia = row["Provincia"].ToString(),

                           Week10 = Convert.ToInt32(row["Week10"]),

                           Week11 = Convert.ToInt32(row["Week11"]),

                           Week13 = Convert.ToInt32(row["Week13"]),

                       }).ToList();

           exp.Export(gridDatos.Model, (IEnumerable)emp, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime");        }

    }

}


public class Order

{

    public string Area { get; set; }

    public string Provincia { get; set; }

    public int Week10 { get; set; }

    public int Week11 { get; set; }

    public int Week13 { get; set; }
}


Screenshot:



Sample: http://www.syncfusion.com/downloads/support/forum/121522/ze/SampleButton_(2)257358207

Regards,
Prasanna Kumar N.S.V



MA Manolo December 30, 2015 11:00 AM UTC

My problem is that de store procedure is a dynamic pivot, so I don't know what "weeks" will be.

So, I've 2 possibilities:

- Make a class with all posibles fields of dynamic pivot, and when I export, remove columns that I don't need
- Try export the store procedure results without a typed class.

I prefeer the second option, but I don't known how do it.

Thanks


MA Manolo December 30, 2015 03:48 PM UTC

Other question.

How can I avoid this message if the columns are autogenerated and the datasource is empy?




PK Prasanna Kumar Viswanathan Syncfusion Team December 31, 2015 07:30 AM UTC

Hi Manolo,

         Queries
                    Response

“DataTable support for exporting feature”

While using sqlDataSource the Grid that has DataTable as its datasource.

Currently we do not have Data table support for exporting feature. For that we have already logged the feature request “Need to provide DataTable support for Exporting feature 

A support incident has been created under your account to resolve the issue. Please log on to our support website to check for further updates. 


https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents



“DataSource must not be empty at initial load since columns are generated from database”


It’s mandatory to provide either datasource or initializing column while rendering Grid. We cannot render the Grid without anyone of these two. If we do not provide columns then the column will be generated using the datasource. So, we need to provide anyone to avoid the alert box.



Regards,
Prasanna Kumar N.S.V



MA Manolo December 31, 2015 07:40 AM UTC

ok!

Thank you very much


PK Prasanna Kumar Viswanathan Syncfusion Team January 4, 2016 05:30 AM UTC

Hi Manolo,

Please get back to us for further queries.

Regards,
Prasanna Kumar N.S.V


EK Edwin Kurniawan September 14, 2016 04:28 AM UTC

Need to provide DataTable support for Exporting feature
Is this feature has been worked out till now?


JK Jayaprakash Kamaraj Syncfusion Team September 15, 2016 11:43 AM UTC

Hi Manolo, 


We have provided “DataTable Support for exporting” from v14.1.0.41. So if you are using older version, please upgrade to the new version.   
 
We have created a sample, which can be downloaded from below location. 


Regards, 

Jayaprakash K. 



EK Edwin Kurniawan September 17, 2016 04:02 AM UTC

In Syncfusion v14.1.0.41, i'm using Assembly Syncfusion.EJ.Export, Version=14.1450.0.41
but it seems there is another
Syncfusion.EJ.Export dll with newer version=14.1460.0.41

My question is is there any way to make sure project is using the latest version of dll other than manual checking?


EK Edwin Kurniawan replied to Jayaprakash Kamaraj September 18, 2016 07:59 AM UTC

Hi Manolo, 


We have provided “DataTable Support for exporting” from v14.1.0.41. So if you are using older version, please upgrade to the new version.   
 
We have created a sample, which can be downloaded from below location. 


Regards, 

Jayaprakash K. 


Thank you for the example. It's working great,
but when i'm using custom summary value, it still give me error object reference


PK Prasanna Kumar Viswanathan Syncfusion Team September 19, 2016 04:25 PM UTC

Hi Manolo, 

We checked in our sample with custom summary value and face the below exception when we export to the Grid. 

Find the screenshot: 

 


Please confirm with the above screenshot that you face the same exception in your sample when exporting the grid with custom summary value. 

Query : “Syncfusion.EJ.Export, Version=14.1450.0.41 and Syncfusion.EJ.Export dll with newer version=14.1460.0.41” 

Syncfusion dependent assemblies will be separated according to the project application framework. We have to refer the Syncfusion DLL’s according to the our project application framework. In this 14.1450.0.41 denotes the framework. 

Regards, 
Prasanna Kumar N.S.V 


Loader.
Live Chat Icon For mobile
Up arrow icon