[VB.NET] การอ่านข้อมูลจาก Google Sheets เพื่อนำมาแสดงผลลงในตารางกริด

thongkorn

การอ่านข้อมูลจาก Google Sheets เพื่อนำมาแสดงผลลงในตารางกริด อย่างแรกเราต้องสร้างไฟล์ Credentials ในการกำหนดสิทธิ์ในการเข้าถึงข้อมูลจาก Google Sheets เสียก่อน ...



Youtube แสดงวิธีการขั้นตอนในการรับไฟล์ Credentials ...

ตัวอย่าง Google Sheets ซึ่งตอนนี้แอดมินได้เปิดให้เป็นแบบ Editor เพื่อให้ได้ลองทำการเขียนอ่านข้อมูลได้ ...

มาดูโค้ดฉบับเต็มกันเถอะ ...

1. Imports Google.Apis.Auth.OAuth2
   
2. Imports Google.Apis.Services
   
3. Imports Google.Apis.Sheets.v4
   
4. 
   
5. '// Sample Google Sheet.
   
6. '// https://docs.google.com/spreadsheets/d/1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs/edit#gid=0
   
7. 
   
8. Public Class frmGoogleSheet
   
9. 
   
10.     Dim Service As SheetsService
    
11.     Dim SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
    
12.     Dim ApplicationName = "SampleSheet"
    
13.     Dim SheetName As String = "Sheet1"
    
14.     '// JSON credential file path.
    
15.     Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
    
16.     '//
    
17.     Private Sub frmGoogleSheet_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
18.         Call Credentials()
    
19.         Call LoadData()
    
20.         Call SetupGridView()
    
21.     End Sub
    
22. 
    
23.     ' / ------------------------------------------------------------------------------------------------
    
24.     '// Initialize Google Sheets API.
    
25.     '// Must be used every time to read and write data.
    
26.     ' / ------------------------------------------------------------------------------------------------
    
27.     Sub Credentials()
    
28.         '// Load credentials from JSON file.
    
29.         Dim credential = GoogleCredential.FromFile(CredentialFilePath).CreateScoped(SheetsService.Scope.SpreadsheetsReadonly)
    
30.         '// Create Google Sheets API service
    
31.         Service = New SheetsService(New BaseClientService.Initializer() With {
    
32.             .HttpClientInitializer = credential,
    
33.             .ApplicationName = ApplicationName
    
34.         })
    
35.     End Sub
    
36. 
    
37.     ' / ------------------------------------------------------------------------------------------------
    
38.     Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
    
39.         Call LoadData()
    
40.     End Sub
    
41. 
    
42.     ' / ------------------------------------------------------------------------------------------------
    
43.     ' / LOAD DATA.
    
44.     ' / ------------------------------------------------------------------------------------------------
    
45.     Private Sub LoadData()
    
46.         '// Specify the range of cells you want to retrieve, e.g., "Sheet1!A1:C10"
    
47.         Dim Range As String = SheetName + "!A1:Z100"
    
48.         Try
    
49.             '// Make the request to the Sheets API
    
50.             Dim Request = Service.Spreadsheets.Values.Get(SpreadsheetId, Range)
    
51.             Dim Response = Request.Execute()
    
52.             '// Process the response and populate the DataGridView.
    
53.             If Response.Values IsNot Nothing AndAlso Response.Values.Any() Then
    
54.                 dgvData.Rows.Clear()
    
55.                 dgvData.Columns.Clear()
    
56.                 '// Assuming the first row contains headers.
    
57.                 For Each Header In Response.Values.First()
    
58.                     dgvData.Columns.Add(Header.ToString(), Header.ToString())
    
59.                 Next
    
60.                 '// Populate data rows.
    
61.                 For RowIndex As Integer = 1 To Response.Values.Count - 1
    
62.                     Dim Row = Response.Values(RowIndex)
    
63.                     dgvData.Rows.Add(Row.ToArray())
    
64.                 Next
    
65.             End If
    
66.             '//
    
67.         Catch ex As Exception
    
68.             MessageBox.Show(ex.Message)
    
69.         End Try
    
70.     End Sub
    
71. 
    
72.     Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
    
73.         Me.Close()
    
74.     End Sub
    
75. 
    
76.     Private Sub frmGoogleSheet_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
    
77.         Me.Dispose()
    
78.         GC.SuppressFinalize(Me)
    
79.         End
    
80.     End Sub
    
81. 
    
82. #Region "DATAGRIDVIEW"
    
83.     '// Initialized DataGridView.
    
84.     Private Sub SetupGridView()
    
85.         With dgvData
    
86.             .RowHeadersVisible = True
    
87.             .AllowUserToAddRows = False
    
88.             .AllowUserToDeleteRows = False
    
89.             .AllowUserToResizeRows = False
    
90.             .MultiSelect = False
    
91.             .SelectionMode = DataGridViewSelectionMode.FullRowSelect
    
92.             .ReadOnly = True
    
93.             '// Data rows
    
94.             .Font = New Font("Tahoma", 10)
    
95.             .RowTemplate.MinimumHeight = 32
    
96.             .RowTemplate.Height = 32
    
97.             '// Autosize Column
    
98.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    
99.             .EnableHeadersVisualStyles = False
    
100.             '// Header
     
101.             With .ColumnHeadersDefaultCellStyle
     
102.                 .BackColor = System.Drawing.Color.SeaGreen
     
103.                 .ForeColor = System.Drawing.Color.White
     
104.                 .Font = New Font(dgvData.Font, FontStyle.Bold)
     
105.             End With
     
106.             '// Before you can adjust the height of the row.
     
107.             .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
     
108.             .ColumnHeadersHeight = 32
     
109.             '/ Accept changes to the header's background color.
     
110.             .EnableHeadersVisualStyles = False
     
111.             '// Even-Odd Color of Rows.
     
112.             .AlternatingRowsDefaultCellStyle.BackColor = System.Drawing.Color.Beige
     
113.         End With
     
114.     End Sub
     
115. 
     
116.     Private Sub dgvData_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgvData.RowPostPaint
     
117.         '// Display row numbers in row headers
     
118.         Using b As New SolidBrush(dgvData.RowHeadersDefaultCellStyle.ForeColor)
     
119.             e.Graphics.DrawString((e.RowIndex + 1).ToString(), dgvData.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 12, e.RowBounds.Location.Y + 8)
     
120.         End Using
     
121.     End Sub
     
122. #End Region
     
123. 
     
124. #Region "FUNCTION"
     
125.     ' / --------------------------------------------------------------------------------
     
126.     ' / Get my project path
     
127.     ' / AppPath = C:\My Project\bin\debug
     
128.     ' / Replace "\bin\debug" with ""
     
129.     ' / Return : C:\My Project\
     
130.     Function MyPath(AppPath As String) As String
     
131.         '/ Return Value
     
132.         MyPath = AppPath.ToLower.Replace("\bin\debug", "").Replace("\bin\release", "").Replace("\bin\x86\debug", "")
     
133.         '// If not found folder then put the \ (BackSlash ASCII Code = 92) at the end.
     
134.         If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
     
135.     End Function
     
136. #End Region
     
137. End Class

คัดลอกไปที่คลิปบอร์ด

ดาวน์โหลดโค้ดฉบับเต็ม VB.NET (2017) และ .Net Framework 4.5+ ...