Formatting dates, text and number in Excel Export

1 Answer 520 Views
Grid
Andrea
Top achievements
Rank 1
Andrea asked on 23 Jan 2023, 11:58 AM

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

1 Answer, 1 is accepted

Sort by
0
Karina
Telerik team
answered on 26 Jan 2023, 11:53 AM

Hello,

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

Regards, Karina

Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Becky
Top achievements
Rank 1
commented on 22 Mar 2023, 11:22 AM

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
Telerik team
commented on 27 Mar 2023, 08:20 AM

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:

I hope this information helps.

Kind Regards,
Anton Mironov

Andrea
Top achievements
Rank 1
commented on 27 Mar 2023, 08:51 AM

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
Top achievements
Rank 1
commented on 27 Mar 2023, 08:55 AM

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.

Tags
Grid
Asked by
Andrea
Top achievements
Rank 1
Answers by
Karina
Telerik team
Share this question
or