Weird output for each DataRow.Add to Excel

Question

I have a method that generates an excel file from a datatable (using linq) and when I generate the file the excel repeats each row, each cell...

My code adds predefined columns and then for each row it should return the entire row, not each cell + each cell.

                var query = db.vistaRobot.ToList().Where(a => a.fechaFiltro2 >= dateInt && a.filtroModifica == null).ToArray();

                DataTable tablaDespacho = new DataTable();

                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Nombre Colaborador"
                });
                //DataTable tablaDespacho = query.CopyToDataTable<DataRow>();
                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Tipo de Tiempo"
                });


                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Tipo de Licencia"
                });


                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Fecha Inicio"
                });


                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Fecha de Finalizacion"
                });

                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Fecha de Alumbramiento"
                });

                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Fuero"
                });


                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Nro Folio"
                });


                tablaDespacho.Columns.Add(new DataColumn()
                {
                    DataType = System.Type.GetType("System.String"),
                    ColumnName = "Lugar de Reposo"
                });

                foreach (var element in query)
                {
                    var row =
                    tablaDespacho.NewRow();
                    //tablaDespacho.Rows.Add(row);
                    tablaDespacho.Rows.Add(row.ItemArray);

                    // A LOT OF ROWS INSIDE

                    //tablaDespacho.Rows.Add(row);
                    tablaDespacho.Rows.Add(row.ItemArray);

                    row["Lugar de Reposo"] = element.Lugar_de_Reposo;
                    //tablaDespacho.Rows.Add(row);
                    tablaDespacho.Rows.Add(row.ItemArray);
                }

I know it's an error on the for each but I can't find the login behind.

Answer 1

It's because you are calling tablaDespacho.Rows.Add(row.ItemArray); multiple times while you set each column's data of row.

You should create new row with tablaDespacho.NewRow() then add properties to row then finally just call tablaDespacho.Rows.Add(row); once for each row as follow:

foreach (var element in query)
{
    var row = tablaDespacho.NewRow();

    row["Lugar de Reposo"] = element.Lugar_de_Reposo;
    //The other properties

    tablaDespacho.Rows.Add(row);

}