Chia sẻ: Kết hợp tương tác Excel và Google SpreadSheet ngay trong VBA

HeSanbi

Thành viên tích cực
Tham gia ngày
24 Tháng hai 2013
Bài viết
838
Được thích
604
Điểm
560
GSPREAD.NET một gói thư viện được viết bằng Microsoft Visual C# và Microsoft .Net Framework, nó cung cấp các Hàm API để kết nối tương tác với Google Spreadsheet từ các ngôn ngữ script như VBScript , VBA, ... mà nó hỗ trợ.

Để sử dụng được, các bạn cần tải và cài đặt GSPREAD.NET, sau đó thêm thư viện vào trong ứng dụng Office của bạn bằng cách:
Mở VBA > Tools > References : tìm đến gói chứa tên GSPREAD.NET và thêm.

Sau đó tạo một file Google Spreadsheet vào Trình biên tập tập lệnh tạo một File Script và copy mã script do trang chủ cung cấp vào Script và chấp nhận xác thực từ Google để Script có đủ quyền thao tác lên Google Spreadsheet. Có Script này thì các Hàm API trong thư viện GSPREAD.NET mới hoạt động.


GSPREAD.NET cung cấp cả mã nguồn nên các bạn có thể cải tiến theo cá nhân.


Hướng dẫn
từ Trang chủ https://scand.com/products/gspread/: (Link tải ngay trên Website)

Bài viết được dịch bởi Google dịch.

Lấy ClientID và Secret được Google cung cấp thông qua tài khoản Google trong phần Nhà Phát Triển.

1
2
3
4
// Name - User name, any you like
// ClientIdAndSecret - `client_id|client_secret` format
// ScriptId - Google Apps script ID
app.MailLogon(Name, ClientIdAndSecret, ScriptId);


Ví dụ: Tạo đơn giản một bảng:

GSpread .NET



PHP:
Set app = CreateObject("GSpreadCOM.Application")
app.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate()

Set cells = app.Cells

' --- Data'

cells(2, 2).Cells.Value = "Product"
For i = 1 To 4
cells(2, 2 + i).Cells.Value = "Quarter " & i
Next
cells(2, 7).Cells.Value = "Year"

cells(4, 2).Cells.Value = "Apples"
cells(5, 2).Cells.Value = "Pears"
cells(6, 2).Cells.Value = "Bananas"
cells(7, 2).Cells.Value = "Oranges"
cells(8, 2).Cells.Value = "Totals"

randomize
For row = 4 To 7
For col = 3 To 6
cells(row, col).Cells.Value = (int(rnd * 100) + 1) * 100
Next
Next

cells.Range("C8:G8").FormulaR1C1 = "=SUM(R[-4]C[0]:R[-1]C[0])"
cells.Range("G4:G7").Formula = "=SUM(C4:F4)"

' --- Styling'

Set tableRange = cells.Range("B2:G8")
tableRange.Borders.Color = "white"
tableRange.NumberFormat = "$0,000" ' currency number format'

cells.Range("B2:B3").Merge()
cells.Range("C2:C3").Merge()
cells.Range("D2:D3").Merge()
cells.Range("E2:E3").Merge()
cells.Range("F2:F3").Merge()
cells.Range("G2:G3").Merge()

Set headerRange = cells.Range("B2:G3")
Set footerRange = cells.Range("B8:G8")

' color #0066CC has index 23 in excel color palette'
headerRange.Interior.ColorIndex = 23
footerRange.Interior.Color = "#0066CC"

' white color has index 2 in excel color palette'
headerRange.Font.ColorIndex = 2
footerRange.Font.Color = "white"

headerRange.Font.Bold = True
footerRange.Font.Bold = True

footerRange.RowHeight = 30

oddRowColor = 14994616 ' RGB(184, 204, 228)'
evenRowColor = 15853019 ' RGB(219, 229, 241)'

cells.Range("B4:G4").Interior.Color = oddRowColor
cells.Range("B5:G5").Interior.Color = evenRowColor
cells.Range("B6:G6").Interior.Color = oddRowColor
cells.Range("B7:G7").Interior.Color = evenRowColor

WScript.Echo "Done"


CÁC CHỨC NĂNG VÀ THUỘC TÍNH ĐƯỢC HỖ TRỢ
Thư viện bị giới hạn và hỗ trợ một tập hợp con các hàm Excel nhất định:

ObjectMethodsProperties
  • Excel.Application
  • MailLogon
  • Quit
  • Save
  • SaveWorkspace
  • ActiveCell
  • ActiveSheet
  • ActiveWorkbook
  • Cells
  • Columns
  • DecimalSeparator
  • Range
  • Rows
  • Sheets
  • ThousandsSeparator
  • Workbooks
  • Worksheets
  • Excel.Workbooks
  • Add – templates will not be supported
  • Open – limited options set will be supported to open only google spreadsheets by name
  • Close
  • Count
  • Item
  • Excel.Workbook
  • Activate
  • Close
  • ExportAsFixedFormat – only PDF format will be supported (may be excluded from 1st version)
  • Save
  • SaveAs – only google spreadsheet format is supported)
  • ActiveSheet
  • Application
  • FullName
  • Name
  • Sheets
  • Worksheets
  • Excel.Worksheets
  • Add – only Worksheet can be added
  • Count
  • Item
  • Excel.Worksheet
  • Activate
  • Delete
  • Application
  • Cells
  • Columns
  • Name
  • Range
  • Rows
  • Excel.Range
  • BorderAround
  • Clear
  • ClearContents
  • ClearFormats
  • Insert
  • Merge
  • UnMerge
  • Address
  • Borders
  • Cells
  • Column
  • Columns
  • ColumnWidth – implemented in pixels
  • Count
  • EntireColumn
  • EntireRow
  • Font
  • Formula
  • FormulaR1C1
  • HorizontalAlignment
  • Interior
  • Item
  • NumberFormat
  • Offset
  • Range
  • Row
  • Rows
  • RowHeight
  • Style
  • Text
  • Value
  • Value2
  • VerticalAlignment
  • Worksheet
  • WrapText
  • Excel.Style
  • Borders
  • Font
  • HorizontalAlignment
  • Interior
  • NumberFormat
  • VerticalAlignment
  • WrapText
  • Excel.Font
  • Bold
  • Color
  • ColorIndex
  • Italic
  • Size
  • Strikethrough
  • Underline
  • Excel.Interior
  • Color
  • ColorIndex
  • Excel.Borders
  • Color
  • ColorIndex
  • Count
  • Item
  • LineStyle
  • Weight
  • Excel.Border
  • Color
  • ColorIndex
  • LineStyle
  • Weight


CÀI ĐẶT TÀI KHOẢN GOOGLE

Theo mặc định, GSpread.NET sử dụng các định danh mẫu của nó. Các bạn nên thay đổi nó thành của riêng bạn. Để thực hiện việc này, vui lòng thực hiện các bước sau: Truy cập Google Drive của bạn và tạo một tập lệnh mới (Chọn: MỚI > KHÁC > Tệp Tập lệnh):

Cài đặt Google


Nếu không có tùy chọn trình đơn như vậy, bạn có thể chọn ứng dụng Kết nối nhiều ứng dụng khác, hãy tìm kiếm tập lệnh ứng dụng của tập tin, trong hộp thoại đã mở và sau đó kết nối với Tập lệnh Google Apps.

.NET rộng rãi


Sao chép mã tập lệnh từ thư mục <Thư mục dự án> / src/GSpreadCOM/Resource/GoogleAppsScript.gs vào Code.gs bạn vừa tạo. Ngoài ra, bạn có thể tìm thấy tập lệnh tại đây: https://scand.com/doad/products/GSpread.NET/GoogleAppsScript.gs

.NET rộng rãi


Lưu dự án tập lệnh với tên GSpreadCOM_Script hoặc tên phù hợp.

.NET rộng rãi


Hơn, mở menu Xuất bản> Triển khai dưới dạng API thực thi API

.NET rộng rãi


Đặt bất kỳ phiên bản nào, ví dụ 1 và nhấn vào Dep Depdio:

.NET rộng rãi


.NET rộng rãi


Bây giờ chúng ta cần cấu hình dự án GSiverseCOM_Script.
Truy cập Bảng điều khiển API của Google https://console.developers.google.com và chọn một dự án:

.NET rộng rãi


Truy cập Tổng quan về> Các API của Google, tìm kiếm và kích hoạt Google Drive API:

.NET rộng rãi


.NET rộng rãi


và API thực thi tập lệnh Google Apps:

.NET rộng rãi


.NET rộng rãi


Bây giờ chúng ta cần tạo thông tin đăng nhập:

.NET rộng rãi


Chọn ID khách hàng OAuth IDD:

.NET rộng rãi


Loại ứng dụng - Giảm giá khác và nhấn vào Tạo Tạo:

.NET rộng rãi


.NET rộng rãi


Sao chép ID khách hàng của IDD

1
2
3
4
5
6
7
<?xml version="1.0" encoding="utf-8"?>
<config>
<user name="any random name"/>
<client id="52444250292-02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com"
secret="q2IOcXHThZ3LPc5nfFCcA_r"/>
<script id="Mvi58b-XTLQOC7mbLFOvENmbpWLEH-geZ"/>
</config>
Bạn có thể sử dụng các giá trị này làm tham số thứ hai của phương thức Application.MailLogon trong định dạng của client client_id | client_secret.

1
2
3
app.MailLogon("any random name", "52444250292-
02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com|q2IOcXH-
ThZ3LPc5nfFCcA_r", "Mx1qw1DNjz3vrzoYbLfc5H0sBksCqgDyL");
 
Lần chỉnh sửa cuối:
Top