Excel Export ASMX FILE

[Hostilio]

Hi, i'm trying to export ejGrid using an ASMX webservie but trhows error if the datasource has data.

When EJGRid is Empty Excel File:

INCIDENTE_ID

INCIDENTE_TITULO

TIPO_INCIDENTE_ID

TIPO_INCIDENTE

INCIDENTE_DESCRIPCION

INCIDENTE_USUARIO_ID

INCIDENTE_USUARIO_LOGIN

INCIDENTE_USUARIO_NOMBRE

INCIDENTE_ALMACEN

INCIDENTE_DEPARTAMENTO

INCIDENTE_CIERRE

INCIDENTE_CALIFICACION

INCIDENTE_ESTADO

TAREA_INCIDENTE_ID

TAREA_INCIDENTE_ACTIVIDAD

TAREA_INCIDENTE_RECURSO_ID

TAREA_INCIDENTE_LOGIN

TAREA_INCIDENTE_USUARIO_NOMBRE

TAREA_INCIDENTE_HORAS

TAREA_INCIDENTE_MINUTOS

INCIDENTE_ALMACEN_ID

CAT_TIPO_SOLUCION

No records to display

But when EJGrid has data:

System.NullReferenceException: Referencia a objeto no establecida como instancia de un objeto.
   en Syncfusion.EJ.Export.GridExcelExport.ProcessRecordCell(Object row, Column column)
   en Syncfusion.EJ.Export.GridExcelExport.<>c__DisplayClasse.<ProcessRecordRow>b__d(Column column)
   en System.Collections.Generic.List`1.ForEach(Action`1 action)
   en Syncfusion.EJ.Export.GridExcelExport.ProcessRecordRow(Object row)
   en Syncfusion.EJ.Export.GridExcelExport.RenderRecord()
   en Syncfusion.EJ.Export.GridExcelExport.ProcessGridContents()
   en Syncfusion.EJ.Export.GridExcelExport.IterateElements()
   en Syncfusion.EJ.Export.GridExcelExport.ExportHandler()
   en Syncfusion.EJ.Export.GridExcelExport.ExecuteResult(GridProperties GridModel, IEnumerable dataSource)
   en Syncfusion.EJ.Export.GridExcelExport.ExportHelper(GridProperties gridModel, IEnumerable dataSource)
   en Syncfusion.EJ.Export.GridExcelExport.Export(GridProperties gridModel, IEnumerable dataSource, Boolean multipleExport)
   en Syncfusion.EJ.Export.ExcelExport.Export(GridProperties gridmaodel, IEnumerable datasource, String excelname, ExcelVersion excelversion, Boolean isHideColumnIncude, Boolean isTemplateColumnIclude, String theme)
   en Sqsit.Datos.ExcelExport() en c:\Users\Sistemas\Documents\Visual Studio 2012\Projects\incidentesUI\incidentesUI\Datos.cs:línea 802

I already check datasource has all columns and dll version.

My HTML page:
<div id="Grid" data-bind="ejGrid: {
             dataSource: dataSource, columns: columnas, allowFiltering: false, showColumnChooser: true, allowTextWrap: true, allowReording: true, allowResizing: true, commonWidth: 200, allowScrolling: true, scrollSettings: { width: '99%', height: 400 }, enableHeaderHover: false, allowGrouping: true, allowSorting: true, allowPaging: true, pageSettings: { pageSize: 5 }, toolbarSettings: { showToolbar: true, toolbarItems: [ej.Grid.ToolBarItems.ExcelExport, ej.Grid.ToolBarItems.PrintGrid, ej.Grid.ToolBarItems.Search] }, toolbarClick: toolbarClick
             }"></div>
My Javascript:
define(['assets/scripts/web/ej.grid.min'], function () {
    function dashBoardIncidentesViewModel(params) {
        var self = this;
        self.currentAnio = ko.observable();
        self.currentMes = ko.observable();
        self.dataSource = ko.observableArray([]);
        self.columnas = [
            { field: "INCIDENTE_ID", headerText: "INCIDENTE_ID" },
            { field: "INCIDENTE_TITULO", headerText: "INCIDENTE_TITULO" },
            { field: "TIPO_INCIDENTE_ID", headerText: "TIPO_INCIDENTE_ID" },
            { field: "TIPO_INCIDENTE", headerText: "TIPO_INCIDENTE" },
            { field: "INCIDENTE_DESCRIPCION", headerText: "INCIDENTE_DESCRIPCION" },
            { field: "INCIDENTE_USUARIO_ID", headerText: "INCIDENTE_USUARIO_ID" },
            { field: "INCIDENTE_USUARIO_LOGIN", headerText: "INCIDENTE_USUARIO_LOGIN" },
            { field: "INCIDENTE_USUARIO_NOMBRE", headerText: "INCIDENTE_USUARIO_NOMBRE" },
            { field: "INCIDENTE_ALMACEN", headerText: "INCIDENTE_ALMACEN" },
             { field: "INCIDENTE_DEPARTAMENTO", headerText: "INCIDENTE_DEPARTAMENTO" },
            { field: "INCIDENTE_CIERRE", headerText: "INCIDENTE_CIERRE" },
            { field: "INCIDENTE_CALIFICACION", headerText: "INCIDENTE_CALIFICACION" },
            { field: "INCIDENTE_ESTADO", headerText: "INCIDENTE_ESTADO" },
            { field: "TAREA_INCIDENTE_ID", headerText: "TAREA_INCIDENTE_ID" },
            { field: "TAREA_INCIDENTE_ACTIVIDAD", headerText: "TAREA_INCIDENTE_ACTIVIDAD" },
            { field: "TAREA_INCIDENTE_RECURSO_ID", headerText: "TAREA_INCIDENTE_RECURSO_ID" },
            { field: "TAREA_INCIDENTE_LOGIN", headerText: "TAREA_INCIDENTE_LOGIN" },
            { field: "TAREA_INCIDENTE_USUARIO_NOMBRE", headerText: "TAREA_INCIDENTE_USUARIO_NOMBRE" },
            { field: "TAREA_INCIDENTE_HORAS", headerText: "TAREA_INCIDENTE_HORAS" },
            { field: "TAREA_INCIDENTE_MINUTOS", headerText: "TAREA_INCIDENTE_MINUTOS" },
            { field: "INCIDENTE_ALMACEN_ID", headerText: "INCIDENTE_ALMACEN_ID" },
            { field: "CAT_TIPO_SOLUCION", headerText: "CAT_TIPO_SOLUCION" },
        ];
        self.actualizaDatos = function() {
           //asmx load data returns json
        }

        self.toolbarClick = function(args){
            switch (args.itemName) {
                case "Excel Export":
                    this.model["anio"] = self.currentAnio();
                    this.model["mes"] = self.currentMes();
                    this.model["userName"] = store.session.has('Login') ? store.session.get('Login') : '';
                    this.model["pwd"] = store.session.has('Password') ? store.session.get('Password') : '';
                    this.ignoreOnExport.splice(this.ignoreOnExport.indexOf('dataSource'), 1);
                    this.export("/Datos.asmx/ExcelExport");
                    //this.export("/api/GridExport/");
                    args.cancel = true;
                    break;
            }
        }
    }
    return dashBoardIncidentesViewModel;
});

ASMX WEBMETHOD
[WebMethod]
        public void ExcelExport()
        {
            string gridModel = HttpContext.Current.Request.Params["GridModel"];
            JObject o = new JObject();
            JObject parametro = JObject.Parse(gridModel);
            DataTable dt = new DataTable();
            try
            {
                using (OracleConnection conn = new OracleConnection())
                {
                    Autentica(conn, parametro["userName"].ToString(), parametro["pwd"].ToString());
                    dt = TransaccionesIncidentes(int.Parse(parametro["anio"].ToString()), int.Parse(parametro["mes"].ToString()), conn);
                }
            }
            catch (Exception)
            {
            }
            finally
            {
            }
            IList result = dt.Select().ToList();
            GridProperties gridProperty = ConvertGridObject(gridModel);
            ExcelExport exp = new ExcelExport();
            exp.Export(gridProperty, result, "Export.xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron");
        }

[Saravanan Arunachalam]

Hi Hostilio,

Thanks for contacting Syncfusion Support.

We have already discussed this query in the following Knowledge Base document.

https://www.syncfusion.com/kb/3018/null-exception-while-exporting

Regards,

Saravanan A.

[Hostilio]

Hi Hostilio,

Thanks for contacting Syncfusion Support.

We have already discussed this query in the following Knowledge Base document.

https://www.syncfusion.com/kb/3018/null-exception-while-exporting

Regards,

Saravanan A.

I already check that.

The columns are defined via knockoutjs variable columnas and the export is using the same query so all the columns are defined. (Already compared)

Finally I solved the issue but looks that it's a trouble with null values on DataTable.

I changed:

 IList result = dt.Select().ToList();
To:
IList datos = dt.AsEnumerable().Select(row => new
            {
                INCIDENTE_ID = row.Field<decimal?>("INCIDENTE_ID").GetValueOrDefault(),
                INCIDENTE_TITULO = row.Field<string>("INCIDENTE_TITULO") ?? string.Empty,
                TIPO_INCIDENTE_ID = row.Field<decimal?>("TIPO_INCIDENTE_ID").GetValueOrDefault(),
                TIPO_INCIDENTE = row.Field<string>("TIPO_INCIDENTE") ?? string.Empty,
                INCIDENTE_DESCRIPCION = row.Field<string>("INCIDENTE_DESCRIPCION") ?? string.Empty,
                INCIDENTE_USUARIO_ID = row.Field<decimal?>("INCIDENTE_USUARIO_ID").GetValueOrDefault(),
                INCIDENTE_USUARIO_LOGIN = row.Field<string>("INCIDENTE_USUARIO_LOGIN") ?? string.Empty,
                INCIDENTE_USUARIO_NOMBRE = row.Field<string>("INCIDENTE_USUARIO_NOMBRE") ?? string.Empty,
                INCIDENTE_ALMACEN = row.Field<string>("INCIDENTE_ALMACEN") ?? string.Empty,
                INCIDENTE_DEPARTAMENTO = row.Field<string>("INCIDENTE_DEPARTAMENTO") ?? string.Empty,
                INCIDENTE_CIERRE = row.Field<DateTime?>("INCIDENTE_CIERRE").GetValueOrDefault(),
                INCIDENTE_CALIFICACION = row.Field<decimal?>("INCIDENTE_CALIFICACION").GetValueOrDefault(),
                INCIDENTE_ESTADO = row.Field<string>("INCIDENTE_ESTADO") ?? string.Empty,
                TAREA_INCIDENTE_ID = row.Field<decimal?>("TAREA_INCIDENTE_ID").GetValueOrDefault(),
                TAREA_INCIDENTE_ACTIVIDAD = row.Field<string>("TAREA_INCIDENTE_ACTIVIDAD") ?? string.Empty,
                TAREA_INCIDENTE_RECURSO_ID = row.Field<decimal?>("TAREA_INCIDENTE_RECURSO_ID").GetValueOrDefault(),
                TAREA_INCIDENTE_LOGIN = row.Field<string>("TAREA_INCIDENTE_LOGIN") ?? string.Empty,
                TAREA_INCIDENTE_USUARIO_NOMBRE = row.Field<string>("TAREA_INCIDENTE_USUARIO_NOMBRE") ?? string.Empty,
                TAREA_INCIDENTE_HORAS = row.Field<decimal?>("TAREA_INCIDENTE_HORAS").GetValueOrDefault(),
                TAREA_INCIDENTE_MINUTOS = row.Field<decimal?>("TAREA_INCIDENTE_MINUTOS").GetValueOrDefault(),
                INCIDENTE_ALMACEN_ID = row.Field<decimal?>("INCIDENTE_ALMACEN_ID").GetValueOrDefault(),
                CAT_TIPO_SOLUCION = row.Field<decimal?>("CAT_TIPO_SOLUCION").GetValueOrDefault()
            }).ToList();

[Saravanan Arunachalam]

Hi Hostilio,

We are unable to reproduce the reported issue “Could not export with null values in datasource”. So, please share the following details to provide a solution as early as possible?

1.       Confirm whether the Grid is render or not when using null value on the Datatable.

2.       Did you mean the issue is exporting is not working when using null value for any of the column of datasource.

3.       If possible please reproduce the issue in the below sample.

We have created a sample that can be downloaded from the below link.

http://www.syncfusion.com/downloads/support/forum/121687/ze/F121687536169933

Regards,

Saravanan A.