-
Dec 4th, 2013, 04:27 PM
#1
Thread Starter
Fanatic Member
Best way to create class to return data as List object
I have a Google spreadsheet that I am reading data from and I want to load the data into a list object so I can use but I am having some issues.
Google Spreadsheet
-Worksheet name: Charts
-Tab: Color_Summarized
-Column 1: ColorSummary
-Column 2: ChildID
-Column 3: ParentID
sample rows:
Aqua|29|28
Blue|49|23
Yellow|55|28
My results are
Yellow Color_Summarized FH 55 28
QUESTION:
How do I need to set it up so it keeps all the data loaded instead of just the last instance? Is there a better way then putting in class?
My code:
Code:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports Google.GData.Spreadsheets
Imports Google.Spreadsheets
Imports Google.GData.Client
Imports System.Configuration
Partial Class GoogTest
Inherits System.Web.UI.Page
Dim service As SpreadsheetsService
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Try
For Each gl As GoogleCategoryList In GetGoogleCategoryList()
litMsg.Text = gl.Attribute & " " & gl.AttributeType & " " & gl.CategoryVol & " " & gl.ChildID & " " & gl.ParentID & "<br />"
Next
Catch ex As Exception
litMsg.Text = ex.ToString
End Try
End Sub
#Region " GetGoogleCategoryList "
Public Function GetGoogleCategoryList() As List(Of GoogleCategoryList)
'Create an GoogleCategoryList Object
Dim MyGoogleCategoryList As New GoogleCategoryList()
'Create a List of objects of type GoogleCategoryList
Dim MyGoogleCategoryListLst As New List(Of GoogleCategoryList)
Dim sWorksheetURL As String = "https://spreadsheets.google.com/feeds/worksheets/<yourowngoogleid>/private/full"
Dim sAppName As String = "Project_App_Name-v1"
Try
If service Is Nothing Then
service = New SpreadsheetsService(sAppName)
'if using 2-step authentication then need to use security created password
service.setUserCredentials("..@gmail.com", "abcdefghijklmnopqrstuvwxyz")
End If
'Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
Dim sheetQuery As SpreadsheetQuery = New SpreadsheetQuery
'Make a request to the API and get all spreadsheets.
Dim ssfSpreadsheetFeed As SpreadsheetFeed = service.Query(sheetQuery)
If ssfSpreadsheetFeed.Entries.Count > 0 Then
'Choose a spreadsheet more intelligently based on your app's needs
Dim sseSpreadSheet As SpreadsheetEntry = ssfSpreadsheetFeed.Entries(0)
Dim worksheetQuery As WorksheetQuery = New WorksheetQuery(sWorksheetURL)
Dim feed As WorksheetFeed = service.Query(worksheetQuery)
Dim wfEntries As AtomEntryCollection = feed.Entries
For Each wsEntry As WorksheetEntry In wfEntries
' Get the list feed URI
Dim listLink As AtomLink = wsEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, AtomLink.ATOM_TYPE)
Dim lqQuery As ListQuery = New ListQuery(listLink.HRef.ToString)
'lqQuery.setSpreadsheetQuery("name = 'person x' and age > 25")
Dim lfResult As ListFeed = service.Query(lqQuery)
'create new instance of object
MyGoogleCategoryList = New GoogleCategoryList()
Select Case wsEntry.Title.Text
Case "Color_Summarized"
'Iterate through each row, add column A1 to hashtable
For Each leRow As ListEntry In lfResult.Entries
MyGoogleCategoryList.Attribute = leRow.Elements(0).Value
MyGoogleCategoryList.AttributeType = "Color_Summarized"
MyGoogleCategoryList.CategoryVol = "FH"
'wasn't sure if this is the best way of handling if the value was empty
Try
MyGoogleCategoryList.ChildID = leRow.Elements(1).Value
Catch ex As Exception
MyGoogleCategoryList.ChildID = ""
End Try
Try
MyGoogleCategoryList.ParentID = leRow.Elements(2).Value
Catch ex As Exception
MyGoogleCategoryList.ParentID = ""
End Try
MyGoogleCategoryListLst.Add(MyGoogleCategoryList)
Next
End Select
Next
End If
Catch ex As Exception
MyGoogleCategoryList = New GoogleCategoryList()
MyGoogleCategoryList.Attribute = ex.ToString
MyGoogleCategoryList.AttributeType = "Error"
MyGoogleCategoryList.CategoryVol = "ER"
MyGoogleCategoryList.ChildID = 0
MyGoogleCategoryList.ParentID = 0
MyGoogleCategoryListLst.Add(MyGoogleCategoryList)
End Try
Return MyGoogleCategoryListLst
End Function
#End Region
End Class
Public Class GoogleCategoryList
#Region " Instance variables "
Dim _Attribute As String = String.Empty
Dim _ChildID As String = String.Empty
Dim _ParentID As String = String.Empty
Dim _AttributeType As String = String.Empty
Dim _CategoryVol As String = String.Empty
#End Region
#Region " New "
Public Sub New()
'Default class Constructor
End Sub
#End Region
#Region " Properties "
Public Property Attribute As String
Get
Return _Attribute
End Get
Set(value As String)
_Attribute = value
End Set
End Property
Public Property AttributeType As String
Get
Return _AttributeType
End Get
Set(value As String)
_AttributeType = value
End Set
End Property
Public Property ChildID As String
Get
Return _ChildID
End Get
Set(value As String)
_ChildID = value
End Set
End Property
Public Property ParentID As String
Get
Return _ParentID
End Get
Set(value As String)
_ParentID = value
End Set
End Property
Public Property CategoryVol As String
Get
Return _CategoryVol
End Get
Set(value As String)
_CategoryVol = value
End Set
End Property
#End Region
End Class
-
Dec 4th, 2013, 08:33 PM
#2
Re: Best way to create class to return data as List object
well for starters, you can stop overwritting it each time you're in the loop
Code:
For Each gl As GoogleCategoryList In GetGoogleCategoryList()
litMsg.Text = gl.Attribute & " " & gl.AttributeType & " " & gl.CategoryVol & " " & gl.ChildID & " " & gl.ParentID & "<br />"
Next
Each time you go through the loop, you overwriting what you did in the last loop... I suspect that isn't what you want... but rather you should be ADDING to litMsg instead.
-tg
-
Dec 4th, 2013, 11:13 PM
#3
Thread Starter
Fanatic Member
Re: Best way to create class to return data as List object
Embarrassing :-) That did help but now my result count is right but the output for each is the last row.
-
Dec 4th, 2013, 11:35 PM
#4
Thread Starter
Fanatic Member
Re: Best way to create class to return data as List object
Figured it out.
Needed to do it like so:
Code:
For Each leRow As ListEntry In lfResult.Entries
'create new instance of object
MyGoogleCategoryList = New GoogleCategoryList()
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|