1
\$\begingroup\$

One of the projects I am currently involved in requires to have a view that is similar to one in the Excel, except that most of the cells are read-only and the area is much more limited (the maximum size is about 300 rows x 10 columns).

Based on some parameters, some aggregated values are fetched from the database (or cube), the user is able to change some data and some figures are automatically computed (just like in Excel).

One solution is to work client side only (Angular), but I will have to deal with chained evaluation of formulas. An alternative I have tried is to have a virtual Excel document (using EPPlus) on the server side. Server receives changes from the client side (usually one cell is changed), changes the virtual Excel document, gets the changes and reports them to the client.

To achieve this I have defined the following classes:

1) Dirty tracking for a single cell

/// <summary>
/// class used for a single value tracking (dirty tracking and previous value)
/// </summary>
/// <typeparam name="T"></typeparam>
public class DirtyTrackingValue<T> where T:struct 
{
    /// <summary>
    /// current value
    /// </summary>
    public T Value { get; private set; }

    /// <summary>
    /// previous value
    /// </summary>
    public T PrevValue { get; private set; }

    /// <summary>
    /// indicates that value has changes after last initialization or dirty clear
    /// </summary>
    public bool IsDirty { get; private set; }

    /// <summary>
    /// implicit assignment operator that allows fast tracked value creation from scalar
    /// </summary>
    /// <param name="value"></param>
    public static implicit operator DirtyTrackingValue<T>(T value)
    {
        return new DirtyTrackingValue<T> {Value = value};
    }

    public bool SetValue(T value)
    {
        bool isDiff = !value.Equals(Value);
        if (isDiff)
        {
            PrevValue = Value;
            IsDirty = true;
            Value = value;
        }

        return isDiff;
    }

    public void ClearDirty()
    {
        IsDirty = false;
    }
}

2) Service models

/// <summary>
/// model to hold a cell change.
/// </summary>
/// <typeparam name="T"></typeparam>
public class ChangeTrackingChangedCellServiceModel<T> where T : struct 
{
    /// <summary>
    /// row number as 0-indexed
    /// </summary>
    public int RowNo { get; set; }

    /// <summary>
    /// column number as 0-indexed
    /// </summary>
    public int ColNo { get; set; }

    /// <summary>
    /// cell value
    /// </summary>
    public T Value { get; set; }
}


/// <summary>
/// section aware cell service model
/// </summary>
/// <typeparam name="T"></typeparam>
public class ChangeTrackingChangedCellServiceModelDenorm<T> : ChangeTrackingChangedCellServiceModel<T>
    where T: struct 
{
    /// <summary>
    /// name of the section within the document
    /// </summary>
    public string SectionName { get; set; }
}

3) A class used to encapsulate all state information

/// <summary>
/// an object used to hold all state information related to a tracked matrix
/// </summary>
/// <typeparam name="T"></typeparam>
public class ChangeTrackingMatrixInfo<T> where T: struct 
{
    /// <summary>
    /// actual data as Section->Values matrix
    /// </summary>
    public Dictionary<string, DirtyTrackingValue<T>[,]> Data { get; set; } = new Dictionary<string, DirtyTrackingValue<T>[,]>();

    /// <summary>
    /// list of sections used for this tracking info
    /// </summary>
    public List<string> SectionNames { get; set; }= new List<string>();
}

4) The service used to track changes

/// <summary>
/// a service that allows matrix values changes. Data is split into sections for easier maintenance
/// </summary>
/// <typeparam name="T">type of hold values</typeparam>
public class ChangeTrackingMatrixService<T> : IChangeTrackingMatrixService<T> where T: struct 
{
    #region Properties
    /// <summary>
    /// holds all state information that can be saved or restored as needed
    /// </summary>
    public ChangeTrackingMatrixInfo<T> StateInfo { get; private set; } = new ChangeTrackingMatrixInfo<T>();
    #endregion

    #region Methods
    /// <summary>
    /// creates data for a new section
    /// </summary>
    /// <param name="sectionName"></param>
    /// <param name="rows"></param>
    /// <param name="cols"></param>
    /// <returns></returns>
    public DirtyTrackingValue<T>[,] InitializeSection(string sectionName, int rows, int cols)
    {
        if (string.IsNullOrWhiteSpace(sectionName))
            throw new ArgumentNullException(nameof(sectionName));

        if (StateInfo.Data.ContainsKey(sectionName))
            throw new ArgumentException($"Section {sectionName} already exists");

        if (rows < 1)
            throw new ArgumentException($"{nameof(rows)} must be greater than 0");

        if (cols < 1)
            throw new ArgumentException($"{nameof(cols)} must be greater than 0");

        StateInfo.Data[sectionName] = new DirtyTrackingValue<T>[rows, cols];
        StateInfo.SectionNames.Add(sectionName);
        return StateInfo.Data[sectionName];
    }

    /// <summary>
    /// removes section from tracked information
    /// </summary>
    /// <param name="sectionName"></param>
    /// <returns></returns>
    public bool RemoveSection(string sectionName)
    {
        StateInfo.SectionNames.Remove(sectionName);
        return StateInfo.Data.Remove(sectionName);
    }

    /// <summary>
    /// sets a typed value for a cell within a section. All coordinates within the section are 0-based
    /// </summary>
    /// <param name="sectionName"></param>
    /// <param name="row"></param>
    /// <param name="col"></param>
    /// <param name="value"></param>
    public void SetCellValue(string sectionName, int row, int col, T value)
    {
        if (!StateInfo.Data.ContainsKey(sectionName))
            throw new ArgumentException($"section {sectionName} does not exist");

        var sectionData = StateInfo.Data[sectionName];
        var rowCount = sectionData.GetLength(0);
        var colCount = sectionData.GetLength(1);

        if (row >= rowCount)
            throw new ArgumentException($"row number is too large - maximum allowed is {rowCount}");

        if (col > colCount)
            throw new ArgumentException($"col number is too large - maximum allowed is {colCount}");

        // creating tracking value on the fly
        if (sectionData[row, col] != null)
            sectionData[row, col].SetValue(value);
        else
            sectionData[row, col] = value;
    }

    /// <summary>
    /// sets a general value for a cell within a section. All coordinates within the section are 0-based.
    /// </summary>
    /// <param name="sectionName"></param>
    /// <param name="row"></param>
    /// <param name="col"></param>
    /// <param name="value">value to be stored. Empty values and strings are ignored, as tracking is performed for numbers only</param>
    public void SetCellValueGeneral(string sectionName, int row, int col, object value)
    {
        // do nothing if empty value is provided. If empty/null values are to be supported, this should be removed
        if (string.IsNullOrWhiteSpace(value?.ToString()))
            return;

        // non-numeric fields (i.e. strings) are ignored
        if (value is string)
            return;

        try
        {
            T actualValue = (T) Convert.ChangeType(value, typeof(T));
            SetCellValue(sectionName, row, col, actualValue);
        }
        catch (Exception)
        {
            // just ignore conversion errors
        }
    }

    /// <summary>
    /// gets a list of dirty cells from a particular section
    /// </summary>
    /// <param name="sectionName"></param>
    /// <returns></returns>
    public List<ChangeTrackingChangedCellServiceModel<T>> GetChanges(string sectionName)
    {
        if (!StateInfo.Data.ContainsKey(sectionName))
            throw new ArgumentException($"section {sectionName} does not exist");

        var changes = new List<ChangeTrackingChangedCellServiceModel<T>>();
        var sectionData = StateInfo.Data[sectionName];
        var rowCount = sectionData.GetLength(0);
        var colCount = sectionData.GetLength(1);

        for (int r = 0; r < rowCount; r ++)
        for (int c = 0; c < colCount; c ++)
        {
            if (sectionData[r, c]?.IsDirty ?? false)
                changes.Add(new ChangeTrackingChangedCellServiceModel<T> { RowNo = r, ColNo = c, Value = sectionData[r, c].Value });
        }

        return changes;
    }

    /// <summary>
    /// gets a list of dirty cells from all sections
    /// </summary>
    /// <returns></returns>
    public IEnumerable<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> GetChanges()
    {
        foreach (var sectionName in StateInfo.SectionNames)
        {
            var changes = GetChanges(sectionName);
            yield return new KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>(sectionName, changes);
        }
    }

    /// <summary>
    /// clears dirty flag for all cells within a section
    /// </summary>
    /// <param name="sectionName"></param>
    public void ClearChanges(string sectionName)
    {
        if (!StateInfo.Data.ContainsKey(sectionName))
            throw new ArgumentException($"section {sectionName} does not exist");

        var sectionData = StateInfo.Data[sectionName];
        var rowCount = sectionData.GetLength(0);
        var colCount = sectionData.GetLength(1);

        for (int r = 0; r < rowCount; r++)
            for (int c = 0; c < colCount; c++)
            {
                sectionData[r, c]?.ClearDirty();
            }
    }

    /// <summary>
    /// clears dirty flag for all sections
    /// </summary>
    public void ClearChanges()
    {
        StateInfo.SectionNames.ForEach(ClearChanges);
    }

    /// <summary>
    /// restores state from a state object
    /// </summary>
    /// <param name="restoreData"></param>
    public void RestoreState(ChangeTrackingMatrixInfo<T> restoreData)
    {
        StateInfo = restoreData;
        ClearChanges();
    }

    /// <summary>
    /// transforms a list of denormalized changes into a normalized structure
    /// </summary>
    /// <param name="pushedChanges"></param>
    /// <returns></returns>
    public List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> GetMatrixChangesFromDenormalizedChanges(List<ChangeTrackingChangedCellServiceModelDenorm<T>> pushedChanges)
    {
        var normalizedChanges = new List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>>();
        pushedChanges.ForEach(pc =>
        {
            string sectionName = pc.SectionName;
            var sectionData = normalizedChanges.FirstOrDefault(c => c.Key.Equals(sectionName));
            if (sectionData.Equals(default(KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>)))
                sectionData = new KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>(sectionName, new List<ChangeTrackingChangedCellServiceModel<T>>());

            sectionData.Value.Add(new ChangeTrackingChangedCellServiceModel<T> { RowNo = pc.RowNo, ColNo = pc.ColNo, Value = pc.Value });
            normalizedChanges.Add(sectionData);
        });

        return normalizedChanges;
    }

    /// <summary>
    /// transforms a list of denormalized changes into a list of normalized ones
    /// </summary>
    /// <param name="changes"></param>
    /// <returns></returns>
    public List<ChangeTrackingChangedCellServiceModelDenorm<T>> GetDenormalizedChangesFromMatrixChanges(List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> changes)
    {
        var denormResult = new List<ChangeTrackingChangedCellServiceModelDenorm<T>>();
        changes.ForEach(section =>
        {
            section.Value.ForEach(change =>
            {
                denormResult.Add(new ChangeTrackingChangedCellServiceModelDenorm<T>
                {
                    SectionName = section.Key,
                    RowNo = change.RowNo,
                    ColNo = change.ColNo,
                    Value = change.Value
                });
            });
        });

        return denormResult;
    }
    #endregion
}

5) The service that allows the consumer to define document ranges to be tracked, to push and get changes after formula computation

/// <summary>
/// manages a virtual excel document by allowing pushing changes, performing computations and getting back changed values
/// </summary>
/// <typeparam name="T">cells type</typeparam>
public class VirtualExcelManagementService<T> : IVirtualExcelManagementService<T> where T: struct 
{
    #region Properties
    // TODO: consider using multiple keys, if multiple flows use virtual excel functionality

    public string VirtualExcelReferenceSessionKey => "VirtualExcelReferenceSessionKey";
    /// <summary>
    /// session key to use when holding tracking information
    /// </summary>
    public string VirtualExcelChangeTrackingInfoSessionKey => "VirtualExcelChangeTrackingInfoSessionKey";

    /// <summary>
    /// session reference
    /// </summary>
    private HttpSessionStateBase Session { get; set; }

    /// <summary>
    /// Excel package reference
    /// </summary>
    private ExcelPackage ExcelDocument { get; set; }

    /// <summary>
    /// Excel worksheet to use for management
    /// </summary>
    private ExcelWorksheet MainSheet => ExcelDocument?.Workbook?.Worksheets?[PositionID: 1];

    /// <summary>
    /// Change tracking manager reference
    /// </summary>
    private IChangeTrackingMatrixService<T> ChangeTrackingMatrixService { get; }

    /// <summary>
    /// a list of ranges to perform tracking upon
    /// </summary>
    private List<KeyValuePair<string, ExcelRangeLite>> TrackedRanges { get; set; }

    private Action<ExcelWorksheet> OnAfterDocumentInitialize { get; set; }
    #endregion

    #region Constructor
    public VirtualExcelManagementService(IChangeTrackingMatrixService<T> changeTrackingMatrixService)
    {
        if (changeTrackingMatrixService == null)
            throw new ArgumentNullException(nameof(changeTrackingMatrixService));

        ChangeTrackingMatrixService = changeTrackingMatrixService;
    }
    #endregion

    #region Methods
    /// <summary>
    /// initializes the virtual excel document or retrieves it from the session
    /// </summary>
    /// <param name="fullTemplatePath"></param>
    private void InitializeExcelDocument(string fullTemplatePath)
    {
        ExcelDocument = Session[VirtualExcelReferenceSessionKey] as ExcelPackage;
        if (ExcelDocument != null)
            return;

        var newFile = new FileInfo(fullTemplatePath);
        ExcelDocument = new ExcelPackage(newFile);

        OnAfterDocumentInitialize(MainSheet);

        Session[VirtualExcelReferenceSessionKey] = ExcelDocument;
    }

    /// <summary>
    /// reads excel data from a specific section and an excel range
    /// </summary>
    /// <param name="sectionName"></param>
    /// <param name="excelRange"></param>
    private void RefreshTrackedInformationFromExcel(string sectionName, ExcelRangeLite excelRange)
    {
        for (int row = excelRange.FromRow; row <= excelRange.ToRow; row++)
        {
            int trackedRow = row - excelRange.FromRow;
            for (int col = excelRange.FromCol; col <= excelRange.ToCol; col ++)
            {
                int trackedCol = col - excelRange.FromCol;
                object excelVal = MainSheet.Cells[row, col].Value;
                ChangeTrackingMatrixService.SetCellValueGeneral(sectionName, trackedRow, trackedCol, excelVal);
            }
        }
    }

    /// <summary>
    /// initializes tracking information by initializing section data and getting information from excel document (all defined sections)
    /// </summary>
    private void InitializeChangeTrackingInformation()
    {
        if (ExcelDocument == null)
            throw new ArgumentNullException(nameof(ExcelDocument));
        if (MainSheet == null)
            throw new ArgumentException("Null main sheet");

        var changeTrackingInformation = Session[VirtualExcelChangeTrackingInfoSessionKey] as ChangeTrackingMatrixInfo<T>;
        if (changeTrackingInformation != null)
        {
            ChangeTrackingMatrixService.RestoreState(changeTrackingInformation);
            return;
        }

        // new tracking information
        TrackedRanges.ForEach(range =>
        {
            int rowCount = range.Value.ToRow - range.Value.FromRow + 1;
            int colCount = range.Value.ToCol - range.Value.FromCol + 1;

            ChangeTrackingMatrixService.InitializeSection(range.Key, rowCount, colCount);

            RefreshTrackedInformationFromExcel(range.Key, range.Value);
        });

        Session[VirtualExcelChangeTrackingInfoSessionKey] = ChangeTrackingMatrixService.StateInfo;
    }

    /// <summary>
    /// initializes the virtual excel management
    /// </summary>
    /// <param name="session">session reference</param>
    /// <param name="fullTemplatePath">full path to template excel file</param>
    /// <param name="trackedRanges">a list of ranges to track</param>
    /// <param name="onAfterDocumentInitialize">an action to be triggered after document is initialized that lets caller to fill data into initialized excel</param>
    public void Initialize(HttpSessionStateBase session, string fullTemplatePath, List<KeyValuePair<string, ExcelRangeLite>> trackedRanges, Action<ExcelWorksheet> onAfterDocumentInitialize)
    {
        if (session == null)
            throw new ArgumentNullException(nameof(session));
        if (fullTemplatePath == null)
            throw new ArgumentNullException(nameof(fullTemplatePath));
        if (trackedRanges == null)
            throw new ArgumentNullException(nameof(trackedRanges));
        if (trackedRanges.Count < 1)
            throw new ArgumentException($"{trackedRanges} must be non-empty");
        if (onAfterDocumentInitialize == null)
            throw  new ArgumentNullException(nameof(onAfterDocumentInitialize));

        Session = session;
        TrackedRanges = trackedRanges;
        OnAfterDocumentInitialize = onAfterDocumentInitialize;

        InitializeExcelDocument(fullTemplatePath);
        InitializeChangeTrackingInformation();
    }

    /// <summary>
    /// gets changes caused by a list of pushed changes
    /// </summary>
    /// <param name="pushedChanges"></param>
    /// <returns></returns>
    public List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> GetChanges(List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> pushedChanges)
    {
        // getting data to easily push and retrieve information
        var rangesData = pushedChanges.Select(pc =>
        {
            string sectionName = pc.Key;
            var sectionChanges = pc.Value;

            // pushing changes to Excel virtual document
            var sectionRangePair = TrackedRanges.FirstOrDefault(tr => tr.Key.Equals(sectionName));
            if (sectionRangePair.Equals(default(KeyValuePair<string, ExcelRangeLite>)))
                throw new ArgumentException($"Tracked changes does not contain a section named {sectionName}");

            var sectionRange = sectionRangePair.Value;

            return new {SectionChanges = sectionChanges, SectionName = sectionName, SectionRange = sectionRange};
        }).ToList();

        // actual pushing comes here
        rangesData.ForEach(rd =>
        {
            rd.SectionChanges.ForEach(sc =>
            {
                int cellRow = rd.SectionRange.FromRow + sc.RowNo;
                int cellCol = rd.SectionRange.FromCol + sc.ColNo;
                MainSheet.Cells[cellRow, cellCol].Value = sc.Value;
            });
        });

        // applying formulas
        MainSheet.Calculate();

        TrackedRanges.ForEach(tr =>
        {
            RefreshTrackedInformationFromExcel(tr.Key, tr.Value);
        });

        var changes = ChangeTrackingMatrixService.GetChanges();
        return changes.ToList();
    }

    /// <summary>
    /// clears all changes from the tracked information
    /// </summary>
    public void ClearChanges()
    {
        ChangeTrackingMatrixService.ClearChanges();
    }

    /// <summary>
    /// clears session information
    /// </summary>
    public void ClearSession()
    {
        (Session[VirtualExcelReferenceSessionKey] as ExcelPackage)?.Dispose();
        Session[VirtualExcelReferenceSessionKey] = null;

        Session[VirtualExcelChangeTrackingInfoSessionKey] = null;
    }

    /// <summary>
    /// gets a memory stream containing a snapshot of the virtual excel file
    /// </summary>
    /// <param name="session"></param>
    /// <returns></returns>
    public MemoryStream GetExcelDocumentStream(HttpSessionStateBase session)
    {
        ExcelDocument = session[VirtualExcelReferenceSessionKey] as ExcelPackage;

        //TODO: consider a more graceful approach (reinitialization or something similar)
        if (ExcelDocument == null)
            throw new ArgumentNullException(nameof(ExcelDocument));

        var bytes = ExcelDocument.GetAsByteArray();
        var ms = new MemoryStream(bytes);
        ExcelDocument.Load(ms);

        return ms;
    }
    #endregion
}

6) A service that defines ranges, Excel document layout to be used for tracking changes

public class LiTransformationExcelService<T> : ILiTransformationExcelService<T> where T: struct 
{
    #region Properties
    public IVirtualExcelManagementService<T> VirtualExcelManagementService { get; }
    public IChangeTrackingMatrixService<T> ChangeTrackingMatrixService { get; }

    private List<KeyValuePair<string, ExcelRangeLite>> TrackedRanges { get; set; }
    #endregion

    public LiTransformationExcelService(IVirtualExcelManagementService<T> virtualExcelManagementService, IChangeTrackingMatrixService<T> changeTrackingMatrixService)
    {
        VirtualExcelManagementService = virtualExcelManagementService;
        if (VirtualExcelManagementService == null) throw  new ArgumentNullException(nameof(virtualExcelManagementService));

        ChangeTrackingMatrixService = changeTrackingMatrixService;
        if (ChangeTrackingMatrixService == null) throw new ArgumentNullException(nameof(changeTrackingMatrixService));
    }

    private void InitializeTrackedRanges()
    {
        //TODO: dynamically initialize sections information based on bookmarks
        TrackedRanges = new List<KeyValuePair<string, ExcelRangeLite>>
        {
            new KeyValuePair<string, ExcelRangeLite>("GeneralData", new ExcelRangeLite(1, 2, 3, 9)),
            new KeyValuePair<string, ExcelRangeLite>("ConditionsData", new ExcelRangeLite(6, 2, 30, 9))
        };
    }

    private void FillExcelDocument(ExcelWorksheet mainSheet)
    {
        //TODO: trigger data fetch + line expanding here
        mainSheet.Cells[1, 1].Value = "fetched data test";
    }

    public void Initialize(HttpSessionStateBase session, string fullTemplatePath)
    {
        if (session == null)
            throw new ArgumentNullException(nameof(session));
        if (string.IsNullOrWhiteSpace(fullTemplatePath))
            throw new ArgumentNullException(nameof(fullTemplatePath));

        InitializeTrackedRanges();
        VirtualExcelManagementService.Initialize(session, fullTemplatePath, TrackedRanges, FillExcelDocument);
    }

    public List<KeyValuePair<string, List<ChangeTrackingChangedCellServiceModel<T>>>> GetChanges(List<ChangeTrackingChangedCellServiceModelDenorm<T>> pushedChanges)
    {
        var matrixChanges = ChangeTrackingMatrixService.GetMatrixChangesFromDenormalizedChanges(pushedChanges);
        var computedChanges = VirtualExcelManagementService.GetChanges(matrixChanges);
        return computedChanges;
    }

    public MemoryStream GetExcelDocumentStream(HttpSessionStateBase session)
    {
        return VirtualExcelManagementService.GetExcelDocumentStream(session);
    }

    public void ClearSession()
    {
        VirtualExcelManagementService.ClearSession();
    }
}

7) Usage example (test action)

        decimal value = 2000m + (decimal) (new Random()).Next(1000);

        var pushedChanges = new List<ChangeTrackingChangedCellServiceModelDenorm<decimal>>
        {
            new ChangeTrackingChangedCellServiceModelDenorm<decimal> { SectionName = "GeneralData", RowNo = 1, ColNo = 1, Value = value }
        };

        string templateFullPath = System.Web.Hosting.HostingEnvironment.MapPath("~/TestPages/Templates/LiTransformationTemplate.xlsx");
        LiTransformationExcelService.Initialize(Session, templateFullPath);

        var changes = LiTransformationExcelService.GetChanges(pushedChanges);
        return Json(changes, JsonRequestBehavior.AllowGet);

8) Proper disposal on session end

    protected void Session_End(object sender, EventArgs e)
    {
        var virtualExcelService = NinjectWebCommon.Kernel.Get<IVirtualExcelManagementService<decimal>>();
        (Session[virtualExcelService.VirtualExcelReferenceSessionKey] as ExcelPackage)?.Dispose();
    }

Question: Can any of the above be improved in terms of architecture?

Performance-wise, the initial Excel package creation and other initializations may take even 2 seconds, but after that, getting changes usually takes 100 ms (send changes, process + get changes back in browser).

There will be only a small number of users to access this functionality (maximum 20 concurrent users), so I do not expect any performance issues.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.