This is a migrated thread and some comments may be shown as answers.

Leading Zero Problem in Excel Export

14 Answers 213 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Alaa'
Top achievements
Rank 1
Alaa' asked on 10 Nov 2010, 12:24 PM
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 ?

14 Answers, 1 is accepted

Sort by
0
Richard Slade
Top achievements
Rank 2
answered on 10 Nov 2010, 12:50 PM
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
0
Alaa'
Top achievements
Rank 1
answered on 10 Nov 2010, 01:15 PM
Hi,

this does not work :(
0
Alaa'
Top achievements
Rank 1
answered on 10 Nov 2010, 01:17 PM
I tried this in the property designer, but nothing changes, I tried it too by code but it does not work 
0
Richard Slade
Top achievements
Rank 2
answered on 10 Nov 2010, 01:18 PM
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
0
Richard Slade
Top achievements
Rank 2
answered on 10 Nov 2010, 01:31 PM
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
0
Alaa'
Top achievements
Rank 1
answered on 10 Nov 2010, 01:46 PM
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

0
Richard Slade
Top achievements
Rank 2
answered on 10 Nov 2010, 01:50 PM
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
0
Emanuel Varga
Top achievements
Rank 1
answered on 10 Nov 2010, 01:57 PM
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
0
Richard Slade
Top achievements
Rank 2
answered on 10 Nov 2010, 02:41 PM
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


0
Martin Vasilev
Telerik team
answered on 15 Nov 2010, 03:32 PM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
justin
Top achievements
Rank 1
answered on 12 Nov 2014, 07:36 AM
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

0
Alessio Bulleri
Top achievements
Rank 1
answered on 17 Nov 2014, 12:17 PM
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.



0
Dimitar
Telerik team
answered on 20 Nov 2014, 09:28 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Alessio Bulleri
Top achievements
Rank 1
answered on 20 Nov 2014, 02:01 PM
Hi Dimitar.

It works fine. 

Thanks.

Alessio
Tags
GridView
Asked by
Alaa'
Top achievements
Rank 1
Answers by
Richard Slade
Top achievements
Rank 2
Alaa'
Top achievements
Rank 1
Emanuel Varga
Top achievements
Rank 1
Martin Vasilev
Telerik team
justin
Top achievements
Rank 1
Alessio Bulleri
Top achievements
Rank 1
Dimitar
Telerik team
Share this question
or