public DictionaryTable Download(string sheetName)
{
var service = new SpreadsheetsService(applicationName);
WorksheetEntry worksheet = GetWorksheetEntres(service).FirstOrDefault(e => e.Title.Text == sheetName) as WorksheetEntry;
if (worksheet == null)
{
return(null);
}
List <DictionaryItem> retval = new List <DictionaryItem>();
// Fetch the cell feed of the worksheet.
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.MaximumColumn = 2;
CellFeed cellFeed = service.Query(cellQuery);
for (int i = 0; i < cellFeed.Entries.Count; i = i + 2)
{
retval.Add(
new DictionaryItem(new string[]
{ ((CellEntry)cellFeed.Entries[i]).Value, ((CellEntry)cellFeed.Entries[i + 1]).Value }));
}
return(new DictionaryTable(sheetName, retval.ToArray()));
}
/// <summary>
/// カラム数が変わっていたら表を拡張してカラム名を更新
/// </summary>
/// <param name="wsEntry"></param>
/// <param name="columns"></param>
/// <returns></returns>
protected WorksheetEntry CheckColumns(WorksheetEntry wsEntry, string[] columns)
{
if (columns.Length > wsEntry.Cols)
{
wsEntry.Cols = (uint)columns.Length;
WorksheetEntry newEntry = (WorksheetEntry)wsEntry.Update();
/*
* http://stackoverflow.com/questions/22719170/updating-cell-in-google-spreadsheets-returns-error-missing-resource-version-id
* http://stackoverflow.com/questions/20841411/google-spreadsheets-api-c-sharp-missing-resource-version-id-on-batch-update/23438381
*
* ETag:*をするとか、そもそもBatchよりPublishの方が分かりやすい、とか。
*/
CellFeed cellFeed = wsEntry.QueryCellFeed();
uint col = 1;
foreach (var it in columns)
{
CellEntry batchEntry = cellFeed[1, col++];
batchEntry.InputValue = it;
batchEntry.Etag = "*";
}
cellFeed.Publish();
return(newEntry);
}
return(wsEntry);
}
public void ObtenerActualizarRelaciones(string linkHojaPrincipal, SpreadsheetsService servicio)
{
//Recibe el link de la hoja principal, obtiene el link de relaciones (si tiene) para la hoja actual y lo actualiza (por si se agregaron relaciones)
var paginaRelaciones = CuentaUsuario.RecuperarValorDeCuentaLocal(linkHojaPrincipal + "|relacionesInsumoProducto");
var relacionesTexto = "";
if (paginaRelaciones != null)
{
_celdas = new ServiciosGoogle().ObtenerCeldasDeUnaHoja(paginaRelaciones, servicio);
foreach (CellEntry celda in _celdas.Entries)
{
if (celda.Row > 2)
{
if (celda.Column == 1 || celda.Column == 3) // 1: Código producto - 3: Código materia prima
{
relacionesTexto += celda.Value + "|";
}
if (celda.Column == 5) // 5: Cantidad de materia prima (3) que lleva el producto (1)
{
relacionesTexto += celda.Value + "?";
}
}
}
}
CuentaUsuario.AlmacenarRelacionesInsumoProducto(relacionesTexto.TrimEnd('?'));
}
public DictionaryTable Download(string sheetName)
{
SpreadsheetsService service = new SpreadsheetsService(applicationName);
service.setUserCredentials(userName, password);
WorksheetEntry worksheet = this.GetWorksheetEntrees(service).FirstOrDefault(e => e.Title.Text == sheetName);
if (worksheet == null)
{
return(null);
}
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.MaximumColumn = 2;
CellFeed cellFeed = service.Query(cellQuery);
List <DictionaryItem> retval = new List <DictionaryItem>();
for (int i = 0; i < cellFeed.Entries.Count; i = i + 2)
{
retval.Add(
new DictionaryItem(new string[] { ((CellEntry)cellFeed.Entries[i]).Value, ((CellEntry)cellFeed.Entries[i + 1]).Value }));
}
return(new DictionaryTable(sheetName, retval.ToArray()));
}
public GoogleWorksheet(WorksheetEntry worksheet)
{
Service = (SpreadsheetsService)worksheet.Service;
// Fetch the cell feed of the worksheet.
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = Service.Query(cellQuery);
this.Columns = cellFeed.RowCount.IntegerValue;
this.Rows = cellFeed.ColCount.IntegerValue;
// Iterate through each cell, printing its value.
foreach (CellEntry cell in cellFeed.Entries)
{
Console.WriteLine(cell.Edited.DateValue);
// Print the cell's address in A1 notation
Console.WriteLine(cell.Title.Text);
// Print the cell's address in R1C1 notation
Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1));
// Print the cell's formula or text value
Console.WriteLine(cell.InputValue);
// Print the cell's calculated value if the cell's value is numeric
// Prints empty string if cell's value is not numeric
Console.WriteLine(cell.NumericValue);
// Print the cell's displayed value (useful if the cell has a formula)
Console.WriteLine(cell.Value);
Console.ReadLine();
}
}
public void CellQuery(WorksheetEntry worksheet)
{
// Fetch the cell feed of the worksheet.
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
//cellQuery.ModifiedSince = new DateTime(2013, 9, 1);
//cellQuery.StartDate = new DateTime(2013, 9, 18);
//cellQuery.MinPublication = new DateTime(2013, 9, 18);
CellFeed cellFeed = this.Service.Query(cellQuery);
// Iterate through each cell, printing its value.
foreach (CellEntry cell in cellFeed.Entries)
{
Console.WriteLine(cell.Edited.DateValue);
// Print the cell's address in A1 notation
Console.WriteLine(cell.Title.Text);
// Print the cell's address in R1C1 notation
Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1));
// Print the cell's formula or text value
Console.WriteLine(cell.InputValue);
// Print the cell's calculated value if the cell's value is numeric
// Prints empty string if cell's value is not numeric
Console.WriteLine(cell.NumericValue);
// Print the cell's displayed value (useful if the cell has a formula)
Console.WriteLine(cell.Value);
Console.ReadLine();
}
}
/**
* Connects to the specified {@link SpreadsheetsService} and uses a batch
* request to retrieve a {@link CellEntry} for each cell enumerated in {@code
* cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
* identifier.
*
* @param service the spreadsheet service to use.
* @param cellFeed the cell feed to use.
* @param cellAddrs list of cell addresses to be retrieved.
* @return a dictionary consisting of one {@link CellEntry} for each address in {@code
* cellAddrs}
*/
private static Dictionary <String, CellEntry> GetCellEntryMap(
SpreadsheetsService service, CellFeed cellFeed, List <CellAddress> cellAddrs)
{
CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
foreach (CellAddress cellId in cellAddrs)
{
CellEntry batchEntry = new CellEntry((uint)cellId.Row, (uint)cellId.Col, cellId.InputValue);
batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.IdString));
batchEntry.BatchData = new GDataBatchEntryData(cellId.IdString, GDataBatchOperationType.query);
batchRequest.Entries.Add(batchEntry);
}
CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));
Dictionary <String, CellEntry> cellEntryMap = new Dictionary <String, CellEntry>();
foreach (CellEntry entry in queryBatchResponse.Entries)
{
cellEntryMap.Add(entry.BatchData.Id, entry);
Console.WriteLine("batch {0} (CellEntry: id={1} editLink={2} inputValue={3})",
entry.BatchData.Id, entry.Id, entry.EditUri,
entry.InputValue);
}
return(cellEntryMap);
}
private string ToGoogleTable()
{
WorksheetFeed wsFeed = TargetTable.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = service.Query(cellQuery);
CellEntry cellEntry = new CellEntry(1, 1, "oid");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 2, "value");
cellFeed.Insert(cellEntry);
cellEntry = new CellEntry(1, 3, "type");
cellFeed.Insert(cellEntry);
ProgressBarSetStartParams(progressBar1, ListData[ListData.Count - 1].Count);
tabControlShow(progressBar1);
tabControlShow(label3);
for (int i = 0; i < ListData[ListData.Count - 1].Count; i++)
{
IncrementProgressbar(progressBar1);
IncrementGeneralLabel(string.Format("Выполнено {0}/{1}", i + 1, ListData[ListData.Count - 1].Count));
service.Insert(listFeed, ListData[ListData.Count - 1].GetCustom(i));
}
tabControlShow(progressBar1, false);
tabControlShow(label3, false);
return("Данные записаны");
}
protected void SetValue(UInt32 index, String value)
{
CellEntry cellEntry;
if (Cells.TryGetValue(index, out cellEntry))
{
cellEntry.InputValue = value;
cellEntry.Update();
}
else if (CellLink != null && Service != null)
{
CellQuery cellQuery = new CellQuery(CellLink.HRef.ToString());
cellQuery.MinimumRow = cellQuery.MaximumRow = RowNbr;
cellQuery.MinimumColumn = cellQuery.MaximumColumn = index;
cellQuery.ReturnEmpty = ReturnEmptyCells.yes;
CellFeed cellFeed = Service.Query(cellQuery);
cellEntry = cellFeed.Entries[0] as CellEntry;
if (cellEntry != null)
{
cellEntry.InputValue = value;
cellEntry.Update();
Cells.Add(cellEntry.Column, cellEntry);
}
}
}
private void GetGameDateName(SpreadsheetsService service, SpreadsheetEntry entry, WorksheetFeed wsFeed)
{
GamesPlayed = new List <GameEvent>();
List <CellFeed> cellFeeds = DoCellQuery(service, wsFeed, 2, 1, 1);
int gameID = 0;
for (int i = 0; i < cellFeeds.Count; i++)
{
CellFeed currentCellFeed = cellFeeds[i];
if (currentCellFeed != null)
{
foreach (CellEntry cell in currentCellFeed.Entries)
{
GameEvent newGameEvent = new GameEvent();
string dateTimeString = cell.InputValue;
DateTime dateTime = DateTime.Parse(dateTimeString);
newGameEvent.Date = dateTime;
newGameEvent.Name = wsFeed.Entries[i].Title.Text;
newGameEvent.ID = gameID;
GamesPlayed.Add(newGameEvent);
gameID++;
}
}
}
GetPlayers(service, entry, wsFeed);
}
private List <CellFeed> DoCellQuery(SpreadsheetsService service, WorksheetFeed wsFeed, uint minRow, uint minColumn, uint maxColumn)
{
List <CellFeed> cellFeeds = new List <CellFeed>();
int i = 0;
foreach (WorksheetEntry worksheet in wsFeed.Entries)
{
if (worksheet.Title.Text[0] != '0')
{
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.MinimumRow = minRow;
cellQuery.MinimumColumn = minColumn;
cellQuery.MaximumColumn = maxColumn;
CellFeed cellFeed = service.Query(cellQuery);
cellFeeds.Insert(i, cellFeed);
i++;
}
else
{
CellFeed cellFeed = null;
cellFeeds.Insert(i, cellFeed);
i++;
}
}
return(cellFeeds);
}
private static void AddNewLocalisation(string key, string text)
{
WorksheetEntry worksheet = GetWorksheet();
uint row = ++worksheet.Rows;
worksheet.Update();
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.MinimumRow = row;
cellQuery.ReturnEmpty = ReturnEmptyCells.yes;
cellQuery.MinimumColumn = 1;
cellQuery.MaximumColumn = 2;
CellFeed cellFeed = ss.Query(cellQuery);
foreach (CellEntry cell in cellFeed.Entries)
{
switch (cell.Column)
{
case 1:
cell.InputValue = key;
break;
case 2:
cell.InputValue = text;
break;
}
cell.Update();
}
}
private Task <IReadOnlyCollection <KeyValuePair <CellFeed, IAggregateProgress> > > MapFeedBatchesAsync(List <List <SheetCellInfo> > cellGroups, string cellsFeedUrl, string batchUrl, IAggregateProgress progress, CancellationToken cancellationToken)
{
return(Task.Run <IReadOnlyCollection <KeyValuePair <CellFeed, IAggregateProgress> > >(() =>
{
var progresses = progress.CreateParallelProgresses(cellGroups.Select(g => (double)g.Count).ToList());
var batches = cellGroups.Zip(progresses, (cells, p) =>
{
var mappingFeed = new CellFeed(new Uri(cellsFeedUrl), Service)
{
Batch = batchUrl
};
foreach (var cellEntry in cells)
{
mappingFeed.Entries.Add(cellEntry.BatchCreateEntry(cellsFeedUrl));
}
cancellationToken.ThrowIfCancellationRequested();
return new KeyValuePair <CellFeed, IAggregateProgress>(mappingFeed, p);
});
return batches.ToList();
}, cancellationToken));
}
/// <summary>
/// Updates a single cell in the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to update</param>
private static void UpdateCell(SpreadsheetsService service, WorksheetEntry entry)
{
AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
Console.WriteLine();
Console.Write("Row of cell to update? ");
string row = Console.ReadLine();
Console.Write("Column of cell to update? ");
string column = Console.ReadLine();
query.MinimumRow = query.MaximumRow = uint.Parse(row);
query.MinimumColumn = query.MaximumColumn = uint.Parse(column);
CellFeed feed = service.Query(query);
CellEntry cell = feed.Entries[0] as CellEntry;
Console.WriteLine();
Console.WriteLine("Current cell value: {0}", cell.Cell.Value);
Console.Write("Enter a new value: ");
string newValue = Console.ReadLine();
cell.Cell.InputValue = newValue;
AtomEntry updatedCell = cell.Update();
Console.WriteLine("Successfully updated cell: {0}", updatedCell.Content.Content);
}
public CellFeed GetCellFeed(int sheetNum)
{
WorksheetFeed wsFeed = spreadSheetEntry.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[sheetNum];
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = service.Query(cellQuery);
return(cellFeed);
}
private async void ObtenerDatosMovimientosDesdeHCG()
{
try
{
ContenedorMovimientos.Children.Add(_indicadorActividad);
IsBusy = true;
await Task.Run(async() => {
if (CuentaUsuario.ValidarTokenDeGoogle())
{
var linkHistoricosCeldas = CuentaUsuario.ObtenerLinkHojaHistoricosCeldas(CuentaUsuario.ObtenerLinkHojaConsulta());
_celdas = _servicioGoogle.ObtenerCeldasDeUnaHoja(linkHistoricosCeldas, _servicio);
}
else
{
//Si se quedó la pantalla abierta un largo tiempo y se venció el token, se cierra y refresca el token
var paginaAuntenticacion = new PaginaAuntenticacion(true);
Navigation.InsertPageBefore(paginaAuntenticacion, this);
await Navigation.PopAsync();
}
});
}
finally
{
IsBusy = false; //Remueve el Indicador de Actividad.
}
_nombresColumnas = new string[_celdas.ColCount.Count];
var movimientos = new List <string[]>();
var movimiento = new string[_celdas.ColCount.Count];
foreach (CellEntry celda in _celdas.Entries)
{
if (celda.Row != 1)
{
if (celda.Column == 1)
{
movimiento = new string[_celdas.ColCount.Count];
}
movimiento.SetValue(celda.Value, (int)celda.Column - 1);
if (celda.Column == _celdas.ColCount.Count)
{
movimientos.Add(movimiento);
}
}
else
{
_nombresColumnas.SetValue(celda.Value, (int)celda.Column - 1);
}
}
LlenarGrillaDeMovimientos(movimientos);
}
public Pedido(Clases.Pedido pedido, SpreadsheetsService servicio, CellFeed celdas = null)
{
InitializeComponent();
InicializarValoresGenerales();
_pedido = pedido;
_servicio = servicio;
_celdas = celdas;
ConstruirVistaDePedido();
}
/// <summary>
/// Sets the list view on the Cells tab
/// </summary>
/// <param name="feed">The feed providing the data</param>
void SetCellListView(CellFeed feed)
{
// Clear out all the old information
this.cellsListView.Items.Clear();
this.cellsListView.Columns.Clear();
this.editUriTable.Clear();
AtomEntryCollection entries = feed.Entries;
// Add in the column headers, as many as the column count asks
// The number of rows, we only care to go as far as the data goes
this.cellsListView.Columns.Add("", 20, HorizontalAlignment.Left);
for (int i = 1; i < feed.ColCount.Count; i++)
{
this.cellsListView.Columns.Add(i.ToString(), 80, HorizontalAlignment.Center);
}
int currentRow = 1;
int currentCol = 1;
ListViewItem item = new ListViewItem();
item.Text = 1.ToString();
for (int i = 0; i < entries.Count; i++)
{
CellEntry entry = entries[i] as CellEntry;
if (entry != null)
{
// Add the current row, since we are starting
// a new row in data from the feed
while (entry.Cell.Row > currentRow)
{
this.cellsListView.Items.Add(item);
item = new ListViewItem();
item.Text = (currentRow + 1).ToString();
currentRow++;
currentCol = 1;
}
// Add blank entries where there is no data for the column
while (entry.Cell.Column > currentCol)
{
item.SubItems.Add("");
currentCol++;
}
// Add the current data entry
item.SubItems.Add(entry.Cell.Value);
this.editUriTable.Add("R" + currentRow + "C" + currentCol,
entry);
currentCol++;
}
}
}
private CellFeed GetCellFeed(SpreadsheetsService service, SpreadsheetEntry spEntry)
{
WorksheetFeed wsFeed = spEntry.Worksheets;
WorksheetEntry wsEntry = (WorksheetEntry)wsFeed.Entries[0];
AtomLink wLink = wsEntry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellQuery = new CellQuery(wLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
return(cellFeed);
}
/// <summary>
/// Get cells in the given <paramref name="worksheet" />.
/// </summary>
/// <param name="query">The <typeparamref name="Google.GData.Spreadsheets.CellQuery" /> which should be executed.</param>
/// <returns>Returns a <typeparamref name="Google.GData.Spreadsheets.CellEntry" /> array.</returns>
/// <exception cref="System.ArgumentNullException"><paramref name="query"/> is null.</exception>
public virtual CellEntry[] GetCells(CellQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
this.cellFeed = this.service.Query(query);
var result = new CellEntry[this.cellFeed.Entries.Count];
this.cellFeed.Entries.CopyTo(result, 0);
return(result);
}
private void writeJob(string siteName, int count, uint index, CellFeed cellFeed)
{
Random rand = new Random();
siteName += " " + new String('I', rand.Next(2) + 1);
string description = this.partNames[rand.Next(partNames.Count - 1)] + " " +
this.repairActivities[rand.Next(repairActivities.Count - 1)];
cellFeed.Insert(new CellEntry(index + 2, 1, "JOB" + index.ToString("0000#")));
cellFeed.Insert(new CellEntry(index + 2, 2, description));
cellFeed.Insert(new CellEntry(index + 2, 3, siteName));
}
void writeDataToGoogle(object sender, DoWorkEventArgs e)
{
WorksheetEntry ws = this.access.getDataWorksheet();
CellFeed cellFeed = this.access.service.Query(new CellQuery(ws.CellFeedLink));
cellFeed.Insert(new CellEntry(1, 1, "JobID"));
cellFeed.Insert(new CellEntry(1, 2, "Job Description"));
cellFeed.Insert(new CellEntry(1, 3, "Job Location"));
if (this.setup())
{
this.writeJobsToGoogleDocs(cellFeed);
}
}
/// <summary>
/// get the cell entry
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
private static CellEntry GetCellEntry(this GS2U_Worksheet worksheet, int column, int row)
{
CellQuery cellQuery = new CellQuery(worksheet.worksheetEntry.CellFeedLink);
cellQuery.MinimumRow = (uint)row;
cellQuery.MaximumRow = (uint)row;
cellQuery.MinimumColumn = (uint)column;
cellQuery.MaximumColumn = (uint)column;
CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
return((CellEntry)cellFeed.Entries[0]);
}
public void AddNewHeader(string header)
{
WorksheetEntry.Cols += 1;
WorksheetEntry.Update();
CellQuery cellQuery = new CellQuery(WorksheetEntry.CellFeedLink);
cellQuery.MaximumRow = 1;
//cellQuery.Range = "A543:L543";
CellFeed cellFeed = service.Query(cellQuery);
CellEntry cellEntry = new CellEntry(1, (uint)cellFeed.Entries.Count + 1, header);
cellFeed.Insert(cellEntry);
}
private async void ObtenerDatosPedidosDesdeHCG()
{
try
{
IsBusy = true;
await Task.Run(async() =>
{
if (CuentaUsuario.ValidarTokenDeGoogle())
{
var linkHojaPedidos = CuentaUsuario.ObtenerLinkHojaPedidos();;
_celdas = _servicioGoogle.ObtenerCeldasDeUnaHoja(linkHojaPedidos, _servicio);
var pedidos = new List <string[]>();
var pedido = new string[_celdas.ColCount.Count];
foreach (CellEntry celda in _celdas.Entries)
{
if (celda.Row != 1)
{
if (celda.Column == 1)
{
pedido = new string[_celdas.ColCount.Count];
}
pedido.SetValue(celda.Value, (int)celda.Column - 1);
if (celda.Column == _celdas.ColCount.Count)
{
pedidos.Add(pedido);
}
}
}
LlenarGrillaDePedidos(pedidos);
}
else
{
// Si se quedó la pantalla abierta un largo tiempo y se venció el token, se cierra y refresca el token.
var paginaAuntenticacion = new PaginaAuntenticacion(true);
Navigation.InsertPageBefore(paginaAuntenticacion, this);
await Navigation.PopAsync();
}
});
}
finally
{
// Remueve el Indicador de Actividad.
IsBusy = false;
}
}
public List <TDataRow> ReadAll <TDataRow>(String documentName, String sheetTitle) where TDataRow : GdataRow, new()
{
SpreadsheetQuery sheetQuery = new SpreadsheetQuery();
SpreadsheetFeed sheetFeed = AuthorisedSpreadsheetsService.Query(sheetQuery);
AtomEntry affiliates = (from x in sheetFeed.Entries where x.Title.Text.Contains(documentName) select x).First();
// Get the first Worksheet...
AtomLink sheetLink = affiliates.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery workSheetQuery = new WorksheetQuery(sheetLink.HRef.ToString());
WorksheetFeed workSheetFeed = AuthorisedSpreadsheetsService.Query(workSheetQuery);
AtomEntry affiliateSheet = workSheetFeed.Entries.First(s => s.Title.Text.Equals(sheetTitle, StringComparison.InvariantCultureIgnoreCase));
// Get the cells...
UInt32 startRow = 2;
AtomLink cellLink = affiliateSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellQuery = new CellQuery(cellLink.HRef.ToString())
{
MinimumRow = startRow,
};
CellFeed cellFeed = AuthorisedSpreadsheetsService.Query(cellQuery);
List <TDataRow> table = new List <TDataRow>();
TDataRow row = null;
foreach (CellEntry currentCell in cellFeed.Entries)
{
if (currentCell.Column == 1)
{
if (currentCell.Value.Equals("-end-", StringComparison.InvariantCultureIgnoreCase))
{
break;
}
row = new TDataRow {
RowNbr = currentCell.Row, Service = AuthorisedSpreadsheetsService, CellLink = cellLink
};
table.Add(row);
}
if (currentCell.Column > row.LastColumn)
{
continue;
}
row.SetValue(currentCell);
}
return(table);
}
private void ClearSheet(string workbookName)
{
var page1 = (WorksheetEntry)GetWorkbookSheets(workbookName)[0];
if (page1 == null)
{
throw new ApplicationException("Worksheet not found");
}
AtomLink listFeedLink = page1.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
ListFeed listFeed = spreadsheetService.Query(new ListQuery(listFeedLink.HRef.ToString()));
CellFeed cells = spreadsheetService.Query(new CellQuery(listFeedLink.HRef.ToString()));
int ctr = 0;
CellEntry toUpdateA;
CellEntry toUpdateB;
AtomFeed batchFeed = new AtomFeed(cells);
// This is for the header row
ctr = (int)cells.ColCount.Count;
// Skip all of this if there are no cells with values in them other than those in the header row
if (cells.Entries.Count > ctr)
{
// Process through all of the cells that have a value in them starting at the cell below the Header
while (true)
{
toUpdateA = (CellEntry)cells.Entries[ctr];
toUpdateA.Cell.InputValue = "";
toUpdateA.BatchData = new GDataBatchEntryData("A", GDataBatchOperationType.update);
batchFeed.Entries.Add(toUpdateA);
ctr++;
if (ctr >= cells.Entries.Count)
{
break;
}
toUpdateB = (CellEntry)cells.Entries[ctr];
toUpdateB.Cell.InputValue = "";
toUpdateB.BatchData = new GDataBatchEntryData("B", GDataBatchOperationType.update);
batchFeed.Entries.Add(toUpdateB);
ctr = ctr + 1;
if (ctr >= cells.Entries.Count)
{
break;
}
}
// Erase all cells
CellFeed batchResultFeed = (CellFeed)spreadsheetService.Batch(batchFeed, new Uri(cells.Batch));
}
}
/// <summary>
/// Get the cell entry
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
private static CellEntry GetCellEntry(this GS2U_Worksheet worksheet, string column, int row)
{
CellQuery cellQuery = new CellQuery(worksheet.worksheetEntry.CellFeedLink);
int colInt = GoogleSheetsToUnityUtilities.GetIndexInAlphabet(column);
cellQuery.MinimumRow = (uint)row;
cellQuery.MaximumRow = (uint)row;
cellQuery.MinimumColumn = (uint)colInt;
cellQuery.MaximumColumn = (uint)colInt;
CellFeed cellFeed = SpreadSheetManager.service.Query(cellQuery) as CellFeed;
return((CellEntry)cellFeed.Entries[0]);
}
public List <string> PerformGoogleLookup(string lookupText)
{
List <string> listNodes = null;
string base64str = "TgBhAHQAaABhAG4AcwAkADEA";
SpreadsheetsService service = AuthenticateGoogleUser(base64str);
SpreadsheetEntry spEntry = GetSpreadSheetEntry(service);
CellFeed cellFeed = GetCellFeed(service, spEntry);
listNodes = GetNodeMatches(cellFeed, lookupText);
return(listNodes);
}
private void ExportDatablocks(Type datablockType)
{
var worksheet = (WorksheetEntry)spreadsheet.Worksheets.Entries[0];
IEnumerable <Datablock> datablocks = DatablockManager.Instance.GetDatablocks(datablockType);
datablockFields = Datablock.GetFields(datablockType);
List <string> headers = datablockFields.Select(f => f.Name).ToList();
headers.Insert(0, "Parent");
headers.Insert(0, "Name");
// Set the worksheet to a single row for our headers
worksheet.Cols = (uint)headers.Count;
worksheet.Rows = (exportData ? (uint)datablocks.Count() : 0) + 1;
worksheet.Update();
if (exportData)
{
// Fetch the cell feed of the worksheet.
var cellQuery = new CellQuery(worksheet.CellFeedLink);
cellQuery.ReturnEmpty = ReturnEmptyCells.yes;
cellFeed = sheetsAPI.Service.Query(cellQuery);
batchRequest = new CellFeed(cellQuery.Uri, sheetsAPI.Service);
currentCellIndex = 0;
// Set headers
for (int index = 0; index < headers.Count; index++)
{
string cellValue = headers[index];
SetNextCellValue(cellValue);
}
currentCellIndex = headers.Count;
IEnumerable <Datablock> baseDatablocks = datablocks.Where(d => d.Parent == null);
// Process the datablocks from the base to their children
foreach (Datablock baseDatablock in baseDatablocks)
{
ProcessChildren(baseDatablock);
}
sheetsAPI.Service.Batch(batchRequest, new Uri(cellFeed.Batch));
}
Debug.Log("Datablocks saved to " + spreadsheet.Title.Text);
}
