How to get the exact value from website to excel?

Question

1.00/5 (1 vote)

See more:

I have a website that when I click Run Report button, it will generate data and will be exported to excel.

My problem is that, I have a data in a column which is the value is for example like this 08-20 that's under a column Requisition No. but when I export it on excel it will became a date like '20-Aug'

What should I do? Is there a code where the data will just display in the excel as it is? Please help me with this. Thanks.

here's my code when clicking Run Report

VB

Private Function FnGenerateReport() As String
    arrColumn(0) = 0
    arrColumn(1) = rm.intColumn1
    arrColumn(2) = rm.intColumn2
    arrColumn(3) = rm.intColumn3
    arrColumn(4) = rm.intColumn4
    arrColumn(5) = rm.intColumn5
    arrColumn(6) = rm.intColumn6
    arrColumn(7) = rm.intColumn7
    arrColumn(8) = rm.intColumn8
    arrColumn(9) = rm.intColumn9
    arrColumn(10) = rm.intColumn10
    arrColumn(11) = rm.intColumn11
    arrColumn(12) = rm.intColumn12
    arrColumn(13) = rm.intColumn13
    arrColumn(14) = rm.intColumn14
    arrColumn(15) = rm.intColumn15
    arrColumn(16) = rm.intColumn16
    arrColumn(17) = rm.intColumn17
    arrColumn(18) = rm.intColumn18
    arrColumn(19) = rm.intColumn19
    arrColumn(20) = rm.intColumn20
    arrColumn(21) = rm.intColumn21
    arrColumn(22) = rm.intColumn22
    arrColumn(23) = rm.intColumn23
    arrColumn(24) = rm.intColumn24
    arrColumn(25) = rm.intColumn25
    arrColumn(26) = rm.intColumn26
    arrColumn(27) = rm.intColumn27

    Dim strHTML As String = "<table border=""1""><tr>"

    Dim dtbReport As DataTable = BLL.FnGenerateReport(rm)
    For intColCount As Int16 = 1 To 27
        If arrColumn(intColCount) = 1 Then
            strHTML += "<td>Applicant Name</td>"
        End If
        If arrColumn(intColCount) = 2 Then
            strHTML += "<td>Job Applied for</td>"
        End If
        If arrColumn(intColCount) = 3 Then
            strHTML += "<td>EEO-1 Job Classification</td>"
        End If
        If arrColumn(intColCount) = 4 Then
            strHTML += "<td>Date Applied</td>"
        End If
        If arrColumn(intColCount) = 5 Then
            strHTML += "<td>Gender</td>"
        End If
        If arrColumn(intColCount) = 6 Then
            strHTML += "<td>Race</td>"
        End If
        If arrColumn(intColCount) = 7 Then
            strHTML += "<td>Source of Application</td>"
        End If
        If arrColumn(intColCount) = 8 Then
            strHTML += "<td>Status of Application</td>"
        End If
        If arrColumn(intColCount) = 9 Then
            strHTML += "<td>Date of Hire</td><"
        End If
        If arrColumn(intColCount) = 10 Then
            strHTML += "<td>Remarks</td>"
        End If
        If arrColumn(intColCount) = 11 Then
            strHTML += "<td>Advertising Cost</td>"
        End If
        If arrColumn(intColCount) = 12 Then
            strHTML += "<td>Time to Hire</td>"
        End If
        If arrColumn(intColCount) = 13 Then
            strHTML += "<td>Division</td>"
        End If
        If arrColumn(intColCount) = 14 Then
            strHTML += "<td>Location</td>"
        End If
        If arrColumn(intColCount) = 15 Then
            strHTML += "<td>Job Title(Localised) </td>"
        End If
        If arrColumn(intColCount) = 16 Then
            strHTML += "<td>Internal or External</td>"
        End If
        If arrColumn(intColCount) = 17 Then
            strHTML += "<td>Recruiter</td>"
        End If
        If arrColumn(intColCount) = 18 Then
            strHTML += "<td>Comments</td>"
        End If
        If arrColumn(intColCount) = 19 Then
            strHTML += "<td>Job Code/Title</td>"
        End If
        If arrColumn(intColCount) = 20 Then
            strHTML += "<td>Position Type</td>"
        End If
        If arrColumn(intColCount) = 21 Then
            strHTML += "<td>Job Status</td>"
        End If
        If arrColumn(intColCount) = 22 Then
            strHTML += "<td>Date Job Posted</td>"
        End If
        If arrColumn(intColCount) = 23 Then
            strHTML += "<td>Job offer accepted Date</td>"
        End If
        If arrColumn(intColCount) = 24 Then
            strHTML += "<td>HC Classification</td>"
        End If
        If arrColumn(intColCount) = 25 Then
            strHTML += "<td>Hourly/Salaried</td>"
        End If
        If arrColumn(intColCount) = 26 Then
            strHTML += "<td>Ethnicity</td>"
        End If
        If arrColumn(intColCount) = 27 Then
            strHTML += "<td>Disability Status</td>"
        End If
        If arrColumn(intColCount) = 28 Then
            strHTML += "<td>Veteran Status</td>"
        End If
        If arrColumn(intColCount) = 29 Then
            strHTML += "<td>Requisition No.</td>"
        End If
    Next
    strHTML += "</tr>"

    If dtbReport.Rows.Count > 0 Then
        For Each dr As DataRow In dtbReport.Rows
            strHTML += "<tr/>"
            For intColCount2 As Int16 = 1 To 27
                If arrColumn(intColCount2) = 1 Then
                    strHTML += "<td><a href=" & strMainFolderName & "Resume_Details.aspx?r=" & dr("iResumeID") & ">" & dr("vFirst") & " " & dr("vMiddle") & " " & dr("vLast") & "</a></td>"
                End If
                If arrColumn(intColCount2) = 2 Then
                    strHTML += "<td>" & dr("vGeneric") & "</td>"
                End If
                If arrColumn(intColCount2) = 3 Then
                    strHTML += "<td nowrap>" & dr("vJobClass") & "</td>"
                End If
                If arrColumn(intColCount2) = 4 Then
                    strHTML += "<td>" & dr("dtApply").ToShortDateString & "</td>"
                End If
                If arrColumn(intColCount2) = 5 Then
                    strHTML += "<td>" & dr("vGender") & "</td>"
                End If
                If arrColumn(intColCount2) = 6 Then
                    strHTML += "<td>" & dr("vRace") & "</td>"
                End If
                If arrColumn(intColCount2) = 7 Then
                    strHTML += "<td>"
                    If Not IsDBNull(dr("iChannelID")) Then
                        Dim strChannel As String = BLL.FnGetChannel(CInt(dr("iChannelID")))
                        strHTML += strChannel
                    End If
                    strHTML += " </td>"
                End If
                If arrColumn(intColCount2) = 8 Then
                    strHTML += "<td>" & dr("vHireStatus") & "</td>"
                End If
                If arrColumn(intColCount2) = 9 Then
                    strHTML += "<td>"
                    If IsDate(dr("dtHire")) Then
                        strHTML += dr("dtHire").ToShortDateString
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 10 Then
                    strHTML += "<td>" & dr("Remarks") & "</td>"
                End If
                If arrColumn(intColCount2) = 11 Then
                    strHTML += "<td>"
                    If Not IsDBNull(dr("ftAdCost")) Then
                        Replace(FormatNumber(dr("ftAdCost"), 2), ",", "")
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 12 Then
                    strHTML += "<td>" & dr("iTimeToHire") & "</td>"
                End If
                If arrColumn(intColCount2) = 13 Then
                    strHTML += "<td>" & dr("iDivisionID") & "</td>"
                End If
                If arrColumn(intColCount2) = 14 Then
                    strHTML += "<td>" & dr("vLocation") & "</td>"
                End If
                If arrColumn(intColCount2) = 15 Then
                    strHTML += "<td>" & dr("vLocalised") & "</td>"
                End If
                If arrColumn(intColCount2) = 16 Then
                    strHTML += "<td>"
                    If (dr("blnEmployee")) = "True" Then
                        strHTML += "Internal"
                    Else
                        strHTML += "External"
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 17 Then
                    strHTML += "<td>" & dr("vFullName") & "</td>"
                End If
                If arrColumn(intColCount2) = 18 Then
                    strHTML += "<td>" & dr("Comments") & "</td>"
                End If
                If arrColumn(intColCount2) = 19 Then
                    strHTML += "<td>" & dr("JobCodeTitle") & "</td>"
                End If
                If arrColumn(intColCount2) = 20 Then
                    strHTML += "<td>"
                    If (dr("PositionType")) = 1 Then
                        strHTML += "Full-Time"
                    ElseIf (dr("PositionType")) = 2 Then
                        strHTML += "Part-Time"
                    ElseIf (dr("PositionType")) = 3 Then
                        strHTML += "Internship"
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 21 Then
                    strHTML += "<td>"
                    If dr("JobStatus") = 1 Then
                        strHTML += "Draft"
                    ElseIf dr("JobStatus") = 2 Then
                        strHTML += "Posted"
                    ElseIf dr("JobStatus") = 3 Then
                        strHTML += "Closed"
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 22 Then
                    strHTML += "<td>"
                    If IsDate(dr("DateJobPosted")) Then
                        strHTML += dr("DateJobPosted").ToShortDateString
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 23 Then
                    strHTML += "<td>"
                    If IsDate(dr("JobOfferAcceptedDate")) Then
                        strHTML += dr("JobOfferAcceptedDate").ToShortDateString
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 24 Then
                    strHTML += "<td>" & dr("ClassificationTitle") & "</td>"
                End If
                If arrColumn(intColCount2) = 25 Then
                    strHTML += "<td>"
                    If dr("HourlySalaried") = 1 Then
                        strHTML += "Hourly"
                    ElseIf dr("HourlySalaried") = 2 Then
                        strHTML += "Salaried"
                    End If
                    strHTML += "</td>"
                End If
                If arrColumn(intColCount2) = 26 Then
                    strHTML += "<td>" & dr("vEthnicity") & "</td>"
                End If
                If arrColumn(intColCount2) = 27 Then
                    strHTML += "<td>" & dr("vDisability") & "</td>"
                End If
                If arrColumn(intColCount2) = 28 Then
                    strHTML += "<td>" & dr("vVeteran") & "</td>"
                End If
                If arrColumn(intColCount2) = 29 Then
                    strHTML += "<td>" & dr("vRequisition") & "</td>"
                End If
            Next
            strHTML += "</tr>"
        Next
    End If
    strHTML += "</table>"
    Return strHTML
End Function

What I have tried:

I tried putting a single quote this

VB

If arrColumn(intColCount2) = 29 Then
						strHTML += "<td>‘" & dr("vRequisition") & "</td>"
                    End If

now it shows like this

'08-20

in the excel. Is it possible to hide the ' in the beginning of the text?

Posted 9-Mar-17 18:17pm

RKeyy Sii

Updated 13-Mar-17 12:15pm

v4

Add a Solution

Comments

CHill60 10-Mar-17 3:10am

Use the Improve question link and share the code that you are using to export the data to Excel. You can usually force excel to have specific formats for certain columns but exactly how that is done will depend on how you are exporting the data. We can't see your screen!

RKeyy Sii 12-Mar-17 21:22pm

Improved my question

ZurdoDev 10-Mar-17 10:59am

You can try putting a single quote ' in front of the data ('08-20) so that excel treats it as text. Or you can google how to use css to export text to excel.

RKeyy Sii 12-Mar-17 21:20pm

sorry for the late reply haven't been online for a while. I already tried putting a single quote in my code in vb and yes it shows '08-20 in excel but is it possible to hide the ' (single quote) when viewing or displaying in the excel?

2 solutions

Solution 2

You are exporting data to Excel by building an html file and you have problems with some values.
The easiest thing to do is to use Excel to build a sample file with those problematic values, and then sate it as an html file, this way, Excel will show you how it deal with special values.

I have made a sample Sheet with:
1
Ab
13/03/2017
08-20 (input as '08-20)

HTML

<tr height=21 style='height:15.75pt'>
  <td height=21 align=right width=112 style='height:15.75pt;width:84pt' x:num>1</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'>a</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 class=xl24 align=right style='height:15.75pt' x:num="42807">13/03/2017</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt' x:str="'08-20">08-20</td>
 </tr>

Posted 13-Mar-17 12:15pm

Patrice T

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-03-12T22:23:00

As you are importing from HTML to excel try adding a non-breaking space to the end of the value. E.g.

VB

rHTML += "<td>" & dr("vRequisition") &  "&nbsp;</td>"

I'm very surprised that the single quote character was visible - that is not standard Excel behaviour. I did notice that the character in your code was ‘ and not '. (Edit - the character is not copying across now! You can see the character in your question though) That might be a feature of the formatting here or from your system, but to overcome that use the single quote ascii code explicitly e.g.

VB

rHTML += "<td>&#39;" & dr("vRequisition") &  "</td>"

The second suggestion is more "excel-like"