/// <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); // } // } // } //} }