2

I have phone numbers stored as a string of numbers, e.g.: 6028035615, but CAST them as integers when querying. I want to format the data in the cell in proper phone number format, such as (602) 803-5615 as Excel would. But how do I state the format type I want to use? If I use:

  objData.Columns.Add("HPhone", GetType(Integer))
  wshUS.Column(i).Width = 15 ' PhoneH
  wshUS.Column(i).Style.Numberformat.Format = "###-###-####"

or

 wshUS.Column(i).Style.Numberformat.Format = "(###) ###-####"

a blank cell is shown as either -- in the former case or ()- in the latter. Is there a format type that I can put after the = to get phone numbers as Excel would? How would I use conditional formatting to overcome that problem? I am writing in VB.NET in codebehind for web pages.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
WebEngineer
  • 21
  • 1
  • 3

2 Answers2

0

What is objData? Generally speaking, you shouldn't need to add columns to a sheet - EPPlus will do this automatically when your reference the cell for the first time. My VB is a little rusty but this works fine for me:

<TestMethod> _
Public Sub PhoneNumberFormatTest()
    Dim file = New FileInfo("c:\temp\temp.xlsx")
    If file.Exists Then
        file.Delete()
    End If

    Dim pck = New ExcelPackage(file)
    Dim workbook = pck.Workbook
    Dim worksheet = workbook.Worksheets.Add("newsheet")

    Const number As String = "6028035615"
    Const format As String = "(###) ###-####"

    Dim startcell = worksheet.Cells(1, 1)

    startcell.Value = Int64.Parse(number)
    startcell.Offset(1, 0).Value = Int64.Parse(number) + 1

    worksheet.Column(1).Width = 15
    worksheet.Column(1).Style.Numberformat.Format = format

    pck.Save()

End Sub

I downloaded the source code solution of EPPlus 3 (stable) and ran this as a unit test...

EDIT: Added string manipulation:

<TestMethod> _
Public Sub PhoneNumberFormatTest()
    Dim file = New FileInfo("c:\temp\temp.xlsx")
    If file.Exists Then
        file.Delete()
    End If

    Dim pck = New ExcelPackage(file)
    Dim workbook = pck.Workbook
    Dim worksheet = workbook.Worksheets.Add("newsheet")

    Const number As String = "6028035615"
    Const format As String = "(###) ###-####"

    Dim startcell = worksheet.Cells(1, 1)

    'Directly to numbers with parse
    startcell.Value = Int64.Parse(number)
    startcell.Offset(1, 0).Value = Int64.Parse(number) + 1

    'OR as a string back to a number.
    Dim stringcell = startcell.Offset(2, 0)
    stringcell.Value = number
    stringcell.Value = Int64.Parse(DirectCast(stringcell.Value, String))
    stringcell.Style.Numberformat.Format = "(###) ###-####"

    'As string - does not work as number
    stringcell.Offset(0, 1).Value = number
    stringcell.Offset(0, 1).Style.Numberformat.Format = "(###) ###-####"

    worksheet.Column(1).Width = 15
    worksheet.Column(1).Style.Numberformat.Format = format

    pck.Save()

End Sub
Ernie S
  • 13,902
  • 4
  • 52
  • 79
  • You can ignore the objData part; I realized when going thru the code now I don't need that. – WebEngineer Oct 11 '14 at 17:57
  • You can ignore the objData part; I realized when going thru the code now I don't need that. But what is germane is that I am loading the data to the worksheet using a datatable 'wshUS.Cells("A1").LoadFromDataTable(objTable, True)', so your Parse suggestion seems to not apply here. The data for the phone number is read as Integer so it is already a number. In your example what would be produced in the cell if the number string was empty? – WebEngineer Oct 11 '14 at 18:05
  • I see what you mean. See my edit. I dont think having as a string will work since excel will see it as such (that small error triangle you get in the corner of the cell). I think you only choice would be to cast it to a number using a loop after it dumps out. Or changing it in the datatable if that is possible. – Ernie S Oct 13 '14 at 12:32
0

The exact formatting that excel use for phone numbers is: "[<=9999999]###-####;(###) ###-####".

I suggest you also check this answer: EPPlus Format Cell as "Accounting" Number. It shows how to get all built-in formatting formula.

tmclolo
  • 1