Kendo UI Grid export Excel

Navigation
1

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 no * .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, so the user has the freedom to choose what to export.

Thank you!

    
asked by anonymous 24.03.2014 / 16:55

1 answer

1

As I've commented, you can get the columns that are visible, as well as other data using the columns property of the grid: 

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

From here 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));

In 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 the necessary filters you can take all the data to generate the document. I used the NPOI lib for C # (got on 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 rows 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 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, incredible as it may seem.

    
25.03.2014 / 21:07
Using CakePHP, how to generate editable pages? Grid Sytem Bootstrap