private void ObtainMeasuresCount(ExcelWorksheet worksheet)
{
var isStartFound = false;
for (var rowIndex = _headerRowsCount + 1; rowIndex < worksheet.Dimension.Rows; rowIndex++)
{
if (WorksheetHelpers.IsDataRow(worksheet, rowIndex) && !isStartFound)
{
isStartFound = true;
_measuresCount++;
continue;
}
if (!isStartFound)
{
continue;
}
if (WorksheetHelpers.IsDataRow(worksheet, rowIndex) ||
WorksheetHelpers.IsGroupRow(worksheet, rowIndex, _totalColumnIndexes) ||
WorksheetHelpers.IsTotalRow(worksheet, rowIndex, _leftPaneWidth + 1))
{
break;
}
_measuresCount++;
}
_isMeasureColumnExists = _leftPaneWidth >= WorksheetHelpers.RowMeasureColumnIndex;
}
private void MergeHeaderCells(ExcelWorksheet worksheet)
{
var mergedCells = worksheet.Cells[1, 1, _headerRowsCount, _leftPaneWidth];
mergedCells.Merge = true;
WorksheetHelpers.SetBordersToCells(mergedCells);
for (var rowIndex = 1; rowIndex <= _headerRowsCount - 1; rowIndex++)
{
var startColumnIndex = _leftPaneWidth + 1;
var currentCellValue = worksheet.Cells[rowIndex, startColumnIndex].Value;
for (var endColumnIndex = startColumnIndex + 1;
endColumnIndex <= worksheet.Dimension.Columns + 1;
endColumnIndex++)
{
var endCell = worksheet.Cells[rowIndex, endColumnIndex];
if (endCell.Value == currentCellValue)
{
continue;
}
var cellBeforeEnd = worksheet.Cells[rowIndex, endColumnIndex - 1];
var horizontalCellsToMerge =
worksheet.Cells[rowIndex, startColumnIndex, rowIndex, endColumnIndex - 1];
horizontalCellsToMerge.Value = cellBeforeEnd.Value;
horizontalCellsToMerge.Merge = true;
WorksheetHelpers.SetBordersToCells(horizontalCellsToMerge);
startColumnIndex = endColumnIndex;
currentCellValue = worksheet.Cells[rowIndex, startColumnIndex].Value;
endColumnIndex++;
}
}
for (var columnIndex = _leftPaneWidth + 1; columnIndex <= worksheet.Dimension.Columns; columnIndex++)
{
WorksheetHelpers.SetBordersToCells(worksheet.Cells[_headerRowsCount, columnIndex]);
}
}
protected override void CompileSheet(ExcelWorksheet worksheet, DataTable table)
{
if (table.Rows.Count == 0)
{
return;
}
WorksheetDataHelper.FillData(worksheet, table, false);
ObtainLeftPaneWidth(worksheet);
ObtainMeasuresCount(worksheet);
ObtainTotalColumns(worksheet);
FillHeaderData(worksheet);
RemoveTotalColumns(worksheet);
MergeHeaderCells(worksheet);
AppendCalculatedTotalColumns(worksheet);
WorksheetHelpers.FormatRows(worksheet, _headerRowsCount + 1, _leftPaneWidth);
WorksheetHelpers.FormatDataRows(worksheet, _headerRowsCount + 1, _totalColumnIndexes, _leftPaneWidth + 1);
WorksheetHelpers.FormatHeader(worksheet, _headerRowsCount, _totalColumnIndexes);
FormatSummaryRows(worksheet);
}
private void ProcessGroups(ExcelWorksheet sheet,
int startRowIndex,
int endRowIndex,
int outlineLevel)
{
if (outlineLevel > 0 && outlineLevel <= 7)
{
for (var i = startRowIndex; i <= endRowIndex; i++)
{
sheet.Row(i).OutlineLevel = outlineLevel;
sheet.Row(i).Collapsed = true;
}
}
for (var rowIndex = startRowIndex; rowIndex <= endRowIndex; rowIndex++)
{
if (!WorksheetHelpers.IsGroupRow(sheet, rowIndex, _totalColumnIndexes))
{
continue;
}
var startGroupRowIndex = rowIndex;
var groupName = sheet.Cells[rowIndex, WorksheetHelpers.RowNameColumnIndex].Value.ToString().Trim();
var endGroupRowIndex = startGroupRowIndex;
var duplicatesCount = 0;
for (var i = startGroupRowIndex + 1; i <= endRowIndex; i++)
{
var nameCell = sheet.Cells[i, WorksheetHelpers.RowNameColumnIndex];
if (nameCell.Value == null)
{
continue;
}
if (nameCell.Value.ToString().Trim() == groupName && !WorksheetHelpers.IsDataRow(sheet, i))
{
duplicatesCount++;
}
if (!WorksheetHelpers.IsTotalRow(sheet, i, _leftPaneWidth + 1) ||
WorksheetHelpers.IsDataRow(sheet, i))
{
continue;
}
var totalName = sheet.Cells[i, WorksheetHelpers.RowNameColumnIndex].Value.ToString().Trim();
if (totalName != $"{WorksheetHelpers.TotalRowIndicator} {groupName}")
{
continue;
}
if (duplicatesCount == 0)
{
endGroupRowIndex = i;
break;
}
else
{
duplicatesCount--;
}
}
ProcessGroups(sheet, startGroupRowIndex + 1, endGroupRowIndex - 1, outlineLevel + 1);
rowIndex = endGroupRowIndex;
var rowsWithData = _cellsWithData.Keys.Where(x => x > startGroupRowIndex && x <= endGroupRowIndex)
.OrderBy(x => x);
if (!rowsWithData.Any())
{
continue;
}
var color = _neutralColorGenerator.GetNextColor();
var columnsWithData = rowsWithData.SelectMany(x => _cellsWithData[x])
.Distinct();
foreach (var column in columnsWithData)
{
var cell = sheet.Cells[startGroupRowIndex, column];
cell.Value = null;
cell.Style.Fill.BackgroundColor.SetColor(color);
}
}
}
private void FormatSummaryRows(ExcelWorksheet sheet)
{
var firstDataRowIndex = _headerRowsCount + 1;
var columnsCount = sheet.Dimension.Columns;
var isPreviousRowTotal = false;
for (var rowIndex = firstDataRowIndex; rowIndex <= sheet.Dimension.Rows; rowIndex++)
{
if (WorksheetHelpers.IsTotalRow(sheet, rowIndex, _leftPaneWidth + 1))
{
isPreviousRowTotal = true;
continue;
}
var nameCell = sheet.Cells[rowIndex, WorksheetHelpers.RowNameColumnIndex];
if (isPreviousRowTotal)
{
if (nameCell.Value == null)
{
continue;
}
isPreviousRowTotal = false;
}
if (WorksheetHelpers.IsGroupRow(sheet, rowIndex, _totalColumnIndexes))
{
continue;
}
var rowsToDelete = new List <int>();
if (WorksheetHelpers.IsDataRow(sheet, rowIndex))
{
for (var shift = 1; shift < _measuresCount; shift++)
{
var measureRowIndex = rowIndex + shift;
rowsToDelete.Add(measureRowIndex);
for (var x = _leftPaneWidth + 1; x <= columnsCount; x++)
{
var nextMeasureCell = sheet.Cells[measureRowIndex, x];
if (_totalColumnIndexes.Contains(x) ||
WorksheetHelpers.IsEmptyCell(nextMeasureCell))
{
continue;
}
sheet.Cells[rowIndex, x].Value = nextMeasureCell.Value;
}
}
}
if (_isMeasureColumnExists)
{
sheet.Cells[rowIndex, WorksheetHelpers.RowMeasureColumnIndex].Value = null;
}
foreach (var rowToDelete in rowsToDelete.OrderByDescending(x => x))
{
sheet.DeleteRow(rowToDelete);
}
var color = _neutralColorGenerator.GetNextColor();
for (var x = _leftPaneWidth + 1; x <= columnsCount; x++)
{
var cell = sheet.Cells[rowIndex, x];
if (!_totalColumnIndexes.Contains(x) &&
WorksheetHelpers.IsEmptyCell(cell))
{
cell.Value = null;
continue;
}
if (cell.Value == null || _totalColumnIndexes.Contains(x))
{
continue;
}
cell.Value = null;
cell.Style.Fill.BackgroundColor.SetColor(color);
if (!_cellsWithData.ContainsKey(rowIndex))
{
_cellsWithData[rowIndex] = new HashSet <int>();
}
_cellsWithData[rowIndex].Add(x);
}
}
}
private IDictionary <string, int> GetRowKeys(ExcelWorksheet sheet)
{
var result = new Dictionary <string, int>();
var uniqueRowIdBuilder = new List <string>();
for (var rowIndex = _headerRowsCount + 1; rowIndex <= sheet.Dimension.Rows; rowIndex++)
{
var nameCellValue = sheet.Cells[rowIndex, WorksheetHelpers.RowNameColumnIndex].Value as string;
bool isTotalRow = WorksheetHelpers.IsTotalRow(sheet, rowIndex, _leftPaneWidth + 1);
if (WorksheetHelpers.IsGroupRow(sheet, rowIndex, _totalColumnIndexes) &&
!isTotalRow)
{
uniqueRowIdBuilder.Add(nameCellValue);
var groupRowKey = string.Join("-", uniqueRowIdBuilder);
result.Add(groupRowKey, rowIndex);
continue;
}
if (isTotalRow)
{
var rowKeyPrefix = string.Join("-", uniqueRowIdBuilder);
var uniqueMeasures = new Dictionary <string, int>();
for (var shift = 0; shift < _measuresCount; shift++)
{
var measureRowIndex = rowIndex + shift;
var measureCellValue = GetMeasureCellValue(sheet, measureRowIndex);
if (!uniqueMeasures.ContainsKey(measureCellValue))
{
uniqueMeasures.Add(measureCellValue, 0);
}
uniqueMeasures[measureCellValue]++;
var counter = uniqueMeasures[measureCellValue];
var uniqueMeasureValue = counter > 1 ? $"{measureCellValue} ({counter})" : measureCellValue;
var totalRowKey = $"{rowKeyPrefix}-{nameCellValue}-{uniqueMeasureValue}";
result.Add(totalRowKey, measureRowIndex);
}
if (uniqueRowIdBuilder.Any())
{
uniqueRowIdBuilder.RemoveAt(uniqueRowIdBuilder.Count - 1);
}
rowIndex += _measuresCount - 1;
continue;
}
if (!WorksheetHelpers.IsDataRow(sheet, rowIndex))
{
continue;
}
var currentIdPrefix = string.Join("-", uniqueRowIdBuilder);
var currentId = $"{currentIdPrefix}-{nameCellValue}";
result.Add(currentId, rowIndex);
rowIndex += _measuresCount - 1;
}
return(result);
}
private void SaveTotalColumn(ExcelWorksheet sheet, int columnIndex, bool appendTotalIndicator = false)
{
var totalColumnName = sheet.Cells[1, columnIndex].Value.ToString();
if (appendTotalIndicator)
{
totalColumnName = $"{totalColumnName} {WorksheetHelpers.TotalRowIndicator}";
}
_calculatedTotals.Add(totalColumnName, new Dictionary <string, IDictionary <string, string> >());
var uniqueRowIdBuilder = new List <string>();
for (var rowIndex = _headerRowsCount + 1; rowIndex <= sheet.Dimension.Rows; rowIndex++)
{
var nameCellValue = sheet.Cells[rowIndex, WorksheetHelpers.RowNameColumnIndex].Value as string;
bool isTotalRow = WorksheetHelpers.IsTotalRow(sheet, rowIndex, _leftPaneWidth + 1);
if (WorksheetHelpers.IsGroupRow(sheet, rowIndex, _totalColumnIndexes) &&
!isTotalRow)
{
uniqueRowIdBuilder.Add(nameCellValue);
continue;
}
if (isTotalRow)
{
var groupRowKey = string.Join("-", uniqueRowIdBuilder);
var totalMeasures = GetMeasures(sheet, rowIndex, columnIndex);
foreach (var measure in totalMeasures)
{
if (!_calculatedTotals[totalColumnName].ContainsKey(measure.Key))
{
_calculatedTotals[totalColumnName].Add(measure.Key, new Dictionary <string, string>());
}
var totalRowKey = $"{groupRowKey}-{nameCellValue}-{measure.Key}";
_calculatedTotals[totalColumnName][measure.Key].Add(groupRowKey, measure.Value);
_calculatedTotals[totalColumnName][measure.Key].Add(totalRowKey, measure.Value);
}
if (uniqueRowIdBuilder.Any())
{
uniqueRowIdBuilder.RemoveAt(uniqueRowIdBuilder.Count - 1);
}
rowIndex += _measuresCount - 1;
continue;
}
if (!WorksheetHelpers.IsDataRow(sheet, rowIndex))
{
continue;
}
var rowKeyPrefix = string.Join("-", uniqueRowIdBuilder);
var rowKey = $"{rowKeyPrefix}-{nameCellValue}";
var measures = GetMeasures(sheet, rowIndex, columnIndex);
foreach (var measure in measures)
{
if (!_calculatedTotals[totalColumnName].ContainsKey(measure.Key))
{
_calculatedTotals[totalColumnName].Add(measure.Key, new Dictionary <string, string>());
}
_calculatedTotals[totalColumnName][measure.Key].Add(rowKey, measure.Value);
}
rowIndex += _measuresCount - 1;
}
}
