Formatting dates, text and number in Excel Export

[Andrea]

Hi,

In the export to Excel from Kendo grid have problem.

Can I put one field as textual, one as a date and all others as a number? When exporting, they all come to me as numbers, so I have a problem with XLSX that eats the first 0, and I would like to put that field in textual.

This is my code:

 SpreadDocumentFormat exportFormat = SpreadDocumentFormat.Xlsx;

            Action<ExportCellStyle> cellStyle = new Action<ExportCellStyle>(ChangeCellStyle);
            Action<ExportRowStyle> rowStyle = new Action<ExportRowStyle>(ChangeRowStyle);
            Action<ExportColumnStyle> columnStyle = new Action<ExportColumnStyle>(ChangeColumnStyle);
            List<ExportColumnSettings> columnsData = new List<ExportColumnSettings>();
            //columnsData.Add(new ExportColumnSettings() { Field = "RB", Title = "R.b.", Hidden = false, Format = null });
            columnsData.Add(new ExportColumnSettings() { Field = "BR", Title = "BR", Hidden = false, Format = null });
            columnsData.Add(new ExportColumnSettings() { Field = "Date", Title = "Date", Format = "{0:MM/dd/yyyy}", Hidden = false });
            columnsData.Add(new ExportColumnSettings() { Field = "Name", Title = "Naziv i sjedište", Format = null, Hidden = false });
            columnsData.Add(new ExportColumnSettings() { Field = "OIB", Title = "OIB", Format = "@", Hidden = false });
            columnsData.Add(new ExportColumnSettings() { Field = "Iznos", Title = "Iznos rn.", Format = null, Hidden = false });

 

and my settings for Cell style:

   private void ChangeCellStyle(ExportCellStyle e)
        {
            bool isHeader = e.Row == 0;
            SpreadBorder border = new SpreadBorder(SpreadBorderStyle.Thin, new SpreadThemableColor(new SpreadColor(0, 0, 0)));

            SpreadCellFormat format = new SpreadCellFormat
            {
                ForeColor = isHeader ? SpreadThemableColor.FromRgb(50, 54, 58) : SpreadThemableColor.FromRgb(0, 0, 0),
                VerticalAlignment = SpreadVerticalAlignment.Center,
                    //NumberFormat = "dd.MM.yyyy.", 
                    //NumberFormat = "@",
                TopBorder = border,
                BottomBorder = border,
          
            };
            e.Cell.SetFormat(format);
        }

 

Thnx,

Andrea

[Karina]

Hello,

Thank you for reaching out to us! The responsible product specialist will contact you and advise you further.

Regards, Karina

Progress Telerik

[Becky]

I am facing the same issue with the server export of a grid. I have a grid with some text fields, some numbers and some dates. I need to set the format for each column but I can't seem to do this. Has anyone got a solution?

[Anton Mironov]

Hi Becky,

Thank you for the details provided.

In order to format cells in a Server Export of a Telerik UI Grid, use the steps from the following article:

• https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/export/server-export

I hope this information helps.

Kind Regards,
Anton Mironov

[Andrea]

That's not the answer we need. We have different columns that need to be exported, and textual and numerical, but also the date.

In SpreadCellFormat format is possible to export only one type :( I need more...

SpreadCellFormat format = new SpreadCellFormat
            {
                ForeColor = isHeader ? SpreadThemableColor.FromRgb(50, 54, 58) : SpreadThemableColor.FromRgb(0, 0, 0),
                VerticalAlignment = SpreadVerticalAlignment.Center,
                    NumberFormat = "dd.MM.yyyy.",  //IF DO THIS EVERYONE FIELD IS IN DATE TYPE
                    //NumberFormat = "@",
                TopBorder = border,
                BottomBorder = border,
          
            };

Please help,

Kind regarts,

Andrea

[Becky]

I have had to hack this to get it to work, by adding in an extra column on my grid that is the date in string format. This is then hidden in the cshtml file and then I unhide it for the excel export and hide the true date. But honestly, this is such a hack and it feels wrong to do this when there should be a way to set column format for each column, instead of setting a cell format for every cell in the spreadsheet.