Difficulty reading from excel with .Net

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

Difficulty reading from excel with .Net

Postposted on Wed Feb 18, 2009 2:00 pm

Title says it all. I build a file uploaded that works fine, and upon receipt of a file, it passes the file location to the following function:
Code: Select all
Function fillColumnHeaders(filePath)
      Dim h as new hashtable
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader         
      if dr.read then            
      Dim strColNum
         for x = 0 to lookup.columns.count
            strColNum = "col" & x
            h.add(strColNum, dr(x))
            response.write (x & "&nbsp;" & h("col" & x) & "<br/>")
         next
      end if
      response.write("<br/>" & lookup.columns.count)
      cnExcel.Close()
      dr.Close()
   end function


It will write out the proper header row column names up to 9 (which I can't figure out what that is) and if you look at my for loop, the max is set to "lookup.columns.count" - which is what 9 equates to.... unfortunately my file has 11 columns, not nine. If I do lookup.columns.count + 2 if gets it all which leads me to believe that "lookup.columns.count" is not what I want. It needs to be dynamic, and obviously + 2 doesn't work for all files, just this one. I'm not sure what I need to be retrieving as I figured lookup.columns.count would give me the total columns in row 0 - it doesn't. Clearly I'm making a mistake here but it's not clear to me what, only where.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11884
Joined: Mon May 26, 2003 4:55 am
Location: Pittsburgh PA

Re: Difficulty reading from excel with .Net

Postposted on Wed Feb 18, 2009 2:27 pm

There's nothing wrong with treating an Excel sheet as JET db (as long as it truly contains data consistently laid out that way) but I assume you're going that route rather than using the .net Office assemblies for some reason?

Looking at your code (and keeping in mind it's been years since I did anything like this), these lines puzzle me
Code: Select all
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.rows(0)("TABLE_NAME") & "]", cnExcel)
What is lookup? Is it declared with a type somewhere else? I'm confused about what the source of this data is, I guess.

Strictly speaking, whenever you have a .Count property you should be iterating from 1 to Count, or from 0 to Count -1.

That you're off by two, or actually 3 (since you're going from 0 to Count) suggests that there's some problem with what you think that lookup variable is giving you.

Did you consider going with an OledbDataAdapter and just binding it to a DataGrid (which you could make read-only if you don't want users messing with the data) rather than manually outputting the rows of cells? This is a pretty primitive way of displaying data, though it might not matter if there aren't many rows.
UberGerbil
Gerbil Khan
 
Posts: 9927
Joined: Thu Jun 19, 2003 2:11 pm

Re: Difficulty reading from excel with .Net

Postposted on Wed Feb 18, 2009 2:37 pm

Thanks for the reply. Actually, the count is spot on. I was trying to figure out where the 9 was coming from so I did the following:

Code: Select all
if dr.read
         for each x in lookup.columns
            response.write(x.tostring & "<br/>")
         next
      end if


It actually prints out 9 different things,
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED


so that mystery is solved, but im unsure of how to access the actual count of the number of columns.Those 2 puzzling lines are responsible for creating the connection to my excel file, and then selecting everything in the "table" whose name I won't know, hence the lookup.rows(0)("TABLE_NAME"), which returns the table commonly called [sheet1$] but it could be renamed to something else.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11884
Joined: Mon May 26, 2003 4:55 am
Location: Pittsburgh PA

Re: Difficulty reading from excel with .Net

Postposted on Wed Feb 18, 2009 2:58 pm

UberGerbil wrote:There's nothing wrong with treating an Excel sheet as JET db (as long as it truly contains data consistently laid out that way) but I assume you're going that route rather than using the .net Office assemblies for some reason?


The PIA's require the Office to be installed to run. Their purpose is to act as a wrapper to make remote calls to manipulate the programs, similar to how VBA macros run.
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 686
Joined: Tue Mar 27, 2007 10:26 am
Location: Toronto, ON

Re: Difficulty reading from excel with .Net

Postposted on Wed Feb 18, 2009 3:39 pm

With anything to do with .NET and excel we use http://www.spreadsheetgear.com

Good for just getting data or writing custom excel files from your program. It does NOT require excel to be installed.
SN95G5v3/A64 3500+ 2.2ghz@1.2v 800mhz@0.8v/1gb Geil UltraX/Seagate 250gb/Seagate 320gb/Liteon DVD/XFX 6600GT/2x2005FPW
Rainbows lie in corded knots
While thunder wakes the sleeping crocs.
dragmor
Grand Gerbil Poohbah
 
Posts: 3577
Joined: Mon Sep 23, 2002 6:24 pm
Location: Oz

Re: Difficulty reading from excel with .Net

Postposted on Thu Feb 19, 2009 8:28 am

In case anyone was curious, I finished the first part this morning. I needed to access the FieldCount method of the OleDbDataReader, and in this scenario, it becomes necessary to use the -1 on the count because it returns the index out of bounds if you do not when accessing the last element.
Code: Select all
Function fillColumnHeaders(filePath)
      'Get all header row information
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader
      If dr.Read Then
         For x = 0 To dr.FieldCount-1
                Response.Write(dr(x) & "<br/>")
            Next
      end if      
      dr.Close()
      cnExcel.Close()      
   end function


Now, with this, It doesn't matter what the sheet name is called in the excel document, it will grab it regardless.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11884
Joined: Mon May 26, 2003 4:55 am
Location: Pittsburgh PA

Re: Difficulty reading from excel with .Net

Postposted on Thu Feb 19, 2009 1:58 pm

pete_roth wrote:In case anyone was curious, I finished the first part this morning. I needed to access the FieldCount method of the OleDbDataReader, and in this scenario, it becomes necessary to use the -1 on the count because it returns the index out of bounds if you do not when accessing the last element.
You know, a few years ago I would've been able to tell you that off the top of my head. But things fade from lack of use. Sorry.
UberGerbil
Gerbil Khan
 
Posts: 9927
Joined: Thu Jun 19, 2003 2:11 pm

Re: Difficulty reading from excel with .Net

Postposted on Mon Feb 23, 2009 2:38 pm

I have the entire thing working now. Upload a file, set column headers, allow them to do matching if their column headers aren't named the same thing, then display a preview dataGrid, and insert into my msSql db. I have one tiny minor annoyance left. When I create the dropdownlist items on the fly (see below) I do so in a loop, and outside the loop I do 2 things: First I add a blank value to the 0 position of each dropdown list that has a value of -1, and originally I had that selected. I then found out the client has a format for their clients so that in most cases the 0 position (or in my case now, the 1 position) will always be prefix, firstname is always 2, etc. So I wanted to default these selected Indices. Anyhow, my problem is that it won't work, when I set it, no matter what I try the last index set effects all the other indices - I am sure it has to be something stupid.

Code: Select all
 Function fillColumnHeaders(filePath)
      Dim dbConn as SqlConnection = new SqlConnection(configurationsettings.appsettings("connMK"))
      Dim rs as SqlDataReader
      Dim strSql as SqlCommand
      'first delete any existing records for this orderID if they exist.
      dbConn.open
      strSql = New SqlCommand("delete from tblMailingList where orderID ='" & session("orderID") & "'", dbConn)
      strSql.executeScalar
      dbConn.close   
      
      gridOuterHolder.visible = false
      listAccept.visible=false
      session("filePath") = filePath
      'remove any existing list items if they upload a different list
      prefix.items.clear()
      FirstName.items.clear()
      LastName.items.clear()
      title.items.clear()
      CompanyName.items.clear()
      Address.items.clear()
      Address2.items.clear()
      City.items.clear()
      State.items.clear()
      Zipcode.items.clear()      
      
      'Get all header row information
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader
      If dr.Read Then
      Try
         For x = 0 To dr.FieldCount-1
                Dim y as new ListItem            
            y.text = dr(x)
            y.value = x
            'Add the item to all the dropdowns
            Prefix.Items.Add(y)
            FirstName.Items.Add(y)
            LastName.Items.Add(y)
            Title.Items.Add(y)
            CompanyName.Items.Add(y)
            Address.Items.Add(y)
            Address2.Items.Add(y)
            City.Items.Add(y)
            State.Items.Add(y)
            Zipcode.Items.Add(y)            
            Next
         
         'Insert blank first selection option
         Prefix.Items.Insert(0,new ListItem("",-1,true))
         FirstName.Items.Insert(0,new ListItem("",-1,true))
         LastName.Items.Insert(0,new ListItem("",-1,true))
         Title.Items.Insert(0,new ListItem("",-1,true))
         CompanyName.Items.Insert(0,new ListItem("",-1,true))
         Address.Items.Insert(0,new ListItem("",-1,true))
         Address2.Items.Insert(0,new ListItem("",-1,true))
         City.Items.Insert(0,new ListItem("",-1,true))
         State.Items.Insert(0,new ListItem("",-1,true))
         Zipcode.Items.Insert(0,new ListItem("",-1,true))   
            
         'default dropdown selection to format specificed by massey
         prefix.selectedIndex = 1
         firstname.selectedIndex=2
         lastname.selectedIndex=3
         title.selectedIndex=4
         companyName.selectedIndex=5
         address.selectedIndex=6
         address2.selectedIndex=7
         city.selectedIndex=8
         state.selectedIndex=9
         zipcode.selectedIndex=10
         
      Catch e as exception
         errorText.text="There was a problem with the file you uploaded. Please contact customer support."
      finally
         dr.Close()
         cnExcel.Close()   
      end try   
      end if               
   end function
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11884
Joined: Mon May 26, 2003 4:55 am
Location: Pittsburgh PA


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 3 guests