public static SpreadsheetCell[][] InsertMatrixOverwrite(SpreadsheetCell[][] matrix, SpreadsheetCell[][] matrixToInsert, int insertAtRow, int insertAtColumn)
{
int matrixMaxColumns = MaxColumns(matrix);
int matrixMaxRows = MaxRows(matrix);
int matrixToInsertMaxColumns = MaxColumns(matrixToInsert);
int matrixToInsertMaxRows = MaxRows(matrixToInsert);
int columnsRequired = (insertAtColumn + matrixToInsertMaxColumns); // - 1;
if (matrixMaxColumns > columnsRequired)
{
columnsRequired = matrixMaxColumns;
}
int rowsRequired = (insertAtRow + matrixToInsertMaxRows); // - 1;
if (matrixMaxRows > rowsRequired)
{
rowsRequired = matrixMaxRows;
}
var combinedMatrix = new SpreadsheetCell[rowsRequired][];
for (int rowIndex = 0; rowIndex < combinedMatrix.Length; rowIndex++)
{
combinedMatrix[rowIndex] = new SpreadsheetCell[columnsRequired];
for (int columnIndex = 0; columnIndex < combinedMatrix[rowIndex].Length; columnIndex++)
{
combinedMatrix[rowIndex][columnIndex] = new SpreadsheetCell();
}
}
for (int rowIndex = 0; rowIndex < matrix.Length; rowIndex++)
{
var row = matrix[rowIndex];
if (row == null)
{
continue;
}
for (int columnIndex = 0; columnIndex < row.Length; columnIndex++)
{
var cell = row[columnIndex];
if (cell == null)
{
cell = new SpreadsheetCell();
}
combinedMatrix[rowIndex][columnIndex] = cell;
}
}
for (int rowIndex = 0; rowIndex < matrixToInsert.Length; rowIndex++)
{
var row = matrixToInsert[rowIndex];
if (row == null)
{
continue;
}
for (int columnIndex = 0; columnIndex < row.Length; columnIndex++)
{
var cell = row[columnIndex];
if (cell == null)
{
cell = new SpreadsheetCell();
}
combinedMatrix[insertAtRow + rowIndex][insertAtColumn + columnIndex] = cell;
}
}
return(combinedMatrix);
}
public static SpreadsheetCell[][] MatrixToHistogram(SpreadsheetCell[][] spreadsheetRowList, decimal[] binsDecimals, int[] columnNumberArray, int startRow = 1, int endRow = -1, bool firstRowHeaders = true)
{
if (spreadsheetRowList == null || spreadsheetRowList.Length == 0)
{
throw new ArgumentOutOfRangeException(nameof(spreadsheetRowList));
}
if (binsDecimals == null || binsDecimals.Length == 0)
{
throw new ArgumentOutOfRangeException(nameof(binsDecimals));
}
if (columnNumberArray == null || columnNumberArray.Length == 0)
{
throw new ArgumentOutOfRangeException(nameof(columnNumberArray));
}
decimal[] binsDecimalsSorted = binsDecimals.ToList().Distinct().OrderBy(d => d).ToArray();
//var binMin = binsDecimals.Min();
decimal binMax = binsDecimals.Max();
int firstRow = startRow > -1 ? startRow : 0;
var headers = new string[columnNumberArray.Length];
if (firstRowHeaders)
{
for (var columnIndex = columnNumberArray.Min(); columnIndex <= columnNumberArray.Max(); columnIndex++)
{
headers[columnIndex - columnNumberArray.Min()] = spreadsheetRowList[firstRow][columnIndex].CellData;
}
firstRow++;
}
int lastRow = endRow > -1 && endRow <= spreadsheetRowList.Length ? endRow : spreadsheetRowList.Length;
//var lessBin = new int[columnNumberArray.Length];
var moreBin = new int[columnNumberArray.Length];
var totals = new int[columnNumberArray.Length];
var binsDictionary = new Dictionary <decimal, int[]>();
foreach (decimal d in binsDecimalsSorted)
{
binsDictionary.Add(d, new int[columnNumberArray.Length]);
}
for (int rowIndex = firstRow; rowIndex < lastRow; rowIndex++)
{
var row = spreadsheetRowList[rowIndex];
for (int columnNumberArrayIndex = 0; columnNumberArrayIndex < columnNumberArray.Length; columnNumberArrayIndex++)
{
int columnNumber = columnNumberArray[columnNumberArrayIndex];
if (columnNumber >= row.Length)
{
continue;
}
var column = row[columnNumber];
if (column == null)
{
continue;
}
decimal columnDecimal;
if (!decimal.TryParse(column.ToString(), out columnDecimal))
{
continue;
}
if (columnDecimal > binMax)
{
moreBin[columnNumberArrayIndex]++;
totals[columnNumberArrayIndex]++;
}
else if (binsDecimalsSorted.Length > 1)
{
for (int index = 0; index < binsDecimalsSorted.Length - 1; index++)
{
decimal d1 = binsDecimalsSorted[index];
decimal d2 = binsDecimalsSorted[index + 1];
if (columnDecimal > d1 && columnDecimal <= d2)
{
binsDictionary[d2][columnNumberArrayIndex]++;
totals[columnNumberArrayIndex]++;
break;
}
if (index == 0 && columnDecimal <= d1)
{
binsDictionary[d1][columnNumberArrayIndex]++;
totals[columnNumberArrayIndex]++;
break;
}
}
}
else if (binsDecimalsSorted.Length == 1)
{
decimal d1 = binsDecimalsSorted[0];
if (columnDecimal <= d1)
{
binsDictionary[d1][columnNumberArrayIndex]++;
totals[columnNumberArrayIndex]++;
}
}
}
}
var headerColumnList = new List <SpreadsheetCell> {
new SpreadsheetCell("Bin")
};
headerColumnList.AddRange(columnNumberArray.Select(columnNumber => new SpreadsheetCell("Total: column " + SpreadsheetFileHandler.AlphabetLetterRollOver(columnNumber))));
if (firstRowHeaders && headers.Length > 0)
{
for (int index = 0; index < headers.Length; index++)
{
var header = headers[index];
if (!string.IsNullOrWhiteSpace(header))
{
headerColumnList[index + 1] = new SpreadsheetCell(header);
}
}
}
var result = new SpreadsheetCell[binsDecimalsSorted.Length + 3][];
int resultRow = 0;
result[resultRow] = headerColumnList.ToArray();
foreach (var kvp in binsDictionary)
{
resultRow++;
var x = new List <SpreadsheetCell>
{
new SpreadsheetCell(kvp.Key.ToString(CultureInfo.InvariantCulture))
};
x.AddRange(kvp.Value.Select(a => new SpreadsheetCell(a.ToString(CultureInfo.InvariantCulture))).ToList());
result[resultRow] = x.ToArray();
}
if (moreBin.Count(a => a > 0) > 0)
{
resultRow++;
result[resultRow] = (new[] { new SpreadsheetCell("More") }).Concat(moreBin.Select(a => new SpreadsheetCell(a.ToString(CultureInfo.InvariantCulture)))).ToArray();
}
resultRow++;
result[resultRow] = (new[] { new SpreadsheetCell("Total") }).Concat(totals.Select(a => new SpreadsheetCell(a.ToString(CultureInfo.InvariantCulture)))).ToArray();
return(result);
}
