public override DataSourceError RecalcMinimumsAndMaximums(bool[] ignore = null) { for (int col = 0; col < m_ColumnNames.Count; col++) { List <DataSourceData> columnAsDataSourceData = new List <DataSourceData>(); DataSourceData.DataType columnDataType = m_ColumnTypes[col]; float min = float.MaxValue; float max = float.MinValue; DataSourceData minDSD = new DataSourceData(); DataSourceData maxDSD = new DataSourceData(); //bool minAndMaxInited = false; List <DataSourceData> valueNames = m_ColumnValueLists[col].m_ValueNames; //for (int row = 0; row < m_NumRows; row++) /* HACK FIX */ for (int row = 0; row < (m_NumRows - 1); row++) { if (!(m_FilteredAndAvailableRows[row] && m_ActiveRows[row])) { continue; } DataSourceData cell = new DataSourceData(); GetCell(row, col, out cell); DataSourceData data = new DataSourceData(); if ((columnDataType == DataSourceData.DataType.FLOAT) || (columnDataType == DataSourceData.DataType.PERCENT)) { float parseVal = 0; //bool isPercent = false; data.m_type = columnDataType; parseVal = cell.m_float; //get min and max of each column data.m_float = parseVal; data.m_string = parseVal.ToString(); if (parseVal < min) { min = parseVal; minDSD = data; } if (parseVal > max) { max = parseVal; maxDSD = data; } } else { data.m_type = DataSourceData.DataType.STRING; data.m_string = cell.m_string; int index = valueNames.FindIndex(r => r.m_string == data.m_string); if (index < 0) { data.m_float = 0; } else { int len = valueNames.Count; if (len > 1) { data.m_float = (float)index / (float)(len - 1); } else if (len == 1) { data.m_float = 0.5f; } else { data.m_float = 0; } if (data.m_float < min) { min = data.m_float; minDSD = data; } if (data.m_float > max) { max = data.m_float; maxDSD = data; } } } } m_LocalMinMaxMap[m_ColumnNames[col]] = new Vector2(min, max); DataSourceDataMinMax dsdmm = new DataSourceDataMinMax(); dsdmm.Min = minDSD; dsdmm.Max = maxDSD; m_LocalMinMaxDataSourceData[m_ColumnNames[col]] = dsdmm; } return(DataSourceError.OK); }
public override DataSourceError GetColumnValueList(string columnName, out DataSourceData[] columnValueList) { columnValueList = null; if (m_WorkBook == null) { return(DataSourceError.DATASOURCE_NOT_OPEN); } int idx = m_ColumnNames.IndexOf(columnName); if (idx == -1) { return(DataSourceError.COLUMN_NAME_NOT_FOUND); } DataSourceData.DataType dType = DataSourceData.DataType.UNDEFINED; GetColumnType(columnName, out dType); if (m_ColumnValueLists[idx] == null) { //m_ColumnValueLists[idx] = new List<DataSourceData>(); m_ColumnValueLists[idx] = new ColumnValueNameWrapper(); // new List<DataSourceData>(); for (int i = 1; i < m_NumRows; i++) { DataSourceData data = new DataSourceData(); GetActualCell(i, idx, out data); if (IndexOf(m_ColumnValueLists[idx].m_ValueNames, data) == -1) { m_ColumnValueLists[idx].m_ValueNames.Add(data); } } columnValueList = m_ColumnValueLists[idx].m_ValueNames.ToArray(); float min = Int64.MaxValue; float max = Int64.MinValue; DataSourceData minDSD = null; DataSourceData maxDSD = null; switch (dType) { case DataSourceData.DataType.STRING: { min = 0; max = 1; //minDSD = m_ColumnValueLists[idx][0]; //maxDSD = m_ColumnValueLists[idx][m_ColumnValueLists[idx].Count - 1]; minDSD = m_ColumnValueLists[idx].m_ValueNames[0]; maxDSD = m_ColumnValueLists[idx].m_ValueNames[m_ColumnValueLists[idx].m_ValueNames.Count - 1]; for (int i = 0; i < m_ColumnValueLists[idx].m_ValueNames.Count; i++) { int len = m_ColumnValueLists[idx].m_ValueNames.Count; if (len > 1) { m_ColumnValueLists[idx].m_ValueNames[i].m_float = (float)i / (float)(len - 1); } else if (len == 1) { m_ColumnValueLists[idx].m_ValueNames[i].m_float = 0.5f; } else { m_ColumnValueLists[idx].m_ValueNames[i].m_float = 0; } } } break; case DataSourceData.DataType.FLOAT: case DataSourceData.DataType.DOLLARS: case DataSourceData.DataType.PERCENT: { } break; } columnValueList = m_ColumnValueLists[idx].m_ValueNames.ToArray(); } else { columnValueList = m_ColumnValueLists[idx].m_ValueNames.ToArray(); } return(DataSourceError.OK); }
public override DataSourceError OpenDataSource(string uri) { Debug.Log("OpenDataSource"); try { if (m_WorkBook != null) { m_WorkBook.Close(); m_WorkBook = null; } m_WorkBook = new XSSFWorkbook(uri); } catch (Exception e) { Debug.Log("Failed to open |" + uri + "| " + e.ToString()); return(DataSourceError.DATASOURCE_NOT_FOUND); } if (m_WorkBook == null) { return(DataSourceError.DATASOURCE_NOT_FOUND); } else { m_Sheet = m_WorkBook.GetSheetAt(0); if (m_Sheet == null) { m_WorkBook.Close(); m_WorkBook = null; return(DataSourceError.DATASOURCE_NOT_OPEN); } else { int rowWithError = 0; if (!SanityCheck(m_Sheet, out rowWithError)) { // put up dialog box: // XLS Format: // Data must be in the first sheet // Data must be rectangular block of cells // Data must start at row 1, column 1 // No blank rows or blank columns // Row 1 must contain the column names // No other data should be on this sheet Debug.LogError("SanityCheck for \"" + uri + "\" failed - row " + (rowWithError + 1).ToString()); return(DataSourceError.PARSE_ERROR); } int firstRow = m_Sheet.FirstRowNum; int lastRow = m_Sheet.LastRowNum; int iRow = 0; int lastCell = m_Sheet.GetRow(iRow).LastCellNum; for (int j = 0; j <= lastCell; j++) { ICell cell = m_Sheet.GetRow(iRow).GetCell(j); if (cell == null) { lastCell = j - 1; break; } } for (iRow = lastRow; iRow >= firstRow; iRow--) { if (m_Sheet.GetRow(iRow) != null) { bool isEmpty = false; for (int j = 0; j < lastCell; j++) { ICell cell = m_Sheet.GetRow(iRow).GetCell(j); if (cell != null) { switch (cell.CellType) { case CellType.Blank: case CellType.Error: case CellType.Unknown: isEmpty = true; break; case CellType.Formula: if (cell.CellFormula.Trim().Length == 0) { isEmpty = true; } break; case CellType.String: if (cell.StringCellValue.Trim().Length == 0) { isEmpty = true; } break; } if (!isEmpty) { break; } } else { isEmpty = true; } if (!isEmpty) { break; } } if (!isEmpty) { break; } } } lastRow = iRow; m_NumRows = (lastRow - firstRow) + 1; //m_NumCols = m_Sheet.GetRow(0).LastCellNum; m_NumCols = lastCell + 1; for (int i = 0; i < m_NumCols; i++) { ICell cell = m_Sheet.GetRow(0).GetCell(i); if (cell.CellType != CellType.String) { m_WorkBook.Close(); m_WorkBook = null; return(DataSourceError.DATASOURCE_NOT_OPEN); } else { m_ColumnNames.Add(cell.StringCellValue); m_ColumnValueLists.Add(null); // set up for lazy evaluation m_ColumnTypes.Add(DataSourceData.DataType.UNDEFINED); // set up for lazy evaluation } } // alas, I cannot use lazy evaluation - must do so here for (int col = 0; col < m_NumCols; col++) { DataSourceData[] columnValues = null; GetColumnValueList(m_ColumnNames[col], out columnValues); float min = Int64.MaxValue; float max = Int64.MinValue; DataSourceData minDSD = null; DataSourceData maxDSD = null; DataSourceData.DataType columnDataType = DataSourceData.DataType.UNDEFINED; for (int row = 1; row < m_NumRows; row++) { DataSourceData data = new DataSourceData(); if (columnDataType == DataSourceData.DataType.UNDEFINED) { columnDataType = data.m_type; } if (GetActualCell(row, col, out data) == DataSourceError.OK) { if ((data.m_type == DataSourceData.DataType.DOLLARS) || (data.m_type == DataSourceData.DataType.FLOAT) || (data.m_type == DataSourceData.DataType.PERCENT)) { if (data.m_float < min) { min = data.m_float; minDSD = data; } if (data.m_float > max) { max = data.m_float; maxDSD = data; } } else if (data.m_type == DataSourceData.DataType.BOOLEAN) { data.m_float = data.m_bool ? 1 : 0; } else if (data.m_type == DataSourceData.DataType.STRING) { //int index = IndexOf(columnValues, data); int index = Array.FindIndex(columnValues, r => r.m_string == data.m_string); if (index < 0) { data.m_float = 0; } else { int len = columnValues.Length; if (len > 1) { data.m_float = (float)index / (float)(len - 1); } else if (len == 1) { data.m_float = 0.5f; } else { data.m_float = 0; } if (data.m_float < min) { min = data.m_float; minDSD = data; } if (data.m_float > max) { max = data.m_float; maxDSD = data; } } } } } if (columnDataType == DataSourceData.DataType.PERCENT) { min = min / 100.0f; max = max / 100.0f; } m_LocalMinMaxMap[m_ColumnNames[col]] = new Vector2(min, max); DataSourceDataMinMax dsdmm = new DataSourceDataMinMax(); dsdmm.Min = minDSD; dsdmm.Max = maxDSD; if (columnDataType == DataSourceData.DataType.PERCENT) { dsdmm.Min.m_float = dsdmm.Min.m_float / 100.0f; dsdmm.Min.m_float = dsdmm.Min.m_float / 100.0f; } m_LocalMinMaxDataSourceData[m_ColumnNames[col]] = dsdmm; } } } //is number of rows -1 since the first row doesn't count; m_ActiveRows = new bool[m_NumRows - 1]; m_FilteredAndAvailableRows = new bool[(m_NumRows - 1)]; for (int i = 0; i < m_ActiveRows.Length; i++) { m_ActiveRows[i] = true; } for (int i = 0; i < m_FilteredAndAvailableRows.Length; i++) { m_FilteredAndAvailableRows[i] = true; } return(DataSourceError.OK); }
public override DataSourceError GetColumnType(string columnName, out DataSourceData.DataType dataColumnType) { dataColumnType = DataSourceData.DataType.UNKNOWN; if (m_WorkBook == null) { return(DataSourceError.DATASOURCE_NOT_OPEN); } int idx = m_ColumnNames.IndexOf(columnName); if (idx == -1) { return(DataSourceError.COLUMN_NAME_NOT_FOUND); } if (m_ColumnTypes[idx] == DataSourceData.DataType.UNDEFINED) { DataSourceData data = new DataSourceData(); int typeRowIdx = 1; bool gotInitialType = false; for (; typeRowIdx < m_NumRows; typeRowIdx++) { if (GetActualCell(1, idx, out data) != DataSourceError.OK) { m_ColumnTypes[idx] = DataSourceData.DataType.UNKNOWN; return(DataSourceError.ERROR); } switch (data.m_type) { case DataSourceData.DataType.BOOLEAN: gotInitialType = true; break; case DataSourceData.DataType.DOLLARS: gotInitialType = true; break; case DataSourceData.DataType.FLOAT: gotInitialType = true; break; case DataSourceData.DataType.PERCENT: gotInitialType = true; break; case DataSourceData.DataType.STRING: if (data.m_string.Trim().Length > 0) { gotInitialType = true; } break; case DataSourceData.DataType.UNDEFINED: case DataSourceData.DataType.UNKNOWN: break; } if (gotInitialType) { break; } } DataSourceData.DataType colType = data.m_type; //for (int i = 2; i < m_NumRows; i++) //{ // if (GetActualCell(i, idx, out data) != DataSourceError.OK) // { // m_ColumnTypes[idx] = DataSourceData.DataType.UNKNOWN; // return DataSourceError.ERROR; // } // if (data.m_type != colType) // { // m_ColumnTypes[idx] = DataSourceData.DataType.UNKNOWN; // return DataSourceError.ERROR; // } //} m_ColumnTypes[idx] = colType; dataColumnType = m_ColumnTypes[idx]; } else { dataColumnType = m_ColumnTypes[idx]; } return(DataSourceError.OK); }