ชุมชนคนรักภาษาเบสิค - Visual Basic Community

 ลืมรหัสผ่าน
 ลงทะเบียน
ค้นหา
ดู: 582|ตอบกลับ: 0

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

[คัดลอกลิงก์]

308

กระทู้

499

โพสต์

6025

เครดิต

ผู้ดูแลระบบ

ทองก้อน ทับทิมกรอบ

Rank: 9Rank: 9Rank: 9

เครดิต
6025




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. แก้ไขค่าตัวแปร ... ให้ตรงกับชีตของแต่ละคน
  1.     Dim Service As SheetsService
  2.     Dim SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
  3.     Dim ApplicationName = "SampleSheet" '// Same as Filename.
  4.     Dim SheetName As String = "Sheet1"  '// Worksheet.
  5.     '// JSON credential file path.
  6.     Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
คัดลอกไปที่คลิปบอร์ด


มาดูโค้ดฉบับเต็มกันเถอะ ...
  1. Imports Google.Apis.Auth.OAuth2
  2. Imports Google.Apis.Services
  3. Imports Google.Apis.Sheets.v4
  4. Imports Google.Apis.Sheets.v4.Data

  5. '// Getting Credentials file (JSON) to contact Google Sheets with VB.NET
  6. '// https://www.youtube.com/watch?v=xdYsctNAGEE

  7. '// Sample Google Sheets for this code.
  8. '// https://docs.google.com/spreadsheets/d/1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs/edit#gid=0
  9. Public Class frmGoogleSheet

  10.     Dim Service As SheetsService
  11.     Dim SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
  12.     Dim ApplicationName = "SampleSheet" '// Same as Filename.
  13.     Dim SheetName As String = "Sheet1"  '// Worksheet.
  14.     '// JSON credential file path.
  15.     Dim CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
  16.     '// Add new or Edit data.
  17.     Dim blnNewData As Boolean = False   '// Edit mode.

  18.     ' / ------------------------------------------------------------------------------------------------
  19.     '// Initialize Google Sheets API
  20.     Public Sub Credentials()
  21.         '// Load credentials from JSON file
  22.         Dim credential = GoogleCredential.FromFile(CredentialFilePath).CreateScoped(SheetsService.Scope.Spreadsheets)
  23.         '// Create Google Sheets API service
  24.         Service = New SheetsService(New BaseClientService.Initializer() With {
  25.             .HttpClientInitializer = credential,
  26.             .ApplicationName = ApplicationName
  27.         })
  28.     End Sub

  29.     ' / ------------------------------------------------------------------------------------------------
  30.     ' / S T A R T ... H E R E
  31.     ' / ------------------------------------------------------------------------------------------------
  32.     Private Sub frmGoogleSheet_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  33.         Call Credentials()
  34.         Call LoadData()
  35.         Call SetupGridView()
  36.         Call NewMode()
  37.     End Sub

  38.     Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
  39.         Call LoadData()
  40.     End Sub

  41.     ' / ------------------------------------------------------------------------------------------------
  42.     Private Sub LoadData()
  43.         '// Specify the range of cells you want to retrieve, e.g., "Sheet1!A1:C10"
  44.         Dim Range As String = SheetName + "!A1:Z100"
  45.         '// Make the request to the Sheets API
  46.         Dim Request = Service.Spreadsheets.Values.Get(SpreadsheetId, Range)
  47.         Dim Response = Request.Execute()

  48.         '// Process the response and populate the DataGridView.
  49.         If Response.Values IsNot Nothing AndAlso Response.Values.Any() Then
  50.             dgvData.Rows.Clear()
  51.             dgvData.Columns.Clear()
  52.             '// Assuming the first row contains headers
  53.             For Each Header In Response.Values.First()
  54.                 dgvData.Columns.Add(Header.ToString(), Header.ToString())
  55.             Next
  56.             '// Populate data rows.
  57.             For RowIndex As Integer = 1 To Response.Values.Count - 1
  58.                 Dim Row = Response.Values(RowIndex)
  59.                 dgvData.Rows.Add(Row.ToArray())
  60.             Next
  61.         End If
  62.         '//
  63.     End Sub

  64.     ' / ------------------------------------------------------------------------------------------------
  65.     ' / NEW DATA
  66.     ' / ------------------------------------------------------------------------------------------------
  67.     Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
  68.         blnNewData = True  '// Add New Mode
  69.         Call EditMode()
  70.         txtEmployeeID.Focus()
  71.     End Sub

  72.     Private Sub dgvData_DoubleClick(sender As Object, e As EventArgs) Handles dgvData.DoubleClick
  73.         If dgvData.RowCount = 0 Then Return
  74.         '// EDIT MODE
  75.         blnNewData = False
  76.         Call EditMode()
  77.         '//
  78.         txtEmployeeID.Text = dgvData.Item(0, dgvData.CurrentRow.Index).Value
  79.         txtEmployeeID.Tag = txtEmployeeID.Text  '// Copy Text to Tag for check exist ID.
  80.         '//
  81.         txtFullname.Text = dgvData.Item(1, dgvData.CurrentRow.Index).Value
  82.         txtPosition.Text = dgvData.Item(2, dgvData.CurrentRow.Index).Value
  83.         txtDepartment.Text = dgvData.Item(3, dgvData.CurrentRow.Index).Value
  84.         dtpHireDate.Text = dgvData.Item(4, dgvData.CurrentRow.Index).Value
  85.         txtSalary.Text = Format(CDbl(dgvData.Item(5, dgvData.CurrentRow.Index).Value), "0.00")
  86.         '//
  87.         txtEmployeeID.Focus()
  88.     End Sub

  89.     ' / ------------------------------------------------------------------------------------------------
  90.     ' / UPDATE DATA.
  91.     ' / ------------------------------------------------------------------------------------------------
  92.     Sub UpdateData()
  93.         '// Initialize Google Sheets API
  94.         Call Credentials()
  95.         '//
  96.         Dim SelectedRowIndex As Integer = -1
  97.         Dim RowUpdate As Integer
  98.         Try
  99.             '// NEW DATA
  100.             If blnNewData Then
  101.                 RowUpdate = dgvData.Rows.Count
  102.                 '// UPDATE
  103.             Else
  104.                 '// Get the index of the selected row.
  105.                 SelectedRowIndex = dgvData.SelectedRows(0).Index + 1
  106.                 '// Because the first row of Google Sheets has the Columns name. So we need to increase the value by 1.
  107.                 RowUpdate = SelectedRowIndex + 1
  108.             End If
  109.             '// UPDATE
  110.             Dim Range As String = SheetName + "!A" & RowUpdate & ":Z" & RowUpdate ' Update with your sheet name and range
  111.             Dim valueRange As New ValueRange()
  112.             valueRange.Values = New List(Of IList(Of Object)) From {New List(Of Object) From {
  113.                     txtEmployeeID.Text.Trim,
  114.                     txtFullname.Text.Trim,
  115.                     txtPosition.Text.Trim,
  116.                     txtDepartment.Text.Trim,
  117.                     Format(dtpHireDate.Value, "dd/MM/yyyy"),
  118.                     txtSalary.Text
  119.                 }}
  120.             '// If New Data then Append Request.
  121.             If blnNewData Then
  122.                 Dim AppendRequest As SpreadsheetsResource.ValuesResource.AppendRequest = Service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, Range)
  123.                 AppendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED
  124.                 Dim AppendResponse As AppendValuesResponse = AppendRequest.Execute()

  125.                 '// Update Request.
  126.             Else
  127.                 Dim UpdateRequest As SpreadsheetsResource.ValuesResource.UpdateRequest = Service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, Range)
  128.                 UpdateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED
  129.                 Dim UpdateResponse As UpdateValuesResponse = UpdateRequest.Execute()
  130.             End If
  131.             Call LoadData()
  132.             MessageBox.Show("UPDATE COMPLETE.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
  133.         Catch ex As Exception
  134.             MessageBox.Show(ex.Message)
  135.         End Try
  136.     End Sub

  137.     ' / ------------------------------------------------------------------------------------------------
  138.     ' / Verify accuracy before saving data.
  139.     ' / ------------------------------------------------------------------------------------------------
  140.     Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
  141.         If txtEmployeeID.Text = "" Or txtEmployeeID.Text.Trim.Length = 0 Then
  142.             MessageBox.Show("Please enter EmployeeID.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Warning)
  143.             txtEmployeeID.Focus()
  144.             Return
  145.         End If
  146.         '// Check exist EmployeeID.
  147.         If txtEmployeeID.Text <> txtEmployeeID.Tag Then
  148.             '// No duplicate EmployeeID found.
  149.             If Not CheckEmployeeID(txtEmployeeID.Text.Trim) Then
  150.                 Call UpdateData()
  151.                 Call NewMode()
  152.             End If
  153.             '// Shows that the original data set has been updated.
  154.         Else
  155.             Call UpdateData()
  156.             Call NewMode()
  157.         End If
  158.     End Sub

  159.     ' / ------------------------------------------------------------------------------------------------
  160.     ' / Check exist EmployeeID.
  161.     ' / ------------------------------------------------------------------------------------------------
  162.     Function CheckEmployeeID(ByVal TargetData As String) As Boolean
  163.         Dim range As String = "Sheet1!A:A" ' Adjust the sheet name and column as needed
  164.         Try
  165.             ' Check for data in the specified column.
  166.             Dim request As SpreadsheetsResource.ValuesResource.GetRequest = Service.Spreadsheets.Values.Get(SpreadsheetId, range)
  167.             Dim response As ValueRange = request.Execute()
  168.             ' Process the data.
  169.             If response IsNot Nothing AndAlso response.Values IsNot Nothing Then
  170.                 For Each row As IList(Of Object) In response.Values
  171.                     If row.Count > 0 AndAlso row(0).ToString = TargetData Then
  172.                         ' Data found in the specified column.
  173.                         MessageBox.Show("Found " & row(0).ToString & " in Column A.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Warning)
  174.                         Return True
  175.                     End If
  176.                 Next
  177.             End If
  178.         Catch ex As Exception
  179.             MessageBox.Show(ex.Message)
  180.         End Try
  181.         '//
  182.         Return False
  183.     End Function

  184.     ' / ------------------------------------------------------------------------------------------------
  185.     Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
  186.         '// If Edit Data Mode
  187.         If btnDelete.Text = "DELETE" Then
  188.             Call DeleteData()
  189.         End If
  190.         Call NewMode()
  191.     End Sub

  192.     ' / ------------------------------------------------------------------------------------------------
  193.     ' / DELETE DATA
  194.     ' / ------------------------------------------------------------------------------------------------
  195.     Sub DeleteData()
  196.         '// Initialize Google Sheets API
  197.         Call Credentials()
  198.         '//
  199.         Dim SelectedRowIndex As Integer = -1
  200.         If dgvData.SelectedRows.Count <= 0 Then Return
  201.         '// Get the index of the selected row.
  202.         SelectedRowIndex = dgvData.SelectedRows(0).Index + 1
  203.         '// Because the first row of Google Sheets has the Columns name. So we need to increase the value by 1.
  204.         Dim RowToRemove As Integer = SelectedRowIndex + 1
  205.         Try
  206.             '// Remove the specified row
  207.             Dim request As New BatchUpdateSpreadsheetRequest()
  208.             request.Requests = New List(Of Request)()
  209.             request.Requests.Add(New Request With {
  210.             .DeleteDimension = New DeleteDimensionRequest With {
  211.             .Range = New DimensionRange With {
  212.                 .SheetId = 0, ' 0 is the default sheet ID, adjust if needed
  213.                 .Dimension = "ROWS",
  214.                 .StartIndex = RowToRemove - 1,
  215.                 .EndIndex = RowToRemove
  216.                 }
  217.             }
  218.         })
  219.             Dim EmpID As String = dgvData.Item(0, dgvData.CurrentRow.Index).Value
  220.             Dim Result As Byte = MessageBox.Show("Are you sure you want to delete " & EmpID & " the data?", "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
  221.             If Result = DialogResult.Yes Then
  222.                 Service.Spreadsheets.BatchUpdate(request, SpreadsheetId).Execute()
  223.                 Call LoadData()
  224.                 MessageBox.Show("DELETE COMPLETE.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
  225.             End If
  226.         Catch ex As Exception
  227.             MessageBox.Show(ex.Message)
  228.         End Try

  229.     End Sub

  230.     ' / ------------------------------------------------------------------------------------------------
  231.     ' / Add New Mode
  232.     ' / ------------------------------------------------------------------------------------------------
  233.     Private Sub NewMode()
  234.         '// Clear all TextBox.
  235.         For Each c In GroupBox1.Controls
  236.             If TypeOf c Is TextBox Then
  237.                 DirectCast(c, TextBox).Clear()
  238.                 DirectCast(c, TextBox).Enabled = False
  239.             End If
  240.         Next
  241.         dtpHireDate.Value = Now()
  242.         dtpHireDate.Enabled = False
  243.         '//
  244.         btnAdd.Enabled = True
  245.         btnSave.Enabled = False
  246.         btnDelete.Enabled = True
  247.         btnDelete.Text = "DELETE"
  248.         btnExit.Enabled = True
  249.         '//
  250.     End Sub

  251.     ' / ------------------------------------------------------------------------------------------------
  252.     ' / Edit Data Mode
  253.     ' / ------------------------------------------------------------------------------------------------
  254.     Private Sub EditMode()
  255.         '// Clear all TextBox
  256.         For Each c In GroupBox1.Controls
  257.             If TypeOf c Is TextBox Then
  258.                 DirectCast(c, TextBox).Enabled = True
  259.             End If
  260.         Next
  261.         btnAdd.Enabled = False
  262.         btnSave.Enabled = True
  263.         btnDelete.Enabled = True
  264.         btnDelete.Text = "CANCEL"
  265.         btnExit.Enabled = False
  266.         '//
  267.         dtpHireDate.Enabled = True
  268.     End Sub

  269.     Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
  270.         Me.Close()
  271.     End Sub

  272.     Private Sub frmGoogleSheet_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
  273.         Me.Dispose()
  274.         GC.SuppressFinalize(Me)
  275.         End
  276.     End Sub

  277.     Private Sub txtSalary_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtSalary.KeyPress
  278.         If Asc(e.KeyChar) = 13 Then
  279.             e.Handled = True
  280.             SendKeys.Send("{TAB}")
  281.         Else
  282.             e.Handled = CheckCurrency(Asc(e.KeyChar), txtSalary.Text)
  283.         End If
  284.     End Sub

  285.     Private Sub txtEmployeeID_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtEmployeeID.KeyPress
  286.         If Asc(e.KeyChar) = 13 Then
  287.             e.Handled = True
  288.             SendKeys.Send("{TAB}")
  289.         End If
  290.     End Sub

  291.     Private Sub txtFullname_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtFullname.KeyPress
  292.         If Asc(e.KeyChar) = 13 Then
  293.             e.Handled = True
  294.             SendKeys.Send("{TAB}")
  295.         End If
  296.     End Sub

  297.     Private Sub txtPosition_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtPosition.KeyPress
  298.         If Asc(e.KeyChar) = 13 Then
  299.             e.Handled = True
  300.             SendKeys.Send("{TAB}")
  301.         End If
  302.     End Sub

  303.     Private Sub txtDepartment_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtDepartment.KeyPress
  304.         If Asc(e.KeyChar) = 13 Then
  305.             e.Handled = True
  306.             SendKeys.Send("{TAB}")
  307.         End If
  308.     End Sub

  309.     Private Sub dtpHireDate_KeyDown(sender As Object, e As KeyEventArgs) Handles dtpHireDate.KeyDown
  310.         If e.KeyCode = Keys.Enter Then
  311.             e.Handled = True
  312.             SendKeys.Send("{TAB}")
  313.         End If
  314.     End Sub

  315. #Region "DATAGRIDVIEW"
  316.     '// Initialized DataGridView.
  317.     Private Sub SetupGridView()
  318.         With dgvData
  319.             .RowHeadersVisible = True
  320.             .AllowUserToAddRows = False
  321.             .AllowUserToDeleteRows = False
  322.             .AllowUserToResizeRows = False
  323.             .MultiSelect = False
  324.             .SelectionMode = DataGridViewSelectionMode.FullRowSelect
  325.             .ReadOnly = True
  326.             '// Data rows
  327.             .Font = New Font("Tahoma", 10)
  328.             .RowTemplate.MinimumHeight = 32
  329.             .RowTemplate.Height = 32
  330.             '// Autosize Column
  331.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
  332.             .EnableHeadersVisualStyles = False
  333.             '// Header
  334.             With .ColumnHeadersDefaultCellStyle
  335.                 .BackColor = System.Drawing.Color.SeaGreen
  336.                 .ForeColor = System.Drawing.Color.White
  337.                 .Font = New Font(dgvData.Font, FontStyle.Bold)
  338.             End With
  339.             '// Before you can adjust the height of the row.
  340.             .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
  341.             .ColumnHeadersHeight = 32
  342.             '/ Accept changes to the header's background color.
  343.             .EnableHeadersVisualStyles = False
  344.             '// Even-Odd Color of Rows.
  345.             .AlternatingRowsDefaultCellStyle.BackColor = System.Drawing.Color.Beige
  346.             '// Block the click on the column header for sort.
  347.             For i As Byte = 0 To dgvData.ColumnCount - 1
  348.                 dgvData.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
  349.             Next
  350.         End With
  351.     End Sub

  352.     Private Sub dgvData_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgvData.RowPostPaint
  353.         '// Display row numbers in row headers
  354.         Using b As New SolidBrush(dgvData.RowHeadersDefaultCellStyle.ForeColor)
  355.             e.Graphics.DrawString((e.RowIndex + 1).ToString(), dgvData.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 12, e.RowBounds.Location.Y + 8)
  356.         End Using
  357.     End Sub

  358. #End Region
  359. End Class
คัดลอกไปที่คลิปบอร์ด

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

  2.     ' / --------------------------------------------------------------------------------
  3.     ' / Get my project path
  4.     ' / AppPath = C:\My Project\bin\debug
  5.     ' / Replace "\bin\debug" with ""
  6.     ' / Return : C:\My Project\
  7.     Function MyPath(AppPath As String) As String
  8.         '/ Return Value
  9.         MyPath = AppPath.ToLower.Replace("\bin\debug", "").Replace("\bin\release", "").Replace("\bin\x86\debug", "")
  10.         '// If not found folder then put the \ (BackSlash ASCII Code = 92) at the end.
  11.         If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
  12.     End Function

  13.     ' / --------------------------------------------------------------------------------
  14.     ' / Function to enter only numeric and decimal values.
  15.     Function CheckCurrency(index As Integer, tmpStr As String) As Boolean
  16.         CheckCurrency = False
  17.         Select Case index
  18.             Case 48 To 57 ' เลข 0 - 9
  19.                 ' Allowed "."
  20.             Case 46
  21.                 ' can present "." only one
  22.                 If InStr(tmpStr, ".") Then CheckCurrency = True

  23.             Case 8, 13 ' Backspace = 8, Enter = 13
  24.             Case Else
  25.                 CheckCurrency = True
  26.         End Select
  27.     End Function

  28. End Module
คัดลอกไปที่คลิปบอร์ด

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

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

คุณจำเป็นต้อง ลงชื่อเข้าใช้ เพื่อดาวน์โหลดหรือดูไฟล์แนบนี้ คุณยังไม่มีบัญชีใช่ไหม? ลงทะเบียน

x
สิ่งที่ดีกว่าการให้ คือการให้แบบไม่มีที่สิ้นสุด
ขออภัย! คุณไม่ได้รับสิทธิ์ในการดำเนินการในส่วนนี้ กรุณาเลือกอย่างใดอย่างหนึ่ง ลงชื่อเข้าใช้ | ลงทะเบียน

รายละเอียดเครดิต

ข้อความล้วน|อุปกรณ์พกพา|ประวัติการแบน|G2GNet.com  

GMT+7, 2024-4-19 06:42 , Processed in 0.075977 second(s), 4 queries , File On.

Powered by Discuz! X3.4, Rev.62

Copyright © 2001-2020 Tencent Cloud.

ตอบกระทู้ ขึ้นไปด้านบน ไปที่หน้ารายการกระทู้