Home / Questions / 87173
Accepted
ByGroxD
Asked: 2020-07-15 19:41:07 +0800 CST 2020-07-15 19:41:07 +0800 CST

Export data to Excel from MVC5 view

  • 772

I need to know how I can export the following table that I have in a view of my project 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 Asp.Net Mvc5. My idea is to make it export that table with the same logic that I am 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 clarified that the table is painted in my view in the following way since the results of the weights and qualifications can vary depending on the person and are not always the same amount, I leave an image so that it is understood a little better: enter image description here

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

In case of doubt, my namespaceisSI_OldMutual

asp.net-mvc-5

2 Answers

  1. Best Answer

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

    You can do it by generating a CSV which is supported by Microsoft Excel. The comma( ,) represents a column (there are cases where a semicolon( ;) is what represents a column but I can't say exactly under what 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: enter image description here

    I made a small adaptation of your code to how it would be generated from a ActionResult. You may have to make some modifications 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:

    Added support for the UTF8 encoding to display accents.

    • 2

  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 you at the end.

    1-Create a button and assign a function to it, let's say "onclick='GenerarExcel()'"

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

    function GenerarExcel(){

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

}

    And ready!!

    Here I leave you the downloadable demo

    http://www.jqueryscript.net/download/Export-Html-Table-To-Excel-Spreadsheet-using-jQuery-table2excel.zip

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

    Remember to put an ID to your table!!

    I hope you find it useful

    • 2