Kendo UI Grid Export Excel

Question

I have the following Grid in *.cshtml:

$.ajax({
                url: '@Url.Action("RetornaJsonGrid", "PacoteLicencas")',
                type: "post",
                cache: false
            }).success(function (json) {
                $("#gridPacoteLicencas").kendoGrid({
                    dataSource: {
                        data: json,
                        schema: {
                            model: {
                                fields: {
                                    Responsavel: { type: "string" },
                                    TicketLicenca: { type: "number" },
                                    Data_Ticket: { type: "date" },
                                    Empresa: { type: "string" },
                                    Faturamento: { type: "string" },
                                    Status: { type: "string" },
                                    Produtos: { type: "string" },
                                    Solicitante: { type: "string" },
                                    CCProjeto: { type: "string" },
                                    Quantidade: { type: "number" },
                                    CustoUsu: { type: "boolean" },
                                    OkAltiris: { type: "boolean" },
                                    RC: { type: "string" },
                                    PC: { type: "string" },
                                    NF: { type: "string" },
                                    Office: { type: "boolean" },
                                    LyncClient: { type: "boolean" },
                                    LyncStand: { type: "boolean" },
                                    Exchange: { type: "boolean" },
                                    WindDvcCal: { type: "boolean" },
                                    Access: { type: "boolean" },
                                    Altiris: { type: "boolean" },
                                    SEP: { type: "boolean" },
                                }
                            }
                        },
                        pageSize: 30
                    },                      
                    height: 690,
                    scrollable: true,
                    sortable: true,
                    resizable: true,
                    filterable: true,
                    pageable: {
                        input: true,
                        numeric: false
                    },
                    columns: [
                        { field: "Responsavel", title: "Responsável", width: "130px" },
                        { field: "TicketLicenca", title: "Ticket", width: "80px" },
                        { field: "Data_Ticket", format: "{0:dd - MMMM - yyyy}", title: "Data Ticket", width: "130px" },
                        { field: "Empresa", width: "130px", hidden: true },
                        { field: "Faturamento", width: "130px", hidden: true },
                        { field: "Status", width: "130px" },
                        { field: "Produtos", width: "130px", hidden: true },
                        { field: "Solicitante", width: "130px" },
                        { field: "CCProjeto", title: "CC Projeto", width: "90px" },
                        { field: "Quantidade", title:"Qtd", width: "55px" },
                        { field: "CustoUsu", title: "Custo Usuario", width: "115px" },
                        { field: "OkAltiris", title: "Ok no Altiris", width: "130px", hidden: true },
                        { field: "RC", width: "100px", hidden: true },
                        { field: "PC", width: "100px", hidden: true },
                        { field: "NF", width: "130px", hidden: true },
                        { field: "Office", title: "Office Standard", width: "130px", hidden: true },
                        { field: "LyncClient", title: "Lync Client Estação", width: "140px", hidden: true },
                        { field: "LyncStand", title: "Lync Standard UsrCAL", width: "160px", hidden: true },
                        { field: "Exchange", title: "Exchange Standard DvcCal", width: "130px", hidden: true },
                        { field: "WindDvcCal", title: "Windows DvcCal", width: "130px", hidden: true },
                        { field: "Access", title: "MS Access", width: "130px", hidden: true },
                        { field: "Altiris", width: "100px", hidden: true },
                        { field: "SEP", width: "130px", hidden: true },

                        { command: { text: "Editar", click: abreDetal }, title: " ", width: "100px" }
                    ],
                    //pacotes de tradução
                    filterable: {
                        messages: {
                            info: "Título:", // sets the text on top of the filter menu
                            filter: "Filtrar", // sets the text for the "Filter" button
                            clear: "Limpar", // sets the text for the "Clear" button

                            // when filtering boolean numbers
                            isTrue: "é verdadeiro", // sets the text for "isTrue" radio button
                            isFalse: "é falso", // sets the text for "isFalse" radio button

                            //changes the text of the "And" and "Or" of the filter menu
                            and: "E",
                            or: "Ou"
                        },
                        operators: {
                            //filter menu for "string" type columns
                            string: {
                                eq: "Igual a",
                                neq: "Diferente de",
                                startswith: "Começa com",
                                contains: "Contém",
                                endswith: "Termina em"
                            },
                            //filter menu for "number" type columns
                            number: {
                                eq: "Igual a",
                                neq: "Diferente de",
                                gte: "Maior que ou igual a",
                                gt: "Maior que",
                                lte: "Menor que ou igual a",
                                lt: "Menor que"
                            },
                            //filter menu for "date" type columns
                            date: {
                                eq: "Igual a",
                                neq: "Diferente de",
                                gte: "Maior que ou igual a",
                                gt: "Mair que",
                                lte: "Menor que ou igual a",
                                lt: "Menor que"
                            }
                        }
                    },
                    groupable: {
                        messages: {
                            empty: "Arraste colunas aqui para agrupar pelas mesmas"
                        }
                    },
                    columnMenu: {
                        messages: {
                            sortAscending: "Crescente",
                            sortDescending: "Decrecente",
                            filter: "Filtro",
                            columns: "Colunas"
                        }
                    }
                });
                function abreDetal(e) {     
                    e.preventDefault();
                    var dataItem = this.dataItem($(e.currentTarget).closest("tr"));                     
                    location.href = "/PacoteLicencas/Edit/"+dataItem.TicketLicenca;
                }
            });

and in *.Cs:

[Authorize]
    public JsonResult RetornaJsonGrid()
    {
        var pacotelicencas = db.PacoteLicencas.Include(p => p.Empresa).Include(p => p.Faturamento).Include(p => p.Status).Include(p => p.Produtos);
        var jsonLicencas = new List<Object>();
        for (int i = 0; i < pacotelicencas.ToList().Count(); i++)
        {
            var objJson = pacotelicencas.ToList()[i];                
            jsonLicencas.Add(new {
                Responsavel = objJson.Responsavel,
                TicketLicenca = objJson.TicketLicenca,
                Data_Ticket = objJson.Data_Ticket,
                Empresa = objJson.Empresa.Apelido,
                Faturamento = objJson.Faturamento.Apelido,
                Status = objJson.Status.Descricao,
                Produtos = objJson.Produtos.Descricao,
                Solicitante = objJson.Solicitante,
                CCProjeto = objJson.CCProjeto,
                Quantidade = objJson.Quantidade,
                CustoUsu = objJson.CustoUsu,
                OkAltiris = objJson.OkAltiris,
                RC = objJson.RC,
                PC = objJson.PC,
                NF = objJson.NF,
                Office = objJson.Office,
                LyncClient = objJson.LyncClient,
                LyncStand = objJson.LyncStand,
                Exchange = objJson.Exchange,
                WindDvcCal = objJson.WindDvcCal,
                Access = objJson.Access,
                Altiris = objJson.Altiris,
                SEP = objJson.SEP
            });
        }
        return this.Json(jsonLicencas, JsonRequestBehavior.AllowGet);            
    }

How to export this Grid to Excel, without exporting the columns that are Hidden, for the user to have the freedom to choose what to export.

Thank you!

Answer 1

As I commented, you can grab the columns that are visible, as well as other data using the property columns grid:

$(el).data("kendoGrid").columns

From there you can create an array/Object with what you need to know in your service to generate your document, for example:

var gridColumns = [
    {field: "Id", visible: true},
    {field: "Name", visible: true}
];

And then serialize this object to send it through a input type="hidden" for example:

$("#hiddenId").val(JSON.stringify(gridColumns));

This way you can receive this content in a Viewmodel list of the same format:

public class GridColumnsViewModel 
{
    public string field { get; set; }
    public string visible { get; set; }
}

And receive this information with the following controller:

public ActionResult ExportToExcel(long id, ..., List<GridColumnsViewModel> gridColumns)

Now with all necessary filters you can grab all the data to generate the document. I used the lib NPOI for C#(has in Nuget). Here are some interesting snippets of the code I used:

• Starting component and creating spreadsheet:

  HSSFWorkbook workbook = null;
  
  try
  {
      workbook = new HSSFWorkbook();
  }
  catch (Exception ex)
  {
      return Content("Erro ao iniciar extensão NPOI. [" + ex.Message + "]");
  }
  
  var fileName = "grid-export";
  var sheet = workbook.CreateSheet(fileName);
  

• Defining styles:

  // Fonte do título
  var fontTitle = workbook.CreateFont();
  fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
  
  // Estilo do título
  var styleTitle = workbook.CreateCellStyle();
  styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
  styleTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
  styleTitle.SetFont(fontTitle);
  styleTitle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
  styleTitle.FillPattern = FillPatternType.SOLID_FOREGROUND;
  
  // Estilo da linha zebrada
  var styleOddRow = workbook.CreateCellStyle();
  styleOddRow.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
  styleOddRow.FillPattern = FillPatternType.SOLID_FOREGROUND;
  

• Creating lines and cells:

  var row = sheet.CreateRow(0); // Linha 1
  
  var cell = row.CreateCell(0); // Coluna A
  cell.SetCellValue("");
  cell.CellStyle = styleOddRow;
  
  // Em caso de necessidade de formatação por tipo - no caso campo numérico
  cell.SetCellType(CellType.NUMERIC);
  cell.SetCellValue(10.50);
  

• Adjust column width automatically:

  // Ajustar tamanho das colunas - considerando que numColumns é o total de colunas
  for (var c = 0; c < numColumns; c++)
  {
      sheet.AutoSizeColumn(c, true);
  }
  

• Finally, generating the file and sending for direct download to the browser:

  try
  {
      // Salva o arquivo na pasta temporária do sistema
      MemoryStream file = new MemoryStream();
      workbook.Write(file);
  
      file.Position = 0;
  
      return File(file, "application/vnd.ms-excel", fileName + ".xls");
  }
  catch (Exception ex)
  {
      return Content("Erro ao salvar temporário. [" + ex.Message + "]");
  }
  

Some of these details were the ones that gave me the most headache when I was creating this routine, especially the last two, as amazing as it may seem.