public override void AddMergedRegion(
ExcelColumnIndex firstColumnIndex,
int firstRowIndex,
ExcelColumnIndex lastColumnIndex,
int lastRowIndex)
{
int firstColumIndexInt = firstColumnIndex.AsInt32();
firstColumIndexInt.ThrowIfValueIsOutOfRange(nameof(firstColumnIndex), 0, int.MaxValue);
firstRowIndex.ThrowIfValueIsOutOfRange(nameof(firstRowIndex), 1, int.MaxValue);
int lastColumIndexInt = lastColumnIndex.AsInt32();
lastColumIndexInt.ThrowIfValueIsOutOfRange(nameof(lastColumnIndex), 0, int.MaxValue);
lastRowIndex.ThrowIfValueIsOutOfRange(nameof(lastRowIndex), 1, int.MaxValue);
var cra = new CellRangeAddress(
firstRow: firstRowIndex - 1, // Because of using one-based indexing.
lastRow: lastRowIndex - 1, // Because of using one-based indexing.
firstCol: firstColumIndexInt,
lastCol: lastColumIndexInt
);
_sheet.AddMergedRegion(cra);
}
public void SetCenterizedCellFormula(ExcelColumnIndex columnIndex, int rowIndex,
string formula)
{
formula.ThrowIfNullOrWhiteSpace(nameof(formula));
GetOrCreateCenterizedCell(columnIndex, rowIndex).SetFormula(formula);
}
public override void AutoSizeColumn(ExcelColumnIndex columnIndex)
{
int columnIndexInt = columnIndex.AsInt32().UseOneBasedIndexing();
columnIndexInt.ThrowIfValueIsOutOfRange(nameof(columnIndex), 1, int.MaxValue);
_sheet.Column(columnIndexInt).AutoFit();
}
public override void AutoSizeColumn(ExcelColumnIndex columnIndex, bool useMergedCells)
{
int columnIndexInt = columnIndex.AsInt32();
columnIndexInt.ThrowIfValueIsOutOfRange(nameof(columnIndex), 1, int.MaxValue);
_sheet.AutoSizeColumn(columnIndexInt, useMergedCells);
}
public override void AutoSizeColumn(ExcelColumnIndex columnIndex, bool useMergedCells)
{
int columnIndexInt = columnIndex.AsInt32().UseOneBasedIndexing();
columnIndexInt.ThrowIfValueIsOutOfRange(nameof(columnIndex), 1, int.MaxValue);
// TODO: find a way to enable auto size for merged cells.
_sheet.Column(columnIndexInt).AutoFit();
}
public BetaDistributionAnalysisPhaseTwo(ParametersPack args, IRegression regression)
{
_args = args.ThrowIfNull(nameof(args));
_regression = regression.ThrowIfNull(nameof(regression));
_iterationsNumber = args.GetIterationsNumber(phaseNumber: 2);
_lastSegmentValueRowIndex = _iterationsNumber.SkipHeader();
_additionalDataColumn = ExcelWrapperForPhaseTwo.GetAdditionalDataColumn(_iterationsNumber);
_sampleSizeColumnIndex = ExcelWrapperForPhaseTwo.GetSampleSizeColumn(_iterationsNumber);
_theoreticalMinColumn = ExcelWrapperForPhaseTwo.GetTheoreticalMinColumn(_iterationsNumber);
_theoreticalMaxColumn = ExcelWrapperForPhaseTwo.GetTheoreticalMaxColumn(_iterationsNumber);
_alphaColumn = GetAlphaColumn();
_betaColumn = GetBetaColumn();
}
public override IExcelCellHolder GetOrCreateCell(ExcelColumnIndex columnIndex, int rowIndex,
bool centrized)
{
int columnIndexInt = columnIndex.AsInt32().UseOneBasedIndexing();
columnIndexInt.ThrowIfValueIsOutOfRange(nameof(columnIndex), 1, int.MaxValue);
rowIndex.ThrowIfValueIsOutOfRange(nameof(rowIndex), 1, int.MaxValue);
ExcelRange excelRange = _sheet.Cells[rowIndex, columnIndexInt];
excelRange = centrized
? excelRange.Center()
: excelRange;
return(new EpplusExcelCellHolder(excelRange));
}
private IModelledFunction GetOptimalFunction(IExcelSheet sheet,
ExcelColumnIndex yColumnIndex)
{
var xValues = new List <double>(_iterationsNumber);
var yValues = new List <double>(_iterationsNumber);
for (int rowIndex = 1.SkipHeader(); rowIndex <= _lastSegmentValueRowIndex; ++rowIndex)
{
IExcelCellHolder sampleSizeCell = sheet[_sampleSizeColumnIndex, rowIndex];
xValues.Add(sampleSizeCell.NumericValue);
IExcelCellValueHolder yValueCell = sheet.EvaluateCell(yColumnIndex, rowIndex);
yValues.Add(yValueCell.NumericValue);
}
return(_regression.Fit(xValues, yValues));
}
public static IExcelCellHolder GetCellHolder(IExcelSheet sheet,
ExcelColumnIndex columnIndex, int rowIndex, ReportOptions excelOptions)
{
sheet.ThrowIfNull(nameof(sheet));
excelOptions.ThrowIfNull(nameof(excelOptions));
return(excelOptions.CellCreationMode switch
{
ExcelCellCreationMode.Default =>
sheet.GetOrCreateCell(columnIndex, rowIndex),
ExcelCellCreationMode.Centerized =>
sheet.GetOrCreateCenterizedCell(columnIndex, rowIndex),
_ => throw new ArgumentOutOfRangeException(
nameof(excelOptions), excelOptions.CellCreationMode,
$"Unknown cell creation mode: '{excelOptions.CellCreationMode.ToString()}'."
)
});
public void ApplyAnalysisToSingleLaunch(IExcelSheet sheet, ExcelColumnIndex currentColumn,
int currentRow, int operationNumber)
{
string dataAddress = sheet[currentColumn, currentRow].Address;
int theoreticalDataRow = currentColumn.AsInt32().UseOneBasedIndexing().SkipHeader();
string theoreticalMinAddress = sheet[_theoreticalMinColumn, theoreticalDataRow].Address;
string theoreticalMaxAddress = sheet[_theoreticalMaxColumn, theoreticalDataRow].Address;
string normalizedFormula = ManualFormulaProvider.Normalize(
dataAddress, theoreticalMinAddress, theoreticalMaxAddress
);
int normalizedDataRowIndex = ExcelWrapperForPhaseTwo.GetNormalizedDataRowIndex(
_args, currentRow
);
sheet[currentColumn, normalizedDataRowIndex].SetFormula(normalizedFormula);
}
public override void AddMergedRegion(
ExcelColumnIndex firstColumnIndex,
int firstRowIndex,
ExcelColumnIndex lastColumnIndex,
int lastRowIndex)
{
int firstColumnIndexInt = firstColumnIndex.AsInt32().UseOneBasedIndexing();
firstColumnIndexInt.ThrowIfValueIsOutOfRange(nameof(firstRowIndex), 1, int.MaxValue);
firstRowIndex.ThrowIfValueIsOutOfRange(nameof(firstRowIndex), 1, int.MaxValue);
int lastColumnIndexInt = lastColumnIndex.AsInt32().UseOneBasedIndexing();
lastColumnIndexInt.ThrowIfValueIsOutOfRange(nameof(lastRowIndex), 1, int.MaxValue);
lastRowIndex.ThrowIfValueIsOutOfRange(nameof(lastRowIndex), 1, int.MaxValue);
_sheet.Cells[
firstRowIndex, firstColumnIndexInt,
lastRowIndex, lastColumnIndexInt
].Merge = true;
}
public override IExcelCellHolder GetOrCreateCell(ExcelColumnIndex columnIndex, int rowIndex,
bool centrized)
{
rowIndex.ThrowIfValueIsOutOfRange(nameof(rowIndex), 1, int.MaxValue);
int columIndexInt = columnIndex.AsInt32();
columIndexInt.ThrowIfValueIsOutOfRange(nameof(columnIndex), 0, int.MaxValue);
// Because of using one-based indexing.
int fixedRowIndex = rowIndex - 1;
IRow row = GetOrCreateRow(fixedRowIndex, centrized);
ICell cell = row.GetCell(columIndexInt);
ICell result = cell is null
? row.CreateCell(columIndexInt)
: cell;
result = centrized
? result.Center()
: result;
return(new NpoiExcelCellHolder(result));
}
public override void SetArrayFormula(
string arrayFormula,
ExcelColumnIndex firstColumnIndex,
int firstRowIndex,
ExcelColumnIndex lastColumnIndex,
int lastRowIndex)
{
arrayFormula.ThrowIfNullOrWhiteSpace(nameof(arrayFormula));
int firstColumnIndexInt = firstColumnIndex.AsInt32().UseOneBasedIndexing();
firstColumnIndexInt.ThrowIfValueIsOutOfRange(nameof(firstRowIndex), 1, int.MaxValue);
firstRowIndex.ThrowIfValueIsOutOfRange(nameof(firstRowIndex), 1, int.MaxValue);
int lastColumnIndexInt = lastColumnIndex.AsInt32().UseOneBasedIndexing();
lastColumnIndexInt.ThrowIfValueIsOutOfRange(nameof(lastRowIndex), 1, int.MaxValue);
lastRowIndex.ThrowIfValueIsOutOfRange(nameof(lastRowIndex), 1, int.MaxValue);
_sheet.Cells[
firstRowIndex, firstColumnIndexInt,
lastRowIndex, lastColumnIndexInt
].CreateArrayFormula(arrayFormula);
}
public abstract void AddMergedRegion(
ExcelColumnIndex firstColumnIndex,
int firstRowIndex,
ExcelColumnIndex lastColumnIndex,
int lastRowIndex);
public abstract void AutoSizeColumn(ExcelColumnIndex columnIndex, bool useMergedCells);
public abstract void SetArrayFormula(
string arrayFormula,
ExcelColumnIndex firstColumnIndex,
int firstRowIndex,
ExcelColumnIndex lastColumnIndex,
int lastRowIndex);
public IExcelCellHolder this[ExcelColumnIndex columnIndex, int rowIndex] => ExcelWrapperHelper.GetCellHolder(this, columnIndex, rowIndex, _excelOptions);
public abstract IExcelCellHolder GetOrCreateCell(ExcelColumnIndex columnIndex, int rowIndex,
bool centrized);
public IExcelCellHolder GetOrCreateCenterizedCell(ExcelColumnIndex columnIndex, int rowIndex)
{
return(GetOrCreateCell(columnIndex, rowIndex, centrized: true));
}
public abstract void AutoSizeColumn(ExcelColumnIndex columnIndex);
public IExcelCellValueHolder EvaluateCell(ExcelColumnIndex columnIndex, int rowIndex)
{
return(this[columnIndex, rowIndex].Evaluate());
}
public void SetCellValue(ExcelColumnIndex columnIndex, int rowIndex, double value)
{
GetOrCreateCell(columnIndex, rowIndex).SetValue(value);
}
public void SetCenterizedCellValue(ExcelColumnIndex columnIndex, int rowIndex,
DateTime value)
{
GetOrCreateCenterizedCell(columnIndex, rowIndex).SetValue(value);
}
