Leading Zero Problem in Excel Export

[Alaa']

Hi All,

I have a problem while exporting to Excel, columns which has data starts with zero is exported without zero.
example 0012003 is exported like  12003, how can I handle this problem ?

[Richard Slade]

Hello,

you may want to ensure that you have the right DisplayFormatType for that column. Please can you try setting it to:

Me.RadGridView1.Columns(0).ExcelExportType = Export.DisplayFormatType.Text

this link may also help.

Let me know if you need further assistance.
Richard

[Alaa']

Hi,

this does not work :(

[Alaa']

I tried this in the property designer, but nothing changes, I tried it too by code but it does not work 

[Richard Slade]

Hi,

I'm sorry to hear that this did not work for you. I am now back at my development environment and will prepare you a sample and will get back to you shortly.
thanks
Richard

[Richard Slade]

Hello, 

Please can you try this very simple sample. It is just a RadGridView and a RadButton on a form. This exports the settings correctly with the leading zero. 

Imports Telerik.WinControls.UI
Imports Telerik.WinControls

Imports Telerik.WinControls.Data
Imports Telerik.WinControls.UI.Export

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.RadGridView1.Columns.Add(New GridViewTextBoxColumn("A"))
        Me.RadGridView1.Columns.Add(New GridViewTextBoxColumn("B"))
        Dim rowInfo As GridViewRowInfo = Me.RadGridView1.Rows.AddNew()
        rowInfo.Cells(0).Value = "0012003"
        rowInfo.Cells(1).Value = "0002365"
        rowInfo = Me.RadGridView1.Rows.AddNew()
        rowInfo.Cells(0).Value = "Some text"
        rowInfo.Cells(1).Value = "This text"
    End Sub

    Private Sub RadButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton1.Click
        Dim exporter As New ExportToExcelML(Me.RadGridView1)
        exporter.ExportVisualSettings = True
        Dim fileName As String = "C:\ExportedData.xls"

        exporter.RunExport(fileName)
    End Sub
End Class

Richard

[Alaa']

Hi,
actually I developed a user control that exports to many Excel, HTML, ExcelML

the second and the third "HTML, ExcelML"  works fine for the same Grid but I have a problem with the first that is export to Excel
I noticed from your code that you used ExportToExcelML exporter , I have no porblem with this

this is my code

Private Sub exportToExcelML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
       SaveFileDialog1.Filter = "Excel (*.xls)|*.xls"
       If openDialog() Then
           RunExportToExcelML(fileName, openExportFile)

       End If
   End Sub

Public Function openDialog() As Boolean
        openDialog = True
        If checkGrd(Me.grdObj) Then
            RadMessageBox.Show(Me.ParentForm, "لا يوجد بيانات للتصدير", "نظام إدارة بيانات الصندوق", MessageBoxButtons.OK, RadMessageIcon.Info, MessageBoxDefaultButton.Button1, Windows.Forms.RightToLeft.Yes)
            openDialog = False
        Else
            If SaveFileDialog1.ShowDialog() <> DialogResult.OK Then
                openDialog = False
                Exit Function
            End If
            fileName = Me.SaveFileDialog1.FileName
            If SaveFileDialog1.FileName.Equals([String].Empty) Then
                '  RadMessageBox.SetThemeName(Me.radGridView1.ThemeName)
                RadMessageBox.Show(Me.ParentForm, "الرجاء إدخال اسم الملف", "نظام إدارة بيانات الصندوق", MessageBoxButtons.OK, RadMessageIcon.Info, MessageBoxDefaultButton.Button1, Windows.Forms.RightToLeft.Yes)
                openDialog = False
                Exit Function
            End If
            'Dim openExportFile As Boolean = False
        End If
    End Function

Private Sub exporttToExcel(ByVal fileName As String)
        Try
            Dim file As New FileInfo(fileName)
            If file.Extension.Equals(".xls") Then
                If file.Exists() Then
                    file.Delete()
                End If
                Export(fileName)
                RadMessageBox.Show(Me.ParentForm, "تمت   العملية بنجاح", "نظام إدارة بيانات الصندوق", MessageBoxButtons.OK, RadMessageIcon.Info, MessageBoxDefaultButton.Button1, Windows.Forms.RightToLeft.Yes)
                'radProgressBar1.Value1 = 0
            Else
                RadMessageBox.SetThemeName(Me.grdObj.ThemeName)
                RadMessageBox.Show(Me.ParentForm, "نوع الملف غير صحيح", "نظام إدارة بيانات الصندوق", MessageBoxButtons.OK, RadMessageIcon.Info, MessageBoxDefaultButton.Button1, Windows.Forms.RightToLeft.Yes)
            End If
            'End If
        Catch ex As Exception
            RadMessageBox.Show(ex.Message)
        End Try
    End Sub

[Richard Slade]

Thanks. 
May I ask why you need both ExportToExcel and ExportToExcelML? 

Please could you post your code for the standard Export and I will take a look for you. 
Richard

[Emanuel Varga]

Hello guys,

Alaa, can you please type in a Cell in excel that number?
And tell me what you see after :).

This is the default behavior for numbers in excel, to remove the leading 0, because it is useless for numbers...
For a way to change this behavior part of excel please take a look at this article.

Or you can try formatting your cells before exporting, like so:

    ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
    exporter.ExcelCellFormatting += new Telerik.WinControls.UI.Export.ExcelML.ExcelCellFormattingEventHandler(exporter_ExcelCellFormatting);
    exporter.ExportVisualSettings = true;
    exporter.RunExport(@"C:\test.xls");
}

void exporter_ExcelCellFormatting(object sender, Telerik.WinControls.UI.Export.ExcelML.ExcelCellFormattingEventArgs e)
{
    //do all the necessary processing
}

Hope this helps, if you have any other questions or comments, please let me know,

Best Regards,
Emanuel Varga

[Richard Slade]

Hi All, 

Emanuel, I'm not convinced this is going to make a difference. In my spreadsheet, I followed the link for the same in Office 2007 and applied the change to Excel and I could still see the zeros correctly, because it is formatted as text. 

Your example shows ExcelML too, but this has already been confirmed to work ok. 

Alaa'If you can post your Export method, I can take a look at that for you. 
Thanks

Richard

[Martin Vasilev]

Hello guys,

Indeed, MS Excel removes the leading zeros, because it consideres the values as numeric values by default. This behavior can be worked around if you format the text as Richard has already pointed.
However, only the ExportToExcelML functionality supports the necessary formatting options. The feature that exports through primary interop assemblies is not affected by the formatting properties and most probably this is the reason for the behavior described by Alaa. In this case I will simply recommend using ExportToExcelML.

Best wishes,
Martin Vasilev
the Telerik team

[justin]

tryt his....

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "@";
xlWorkSheet.Cells[1, 1] = "098";

Source : http://csharp.net-informations.com/excel/csharp-format-excel.htm

Justin

[Alessio Bulleri]

Hi guys.....

I have same problem using also Telerik.WinControls.UI.Export.SpreadExport.

Into CellFormattingEvent I tried setting format, but it doesn't work

                   CellValueFormat cvf = new CellValueFormat("@");
                    e.CellSelection.SetFormat(cvf);

 Can someone help me?

Thanks.

[Dimitar]

Hello Alessio,

Thank you for writing.

In this case you should manually set the value after the format is applied:

void spreadExporter_CellFormatting(object sender, Telerik.WinControls.UI.Export.SpreadExport.CellFormattingEventArgs e)
{
    CellValueFormat cvf = new CellValueFormat("@");
    e.CellSelection.SetFormat(cvf);

    string Value = e.GridCellInfo.Value.ToString();
    e.CellSelection.SetValue(Value);
}

Please let me know if there is something else I can help you with. 

 

Regards,
Dimitar
Telerik

 

 

[Alessio Bulleri]

Hi Dimitar.

It works fine. 

Thanks.

Alessio