Export data to Excel from MVC5 view

2

I need to know how I can export the next table I have in a project view to an excel document.

The table I want to export to excel is as follows:

    <table class="exampletb table table-bordered table-hover display">
        <thead>
            <tr>
                <th>Colaborador</th>
                <th>Lider</th>
                <th>Eje</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in ViewBag.colaboradores)
    {
        var namelider = item.lider;
        var codigo_col = item.codigo;
        var lider = (from p in ((List<SI_OldMutual.Models.Collaborators>)ViewBag.email_lider).Where(n => n.nombres == namelider)
                     select p.email).ToList();
        if (lider.Count == 0)
        {
            ViewBag.test = "No existe";
        }
        else
        {
            ViewBag.test = lider[0];
        }
        <tr>
            <td>@item.nombres</td>
            <td>@item.lider</td>
            <td>@item.eje_funcional</td>
            @foreach (var peso in ((List<SI_OldMutual.Models.Objectives>)ViewBag.pesos).Where(n => n.Lider == ViewBag.test))
            {
                <td>@peso.Peso_Objetivo</td>
            }
            @foreach (var calificacion in ((List<SI_OldMutual.Models.CalificarColaborador>)ViewBag.calificaciones).Where(n => n.codigo_colaborador == codigo_col))
        {
                <td>@calificacion.calificacion</td>
            }

        </tr>
            }
        </tbody>
    </table>

They tell me that I can do it from the controller but I have no idea how to do it, I'm new to this Asp.Net Mvc5. My idea is to make it export that table with the same logic that I'm using in view.

My controller is this:

public ActionResult Index()
        {
            ViewBag.colaboradores = (from p in db.Collaborators
                         where p.grupo_lider == "NO"
                         select p).ToList();

            ViewBag.pesos = (from p in db.Objectives
                         select p).ToList();

            ViewBag.calificaciones = (from p in db.CalificarColaboradors
                        select p).ToList();

            ViewBag.email_lider = (from p in db.Collaborators where p.grupo_lider == "SI"
                               select p).ToList();

            return View();
        }

It should be noted that the table is painted in the view as follows since the results of the weights and grades may vary depending on the person and are not always the same amount, I leave an image to understand a little better :

This is why I need to be able to export this table as it is painted in the view or at least using the same logic.

In case of doubt my namespace is SI_OldMutual

    
asked by ByGroxD 15.07.2017 в 05:41
source

2 answers

2

This answer is based on a previous answer to a similar question :

You can do it by generating a CSV that is supported by Microsoft Excel. The comma ( , ) represents a column (there are cases where a semicolon ( ; ) is what a column represents but I can not say exactly in which circumstances this behavior occurs) and the line break ( \n ) represents a new row.

If we were to generate the following table:

  <table>
     <thead>
       <tr>
         <th>Header1</th>
         <th>Header1</th>
       </tr>
      </thead>
      <tbody>
        <tr>
          <td>Dato</td>
          <td>Dato</td>
        </tr>
        <tr>
          <td>Dato</td>
          <td>Dato Dato</td>
        </tr>
      </tbody>
    </table> 

To save it as CSV it would be:

var datos = "Header1,Header2 \n Dato,Dato\n Dato, Dato Dato";

This is the result:

I made a small adaptation of your code to how it would be generated from ActionResult . Maybe you have to make some changes but you already have the idea of how to generate it:

public ActionResult GenerarExcel()
{
var colaboradores = (from p in db.Collaborators 
                    where p.grupo_lider == "NO" 
                    select p).ToList(); 
var pesos = (from p in db.Objectives 
            select p).ToList(); 

var calificaciones = (from p in db.CalificarColaboradors 
                        select p).ToList(); 

var email_lider = (from p in db.Collaborators 
select p).ToList(); 


StringBuilder builder = new StringBuilder(); 

//Agregamos las cabezeras 
builder.Append("Colaborador").Append(";") 
.Append("Lider").Append(";") 
.Append("Eje"); 
builder.Append("\n"); 

foreach (var item in colaboradores) 
{ 

    var namelider = item.lider; 
    var codigo_col = item.codigo; 
    var lider = (from p in email_lider.Where(n => n.nombres == namelider) 
    select p.email).ToList(); 

    var nombreLider = string.Empty; 

    if (lider.Count == 0) 
    { 
        nombreLider = "No existe"; 
    } 
    else 
    { 
        nombreLider = lider[0]; 
    } 

    builder.Append(item.nombres).Append(";") 
    .Append(item.lider).Append(";") 
    .Append(item.eje_funcional).Append(";"); 

    foreach (var peso in pesos.Where(n => n.Lider == nombreLider)) 
    { 
        builder.Append(peso.Peso_Objetivo).Append(";"); 
    } 

    foreach (var calificacion in calificaciones.Where(n => n.codigo_colaborador == codigo_col)) 
    { 
        builder.Append(calificacion.calificacion).Append(";"); 
    } 

    builder.Append("\n");// agregamos una nueva fila 
} 


// Lo encodeamos con UTF8 para mostrar los acentos correctamente.
var excelBytes = Encoding.UTF8.GetBytes(builder.ToString());
var excelConUT8Encoding = Encoding.UTF8.GetPreamble().Concat(excelBytes).ToArray();

// guardamos el contenido del archivo en la ruta especificada
var rutaExcel = Server.MapPath("~/App_Data/excel.csv"); 
System.IO.File.WriteAllBytes(rutaExcel, excelConUT8Encoding); 

return File(rutaExcel, "text/csv", "Calificaciones.csv"); 
 }

Update:

The support for the encoding of UTF8 is added to show the accents.

    
answered by 17.07.2017 / 15:49
source
2

I had exactly the same problem and I show you how I solved it. You have to use a Javascript library that I leave at the end.

1-Create a button and assign it a function, say "onclick = 'GenerateExcel ()'"

2-Then in the Generate Excel function you write the following:

function GenerarExcel(){

      $('#NombreDeTuTabla').table2excel({ name: "Prueba", filename: "NombreDelArchivo"});

}

And ready!

Here I leave the demo downloadable

link

what you have to do is include in the header of your page the file jquery.table2excel.js , which is located in the folder src

Remember to put an ID to your table !!

I hope you find it useful

    
answered by 17.07.2017 в 19:59