Example #1
0
        public async Task AnalyzeAsync(int analysisId, string googleFileId, Stream stream)
        {
            Data.DTO.WorkbookDto workbook;
            try
            {
                workbook = await _repository.RetrieveWorkbookByGoogleFileIdAsync(googleFileId);
            }
            catch (Exception err)
            {
                throw new InvalidOperationException($"Unable to get workbook with googleFileId { googleFileId }", err);
            }
            ExcelReader reader;

            try
            {
                reader = new ExcelReader(stream);
            }
            catch (Exception err)
            {
                throw;
            }

            await _repository.AddWorksheetsAsync(workbook.Id, reader.GetSheetNames());

            // now that we've read the workbook we can save the analysis results
            foreach (var cellInfo in reader.ReadFile())
            {
                await _repository.AddCellAsync(cellInfo.SheetName, cellInfo.RowIndex, cellInfo.ColumnIndex, cellInfo.Reference, cellInfo.Value, cellInfo.Formula);
            }

            var readerWorkbook   = reader.GetWorkbook();
            var readerWorksheets = reader.GetWorksheets();

            if (readerWorkbook.HasExternalConnections)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 1, "Has External Connections");
            }
            if (readerWorkbook.HasCustomCode)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 2, "Has Custom Code");
            }
            if (readerWorkbook.HasDataConnections)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 3, "Has Data Connections");
            }
            if (readerWorkbook.HasExternalHyperLinks)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 4, "Has External Hyper Links");
            }
            if (readerWorkbook.HasExternalRelationships)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 5, "Has External Relationships");
            }

            uint rowCountTotal = 0;

            foreach (Worksheet worksheet in readerWorksheets)
            {
                rowCountTotal += worksheet.CellCount;
            }
            if (rowCountTotal > 2000000)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 6, string.Format("Row count exceeds 2,000,000. Row count is {0}", rowCountTotal));
            }

            int formulaCount = readerWorkbook.FormulaCount;

            if (formulaCount > 40000)
            {
                await _repository.AddWorkbookIssueAsync(analysisId, 7, string.Format("Formula count exceeds 40,000. Formula count is {0}", formulaCount));
            }
        }