Export DataTable to excel

[Manolo]

Hi,

I fill a grid with a datatable and the columns are autogenerat. I want export the grid to excel.

My method is:

protected void gridDatos_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs
{         
    ExcelExport exp = new ExcelExport();
    var datos = ((DataTable)gridDatos.DataSource).AsEnumerable();
    exp.Export(gridDatos.Model, (IEnumerable)datos, "Export.xlsx", ExcelVersion.Excel2010, true, true, "default-theme");
}


But i get an error,  "Object reference not stablished how object instance" (or similar, my translation isn't good)

Datos contains a IEnumerable <DataRow>

Thanks

[Sellappandi Ramu]

Hi Manolo, 

Thanks for contacting Syncfusion support.

We considered this “Data table exporting” as an feature and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates. 

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

As of now, we converted the data table to list before the exporting functionality as a workaround.

Please refer to the following example code and sample,

public class HomeController : Controller

    {

        DataTable _dTable = new DataTable();

        List<Product> data = new List<Product>();

        public ActionResult Index()

        {

            GetGridDT();

            ViewBag.dataTable = _dTable;

            return View();

        }

        public void ExportToExcel(string GridModel)

        {

            ExcelExport exp = new ExcelExport();

            GetGridData(_dTable);

            var DataSource = data;

            GridProperties obj = ConvertGridObject(GridModel);

            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");

        }

        public void ExportToWord(string GridModel)

        {

            WordExport exp = new WordExport();

            GetGridData(_dTable);

            var DataSource = data;

            GridProperties obj = ConvertGridObject(GridModel);

            exp.Export(obj, DataSource, "Export.docx", false, false, "flat-saffron");

        }

        public void ExportToPdf(string GridModel)

        {

            PdfExport exp = new PdfExport();

            GetGridData(_dTable);

            var DataSource = data;

            GridProperties obj = ConvertGridObject(GridModel);

            exp.Export(obj, DataSource, "Export.pdf", false, false, "flat-saffron");

        }

        private GridProperties ConvertGridObject(string gridProperty)

        {

            JavaScriptSerializer serializer = new JavaScriptSerializer();

            IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable));

            GridProperties gridProp = new GridProperties();

            foreach (KeyValuePair<string, object> ds in div)

            {

                var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public |BindingFlags.IgnoreCase);

                if (property != null)

                {

                    Type type = property.PropertyType;

                    string serialize = serializer.Serialize(ds.Value);

                    object value = serializer.Deserialize(serialize, type);

                    property.SetValue(gridProp, value, null);

                }

            }

            return gridProp;

        }

        public void GetGridDT()

        {

            var connection = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;

            using (var dataAdapter = new SqlDataAdapter("SELECT * from Products", connection))

            {

                var dataTable = new DataTable();

                dataAdapter.Fill(_dTable);

            }

        }

        public void GetGridData(DataTable dt)

        {

            GetGridDT();

            foreach (DataRow row in dt.Rows)

            {

                Product item = GetItem<Employee>(row);

                data.Add(item);

            }

        }

        private static Product GetItem<Employee>(DataRow dr)

        {

            Type temp = typeof(Product);

            Product obj = new Product();

            foreach (DataColumn column in dr.Table.Columns)

            {

                foreach (PropertyInfo pro in temp.GetProperties())

                {

                    if (pro.Name == column.ColumnName)

                        pro.SetValue(obj, dr[column.ColumnName], null);

                    else

                        continue;

                }

            }

            return obj;
        }

Sample: http://www.syncfusion.com/downloads/support/forum/121022/ze/Sample_121022-1065372406

Regards, 

Sellappandi R

[Manolo]

Ok, Thank you

[Sellappandi Ramu]

Hi Manolo, 

Thanks for the update.

Please get back to us if you need any further assistance.  

Regards, 
Sellappandi R