Sheet Management in Cloud Spreadsheets
Requirements: Cloud Spreadsheets
Cloud Spreadsheets provides a suite of easy-to-use spreadsheet and sheet management components. This article covers spreadsheet management in popular cloud services like Google and Office365(Graph API)
Contents
GoogleSheets
The GoogleSheets component provides an easy to use interface to Google Sheets using Google's REST API. The GoogleSheets component supports listing spreadsheets and worksheets, creating, updating, exporting and deleting both spreadsheets and sheets.
OAuth Authentication
Before using the component, an OAuth token must be provided through the Authorization property. The OAuth component included in the toolkit can be used to assist in this process.
By design, user-interaction is required to fetch an OAuth authorization string the first time. A user must be directed to a URL where they will authenticate and grant access to the connecting application. The user is then redirected back to the application along with a code which will be exchanged for an authorization string by the OAuth component. The authorization string is then used in requests to the cloud service.
The authorization string is valid only for a limited period of time; however a parameter can be included in the initial authorization to request a refresh token which can be used without user interaction to update the authorization token. To add an additional parameter the AddParam method can be used. In the below example, the "access_type" parameter is set to "offline".
Fetching an OAuth authorization string using the OAuth component:
Oauth oauth = new Oauth();
oauth.ClientId = "CLIENT_ID"; // This is for testing purposes only
oauth.ClientSecret = "CLIENT_SECRET"; // This is for testing purposes only
oauth.ServerAuthURL = "https://accounts.google.com/o/oauth2/auth";
oauth.ServerTokenURL = "https://accounts.google.com/o/oauth2/token";
oauth.AuthorizationScope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive";
oauth.GrantType = OauthGrantTypes.ogtAuthorizationCode;
oauth.AddParam("access_type", "offline");
gsheets.Authorization = oauth.GetAuthorization();
Creating Spreadsheets
Use the CreateSpreadsheet method to create a new spreadsheet. After creating a spreadsheet with basic information, you can update it and set additional fields by editing the spreadsheet in the Spreadsheets collection and calling the UpdateSpreadsheet method.
Creating a Spreadsheet:
// Create a Spreadsheet
gsheets.CreateSpreadsheet("NewSpreadsheet", "", "");
// Set properties to update
gsheets.Spreadsheets[0].Name = "UpdatedSpreadsheet";
gsheets.Spreadsheets[0].Locale = "en_AU";
gsheets.Spreadsheets[0].TimeZone = "Australia/Sydney";
// Update the Spreadsheet
gsheets.UpdateSpreadsheet();
Listing Spreadsheets
Spreadsheets in can be retrieved using the ListSpreadsheets method. The SpreadsheetList event will fire for each spreadsheet listed, and the Spreadsheets collection will be populated when the method returns. If the number of spreadsheets exceeds the page size, the ListSpreadsheetsMarker will be populated. You can call ListSpreadsheets again to retrieve the next page of spreadsheets. These spreadsheets will be added to the end of the Spreadsheets collection.
Listing Spreadsheets:
do
{
gsheets.ListSpreadsheets();
} while (gsheets.SpreadsheetsMarker.Length > 0);
Deleting Spreadsheets
Spreadsheets for the user can be deleted via the DeleteSpreadsheet method.
Delete a Spreadsheet:
// List spreadsheets and search for a spreadshet with the name "January 23"
gsheets.ListSpreadsheets();
for (int i = 0; i < gsheets.Spreadsheets.Count; i++) {
if (gsheets.Spreadsheets[i].Name == "January 23") {
//Delete the spreadsheet
gsheets.DeleteSpreadsheet(gsheets.Spreadsheets[i].Id);
break;
}
}
Exporting Spreadsheet
A spreadsheet can be exported using the Export method. You will need to provide the Id of the spreadsheet and the path where the spreadsheet will be stored in. It's important to keep in mind that the file extension must be included in the file path because that will determine the type of file it will be exported to.
Supported file extensions are: csv, ods, pdf, tsv, xlsx, zip.
Export a Spreadsheet:
// Retrieve the Id of the spreadsheet you need to export
string spreadsheetId = gsheets.Spreadsheets[0].Id;
// Set save directory and the spreadsheet name
string saveDir = "../../Documents/MyDocuments/";
string fileName = "MySpreadsheet.pdf";
string filePath = $"{saveDir}{fileName}";
// The spreadsheet will be exported as a pdf file in 'MyDocuments' directory
gsheets.Export(spreadsheetId, filePath);
Adding Sheets
Use the AddSheet to add a new sheet to an existing spreadsheet. The new sheet will be added at the beginning of the Sheets collection. After adding the sheet while specifying the name and index, you can update its properties and set additional fields by editing the sheet in the Sheets collection and calling the UpdateSheet method.
Adding a new sheet:
// Load a spreadsheet to work with first
gsheets.LoadSpreadsheet(spreadsheetId);
// Add a new sheet
gsheets.AddSheet(1, "Expenses");
GSSheet sheet1 = gsheets.Sheets[0];
Loading Ranges
When loading a range, there is a limit to the amount of cells you can update. Specify the range to be loaded when calling LoadRange in standard A1 notation, such as 'A1:B3'. Attempting to modify any cells outside the second column and third row will result in a "Value out of range" error.
Loading a specific range:
// Retrieve sheet id
int sheetId = gsheets.Sheets[0].SheetId;
// Load a range
gsheets.LoadRange(sheetId, "A1:D10");
gsheets.Cell = "A4";
gsheets.CellValue = "4th row";
gsheets.UpdateSheet();
The currently loaded range:
Updating Sheets
Sheet properties can be updated after adding the sheet by calling the UpdateSheet method after selecting the sheet from the collection. The UpdateSheet method will always look for changes in the first sheet in the collection.
Updating Sheet properties:
// Selecting sheet we want to update
GSSheet sheet1 = gsheets.Sheets[0];
// Setting properties to update
sheet1.SheetIndex = 3;
sheet1.Title = "January Expenses";
sheet1.RightToLeft = true;
sheet1.HideGridlines = true;
sheet1.TotalColumnCount = 5;
sheet1.TotalRowCount = 20;
// Updating sheet
gsheets.UpdateSheet();
The UpdateSheet method can also be used to update cells in a certain sheet or range. Before selecting a cell make sure that at least one sheet is loaded into the Sheets collection. When selecting a cell make sure to use A1 notation, such as 'C3' which represents the third row in the third column.
Updating sheet cells:
// Load a sheet
gsheets.LoadSheet(sheetId);
// Update cell values accordingly
gsheets.Cell = "A1";
gsheets.CellValue = "1";
gsheets.Cell = "B1";
gsheets.CellValue = "2";
gsheets.Cell = "C1";
gsheets.CellValue = "=A1+B1";
gsheets.CellNote = "Sum of A1 and B1";
gsheets.UpdateSheet();
Deleting Sheets
Sheets in a spreadsheet can be deleted using the DeleteSheet method. Simply call the method, provide the sheet Id as an argument and the specified sheet will be removed permanently.
Delete a Sheet:
int sheetId = gsheets.Sheets[0].SheetId;
gsheets.DeleteSheet(sheetId);
Office365Excel
The Office365Excel component provides an easy to use interface to workbook management via Microsoft's Graph API. The Office365Excel component supports updating worksheets, creating and deleting both workbooks and worksheets.
OAuth Authentication
Before using the component, an OAuth token must be provided through the Authorization property. The OAuth component included in the toolkit can be used to assist in this process.
By design, user-interaction is required to fetch an OAuth authorization string the first time. A user must be directed to a URL where they will authenticate and grant access to the connecting application. The user is then redirected back to the application along with a code which will be exchanged for an authorization string by the OAuth component. The authorization string is then used in requests to the cloud service.
The authorization string is valid only for a limited period of time; however a parameter can be included in the initial authorization to request a refresh token which can be used without user interaction to update the authorization token.
Fetching an OAuth authorization string using the OAuth component:
Oauth oauth = new Oauth();
oauth.ClientId = "CLIENT_ID"; // This is for testing purposes only
oauth.ClientSecret = "CLIENT_SECRET"; // This is for testing purposes only
oauth.ServerAuthURL = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize";
oauth.ServerTokenURL = "https://login.microsoftonline.com/common/oauth2/v2.0/token";
oauth.AuthorizationScope = "offline_access files.readwrite user.read";
oauth.GrantType = OauthGrantTypes.ogtAuthorizationCode;
oexcel.Authorization = oauth.GetAuthorization();
Creating Workbooks
Use the CreateWorkbook method to create a new workbook. The workbook attributes cannot be updated after creation.
Creating a new Workbook:
oexcel.CreateWorkbook("NewWorkbook");
OEWorkbook latestWorkbook = oexcel.Workbooks[0]; // Newly added workbook will be at the beginning of the Workbooks collection
Listing Workbooks
Workbooks for the user can be retrieved via the ListWorkbooks method. The WorkbookList event will fire for each workbook, and the Workbooks collection will be populated when the method returns. If the number of workbooks exceeds the page size, the WorkbooksMarker will be populated. You can call ListWorkbooks again to retrieve the next page of workbooks. These workbooks will be added to the end of the Workbooks collection.
Listing Workbooks:
do
{
oexcel.ListWorkbooks();
} while (oexcel.WorkbooksMarker.Length > 0);
Deleting Workbooks
Workbooks for the user can be deleted via the DeleteWorkbook method. Once this method is called, the workbook will be deleted permanently and also removed from the Workbooks collection.
Delete a Workbook:
// List workbooks and delete the workbook named "UnusedWorkbook"
oexcel.ListWorkbooks();
for (int i = 0; i < oexcel.Workbooks.Count; i++) {
if (oexcel.Workbooks[i].Name == "UnusedWorkbook") {
oexcel.DeleteWorkbook(oexcel.Workbooks[i].Id);
break;
}
}
Creating Sessions
The CreateSession method can be used to create a new session after loading a workbook. We can makePersistent,Non-Persistent orSessionless requests to the workbook in the server, this is determined by the type of session created, if one is even created. Sessionless requests can also be done to the server but that would mean the workbook would be closed and opened each time. It is recommended to use persistent sessions if working with a Workbook where continious changes will be done to the workbook data.
Creating a new session:
// Load the workbook first
oexcel.LoadWorkbook(workbookId);
oexcel.CreateSession(true); // Creating a persistent session
oexcel.CreateSession(false); // Creating a non-persistent session
Creating Worksheets
Use the AddWorksheet to add a new worksheet. The new worksheet is added at the end of the workbook, but in the Worksheets collection it will be the first with an index of 0. After adding the worksheet with just the name, you can update it and set additional fields by editing the worksheet in the Worksheets collection and calling UpdateWorksheet.
Adding a worksheet:
// Load a workbook
oexcel.LoadWorkbook(spreadsheetId);
oexcel.AddWorksheet("New worksheet"); // The new sheet will be added at the end of the workbook
string lastAddedWorksheet = oexcel.Worksheets[0].WorksheetId; // The new sheet will be in the beginning of the Worksheets collection
Update Worksheets
Worksheet properties can be updated after adding the worksheet by calling the UpdateWorksheet method after selecting the worksheet from the collection. The UpdateWorksheet method will always look for changes in the first worksheet in the collection.
Updating Worksheet properties:
// Setting properties to update
worksheet.Visibility = TOEWorksheetVisibilities.oewvHidden;
worksheet.Position = 2;
worksheet.Name = "UpdatedWorksheet";
// Updating worksheet
oexcel.UpdateWorksheet();
The UpdateWorksheet method can also be used to update cells in a certain worksheet. Before selecting a cell make sure that at least one worksheet is loaded into the Worksheets collection. When selecting a cell make sure to use A1 notation, such as 'C3' which represents the third row in the third column.
Updating worksheet cells:
// Load a worksheet
oexcel.LoadWorksheet(worksheetId);
// Update cell values accordingly
oexcel.Cell = "A1";
oexcel.CellValue = "1";
oexcel.Cell = "D1";
oexcel.CellFormula = "=A1+B1+C1";
oexcel.UpdateWorksheet();
Deleting Worksheets
Worksheets in a workbook can be deleted using the DeleteWorksheet method. Simply call the method, provide the worksheet Id as an argument and the specified worksheet will be removed permanently.
Delete a Worksheet:
string worksheetId = oexcel.Worksheets[0].WorksheetId;
oexcel.DeleteWorksheet(worksheetId);
We appreciate your feedback. If you have any questions, comments, or suggestions about this article please contact our support team at support@nsoftware.com.