|
ต้อง 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+ ... ได้ที่นี่ ...
|
ขออภัย! โพสต์นี้มีไฟล์แนบหรือรูปภาพที่ไม่ได้รับอนุญาตให้คุณเข้าถึง
คุณจำเป็นต้อง ลงชื่อเข้าใช้ เพื่อดาวน์โหลดหรือดูไฟล์แนบนี้ คุณยังไม่มีบัญชีใช่ไหม? ลงทะเบียน
x
|