/// <summary>
/// Gets the data from an exact cell reference
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
public static CellData GetCellData(this GS2U_Worksheet worksheet, string column, int row)
{
int colInt = GoogleSheetsToUnityUtilities.GetIndexInAlphabet(column);
CellEntry entry = worksheet.GetCellEntry(colInt, row);
List <string> rows = worksheet.GetRowTitles();
List <string> cols = worksheet.GetColumnTitles();
CellData cellData = new CellData(entry.InputValue, rows[row - 1], cols[colInt - 1]);
return(cellData);
}
/// <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 GstuSpreadSheet(GSTU_SpreadsheetResponce data)
* {
* string startColumn = Regex.Replace(data.StartCell(), "[^a-zA-Z]", "");
* int startRow = int.Parse(Regex.Replace(data.StartCell(), "[^0-9]", ""));
*
* int startColumnAsInt = GoogleSheetsToUnityUtilities.NumberFromExcelColumn(startColumn);
* int currentRow = startRow;
*
* foreach (List<string> dataValue in data.valueRange.values)
* {
* int currentColumn = startColumnAsInt;
*
* foreach (string entry in dataValue)
* {
* string realColumn = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(currentColumn);
* GSTU_Cell cell = new GSTU_Cell(entry, realColumn, currentRow);
*
* Cells.Add(realColumn + currentRow, cell);
*
* if (!rows.ContainsKey(currentRow))
* {
* rows.Add(currentRow, new List<GSTU_Cell>());
* }
*
* rows[currentRow].Add(cell);
*
* if (!columns.ContainsPrimaryKey(realColumn))
* {
* columns.Add(realColumn, new List<GSTU_Cell>());
* }
*
* columns[realColumn].Add(cell);
*
* currentColumn++;
* }
*
* currentRow++;
* }
*
* if(data.sheetInfo != null)
* {
* foreach(var merge in data.sheetInfo.merges)
* {
* Debug.Log("Merge starts at : " + merge.startRowIndex + " " + GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(merge.startColumnIndex));
* }
* }
* }*/
public GstuSpreadSheet(GSTU_SpreadsheetResponce data, string titleColumn, int titleRow)
{
string startColumn = Regex.Replace(data.StartCell(), "[^a-zA-Z]", "");
int startRow = int.Parse(Regex.Replace(data.StartCell(), "[^0-9]", ""));
int startColumnAsInt = GoogleSheetsToUnityUtilities.NumberFromExcelColumn(startColumn);
int currentRow = startRow;
Dictionary <string, string> mergeCellRedirect = new Dictionary <string, string>();
if (data.sheetInfo != null)
{
foreach (var merge in data.sheetInfo.merges)
{
string cell = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(merge.startColumnIndex + 1) + (merge.startRowIndex + 1);
for (int r = merge.startRowIndex; r < merge.endRowIndex; r++)
{
for (int c = merge.startColumnIndex; c < merge.endColumnIndex; c++)
{
string mergeCell = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(c + 1) + (r + 1);
mergeCellRedirect.Add(mergeCell, cell);
}
}
}
}
foreach (List <string> dataValue in data.valueRange.values)
{
int currentColumn = startColumnAsInt;
foreach (string entry in dataValue)
{
string realColumn = GoogleSheetsToUnityUtilities.ExcelColumnFromNumber(currentColumn);
string cellID = realColumn + currentRow;
GSTU_Cell cell = null;
if (mergeCellRedirect.ContainsKey(cellID) && Cells.ContainsKey(mergeCellRedirect[cellID]))
{
cell = Cells[mergeCellRedirect[cellID]];
}
else
{
cell = new GSTU_Cell(entry, realColumn, currentRow);
//check the title row and column exist, if not create them
if (!rows.ContainsKey(currentRow))
{
rows.Add(currentRow, new List <GSTU_Cell>());
}
if (!columns.ContainsPrimaryKey(realColumn))
{
columns.Add(realColumn, new List <GSTU_Cell>());
}
rows[currentRow].Add(cell);
columns[realColumn].Add(cell);
//build a series of seconard keys for the rows and columns
if (realColumn == titleColumn)
{
rows.LinkSecondaryKey(currentRow, cell.value);
}
if (currentRow == titleRow)
{
columns.LinkSecondaryKey(realColumn, cell.value);
}
}
Cells.Add(cellID, cell);
currentColumn++;
}
currentRow++;
}
// TODO: MCPGNZ DISABLED FOR FASTNESSSS
//build the column and row string Id's from titles
// foreach (GSTU_Cell cell in Cells.Values)
// {
// cell.columnId = Cells[cell.Column() + titleRow].value;
// cell.rowId = Cells[titleColumn + cell.Row()].value;
// }
//build all links to row and columns for cells that are handled by merged title fields.
// TODO: MCPGNZ DISABLED FOR FASTNESSSS
//foreach(GSTU_Cell cell in Cells.Values)
//{
// foreach(KeyValuePair<string,GSTU_Cell> cell2 in Cells)
// {
// if (cell.columnId == cell2.Value.columnId && cell.rowId == cell2.Value.rowId)
// {
// if (!cell.titleConnectedCells.Contains(cell2.Key))
// {
// cell.titleConnectedCells.Add(cell2.Key);
// }
// }
// }
//}
}
