To start with, we have following spreadsheet available in my Google Doc. We have EmpDetails spreadsheet and it two worksheets EmpDetails and EmpSalaryDetails. We will delete the existing worksheet EmpSalaryDetails from this spreadsheet.
We have following .NET GUI interface to deal with this example.
We have two listview control and two buttons on the form. The concept is when we click on Pull SpreadSheet List button; the .NET code will connect with the Google Doc and pull all the spreadsheet list available and show it in Listview 1 control. After selecting an individual SpreadSheet from ListView1, all the available worksheet in that spreadsheet and their details will be shown in ListView 2. We will select the worksheet that we want to delete from ListView 2 and click on Delete Worksheet; the code logic will connect with the Google Doc and delete the selected worksheet under Spreadsheet.
We have downloaded and installed the Google Data API library, so we are using following namespace in our program.
using Google.GData; using Google.GData.Client; using Google.GData.Extensions; using Google.GData.Spreadsheets;
We are using following two variables in our form spreadsheetName stores the Google Spreadsheet name which users select from the list. Variable wrkSheetName stores the worksheet name which users selects from Listview 2.
public partial class Form1 : Form { string spreadsheetName; string wrkSheetName;
The .NET code snippet on button “Pull SpreadSheet List” is following. In this code logic, we are defining an instance of Google Spreadsheets Service and passing the Gmail credentials to connect with Googl e Doc. We are creating the Spreasheet Query and passing it to the Spreadsheet feed. The Spreadsheet feed connects with the Google Doc and returns feed. Each feed represents a Google Spreadsheet. We have a foreach loop where we are navigating through each of the feed and reading its name, summary and updated date.
private void button1_Click(object sender, EventArgs e) { listView1.Columns.Add("SpreadSheet Name"); listView1.Columns.Add("Summary"); listView1.Columns.Add("Published Date"); listView1.FullRowSelect = true; SpreadsheetsService GoogleExcelService; GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App"); GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword"); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed myFeed = GoogleExcelService.Query(query); foreach (SpreadsheetEntry mySpread in myFeed.Entries) { listView1.Items.Add(new ListViewItem(new string[] { mySpread.Title.Text, mySpread.Summary.Text, mySpread.Updated.ToShortDateString() })); } }
The .NET code logic on Listview 1 SelectedIndexChanged event is following. We are reading the spreadsheet name selected by the user and reading the Spreadsheet properties (worksheet name, no. Of rows and columns into each worksheet) and adding it to ListView 2.
private void listView1_SelectedIndexChanged(object sender, EventArgs e) { spreadsheetName = ""; listView2.Clear(); listView2.Columns.Add("Worksheet Name"); listView2.Columns.Add("No. of Columns"); listView2.Columns.Add("No. of Rows"); listView2.Columns.Add("Summary"); ListView.SelectedListViewItemCollection SpreadsheetList = listView1.SelectedItems; foreach (ListViewItem item in SpreadsheetList) { spreadsheetName = item.SubItems[0].Text.ToString(); } if (spreadsheetName.Length > 0) { SpreadsheetsService GoogleExcelService; GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App"); GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword"); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed myFeed = GoogleExcelService.Query(query); foreach (SpreadsheetEntry mySpread in myFeed.Entries) { if (mySpread.Title.Text == spreadsheetName) { WorksheetFeed wfeed = mySpread.Worksheets; foreach (WorksheetEntry wsheet in wfeed.Entries) { listView2.Items.Add(new ListViewItem(new string[] { wsheet.Title.Text, wsheet.Cols.ToString(), wsheet.Rows.ToString(), wsheet.Summary.Text })); } } } } }
We have following .NET code interface on Listview2 SelectedIndexChanged event which simply stores the name of the worksheet selected in the list view 2 control.
private void listView2_SelectedIndexChanged(object sender, EventArgs e) { wrkSheetName = ""; ListView.SelectedListViewItemCollection WorkSheetList = listView2.SelectedItems; foreach (ListViewItem item in WorkSheetList) { wrkSheetName = item.SubItems[0].Text.ToString(); } }
The final piece of code we have on button "Delete Worksheet" is following. Here, we are navigating through each spreadsheet and creating a worksheet feed which returns individual worksheet into its each feed. We are comparing each worksheet name and if it matches to the selected worksheet in Lisview 2, we are simply deleting this. In this code block we are deleting worksheet EmpSalaryDetails.
private void button5_Click(object sender, EventArgs e) { if (wrkSheetName.Length > 0) { SpreadsheetsService GoogleExcelService; GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App"); GoogleExcelService.setUserCredentials("gmailuserid", "Gmailpassword"); SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed myFeed = GoogleExcelService.Query(query); foreach (SpreadsheetEntry mySpread in myFeed.Entries) { if (mySpread.Title.Text == spreadsheetName) { WorksheetFeed wfeed = mySpread.Worksheets; foreach (WorksheetEntry wsheet in wfeed.Entries) { if (wsheet.Title.Text == wrkSheetName) { wsheet.Delete(); } } } } System.Windows.Forms.MessageBox.Show("Worksheet" + wrkSheetName + "deleted Successfully"); } }
After setting up the code logic, I connected to the internet and clicked on button “Pull SpreadSheet Lists”, which returns the entire spreadsheet lists available into my Google Doc account and added it to ListView 1. Then, I selected Spreadsheet EmpDetails and the code logic of ListView1 SelectedIndexChanged runs and return all the worksheet available into SpreadSheet EmpDetails. I selected the worksheet EmpSalaryDetails and clicked on button Delete Worksheet . The code runs and deleted the worksheet EmpSalaryDetals.
I login into my Google Doc account and open the spreadsheet EmpDetails to verify the deletion. As expected, the worksheet EmpSalaryDetails was successfully deleted.
So, our objective to learn deletion of worksheet has been achived.
Thanks for reading this post.
If you want to explore with the Source code, you can find the source code of this .NET program into Download Zone
