[VB.NET] การสร้างชีตและหลักให้กับ Google Sheet เพื่อเก็บข้อมูลชิ้นส่วนอุปกรณ์คอมพิวเตอร์ลงไปในชีต
http://www.g2gsoft.com/webboard/images/VBNet/googlesheetcompartrun.pnghttp://www.g2gsoft.com/webboard/images/VBNet/googlesheet.png
http://www.g2gsoft.com/webboard/images/VBNet/googlesheetreference.png
ต้อง Add Reference System.Management เข้าไปด้วย เนื่องจากเราต้องใช้งาน WMI ...
สำหรับโค้ดชุดนี้แอดมินจะนำเสนอการสร้างชีต พร้อมกับหลักขึ้นมาใหม่ เพื่อทำการส่งข้อมูลออกจาก ListView Control ที่แสดงผลรายการชิ้นส่วนอุปกรณ์คอมพิวเตอร์ ด้วยการใช้งาน WMI (Windows Management Instrumental) และนำข้อมูลทั้งหมดไปเก็บไว้ใน Google Sheet ได้ ... พร้อมกับสามารถลบชีตที่ต้องการออกไปได้ ... Youtube อธิบายการทำงานประกอบสำหรับโค้ดชุดนี้ ...
สิ่งที่ควรจะต้องทำ ...
1. รับไฟล์ Credentials จาก Google เพื่อกำหนดสิทธิ์การเข้าถึงชีต ... Youtube แสดงวิธีการขั้นตอนในการรับไฟล์ Credentials ...
2. สร้าง Google Sheets ขึ้นมาใหม่ จะได้ SpreadSheetID และแก้ไขชื่อไฟล์ หรือใน Google Sheets เรียกว่า Application Name
3. แก้ไขค่าตัวแปร ... ให้ตรงกับชีตของแต่ละคน
Public Service As SheetsService
Public SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
Public ApplicationName = "SampleSheet" '// Same as Filename.
'// JSON credential file path.
Public CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
หากท่านรันโปรแกรมโดยที่ไม่ได้แก้ไขอะไร มันจะไปเก็บข้อมูลไว้ในชีตของแอดมิน แต่ก็สามารถลบชีตออกได้ ... ชีตตัวอย่างของโค้ดชุดนี้
มาดูโค้ดฉบับเต็มกันเถอะ ...
Imports System.Management
Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
Public Class frmComputerPart
Dim SheetName As String
' / ---------------------------------------------------------------------------------------------
Private Sub frmComputerPart_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'// Initialize Authenticate with Google Sheets API.
Call Credentials()
'// Set the form to stay on top
'Me.TopMost = True
Call btnListPart_Click(sender, e)
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub frmComputerPart_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
Me.Dispose()
GC.SuppressFinalize(Me)
Application.Exit()
End Sub
'// ---------------------------------------------------------------------------------------------
'// Populate any parts in computer to ListView Control.
'// ---------------------------------------------------------------------------------------------
Private Sub btnListPart_Click(sender As Object, e As EventArgs) Handles btnListPart.Click
'// Initialize ListView Control
With ListView1
.Clear()
.View = View.Details
.GridLines = True
.FullRowSelect = True
.HideSelection = False
.MultiSelect = False
.Columns.Add("Part name", ListView1.Width \ 2 - 50)
.Columns.Add("Description", ListView1.Width \ 2 + 20)
End With
Dim LV As ListViewItem
Dim i As Integer
'// Processor
Dim Searcher As New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_Processor")
For Each QueryObj As ManagementObject In Searcher.Get()
'// ---------------------------------------------------------------------------------------------
'// Get System Name for create sheet name in Google Sheet.
SheetName = QueryObj("SystemName")
'// ---------------------------------------------------------------------------------------------
LV = ListView1.Items.Add("System Name")
LV.SubItems.Add(QueryObj("SystemName"))
LV = ListView1.Items.Add("CPU Name")
LV.SubItems.Add(QueryObj("Name"))
LV = ListView1.Items.Add("Processor ID")
LV.SubItems.Add(QueryObj("ProcessorID"))
Next
'// BaseBoard
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_BaseBoard")
For Each QueryObj As ManagementObject In Searcher.Get()
LV = ListView1.Items.Add("MainBoard Manufacturer")
LV.SubItems.Add(QueryObj("Manufacturer"))
LV = ListView1.Items.Add("MainBoard Serial Number")
LV.SubItems.Add(QueryObj("SerialNumber"))
LV = ListView1.Items.Add("MainBoard Product Name")
LV.SubItems.Add(QueryObj("Product"))
Next
'// Hard Disk Drive use PhysicalMedia Class
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_PhysicalMedia")
i = 1
For Each QueryObj As ManagementObject In Searcher.Get()
If InStr(QueryObj("Tag"), "CDROM") = 0 Then
LV = ListView1.Items.Add("Hard Disk Serial Number (" & i & ")")
LV.SubItems.Add(Trim(QueryObj("SerialNumber")))
i = i + 1
End If
Next
'// Video Controller.
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_VideoController")
i = 1
For Each QueryObj As ManagementObject In Searcher.Get()
LV = ListView1.Items.Add("Video Controller (" & i & ")")
LV.SubItems.Add(Trim(QueryObj("Name")))
i = i + 1
Next
'// CD/DVD
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_CDROMDrive")
i = 1
For Each QueryObj As ManagementObject In Searcher.Get()
LV = ListView1.Items.Add("CD/DVD Manufacturer (" & i & ")")
LV.SubItems.Add(Trim(QueryObj("Name")))
LV = ListView1.Items.Add("CD/DVD Serial Number (" & i & ")")
LV.SubItems.Add(Trim(QueryObj("SerialNumber")))
i = i + 1
Next
'// Memory
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_PhysicalMemory")
i = 1
For Each QueryObj As ManagementObject In Searcher.Get()
LV = ListView1.Items.Add("Memory (" & i & ")")
LV.SubItems.Add(Trim(QueryObj("Manufacturer")) & "/" & Trim(QueryObj("SerialNumber")))
i = i + 1
Next
'// Network adapter
Searcher = New ManagementObjectSearcher("root\CIMV2", "SELECT * FROM Win32_NetworkAdapter")
For Each QueryObj As ManagementObject In Searcher.Get()
If Not String.IsNullOrEmpty(QueryObj("MACAddress")) And Microsoft.VisualBasic.Left(QueryObj("MACAddress"), 2) <> "00" Then
LV = ListView1.Items.Add("MAC Address")
LV.SubItems.Add(QueryObj("MACAddress"))
LV = ListView1.Items.Add("Network Card Manufacturer")
LV.SubItems.Add(QueryObj("Manufacturer"))
LV = ListView1.Items.Add("Network Product Name")
LV.SubItems.Add(QueryObj("ProductName"))
End If
Next
End Sub
Private Sub frmComputerPart_Resize(sender As Object, e As EventArgs) Handles Me.Resize
' Resize ListView Control.
If ListView1.Columns.Count > 0 Then
With ListView1
.Columns(0).Width = ListView1.Width \ 2 - 50
.Columns(1).Width = ListView1.Width \ 2 + 20
End With
End If
End Sub
'// ---------------------------------------------------------------------------------------------
'// Create new sheet and columns then load data to the Google Sheets.
'// ---------------------------------------------------------------------------------------------
Private Sub btnExport2Sheet_Click(sender As Object, e As EventArgs) Handles btnExport2Sheet.Click
'// If exist sheet name then remove it.
'// อันที่จริงเราไม่ต้องส่งชื่อ SheetName ไป เพราะว่าค่าตัวแปรนี้เป็นแบบมองเห็นได้ทั้งฟอร์ม
'// แต่ผมเขียนเป็นโปรแกรมย่อย เผื่อที่เราจะนำเอาไปใช้ในงานอื่นได้ โดยที่จะต้องระบุชื่อชีตไปด้วยครับ
'// Function SheetExists(ByVal SheetName As String) As Boolean
If SheetExists(SheetName) Then Call RemoveSheetByName(SheetName)
'// Create the new sheet.
Call CreateSheet()
'// Add columns to the new sheet.
Call AddDataColumns()
'//
MessageBox.Show("New sheet with new columns created successfully.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
'// ---------------------------------------------------------------------------------------------
'// Method to create a new sheet.
'// ---------------------------------------------------------------------------------------------
Private Sub CreateSheet()
Dim AddSheetRequest As AddSheetRequest = New AddSheetRequest()
AddSheetRequest.Properties = New SheetProperties() With {
.Title = SheetName'// Use System Name for new Sheet.
}
Dim AddSheetBatchUpdateRequest As BatchUpdateSpreadsheetRequest = New BatchUpdateSpreadsheetRequest()
AddSheetBatchUpdateRequest.Requests = New List(Of Request)()
AddSheetBatchUpdateRequest.Requests.Add(New Request() With {.AddSheet = AddSheetRequest})
'// Execute the request to create the new sheet.
Service.Spreadsheets.BatchUpdate(AddSheetBatchUpdateRequest, SpreadsheetId).Execute()
End Sub
'// ---------------------------------------------------------------------------------------------
'// Method to add columns and data to Google Sheets.
'// ---------------------------------------------------------------------------------------------
Private Sub AddDataColumns()
'// Specify the Range for data insertion.
Dim Range As String = SheetName + "!A1:B"
'// Create the ValueRange object.
Dim ValueRange As New ValueRange With {.Values = New List(Of IList(Of Object))()}
'// Add headers to ValueRange.
Dim Headers As New List(Of Object) From {"Part Name", "Description"}
ValueRange.Values.Add(Headers)
'// Populate values from ListView to ValueRange.
For Each item As ListViewItem In ListView1.Items
Dim RowValues As New List(Of Object)
RowValues.Add(item.SubItems(0).Text) ' Assuming the first column is at index 0
RowValues.Add(item.SubItems(1).Text) ' Assuming the second column is at index 1
ValueRange.Values.Add(RowValues)
Next
'// Create the request to update values.
Dim UpdateRequest As SpreadsheetsResource.ValuesResource.UpdateRequest = Service.Spreadsheets.Values.Update(ValueRange, SpreadsheetId, Range)
UpdateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW
'// Execute the request to update values.
Dim UpdateResponse As UpdateValuesResponse = UpdateRequest.Execute()
'MessageBox.Show("Data added to Google Sheets successfully.")
End Sub
'// ---------------------------------------------------------------------------------------------
'// Method to remove a sheet name.
'// In fact, there is no need to pass parameters or sheet names to the subprogram.
'// But I Set aside time To use In other tasks.
'// ---------------------------------------------------------------------------------------------
Sub RemoveSheetByName(ByVal SheetName As String)
'// Get the list of sheets in the spreadsheet.
Dim spreadsheet = Service.Spreadsheets.Get(SpreadsheetId).Execute()
Dim sheets = spreadsheet.Sheets
'// Find the sheet by name.
Dim sheet = sheets.FirstOrDefault(Function(s) s.Properties.Title = SheetName)
'// This is necessary because the Google Sheets API primarily operates with sheet IDs,
'// and there's no direct method to delete a sheet by name.
If sheet IsNot Nothing Then
'// Get the sheet ID.
Dim sheetId = sheet.Properties.SheetId
'// Create a batch update request to remove the sheet.
Dim request = New Request With {
.DeleteSheet = New DeleteSheetRequest With {
.SheetId = sheetId
}
}
Dim BatchUpdateRequest = New BatchUpdateSpreadsheetRequest With {
.Requests = New List(Of Request) From {request}
}
'// Execute the batch update request.
Service.Spreadsheets.BatchUpdate(BatchUpdateRequest, SpreadsheetId).Execute()
MessageBox.Show("Sheet " & SheetName & " has been removed.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Sheet " & SheetName & " not found.", "Report Status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End Sub
'// ---------------------------------------------------------------------------------------------
'// Method to check if a sheet with a specific name exists.
'// ---------------------------------------------------------------------------------------------
Private Function SheetExists(ByVal SheetName As String) As Boolean
'// Get the list of sheets in the SpreadSheet.
Dim SpreadSheet = Service.Spreadsheets.Get(SpreadsheetId).Execute()
Dim sheets = SpreadSheet.Sheets
'// Check if a sheet with the specified name exists.
Return sheets.Any(Function(s) s.Properties.Title = SheetName)
End Function
Private Sub btnRemoveSheet_Click(sender As Object, e As EventArgs) Handles btnRemoveSheet.Click
Call RemoveSheetByName(SheetName)
End Sub
End Class
โมดูลของ Credentials เพื่อกำหนดสิทธิ์ในการเข้าถึงชีต ... คลิปวิดีโอในการรับไฟล์ Credentials (JSON) ...
Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Services
Imports Google.Apis.Sheets.v4
Module modCredentials
Public Service As SheetsService
Public SpreadsheetId As String = "1nBOWl-PDGwYng6IOifi6bhoLfvamTn-45CWtn5t59qs"
Public ApplicationName = "SampleSheet" '// Same as Filename.
'// JSON credential file path.
Public CredentialFilePath As String = MyPath(Application.StartupPath) & "credentials\GoogleSheet.json"
' / ------------------------------------------------------------------------------------------------
'// 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
End Module
โมดูลของฟังค์ชั่น ...
Module modFucntion
' / --------------------------------------------------------------------------------
' / Get my project path
' / AppPath = C:\My Project\bin\debug
' / Replace "\bin\debug" with "\"
' / Return : C:\My Project\
Public 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 Module
ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2017) - .Net Framework 4.5+ ... ได้ที่นี่ ...
หน้า:
[1]