Để lập trình với Table trong Excel Online chúng ta làm việc với hai class chính là BSCloudTable và BSCloudTables và các class thuộc quản lý của hai class này.

Class BSCloudTables gồm có các thành phần sau:
+ Add() : là hàm tạo table, hàm trả về đối tượng BSCloudTable.
+ Count : là thuộc tính đếm số table trong danh sách
+ Item() : là thuộc tính trỏ đến một table trong danh sách thông qua khai báo tên hoặc Index. Trả về đối tượng BSCloudTable.
+ Refresh () : hàm cập nhật lại toàn bộ danh sách table trong BSCloudWorkbook hoặc BSCloudWorksheet
+ Parent : là thuộc tính đối tượng trả về đối tượng cha của nó. Nó có thể là BSCloudWorkbook hoặc BSCloudWorksheet

Tạo một table

Vùng dữ liệu trên Excel Online tại sheet "Some Tables" như dưới đây:


Bây giờ cần tạo table cho vùng A7:E10

Tạo một Module. Để code các ví dụ ngắn gọn tôi khai báo hai hằng số là FileID và MyCloudType để thực hiện kết nối với Excel Online trên OneDrive.

'FileID can be ID or url
'Private Const FileID = "11870394B81CE870!34948"
Private Const FileID = "https://onedrive.live.com/edit?id=11870394B81CE870!34948&resid=11870394B81CE870!34948&ithint=file%2cxlsx&wdo=2&cid=11870394b81ce870" 
Private Const MyCloudType = ctOneDrive 

Sub ExcelOnline_Table_CreateTable() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook  'Excel.Workbook
   Dim tb As BSCloudTable  'Excel.Table
   On Error GoTo lbEndSub 
   'Check Connection
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         MsgBox "Connection fail.", vbCritical 
         Exit Sub 
      End If 
   End If 
   'DO something
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables.Add("'Some Tables'!A7:E10") 
   Debug.Print tb.Name, tb.ID 
lbEndSub: 
   If Err <> 0 Then 
      MsgBox Err.Description, vbCritical 
   End If 
   'Free memory
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Theo ví dụ trên bạn cần chú ý: 

Hàm Add() tạo table và trả về đối tượng BSCloudTable để người lập trình điều khiển nó, vì vậy ta khai báo biến tbl và set vào hàm Add().

Tên sheet nếu có ký tự đặc biệt hoặc có dấu cách (space) thì phải đặt vào trong hai dấu nháy đơn.

Như thế này là đúng:
Set tb = wb.Tables.Add("'Some Tables'!A7:E10")

Như dưới đây là lỗi vì không có nháy đơn bao tên sheet.
Set tb = wb.Tables.Add("Some Tables!A7:E10")

Dòng lệnh Debug.Print là để in thông tin ra cửa sổ Immediate (cửa sổ này mở trong menu View - > Immediate Window).

 Kết quả chạy macro trên ta được bảng table trên Excel Online như sau


Thay đổi thuộc tính của table

Sub ExcelOnline_Table_Style() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook, tb As BSCloudTable 
   On Error GoTo lbEnd 
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         Exit Sub 
      End If 
   End If 
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables("Table1") 
   tb.Style = TableStyleDark11  'Change style
   'tb.Name = "MyTable" 'Change
   tb.ShowTotals = True  'Show total row
   tb.ShowHeaders = True  'Show Header row
   'You can use function Update() to change any properties
   'tb.Update "MyTable", True, True, TableStyleDark11
lbEnd: 
   If Err <> 0 Then 
      Debug.Print "Error: " & Err.Description 
   End If 
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Ta được bảng table có hình dáng như dưới đây


Liệt kê tất cả table trong BSCloudWorkbook (Workbook - file)

Sub ExcelOnline_Show_all_Tables() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook  'Excel.Workbook
   Dim tb As BSCloudTable  'Excel.Table
   On Error GoTo lbEndSub 
   'Check Connection
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         MsgBox "Connection fail.", vbCritical 
         Exit Sub 
      End If 
   End If 
   'DO something
   Set wb = MyCloud.Workbooks.Open(FileID) 
   For Each tb In wb.Tables 
      Debug.Print tb.Name, tb.ID 
   Next 
lbEndSub: 
   If Err <> 0 Then 
      MsgBox Err.Description, vbCritical 
   End If 
   'Free memory
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Class BSCloudTable

Vừa rồi tôi đã trình bày ví dụ về tạo một table và thay đổi các thuộc tính của nó. Dưới đây là các hàm và những thành phần của BSCloudTable.

+ Name : tên bảng.
+ ID: mã định danh của bảng;
+ LegacyId : mã định danh.
+ Index : chỉ mục của bảng trong danh sách.
+ ShowTotals : thiết lập trạng thái dòng tổng cộng.
+ ShowHeaders : thiết lập trạng thái dòng tiêu đề.
+ Style : thiết lập style cho bảng (cách định dạng cả bảng).
+ StyleName : tên style. Chỉ đọc.
+ Parent : trả về BSCloudTables
+ Update() : thay đổi các thuộc tính của bảng.
+ ClearFilters() : xóa trạng thái filter - lock
+ ApplyFilters() : lọc dữ liệu
+ Refresh() : cập nhật thông tin của bảng.
+ ListRows : kiểu BSCloudTableRows quản lý danh sách các dòng (mỗi dòng là một đối tượng/class BSCloudTableRow).
+ ListColumns : kiểu BSCloudTableColumns quản lý danh sách các cột (mỗi cột là một đối tượng/class BSCloudTableColumn).
+ AppendRows() : thêm dòng;
+ AppendColumns() : tạo thêm cột.
+ DataBodyRange: trả về kiểu BSCloudRange. Là vùng dữ liệu không bao gồm dòng tiêu đề và dòng tổng cộng.
+ HeaderRowRange : trả về BSCloudRange. Là vùng của dòng tiêu đề.
+ TableRange : trả về BSCloudRange. Là vùng của toàn bộ bảng.
+ TotalRowRange : trả về BSCloudRange. Là vùng của dòng tổng cộng.

+ HighlightFirstColumn : trạng thái tô sáng dòng đầu tiên.
+ HighlightLastColumn : trạng thái tô sáng cột đầu tiên.
+ ShowBandedColumns : trạng thái tô màu nền của cột.
+ ShowBandedRows : trạng thái tô màu nền của dòng.
+ ShowFilterButton : trạng thái hiển thị nút filter trên dòng tiêu đề.
+ UpdateRow() : cập nhật dữ liệu cho một dòng.
+ UpdateColumn() : cập nhật dữ liệu cho một cột.
+ GetRowInfo(): nhận thông tin cũng như mảng dữ liệu của một dòng. Thông tin nhận ở tham số TableRow có kiểu BSTableRowInfo
+ GetColumnInfo() : nhận thông tin cũng như mảng dữ liệu của một cột. Thông tin nhận ở tham số TableColumn có kiểu BSTableColumnInfo.
+ DeleteRow() : xóa dòng.
+ DeleteColumn() : xóa cột.
+ Delete : xóa bảng;
+ ConvertToRange() : trả về kiểu BSCloudRange là vùng của toàn bộ bảng, đồng thời xóa chế độ Table.

(*) Lưu ý: chỉ mục (Index) của các thành phần thuộc BSCloudTable tính từ 0 (base zero), tức Index dòng, cột đầu tiên là  0, phần tử mảng giá trị trong BSTableRowInfo.Values và BSTableColumnInfo.Values tính từ tọa độ 0,0.

Thêm dòng dữ liệu vào table/bảng

Sub ExcelOnline_Table_Append_rows() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook, tb As BSCloudTable 
   On Error GoTo lbEnd 
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         Exit Sub 
      End If 
   End If 
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables("Table1") 
   'Add values to 5 columns per row
   '(If the 5th column in "Table1" is the formula to be copied,
   'the value added to column 5 must be NULL.)
   Dim myRow As BSTableRowInfo 
   tb.AppendRows Array(4, "D", 10, 12000, Null), , myRow 
   tb.AppendRows Array(5, "E", 8, 10000, Null), , myRow 
   Debug.Print myRow.Index, myRow.Values(0, 4) 
lbEnd: 
   If Err <> 0 Then 
      Debug.Print "Error: " & Err.Description 
   End If 
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Sau khi chạy macro trên ta được kết quả dưới đây

Giải thích ví dụ trên

tb.AppendRows là hàm thêm dữ liệu vào dòng cuối của table. Cấu trúc hàm 

Function AppendRows(Values, [RowIndex], TableRow As BSTableRowInfo, [Async As Boolean = False]) As Boolean
 

+ Values: là mảng giá trị. Các phần tử cột là giá trị đưa vào các cột tương ứng của bảng. Với cột có công thức thì giá trị phần tử mảng phải để là NULL để không ghi đè vào công thức.

Array(4, "D", 10, 12000, Null) : là mảng ghi các giá trị vào 5 cột, cột thứ 5 không ghi.

Values có thể là mảng 1D mỗi phần tử trong mảng là một mảng và các phần tử ứng với các cột;
Value có thể là mảng 2D mỗi dòng ứng với một dòng được thêm vào table, các cột trong mảng là giá trị ứng với các cột của table. Bạn hãy xem ví dụ sau để nắm rõ các cách ghi mảng giá trị lên table.

+ RowIndex : tham số tùy chọn. Nếu là số >=0 dòng sẽ được chèn vào vị trí đó. Nếu bỏ qua, thì dòng sẽ được thêm vào cuối table.

+ TableRow là tham số có kiểu BSTableRowInfo. Tham số này nhận thông tin của dòng được ghi lên table. Các thông tin nhận được như sau:

BSTableRowInfo.Index : vị trí của dòng trong table
BSTableRowInfo.Values :  là mảng 2D chứa các giá trị được cập nhật của dòng trong table. Chỉ mục của mảng (Index) tính từ 0. Đây là điểm khác biệt với chỉ mục của BSCloudRange.Value là tính từ 1.

+ Async : nếu là FALSE (ngầm định) hàm AppendRows sẽ đợi đến khi lệnh được thực thi xong, nếu là TRUE hàm sẽ thực hiện lệnh mà không đợi phản hồi từ server. Bạn có thể dùng TRUE trong trường hợp thêm nhiều dòng dữ liệu và không quan tâm thứ tự đẩy lên table do đó tốc độ chạy sẽ nhanh.

(Với hàm AppendColumns() có cấu trúc và nguyên tắc làm việc giống với hàm AppendRows)

Thêm mảng dữ liệu vào table/bảng (thêm nhiều dòng cùng một lúc)

Ví dụ này bạn sẽ học cách thêm nhiều dòng một lúc vào cuối table, cách này sẽ rất phù hợp khi bạn cần nhập nhiều dữ liệu và đảm bảo tốc độ nhanh. Ví dụ dưới đây bạn hãy chú ý các tạo mảng để đưa vào hàm AppendRows.

Sub ExcelOnline_Table_Write() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook, tb As BSCloudTable, myRow As BSTableRowInfo 
   On Error GoTo lbEnd 
   'Check Connection
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         Exit Sub 
      End If 
   End If 
   Dim v()  'Dynamic array
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables("Table1") 
   'Add values to 5 columns per row
   '(If the 5th column in "Table1" is the formula to be copied,
   'the value added to column 5 must be NULL.)
   'Wb.BeginUpdate'Remember run EndUpdate()
   '(*)TH1: Write row by row
   tb.AppendRows Array(3, "HH001", 10, 12000, Null), , myRow 
   tb.AppendRows Array(4, "HH002", 20, 20000, Null), , myRow 
   '(*)TH2: Write multiple rows at once
   ReDim v(1 To 2) 
   v(1) = Array(5, "HH003", 10, 12000, Null) 
   v(2) = Array(6, "HH004", 15, 15000, Null) 
   tb.AppendRows v, , myRow, False 
   '(*) TH3: Write multiple rows at once with array 2D
   ReDim v(1 To 2, 1 To 5) 
   v(1, 1) = 7: v(1, 2) = "HH005": v(1, 3) = 10: v(1, 4) = 12000: v(1, 5) = Null 
   v(2, 1) = 8: v(2, 2) = "HH006": v(2, 3) = 14: v(2, 4) = 11000: v(2, 5) = Null 
   tb.AppendRows v, , myRow 
   'Format column 4,5
   tb.ListColumns(3).Range.NumberFormat = "#,##0" 
   tb.ListColumns(4).Range.NumberFormat = "#,##0" 
   'Wb.EndUpdate 'Sync all
lbEnd: 
   If Err <> 0 Then 
      Debug.Print "Error: " & Err.Description 
   End If 
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Sau khi chạy macro trên ta được kết quả của Table1 trên Excel Oneline như sau

Hai dòng lệnh dưới đây là định dạng cột "Đ. GIÁ" (Index là 3), cột "T.TIỀN" (Index là 4). Các bạn lưu ý Index của các thành phần thuộc BSCloudTable tính từ 0.

tb.ListColumns(3).Range.NumberFormat = "#,##0"
tb.ListColumns(4).Range.NumberFormat = "#,##0"

Lấy mảng giá trị của table

Có 4 thành phần thuộc tính đối tượng để chúng ta lấy mảng giá trị, gồm:

+ DataBodyRange: trả về kiểu BSCloudRange. Là vùng dữ liệu không bao gồm dòng tiêu đề và dòng tổng cộng.
+ HeaderRowRange : trả về BSCloudRange. Là vùng của dòng tiêu đề.
+ TableRange : trả về BSCloudRange. Là vùng của toàn bộ bảng.
+ TotalRowRange : trả về BSCloudRange. Là vùng của dòng tổng cộng.

Ví dụ dưới đây tôi lấy mảng dữ liệu của DataBodyRange

Sub ExcelOnline_Table_Read_Values() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook, tb As BSCloudTable 
   On Error GoTo lbEnd 
   'Check Connection
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         Exit Sub 
      End If 
   End If 
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables("Table1") 
   Dim v As Variant  'Array 2D
   tb.Refresh  'Update to ensure you get the latest data.
   v = tb.DataBodyRange.Value 
lbEnd: 
   If Err <> 0 Then 
      Debug.Print "Error: " & Err.Description 
   End If 
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

Ở ví dụ trên, biến V là kiểu Variant sẽ nhận mảng 2D từ vùng địa chỉ được xác định từ DataBodyRange. Đây là một BSCloudRange, giống với Excel.Range vì thế mảng giá trị là mảng 2D mà chỉ mục được tính từ 1. Với mảng V nhận được bạn có phân tích, nhập lên sheet, đưa lên các ListBox, ComboBox của Userform,...

Các đối tượng quản lý các dòng, các cột của table

Việc quản trị cột và dòng trong table các bạn hãy chú ý có hai thuộc tính đối tượng là:

+ BSCloudTable.ListRows : kiểu BSCloudTableRows quản lý danh sách các dòng (mỗi dòng là một đối tượng/class BSCloudTableRow).
+ BSCloudTable.ListColumns : kiểu BSCloudTableColumns quản lý danh sách các cột (mỗi cột là một đối tượng/class BSCloudTableColumn).

Đặc điểm hai đối tượng trên: khá giống nhau về kiến trúc:
- Đều có hai hàm Add() để thêm dòng và thêm cột, giống với Table.AppendRows(), Table.AppendColumns().
+ Đều có hàm Load() để cập nhật toàn bộ các dòng và cột.
+ Đều có thuộc tính Count để đếm số dòng hay số cột.
+ Parent : trả về đối tượng cha.
+ Item() : trỏ đến một dòng và trả về điều khiển kiểu BSCloudTableRow hay một cột và trả về điều khiển kiểu BSCloudTableColumn.

Class BSCloudTableRow, BSCloudTableColumn

Hai class này thuộc thành phần quản lý bởi BSCloudTable.ListRows và BSCloudTable.ListColumns. Chúng đều có các thuộc tính và hàm có cách thức làm việc giống nhau và giống với các hàm trong BSCloudTable như sau:

- Hàm Delete() để xóa dòng, xóa cột giống với Table.DeleteRow(), Table.DeleteColumn().
- Hàm Update() để cập nhật giá trị, cơ bản giống với Table.UpdateRow(), Table.UpdateColumn().
- Values: là thuộc tính trả về mảng giá trị của cả dòng hoặc cả cột. Cơ bản giống với Table.GetRowInfo(), Table.GetColumnInfo().
+ Refresh() : hàm cập nhật giá trị của dòng hay cột.

Ví dụ lấy các dòng giá trị thuộc ListRows

Sub ExcelOnline_Table_ReadRows() 
   Dim MyCloud As New BSCloud 
   Dim wb As BSCloudWorkbook, tb As BSCloudTable 
   On Error GoTo lbEnd 
   'Check Connection
   If Not MyCloud.Connected(MyCloudType) Then 
      If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then 
         Exit Sub 
      End If 
   End If 
   Set wb = MyCloud.Workbooks.Open(FileID) 
   Set tb = wb.Tables("Table1") 
   Dim v, Row As BSCloudTableRow, Col As BSCloudTableColumn 
   Dim s, tmp, myRow As BSTableRowInfo 
   For Each Row In tb.ListRows 
      v = Row.Values 
      s = "" 
      For Each tmp In v 
         s = s & vbTab & tmp 
      Next 
      Debug.Print s 
   Next 
   
lbEnd: 
   If Err <> 0 Then 
      Debug.Print "Error: " & Err.Description 
   End If 
   Set tb = Nothing 
   Set wb = Nothing 
   Set MyCloud = Nothing 
End Sub 

(*) Lưu ý: Khi dùng đối tượng ListRows, ListColumns bạn nên dùng trong trường hợp xóa dòng, cột, đếm số phần tử trong danh sách, cập nhật danh sách, lấy hoặc cập nhật giá trị của một dòng hay cột. Còn nếu bạn muốn lấy toàn bộ giá trị của table thì nên lấy thông qua Table.DataBodyRange.Value để đạt tốc độ nhanh nhất.

Các ví dụ khác như: Cập nhật giá trị cho dòng, cột, thêm cột, xóa dòng, xóa cột các bạn hãy xem trong tập tin ví dụ trong đường dẫn: "C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\Cloud\Write data to GoogleSheets_ExcelOnline.xlsm".

Còn tiếp... 

Download Add-in A-Tools để thực hành
Tác giả Nguyễn Duy Tuân