[VB.NET] การอ่านข้อมูลจาก Google Sheets เพื่อนำมาแสดงผลลงในตารางกริด
http://www.g2gsoft.com/webboard/images/VBNet/googlesheetbasic.pnghttp://www.g2gsoft.com/webboard/images/VBNet/googlesheetreference.png
การอ่านข้อมูลจาก Google Sheets เพื่อนำมาแสดงผลลงในตารางกริด อย่างแรกเราต้องสร้างไฟล์ Credentials ในการกำหนดสิทธิ์ในการเข้าถึงข้อมูลจาก Google Sheets เสียก่อน ...
http://www.g2gsoft.com/webboard/images/VBNet/googlesheetcredential.png
Youtube แสดงวิธีการขั้นตอนในการรับไฟล์ Credentials ...
ตัวอย่าง Google Sheets ซึ่งตอนนี้แอดมินได้เปิดให้เป็นแบบ Editor เพื่อให้ได้ลองทำการเขียนอ่านข้อมูลได้ ...
มาดูโค้ดฉบับเต็มกันเถอะ ...
Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Services
Imports Google.Apis.Sheets.v4
'// Sample Google Sheet.
'// https://docs.google.com/spreadsheets/d/1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs/edit#gid=0
Public Class frmGoogleSheet
Dim Service As SheetsService
Dim SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
Dim ApplicationName = "SampleSheet"
Dim SheetName As String = "Sheet1"
'// JSON credential file path.
Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
'//
Private Sub frmGoogleSheet_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Call Credentials()
Call LoadData()
Call SetupGridView()
End Sub
' / ------------------------------------------------------------------------------------------------
'// Initialize Google Sheets API.
'// Must be used every time to read and write data.
' / ------------------------------------------------------------------------------------------------
Sub Credentials()
'// Load credentials from JSON file.
Dim credential = GoogleCredential.FromFile(CredentialFilePath).CreateScoped(SheetsService.Scope.SpreadsheetsReadonly)
'// Create Google Sheets API service
Service = New SheetsService(New BaseClientService.Initializer() With {
.HttpClientInitializer = credential,
.ApplicationName = ApplicationName
})
End Sub
' / ------------------------------------------------------------------------------------------------
Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
Call LoadData()
End Sub
' / ------------------------------------------------------------------------------------------------
' / LOAD DATA.
' / ------------------------------------------------------------------------------------------------
Private Sub LoadData()
'// Specify the range of cells you want to retrieve, e.g., "Sheet1!A1:C10"
Dim Range As String = SheetName + "!A1:Z100"
Try
'// Make the request to the Sheets API
Dim Request = Service.Spreadsheets.Values.Get(SpreadsheetId, Range)
Dim Response = Request.Execute()
'// Process the response and populate the DataGridView.
If Response.Values IsNot Nothing AndAlso Response.Values.Any() Then
dgvData.Rows.Clear()
dgvData.Columns.Clear()
'// Assuming the first row contains headers.
For Each Header In Response.Values.First()
dgvData.Columns.Add(Header.ToString(), Header.ToString())
Next
'// Populate data rows.
For RowIndex As Integer = 1 To Response.Values.Count - 1
Dim Row = Response.Values(RowIndex)
dgvData.Rows.Add(Row.ToArray())
Next
End If
'//
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub frmGoogleSheet_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
Me.Dispose()
GC.SuppressFinalize(Me)
End
End Sub
#Region "DATAGRIDVIEW"
'// Initialized DataGridView.
Private Sub SetupGridView()
With dgvData
.RowHeadersVisible = True
.AllowUserToAddRows = False
.AllowUserToDeleteRows = False
.AllowUserToResizeRows = False
.MultiSelect = False
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
.ReadOnly = True
'// Data rows
.Font = New Font("Tahoma", 10)
.RowTemplate.MinimumHeight = 32
.RowTemplate.Height = 32
'// Autosize Column
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
.EnableHeadersVisualStyles = False
'// Header
With .ColumnHeadersDefaultCellStyle
.BackColor = System.Drawing.Color.SeaGreen
.ForeColor = System.Drawing.Color.White
.Font = New Font(dgvData.Font, FontStyle.Bold)
End With
'// Before you can adjust the height of the row.
.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
.ColumnHeadersHeight = 32
'/ Accept changes to the header's background color.
.EnableHeadersVisualStyles = False
'// Even-Odd Color of Rows.
.AlternatingRowsDefaultCellStyle.BackColor = System.Drawing.Color.Beige
End With
End Sub
Private Sub dgvData_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgvData.RowPostPaint
'// Display row numbers in row headers
Using b As New SolidBrush(dgvData.RowHeadersDefaultCellStyle.ForeColor)
e.Graphics.DrawString((e.RowIndex + 1).ToString(), dgvData.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 12, e.RowBounds.Location.Y + 8)
End Using
End Sub
#End Region
#Region "FUNCTION"
' / --------------------------------------------------------------------------------
' / Get my project path
' / AppPath = C:\My Project\bin\debug
' / Replace "\bin\debug" with "\"
' / Return : C:\My Project\
Function MyPath(AppPath As String) As String
'/ Return Value
MyPath = AppPath.ToLower.Replace("\bin\debug", "\").Replace("\bin\release", "\").Replace("\bin\x86\debug", "\")
'// If not found folder then put the \ (BackSlash ASCII Code = 92) at the end.
If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
End Function
#End Region
End Class
ดาวน์โหลดโค้ดฉบับเต็ม VB.NET (2017) และ .Net Framework 4.5+ ...
หน้า:
[1]