reading an excel file

amahmood · #1 08-30-2005, 01:56 PM

Hi

I have an excel document and I am trying to read it using asp or php but to no good.

could somebody give me some suggestions for that.

I tried the following code but it needs that the data in a column either be a number or text and not both

or I get errors.

PHP Code:


			
' Selected constants from adovbs.inc

Const adOpenStatic = 3

Const adLockPessimistic = 2



Dim cnnExcel

Dim rstExcel

Dim I

Dim iCols



' This is all standard ADO except for the connection string.

' You can also use a DSN instead, but so it'll run out of the

' box on your machine I'm using the string instead.

Set cnnExcel = Server.CreateObject("ADODB.Connection")

cnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & _

    "DRIVER={Microsoft Excel Driver (*.xls)};"



' Same as any other data source.

' FYI: TestData is my named range in the Excel file

Set rstExcel = Server.CreateObject("ADODB.Recordset")

rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _

    adOpenStatic, adLockPessimistic



' Get a count of the fields and subtract one since we start

' counting from 0.

iCols = rstExcel.Fields.Count

%>

<table border="1">

    <thead>

        <%

        ' Show the names that are contained in the first row

        ' of the named range.  Make sure you include them in

        ' your range when you create it.

        For I = 0 To iCols - 1

            Response.Write "<th>"

            Response.Write rstExcel.Fields.Item(I).Name

            Response.Write "</th>" & vbCrLf

        Next 'I

        %>

    </thead>

    <%

    rstExcel.MoveFirst



    ' Loop through the data rows showing data in an HTML table.

    Do While Not rstExcel.EOF

        Response.Write "<tr>" & vbCrLf

        For I = 0 To iCols - 1

            Response.Write "<td>"

            Response.Write rstExcel.Fields.Item(I).Value

            Response.Write "</td>" & vbCrLf

        Next 'I

        Response.Write "</tr>" & vbCrLf



        rstExcel.MoveNext

    Loop

    %>

</table>



<%

rstExcel.Close

Set rstExcel = Nothing



cnnExcel.Close

Set cnnExcel = Nothing

russell_g_1 · #2 09-04-2005, 03:08 PM

your problems are caused by the excel driver guessing what the datatype of the column is. it examines the first 8 rows of each column and uses the majority datatype for all the rows, all the rows with other types come back as nulls.

here's an example of it working. you need to change your connection string to get it going basically. its the addition of "IMEX=1" that makes it work with different datatypes in the same column.

Code:

<html>
<body>


<%

writeExcelData()

%>



</body>
</html>

<%


function writeExcelData()

	Dim rs,sql,i

	sql = "SELECT * FROM [TestData$];"

	if runsql(sql,rs) then

		%>
		<table border="1">
		    <thead>
		    	<tr>
			        <%
			        For I = 0 To rs.Fields.Count - 1
			            Response.Write "<th>" & rs.Fields.Item(I).Name & "</th>"
			        Next
			        %>
		        </tr>
		    </thead>
		    <tbody>
		    <%

		    Do While Not rs.EOF

		        Response.Write "<tr>"

		        For I = 0 To rs.Fields.Count - 1
		            Response.Write "<td>" & rs.Fields.Item(I).Value & "</td>"
		        Next

		        Response.Write "</tr>"

		        rs.MoveNext
		    Loop
		    %>
		    </tbody>
		</table>
		<%

		rs.Close

	end if

	Set rs = Nothing

end function




function runSQL(SQL,rs)

	on error resume next

	dim myrs

	set myRs = createobject("ADODB.recordset")
	myRs.Open SQL,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("x1_data.xls") & ";Extended Properties=""Excel 8.0;IMEX=1;""", 1, 3
	set rs = myRs
	if err then
		runSQL = false

		response.write err.description

	else
		runSQL = true
	end if

end function

%>

Bullschmidt · #3 09-08-2005, 11:10 PM

And don't know if this might help at all:

Connection String Home Page
http://www.carlprothman.net/Default.aspx?tabid=81

mutu26 · #4 05-18-2009, 05:59 AM

First of all there are a lot of tools which work with excel files,and I know one of it-Excel file repair tool,as far as I know it is free,software keep several copies of your workbook in different places, you can avoid the loss of information as a result of data corruption or virus attack,ecover corrupted information in Microsoft Excel format, please download Excel files repairing tools right now and try to repair your Excel files with MS Excel repair tool,can open and recover damaged documents in Microsoft Excel format,sove next problems on example if your document was seriously damaged, for example due to HDD failure, MS Excel repairing tool will show these areas to be pretty large,very good solution to repair Excel files.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)