[VB.NET] การเพิ่ม แก้ไข ลบข้อมูลที่อยู่ใน Google Sheets ผ่านทาง Windows Application

thongkorn · โพสต์ 2023-12-15 12:01:50

Add References ...

Sample Google Sheets ...

สำหรับโค้ดชุดนี้จะเป็นการเข้าถึงข้อมูลใน Google Sheets ผ่านทาง Windows Application โดยสามารถเพิ่ม แก้ไข และลบข้อมูลในแบบออนไลน์ได้ รวมไปถึงการตรวจสอบรหัส EmployeeID เพื่อไม่ให้เกิดการบันทึกข้อมูลซ้ำกันได้ โดยใช้วิธีการธรรมดาด้วยการนำค่าจากคุณสมบัติ TextBox Control จาก Text ไปเก็บไว้ใน Tag แล้วนำมาเปรียบเทียบค่ากัน ...

สิ่งที่ควรจะต้องทำ ...
1. รับไฟล์ Credentials จาก Google เพื่อกำหนดสิทธิ์การเข้าถึงชีต ... Youtube แสดงวิธีการขั้นตอนในการรับไฟล์ Credentials ...
2. สร้าง Google Sheets ขึ้นมาใหม่ จะได้ SpreadSheetID และแก้ไขชื่อไฟล์ หรือใน Google Sheets เรียกว่า Application Name
3. แก้ไขค่าตัวแปร ... ให้ตรงกับชีตของแต่ละคน

Dim Service As SheetsService
Dim SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
Dim ApplicationName = "SampleSheet" '// Same as Filename.
Dim SheetName As String = "Sheet1" '// Worksheet.
'// JSON credential file path.
Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"

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

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

Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Services
Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
'// Getting Credentials file (JSON) to contact Google Sheets with VB.NET
'// https://www.youtube.com/watch?v=xdYsctNAGEE
'// Sample Google Sheets for this code.
'// 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" '// Same as Filename.
Dim SheetName As String = "Sheet1" '// Worksheet.
'// JSON credential file path.
Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
'// Add new or Edit data.
Dim blnNewData As Boolean = False '// Edit mode.
' / ------------------------------------------------------------------------------------------------
'// Initialize Google Sheets API
Public Sub Credentials()
'// Load credentials from JSON file
Dim credential = GoogleCredential.FromFile(CredentialFilePath).CreateScoped(SheetsService.Scope.Spreadsheets)
'// Create Google Sheets API service
Service = New SheetsService(New BaseClientService.Initializer() With {
.HttpClientInitializer = credential,
.ApplicationName = ApplicationName
})
End Sub
' / ------------------------------------------------------------------------------------------------
' / S T A R T ... H E R E
' / ------------------------------------------------------------------------------------------------
Private Sub frmGoogleSheet_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Call Credentials()
Call LoadData()
Call SetupGridView()
Call NewMode()
End Sub
Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
Call LoadData()
End Sub
' / ------------------------------------------------------------------------------------------------
Private Sub LoadData()
'// Specify the range of cells you want to retrieve, e.g., "Sheet1!A1:C10"
Dim Range As String = SheetName + "!A1:Z100"
'// 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
'//
End Sub
' / ------------------------------------------------------------------------------------------------
' / NEW DATA
' / ------------------------------------------------------------------------------------------------
Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
blnNewData = True '// Add New Mode
Call EditMode()
txtEmployeeID.Focus()
End Sub
Private Sub dgvData_DoubleClick(sender As Object, e As EventArgs) Handles dgvData.DoubleClick
If dgvData.RowCount = 0 Then Return
'// EDIT MODE
blnNewData = False
Call EditMode()
'//
txtEmployeeID.Text = dgvData.Item(0, dgvData.CurrentRow.Index).Value
txtEmployeeID.Tag = txtEmployeeID.Text '// Copy Text to Tag for check exist ID.
'//
txtFullname.Text = dgvData.Item(1, dgvData.CurrentRow.Index).Value
txtPosition.Text = dgvData.Item(2, dgvData.CurrentRow.Index).Value
txtDepartment.Text = dgvData.Item(3, dgvData.CurrentRow.Index).Value
dtpHireDate.Text = dgvData.Item(4, dgvData.CurrentRow.Index).Value
txtSalary.Text = Format(CDbl(dgvData.Item(5, dgvData.CurrentRow.Index).Value), "0.00")
'//
txtEmployeeID.Focus()
End Sub
' / ------------------------------------------------------------------------------------------------
' / UPDATE DATA.
' / ------------------------------------------------------------------------------------------------
Sub UpdateData()
'// Initialize Google Sheets API
Call Credentials()
'//
Dim SelectedRowIndex As Integer = -1
Dim RowUpdate As Integer
Try
'// NEW DATA
If blnNewData Then
RowUpdate = dgvData.Rows.Count
'// UPDATE
Else
'// Get the index of the selected row.
SelectedRowIndex = dgvData.SelectedRows(0).Index + 1
'// Because the first row of Google Sheets has the Columns name. So we need to increase the value by 1.
RowUpdate = SelectedRowIndex + 1
End If
'// UPDATE
Dim Range As String = SheetName + "!A" & RowUpdate & ":Z" & RowUpdate ' Update with your sheet name and range
Dim valueRange As New ValueRange()
valueRange.Values = New List(Of IList(Of Object)) From {New List(Of Object) From {
txtEmployeeID.Text.Trim,
txtFullname.Text.Trim,
txtPosition.Text.Trim,
txtDepartment.Text.Trim,
Format(dtpHireDate.Value, "dd/MM/yyyy"),
txtSalary.Text
}}
'// If New Data then Append Request.
If blnNewData Then
Dim AppendRequest As SpreadsheetsResource.ValuesResource.AppendRequest = Service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, Range)
AppendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED
Dim AppendResponse As AppendValuesResponse = AppendRequest.Execute()
'// Update Request.
Else
Dim UpdateRequest As SpreadsheetsResource.ValuesResource.UpdateRequest = Service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, Range)
UpdateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED
Dim UpdateResponse As UpdateValuesResponse = UpdateRequest.Execute()
End If
Call LoadData()
MessageBox.Show("UPDATE COMPLETE.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
' / ------------------------------------------------------------------------------------------------
' / Verify accuracy before saving data.
' / ------------------------------------------------------------------------------------------------
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
If txtEmployeeID.Text = "" Or txtEmployeeID.Text.Trim.Length = 0 Then
MessageBox.Show("Please enter EmployeeID.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtEmployeeID.Focus()
Return
End If
'// Check exist EmployeeID.
If txtEmployeeID.Text <> txtEmployeeID.Tag Then
'// No duplicate EmployeeID found.
If Not CheckEmployeeID(txtEmployeeID.Text.Trim) Then
Call UpdateData()
Call NewMode()
End If
'// Shows that the original data set has been updated.
Else
Call UpdateData()
Call NewMode()
End If
End Sub
' / ------------------------------------------------------------------------------------------------
' / Check exist EmployeeID.
' / ------------------------------------------------------------------------------------------------
Function CheckEmployeeID(ByVal TargetData As String) As Boolean
Dim range As String = "Sheet1!A:A" ' Adjust the sheet name and column as needed
Try
' Check for data in the specified column.
Dim request As SpreadsheetsResource.ValuesResource.GetRequest = Service.Spreadsheets.Values.Get(SpreadsheetId, range)
Dim response As ValueRange = request.Execute()
' Process the data.
If response IsNot Nothing AndAlso response.Values IsNot Nothing Then
For Each row As IList(Of Object) In response.Values
If row.Count > 0 AndAlso row(0).ToString = TargetData Then
' Data found in the specified column.
MessageBox.Show("Found " & row(0).ToString & " in Column A.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return True
End If
Next
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'//
Return False
End Function
' / ------------------------------------------------------------------------------------------------
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
'// If Edit Data Mode
If btnDelete.Text = "DELETE" Then
Call DeleteData()
End If
Call NewMode()
End Sub
' / ------------------------------------------------------------------------------------------------
' / DELETE DATA
' / ------------------------------------------------------------------------------------------------
Sub DeleteData()
'// Initialize Google Sheets API
Call Credentials()
'//
Dim SelectedRowIndex As Integer = -1
If dgvData.SelectedRows.Count <= 0 Then Return
'// Get the index of the selected row.
SelectedRowIndex = dgvData.SelectedRows(0).Index + 1
'// Because the first row of Google Sheets has the Columns name. So we need to increase the value by 1.
Dim RowToRemove As Integer = SelectedRowIndex + 1
Try
'// Remove the specified row
Dim request As New BatchUpdateSpreadsheetRequest()
request.Requests = New List(Of Request)()
request.Requests.Add(New Request With {
.DeleteDimension = New DeleteDimensionRequest With {
.Range = New DimensionRange With {
.SheetId = 0, ' 0 is the default sheet ID, adjust if needed
.Dimension = "ROWS",
.StartIndex = RowToRemove - 1,
.EndIndex = RowToRemove
}
}
})
Dim EmpID As String = dgvData.Item(0, dgvData.CurrentRow.Index).Value
Dim Result As Byte = MessageBox.Show("Are you sure you want to delete " & EmpID & " the data?", "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
If Result = DialogResult.Yes Then
Service.Spreadsheets.BatchUpdate(request, SpreadsheetId).Execute()
Call LoadData()
MessageBox.Show("DELETE COMPLETE.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
' / ------------------------------------------------------------------------------------------------
' / Add New Mode
' / ------------------------------------------------------------------------------------------------
Private Sub NewMode()
'// Clear all TextBox.
For Each c In GroupBox1.Controls
If TypeOf c Is TextBox Then
DirectCast(c, TextBox).Clear()
DirectCast(c, TextBox).Enabled = False
End If
Next
dtpHireDate.Value = Now()
dtpHireDate.Enabled = False
'//
btnAdd.Enabled = True
btnSave.Enabled = False
btnDelete.Enabled = True
btnDelete.Text = "DELETE"
btnExit.Enabled = True
'//
End Sub
' / ------------------------------------------------------------------------------------------------
' / Edit Data Mode
' / ------------------------------------------------------------------------------------------------
Private Sub EditMode()
'// Clear all TextBox
For Each c In GroupBox1.Controls
If TypeOf c Is TextBox Then
DirectCast(c, TextBox).Enabled = True
End If
Next
btnAdd.Enabled = False
btnSave.Enabled = True
btnDelete.Enabled = True
btnDelete.Text = "CANCEL"
btnExit.Enabled = False
'//
dtpHireDate.Enabled = True
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
Private Sub txtSalary_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtSalary.KeyPress
If Asc(e.KeyChar) = 13 Then
e.Handled = True
SendKeys.Send("{TAB}")
Else
e.Handled = CheckCurrency(Asc(e.KeyChar), txtSalary.Text)
End If
End Sub
Private Sub txtEmployeeID_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtEmployeeID.KeyPress
If Asc(e.KeyChar) = 13 Then
e.Handled = True
SendKeys.Send("{TAB}")
End If
End Sub
Private Sub txtFullname_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtFullname.KeyPress
If Asc(e.KeyChar) = 13 Then
e.Handled = True
SendKeys.Send("{TAB}")
End If
End Sub
Private Sub txtPosition_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtPosition.KeyPress
If Asc(e.KeyChar) = 13 Then
e.Handled = True
SendKeys.Send("{TAB}")
End If
End Sub
Private Sub txtDepartment_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtDepartment.KeyPress
If Asc(e.KeyChar) = 13 Then
e.Handled = True
SendKeys.Send("{TAB}")
End If
End Sub
Private Sub dtpHireDate_KeyDown(sender As Object, e As KeyEventArgs) Handles dtpHireDate.KeyDown
If e.KeyCode = Keys.Enter Then
e.Handled = True
SendKeys.Send("{TAB}")
End If
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
'// Block the click on the column header for sort.
For i As Byte = 0 To dgvData.ColumnCount - 1
dgvData.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
Next
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
End Class

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

โค้ดในส่วนของโมดูลฟังค์ชั่น ... modFunction.vb ...

Module modFucntion
' / --------------------------------------------------------------------------------
' / 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
' / --------------------------------------------------------------------------------
' / Function to enter only numeric and decimal values.
Function CheckCurrency(index As Integer, tmpStr As String) As Boolean
CheckCurrency = False
Select Case index
Case 48 To 57 ' เลข 0 - 9
' Allowed "."
Case 46
' can present "." only one
If InStr(tmpStr, ".") Then CheckCurrency = True
Case 8, 13 ' Backspace = 8, Enter = 13
Case Else
CheckCurrency = True
End Select
End Function
End Module

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

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

		จำสถานะนี้ไว้ในครั้งหน้า	ลืมรหัสผ่าน
รหัสผ่าน			ลงทะเบียน

[VB.NET] การเพิ่ม แก้ไข ลบข้อมูลที่อยู่ใน Google Sheets ผ่านทาง Windows Application

ขออภัย! โพสต์นี้มีไฟล์แนบหรือรูปภาพที่ไม่ได้รับอนุญาตให้คุณเข้าถึง