Leading Zero Problem in Excel Export

15 posts, 0 answers
  1. Alaa'
    Alaa' avatar
    4 posts
    Member since:
    Nov 2010

    Posted 10 Nov 2010 Link to this post

    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 ?
  2. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 10 Nov 2010 Link to this post

    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
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Alaa'
    Alaa' avatar
    4 posts
    Member since:
    Nov 2010

    Posted 10 Nov 2010 Link to this post

    Hi,

    this does not work :(
  5. Alaa'
    Alaa' avatar
    4 posts
    Member since:
    Nov 2010

    Posted 10 Nov 2010 Link to this post

    I tried this in the property designer, but nothing changes, I tried it too by code but it does not work 
  6. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 10 Nov 2010 Link to this post

    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
  7. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 10 Nov 2010 Link to this post

    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
  8. Alaa'
    Alaa' avatar
    4 posts
    Member since:
    Nov 2010

    Posted 10 Nov 2010 Link to this post

    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

  9. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 10 Nov 2010 Link to this post

    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
  10. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 10 Nov 2010 Link to this post

    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
  11. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 10 Nov 2010 Link to this post

    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


  12. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 15 Nov 2010 Link to this post

    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
  13. justin
    justin avatar
    1 posts
    Member since:
    Nov 2014

    Posted 12 Nov 2014 in reply to Alaa' Link to this post

    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

  14. Alessio Bulleri
    Alessio Bulleri avatar
    13 posts
    Member since:
    Sep 2008

    Posted 17 Nov 2014 in reply to justin Link to this post

    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.



  15. Dimitar
    Admin
    Dimitar avatar
    1415 posts

    Posted 20 Nov 2014 Link to this post

    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.

     
  16. Alessio Bulleri
    Alessio Bulleri avatar
    13 posts
    Member since:
    Sep 2008

    Posted 20 Nov 2014 in reply to Dimitar Link to this post

    Hi Dimitar.

    It works fine. 

    Thanks.

    Alessio
Back to Top
UI for WinForms is Visual Studio 2017 Ready