export sprat sheet rad grid view to excel

1 Answer 138 Views
DateTimePicker Form GridView
ebrahim
Top achievements
Rank 1
Iron
Iron
ebrahim asked on 29 May 2022, 11:29 AM | edited on 29 May 2022, 11:33 AM

hi

1-one columns(date) in grid view by formatinfo(fa-ir, yyyy, Mm, dd) view in run application

But Column date the output is in English

2-cell border  How it's set

3-what not show row number set by cell formatting in expory excel?

4--in pdf export rtl language not suport?

1 Answer, 1 is accepted

Sort by
0
Dinko | Tech Support Engineer
Telerik team
answered on 01 Jun 2022, 08:01 AM

Hi Еbrahim,

Let me get straight to your questions.

1. I am not sure what you mean here by the date of the output in English. Can you share an image of the RadGridView and the exported pdf so that I can compare the data?

2. You can set the ExportVisualSettings to true. Then you can subscribe to the CellFormatting event of the GridViewPdfExport object. Inside the event handler, you can get the CellElement from the event arguments and customize its properties.

Telerik.WinControls.Export.GridViewPdfExport pdfExporter = new Telerik.WinControls.Export.GridViewPdfExport(this.radGridView1);
pdfExporter.ExportVisualSettings = true;
pdfExporter.CellFormatting += PdfExporter_CellFormatting;

private void PdfExporter_CellFormatting(object sender, Telerik.WinControls.Export.PdfExportCellFormattingEventArgs e)
{
    e.CellElement.TextAlignment = ContentAlignment.MiddleLeft;
    e.CellElement.BorderTopColor = Color.Blue;
    e.CellElement.BorderBottomWidth = 1;
    e.CellElement.BorderBottomColor = Color.Red;
    e.CellElement.BorderBoxStyle = Telerik.WinControls.BorderBoxStyle.FourBorders;
    
}

3. Can you elaborate on which number you don't want to show? I have tested it on my side and no row numbers are appearing. Do you have an additional column for row numbers?

4. RightToLeft column layout is not taken into consideration when exporting. A feature request for this is already available. You can subscribe to the Feedback Item to get notified of its status change. For the time being, you can set the TextAlignment property of the CellElement to MiddleLeft. You can use the above code in the CellFormatting event handler.

Regards,
Dinko
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/.

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 01 Jun 2022, 11:31 AM

hi mr

1-I have set the date column as the Iranian calendar when it is displayed, but it is in the Gregorian output of Excel.

3-I display the number row with this command in Grid View but it is not in the output, it is the name of the column but without any number

if (e.CellElement.ColumnInfo.Name == "RowNumber")
            {

                e.CellElement.Text = (e.CellElement.RowIndex + 1).ToString();
            }
4-In the PDF output, it breaks down all the letters, ie the words become unreadable. Is this output not a problem with the Persian language?
Dinko | Tech Support Engineer
Telerik team
commented on 02 Jun 2022, 09:26 AM

1. May I ask you to share how are you setting the custom format? Is it only set to the column?

2. I have tested this behavior when exporting the control to PDF and I can see the custom column with the row numbers. Can you specify the exported type of the file: PDF or Excel.

3. I will need to reproduce this behavior to debug it on my side.

Could it be possible to prepare a sample project which demonstrates how the RadGridView columns format is set and how the control is exported in the different formats? This way I can take a closer look and try to find a suitable solution. Without reproducing this I can only guess here.

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 02 Jun 2022, 02:30 PM

hi mr

1-by this code


 public frmInputfuel()
        {
            InitializeComponent();
            RadGridLocalizationProvider.CurrentProvider = new PersianRadGridLocalizationProvider();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("fa-IR");
}

2- export excel and pdf

3-ok  code for export in project 


        private void spreadExporter_AsyncExportProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            this.radProgressBarExport.Value1 = e.ProgressPercentage;
        }
        private void spreadExporter_AsyncExportCompleted(object sender, AsyncCompletedEventArgs e)
        {
            MessageOperat.MessageShow("ExportComplet");

            this.radProgressBarExport.Value1 = 0;
        }


        private void simpleButtonExportExcel_Click(object sender, EventArgs e)
        {


            string fileName = "C:\\" + "ExportExcel" + DateTime.Now.MiladiToShamsiDateAndTime() + ".xlsx";
            GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridViewListDevice);
            spreadExporter.AsyncExportProgressChanged += spreadExporter_AsyncExportProgressChanged;
            spreadExporter.AsyncExportCompleted += spreadExporter_AsyncExportCompleted;
            SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
            spreadExporter.ExportVisualSettings = true;
            spreadExporter.HiddenRowOption = Telerik.WinControls.UI.Export.HiddenOption.ExportAlways;
            spreadExporter.HiddenColumnOption = Telerik.WinControls.UI.Export.HiddenOption.DoNotExport;
            spreadExporter.RunExportAsync(fileName, exportRenderer);




        }

file export in attach

thanks

 

Dinko | Tech Support Engineer
Telerik team
commented on 03 Jun 2022, 12:57 PM

Thank you for the provided code snippet. I have reviewed your approach and here is my observation.

1. By default, when the RadGridView is exported to Excell it does not respect the current culture applied to the application. In this case, you can use the ExcelExportType and ExcelExportFormatString properties of the DateTime column.

oprderDateColumn.ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.Custom;
oprderDateColumn.ExcelExportFormatString = "yyyy/MM/dd hh/mm";

3. To see the row number you need to set the Value property of the cell, instead of the Text. When exporting to PDF the Value property of the cell is taken into consideration.

4. To avoid any misunderstanding, may I ask you to share some text so that I can test it on my side. Just to confirm, the letters are messed up only when the control is exported in PDF?

 

 

 

 

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 03 Jun 2022, 06:29 PM | edited

hi mr dinko

1-My problem with the output of the date column is that it sends it to the output in miladi, while the date of my column is in Iranian, as in the photo I sent.

3-It does not display the row column in Excel at all in the output. I do not know the reason why I even changed the type

4-For example, a sample Persian text is as follows:

 

سلام آقای عزیز ممنون از پاسخگویی شما

5-Is there no cell formatting in GridViewSpreadExport?

Dinko | Tech Support Engineer
Telerik team
commented on 08 Jun 2022, 08:58 AM

Thank you for the provided text.

1. I have searched for a way to make this work and find out that Excel is converting the date to a gregorian format. You can find more information in this MSDN article which describe you could set a Persian date. When you follow the steps and set the Persian date you can go to the Custom tab. There you can find the string format which you can use in your application.

oprderDateColumn.ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.Custom;
oprderDateColumn.ExcelExportFormatString = "[$-fa-IR,16]dd/mm/yyyy;@";

3. I have double-checked this I am able to export the row column. You can check the attached project. When you click on the button it will export the control in PDF and Excell format. You just need to change the path of the exported files.

4. Generally, speaking the right to left is not supported by our document library. That is why you are seeing this. The problem in PDF is that the text is reverted. You can workaround this by reversing the text in the CellFormatting event of the GridViewPdfExport. Then when the internal logic reverts it will be in a correct state.

public static string Reverse(string s)
{
    char[] charArray = s.ToCharArray();
    Array.Reverse(charArray);
    return new string(charArray);
}

private void PdfExporter_CellFormatting(object sender, Telerik.WinControls.Export.PdfExportCellFormattingEventArgs e)
{
    e.CellElement.TextAlignment = ContentAlignment.MiddleLeft;
    e.CellElement.BorderTopColor = Color.Blue;
    e.CellElement.BorderBottomWidth = 1;
    e.CellElement.BorderBottomColor = Color.Red;
    e.CellElement.BorderBoxStyle = Telerik.WinControls.BorderBoxStyle.FourBorders;
    e.CellElement.BorderColor = Color.Blue;
    if(!(e.Row is GridViewTableHeaderRowInfo) && e.ColumnIndex== 1)
    {
        var reverseText = Reverse(e.CellElement.Text);
        e.CellElement.Text = reverseText ;
    }
}

5. GridViewSpreadExport exposes CellFormatting event. However, this event will be called when the RunExport method is used. The async RunExportAsync() method will not trigger this event to increase the performance. Also, the content of the cells in RunExportAsync() will be exported as plain text.

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 11 Jun 2022, 12:51 PM

thanks mr dinko

1-My date column problem was fixed

2-But because I use the progress bar to progress the output  excel,in spread async row not show

export pdf by this code

ContentAlignment.MiddleLeft;

it is the same as before

Thank you for your response

Dinko | Tech Support Engineer
Telerik team
commented on 15 Jun 2022, 01:21 PM

I am happy to hear that we have made some progress here. Reading the second point (2-But because I use the progress bar to progress the output excel,in spread async row not show) I am not sure if everything is working now. Can you confirm it?

Regarding the PDF export, in the project from my previous reply, the e.CellElement.TextAlignment property is set to ContentAlignment.MiddleLeft. Can you share if you are referencing this property or some other one?

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 16 Jun 2022, 10:11 AM

hi mr dinko

row not shoe in export spread async

2-export pdf

In the program that you have attached, in the PDF output, if you have noticed, the words are separated from each other


private void PdfExporter_CellFormatting(object sender, Telerik.WinControls.Export.PdfExportCellFormattingEventArgs e)
{
    e.CellElement.TextAlignment = ContentAlignment.MiddleLeft;
    e.CellElement.BorderTopColor = Color.Blue;
    e.CellElement.BorderBottomWidth = 1;
    e.CellElement.BorderBottomColor = Color.Red;
    e.CellElement.BorderBoxStyle = Telerik.WinControls.BorderBoxStyle.FourBorders;
    e.CellElement.BorderColor = Color.Blue;
    if(!(e.Row is GridViewTableHeaderRowInfo) && e.ColumnIndex== 1)
    {
        var reverseText = Reverse(e.CellElement.Text);
        e.CellElement.Text = reverseText ;
    }
}

Dinko | Tech Support Engineer
Telerik team
commented on 17 Jun 2022, 11:18 AM

So far I can't be sure why the row-column is not shown in your file when it is exported. I have tested my project and it is working. Can you test again the attached project and share when you export the grid in excel, the row-column is populated correctly. Here is the result on my side.

As for the PDF export. Can you confirm that there should not be empty space when the grid is exported in PDF? I have checked the string and there is an empty space. Basically, you can remove them in the Reverse method if this is the case. If not may I ask you to give me some more details? The string in the exported PDF is not grammatically correct or the words are separated or not.

// a way to remove empty space
public static string Reverse(string s)
{
    char[] charArray = s.ToCharArray();
    Array.Reverse(charArray);
    string finalString = string.Empty;
    foreach (char item in charArray)
    {
        if (item != ' ')
        {
            finalString += (item.ToString());
        }
    }
    return finalString;
}

ebrahim
Top achievements
Rank 1
Iron
Iron
commented on 18 Jun 2022, 11:08 AM

hi mr dinko

As for the PDF export

Please pay attention to the following two words, which are written in phasesloyal dog

سگ وفادار

If I send this text to PDF output it will be displayed as follows

س گ و ف ا د ا ر

If I delete the blanks with your code, these two words, which should have a space between them, will stick together.

سگوفادار

It's like writing a( badstudent)

Dess | Tech Support Engineer, Principal
Telerik team
commented on 21 Jun 2022, 11:42 AM

Hello, Ebrahim,

My colleague, Dinko, is out of office this week so I will be assisting you with this case. You can keep the empty character if it exists in the original text: 
        public static string Reverse(string s)
        {
            char[] charArray = s.ToCharArray();
            Array.Reverse(charArray);
            var finalString = string.Empty;
            foreach (char item in charArray)
            {
                //if (item != ' ')
                {
                    finalString += (item.ToString());
                }
            }
            return new string(charArray);
        }
Please have in mind that the Reverse method is just a sample approach how to reverse a string. It may not cover all possible cases. Feel free to modify and extend it in a way which suits your custom requirements best. Thus you can achieve the desired spacing between the characters. 
Tags
DateTimePicker Form GridView
Asked by
ebrahim
Top achievements
Rank 1
Iron
Iron
Answers by
Dinko | Tech Support Engineer
Telerik team
Share this question
or