PDA

Click to See Complete Forum and Search --> : Searching through an excel Document

Darth Circuit
03-27-2006, 07:10 PM
Hi!

I am somewhat new here.

I have asked this question in the SQL forum, and someone was helping me abit, and he said that he would get back to me, but i have not heard from him in about 3 months.

I was reading on the net, and in my book that asp.net may be a better sollution to my problem anyways.

Anyways - I need to create a web page that has the ability to search through an excel document.

For the sake of being more clear, here is part of the actual document i will be searching from

http://darthcircuit.mobstop.com/sample.xls

the original document has over 10,000 entries, but it has all of the people have died since 1892 or sometime.

i want to be able to search a name or a date and return all the records matching that criteria.

i have very limited asp skills
i also have limited c#, vb, and c++ skills.

i have been trying this for some time without results, and i am about to hit rock bottom.

also, my boss lost all of the copies of office we have, and now i am forced to use open office. I read that it probably would be easier to put it all into an access database, and i have tried doing so into the open office db, but really have no clue what is going on.

thank you in advance!

i greatly appreciate it!

if i didnt explain something well enough, please tell me, and i will do my best to try and explain it better.
sirpelidor
03-27-2006, 08:34 PM
is this what u are looking for?

http://www.aspfree.com/c/a/ASP.NET-Code/Read-Excel-files-from-ASPNET/
Darth Circuit
03-28-2006, 06:47 PM
hey thanks for the reply!

i think this will give me a better step forward than what i was looking at before! thank you so much.

i have been messing around with it a little, and will post my results once i have it figured out.

thank you again for the help, and i am sure i will have more questions by the time this is done
Darth Circuit
03-28-2006, 06:56 PM
ok, i have been playing with this a little, and i keep generating an error. i have changed the code a little, just stating where the file is and what reference it should use for the titles. here is my code.


<%@ Page Language="VB" Debug=true%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>

<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim myDataset As New DataSet()

''You can also use the Excel ODBC driver I believe - didn''t try though
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ge.xls;" & _
"Extended Properties=""Excel 8.0;"""

''You must use the $ after the object you reference in the spreadsheet
Dim myData As New OleDbDataAdapter("SELECT * FROM All", strConn)
myData.TableMappings.Add("Table", "ExcelTest")
myData.Fill(myDataset)

DataGrid1.DataSource = myDataset.Tables(0).DefaultView
DataGrid1.DataBind()
End Sub
</script>

<html>
<head></head>
<body>
<p><asp:Label id=Label1 runat="server">SpreadSheetContents:</asp:Label></p>
<asp:DataGrid id=DataGrid1 runat="server"/>\
</body>
</html>



here is the error i keep generating...


Server Error in '/WebSite3' Application.
--------------------------------------------------------------------------------

Syntax error in FROM clause.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in FROM clause.

Source Error:


Line 15: Dim myData As New OleDbDataAdapter("SELECT * FROM All", strConn)
Line 16: myData.TableMappings.Add("Table", "ExcelTest")
Line 17: myData.Fill(myDataset)
Line 18:
Line 19: DataGrid1.DataSource = myDataset.Tables(0).DefaultView


Source File: C:\Documents and Settings\Administrator.UINTAH\My Documents\Visual Studio 2005\WebSites\WebSite3\Default2.aspx Line: 17

Stack Trace:


[OleDbException (0x80040e14): Syntax error in FROM clause.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +82
ASP.default2_aspx.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator.UINTAH\My Documents\Visual Studio 2005\WebSites\WebSite3\Default2.aspx:17
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42



oh, and if it is important, i am using ie7 beta 2. i also tried it in firefox, but ended with the same results. i also tried the c# version and came upon the same problem. if i had the choice though, i understand C a little better than vb.
sirpelidor
03-28-2006, 08:33 PM
Hi, although I never try to read data off excel spreedsheet before, but I'm guessing your problem is:

"SELECT * FROM All"


i think "All" is not being recongized as a keyword, you gotta use the name of the sheet u are working with. e.g: [Shee1$]

what I normally would do, is try to understand what the code is doing first, before I copy to my applcation, that way I can avoid most of the expected problems.

Try the technique I use and see if that helps.
Darth Circuit
03-29-2006, 05:42 PM
"SELECT * FROM All"


Is this a sql statement? i recognized it from one of my sql workthroughs i tried in frontpage.

also, when i first tried the code, i left it as it was, just to see if it would work, except i changed the default directory, so i did try the [Sheet1$] thing and got the same error.

I am trying to understand the code, but i really have limited asp/c#/vb skills.
sirpelidor
03-29-2006, 05:55 PM
1) Is this a sql statement?

2) i did try the [Sheet1$] thing and got the same error.


1) yes, The OleDbDataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data. So it does take a SQL statement in order to retrieve and to save data.

the statement "Select * from All" says: show me a result list of every fields from my table named "All".

Your program crashed because u don't have a table call "All".

2) you got error because you don't have a table named [Sheet1$]
Darth Circuit
03-29-2006, 06:22 PM
Your program crashed because u don't have a table call "All".


in the original table, i do have a selection call "All". I was reading about this when i was using the sql solution, but when i slimmed it down, i didnt put the selection labels back in.
sirpelidor
03-30-2006, 01:09 AM
in the original table, i do have a selection call "All".

in that case, it shoule be:

'You must use the $ after the object you reference in the spreadsheet
Dim myData As New OleDbDataAdapter("SELECT * FROM [All$]", strConn)
Darth Circuit
03-30-2006, 05:43 PM
i just tried that, and i got the same error. just in case, i tried the other label names i have in the sheet. same error
sirpelidor
03-30-2006, 06:25 PM
i just tried that, and i got the same error.

These are the steps I did, and it worked:
1) I copied your excel file form this thread, and for the sake of keep things simple, I copied to C:\, so the exact path is: C:\sample.xls
2) I right click your file, choose property, granted permission for read and write access.
3) I created a asp.net web form, just to make sure it is working i have 1 datagrid control there call datagrid1
4)and it worked... here's the code from code behind...

webform1.aspx.vb (i assume you wanna see in vb since the examples have been done in vb)

Imports System.Data
Imports System.Data.OleDb

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myDataSet As New DataSet
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\sample.xls;" & _
"Extended Properties=Excel 8.0;"

Dim mydata As New OleDbDataAdapter("Select * from [Sheet1$]", strConn)
mydata.Fill(myDataSet)
DataGrid1.DataSource = myDataSet
Me.DataBind()
End Sub
Darth Circuit
03-30-2006, 06:59 PM
Woo!!!!

it worked!!!!

i think that the problem i had was with my spread sheet. it wouldnt work at first, but then i tried my sample, and left it the way that you had it it it worked!

thank you so very much.


ok, for the other part of my question.

do you have any idea of how i could have a search engine type thing that would allow people to search a name, and then output the results?

thank you again for all of the help.
sirpelidor
03-30-2006, 07:02 PM
ok, for the other part of my question.


e.g: if you wanna search name ABPLANALP, Emily Kraus

you change from Select * from [Sheet1$] to:

Select * from [Sheet1$] where DECEASED = 'ABPLANALP, Emily Kraus'
Darth Circuit
03-30-2006, 07:44 PM
would you like it more if started a new thread, like you said before you edited your post?
sirpelidor
03-31-2006, 02:15 PM
It would be easier for readers to search or to google, if each threads address to the a particular problem.

The reason I changed my mind and decided to answer within this thread is because your second question wasn't just a standard SQL statement question.

Sorry for the confusion
Darth Circuit
03-31-2006, 03:34 PM
np

ill just create a new thread