如何从网站获得精确的价值？ [英] How to get the exact value from website to excel?
本文介绍了如何从网站获得精确的价值？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个网站，当我点击运行报告按钮时，它将生成数据并将导出为excel。
我的问题是，我有一个列中的数据就是这个值，例如像08-20那样，在列申请号下，但是当我在excel上导出它时，它将变成像'20 -Aug'的日期
我该怎么办？是否存在数据只会在excel中显示的代码？请帮我解决一下这个。谢谢。
这是我点击运行报告时的代码
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
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
What I have tried:
I tried putting a single quote this
If arrColumn(intColCount2) = 29 Then
strHTML += "<td>‘" & dr("vRequisition") & "</td>"
End If
now it shows like this
now it shows like this
'08-20in the excel. Is it possible to hide the ’ in the beginning of the text?
in the excel. Is it possible to hide the ' in the beginning of the text?
推荐答案
As you are importing from HTML to excel try adding a non-breaking space to the end of the value. E.g.
As you are importing from HTML to excel try adding a non-breaking space to the end of the value. E.g.
rHTML += "<td>" & dr("vRequisition") & " </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.
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.
rHTML += "<td>'" & dr("vRequisition") & "</td>"The second suggestion is more \"excel-like\"
The second suggestion is more "excel-like"
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)
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)
<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>这篇关于如何从网站获得精确的价值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
