Not able to get data in downloaded excel due to custom columns in grid.

Title:

Hi I am trying to implement GridExportToExcelDemo. But I have a different scenario, I am getting the columns and rows from the database. The headers in DB result are used as columns and rows as rows.

    public ActionResult GetItems(GridParams g, string search)
    {
       return Json(BuildGridModel(g).ToDto(), JsonRequestBehavior.AllowGet);
    }

    private GridModel<GridArrayRow> BuildGridModel(GridParams g)
    {
       ReportParam param = new ReportParam();
       ReportServiceReference.Report reportContract = null;
       ServiceProcessComponent.Use<ReportServiceClient>(
           reportService =>
           {
              reportContract = reportService.GetReport(param);
           });
           var columns = new List<Column>();
           if (reportContract != null)
           {
             for (int i = 1; i < reportContract.ReportTable.Columns.Count; i++)
             {
               columns.Add(new Column
               {
                 Groupable = true,
                 Id = reportContract.ReportTable.Columns[i].ToString(),
                 Bind = reportContract.ReportTable.Columns[i].ToString(),
                 Header = reportContract.ReportTable.Columns[i].ToString(),
                 ClientFormatFunc = "getVal(" + i + ")"
               });
              }
           }
            var items = new List<GridArrayRow>();
            if (reportContract != null)
            {
                for (int i = 0; i < reportContract.ReportTable.Rows.Count; i++)
                {
                    items.Add(new GridArrayRow
                    {
                        Id = reportContract.ReportTable.Rows[i][0].ToString(),
                        Values = reportContract.ReportTable.Rows[i].ItemArray.Select(x => x.ToString()).ToArray()
                    });
                }
            }
            g.Columns = columns.ToArray();
            return new GridModelBuilder<GridArrayRow>(items.AsQueryable(), g).BuildModel();
        }

The Data is shown in grid correctly but when I click on download button, the excel is downloaded without any data only the headers.
Below is the method to export to excel:-

    [HttpPost]
    public ActionResult ExportGridToExcel(GridParams g, GridExpParams expParams, bool? allPages)
    {
       if (allPages.HasValue && allPages.Value)
       {
                g.Paging = false;
       }
       var gridModel = BuildGridModel(g);
       var workbook = new GridExcelBuilder(getExpColumns(g))
       {
          // adding ExpParams so the hidden columns won't get exported
           ExpParams = expParams
       }.Build(gridModel);
       using (var stream = new MemoryStream())
       {
                workbook.Write(stream);
                stream.Close();
                return File(stream.ToArray(), "application/vnd.ms-excel", "Report_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");
        }
    }

SalesARM

asked at 15 Jan 2020

`GridExcelBuilder` is getting the values using column ClientFormat or Bind, you're using ClientFormatFunc `getVal` which is js function on the client, obviously the server doesn't has it, basically our demo doesn't cover the scenario when you use `ClientFormatFunc`, we'll try to add it in the future, you should debug our demo to learn how to `GridExcelBuilder` works, and after you'll debug your app, this will help you solve the problem.

at 15 Jan 2020 Omu
try this updated demo: https://files.fm/u/gjnk75er it handles `ClientFormatFunc` , in your app you'll have rewrite the `getVal` in the controller

at 15 Jan 2020 Omu
I am not able to run it as I am having vs 2015 and demo is written in vs 2019. Can you paste me the code of getVal if only that change is required.

at 16 Jan 2020 SalesARM
you can open each file with notepad, or install VS2019, all our demos are now on VS2019

at 17 Jan 2020 Omu
Thing is I am trying to club two features of Grid, Get data from array source(I am getting from data table) and then able to download that data in excel. I have made the changes for ClientFormatFunc as given in new demo, but at the below line I'm still getting null value:- ClientFormatFunc = (rowModel, property) => { var value = rowModel.GetType().GetProperty(property)?.GetValue(rowModel).ToString(); return value; } I have a contract in which I have datatable property public DataTable ReportTable { get; set; } In this I get the data in table format from DB.

at 18 Jan 2020 SalesARM
In my BuildGridModel I create columns and rows separately from the DataTable Property as below:- var columns = new List<Column>(); for (int i = 0; i < reportContract.ReportTable.Columns.Count; i++) { columns.Add(new Column { Id = reportContract.ReportTable.Columns[i].ToString(), Bind = reportContract.ReportTable.Columns[i].ToString(), Header = reportContract.ReportTable.Columns[i].ToString(), ClientFormatFunc = "getVal(" + i + ")" }); }

at 18 Jan 2020 SalesARM
And rows like below :- var items = new List<GridArrayRow>(); for (int i = 0; i < reportContract.ReportTable.Rows.Count; i++) { items.Add(new GridArrayRow { Id = reportContract.ReportTable.Rows[i][0].ToString(), Values = reportContract.ReportTable.Rows[i].ItemArray.Select(x => x.ToString()).ToArray() }); } g.Columns = columns.ToArray(); return new GridModelBuilder<GridArrayRow>(items.AsQueryable(), g).BuildModel(); This thing is working fine and data is correctly displayed on Grid.

at 18 Jan 2020 SalesARM
Problem occurs when I try to download in Excel. Below is my ExcelDownload method code:- public ActionResult ExportGridToExcel(GridParams g, GridExpParams expParams, bool? allPages) { if (allP....) { g.Pagi... } var gridModel = BuildGridModel(g, FilterValue); var workbook = new GridExcelBuilder(getExpColumns(g)) { ExpParams = expParams }.Build(gridModel); using (var stream = new MemoryStream()) { .. return File(stream.ToArray(), "application/vnd.ms-excel", "xyz.xls"); } }

at 18 Jan 2020 SalesARM
private ExpColumn[] getExpColumns(GridParams g) { var columns = g.Columns.Select(r => r.Header).ToArray(); var expColumns = new List<ExpColumn>(); columns.ToList().ForEach(x => { expColumns.Add(new ExpColumn() { Header = x.ToString(), Name = x.ToString(), ClientFormatFunc = (rowModel, property) => { var value = rowModel.GetType().GetProperty(property)?.GetValue(rowModel).ToString(); return value; } }); }); return expColumns.ToArray(); }

at 18 Jan 2020 SalesARM
you need to open the demo we provided and pay special attention to the column with ClientFormatFunc on the client and server side

at 18 Jan 2020 Omu
what does this line suggest :- in your app you'll have rewrite the getVal in the controller Right now my getVal method is only at Jquery side

at 19 Jan 2020 SalesARM
it means you'll have to write in on the server as well, like in our demo

at 19 Jan 2020 Omu
As my columns are coming from DB and are dynamic, grouping and sorting is not working on them. Do I need to have strong/model binding with my columns for grouping and sorting to work ?

at 19 Jan 2020 SalesARM
Column.Bind needs to be specified, after in the controller you can read these bind values from g.SortNames g.SortDirections and do the sorting yourself like here: https://demo.aspnetawesome.com/GridDemo/CustomQuerying

at 19 Jan 2020 Omu

Answers

please Sign In to leave an answer