Source

main-vinayFork / Source / WebClient / Web.BizLogic / Services / ExcelService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using DataUp.Web.DataLayer;
using DataUp.Web.Common;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using Ionic.Zip;

namespace DataUp.Web.BizLogic
{
    public class ExcelService : IExcelService
    {
        private IDocumentStoreRepository documentStoreRepository;
        private IBlobDataRepository blobDataRepository;
        private IUploadService uploadService;

        public ExcelService(IUploadService uploadService, IDocumentStoreRepository documentStoreRepository, IBlobDataRepository blobDataRepository)
        {
            this.documentStoreRepository = documentStoreRepository;
            this.blobDataRepository = blobDataRepository;
            this.uploadService = uploadService;
        }

        public IEnumerable<ErrorDetails> Validate(int documentId, int userId)
        {
            IEnumerable<ErrorDetails> errors = new List<ErrorDetails>();
            //var document = this.documentStoreRepository.GetItem(item => item.UserId == userId && item.Id == documentId);
            var document = this.documentStoreRepository.GetItem(userId, documentId);

            if (document != null)
            {
                var documentStream = GetFileStream(document);
                using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(documentStream, true))
                {
                    errors = Validate(excelDocument);
                }
            }

            return errors;
        }

        public IEnumerable<ErrorDetails> ValidateCsv(int documentId, int userId)
        {
            IEnumerable<ErrorDetails> errors = new List<ErrorDetails>();
            //var document = this.documentStoreRepository.GetItem(item => item.UserId == userId && item.Id == documentId);
            var document = this.documentStoreRepository.GetItem(userId, documentId);

            if (document != null)
            {
                var documentStream = GetFileStream(document);
                using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(documentStream, true))
                {
                    errors = Validate(excelDocument,true);
                }
            }

            return errors;
        }

        public IEnumerable<ErrorDetails> Validate(DocumentStore document)
        {
            IEnumerable<ErrorDetails> errors = new List<ErrorDetails>();
           
            if (document != null)
            {
                var documentStream = GetFileStream(document);
                using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(documentStream, true))
                {
                    errors = Validate(excelDocument);
                }
            }

            return errors;
        }

        public static IEnumerable<ErrorDetails> Validate(SpreadsheetDocument document)
        {
            return Validate(document, false);
        }

        public static IEnumerable<ErrorDetails> Validate(SpreadsheetDocument document, bool csvOnly)
        {
            List<ErrorDetails> error = new List<ErrorDetails>();
            try
            {
                var errorList = (csvOnly == true)? ValidateExcel.ValidateCsvData(document): ValidateExcel.ValidateGraphics(document);
                if (errorList.Count() > 0)
                {
                    error.AddRange(errorList);
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }
            return error;
        }

        public void RemoveAll(int documentId, int userId)
        {
            //MemoryStream destination = null;
            try
            {
                //var document = this.documentStoreRepository.GetItem(item => item.UserId == userId && item.Id == documentId);
                var document = this.documentStoreRepository.GetItem(userId, documentId);
                DocumentDetail detail = new DocumentDetail();
                if (document != null)
                {
                    //destination = new MemoryStream();
                    detail.SetValuesFrom(document);
                    var documentStream = GetFileStream(document);
                    using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(documentStream, true))
                    {
                        FixAll(excelDocument);
                        excelDocument.WorkbookPart.Workbook.Save();
                    }
                    //documentStream.CopyToStream(destination);
                    documentStream.Position = 0;
                    detail.Data.DataStream = documentStream;
                    // Document Save to the Repository.

                    if (detail.Data != null && detail.Data.DataStream != null)
                    {
                        UploadDetails uploadDetails = new UploadDetails();
                        uploadDetails.Data = detail.Data;
                        this.uploadService.UploadFile(uploadDetails, document);
                    }
                    this.documentStoreRepository.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                OperationStatus.CreateFailureStatus(ex.Message, ex);
            }
            finally
            {
                //destination.Dispose();
            }

        }

        public void FixAll(SpreadsheetDocument document)
        {
            FixExcel.RemoveAllObjects(document);
        }

        public void RemoveSelected(int documentId, int userId, ErrorDetails detail)
        {
            //MemoryStream destination = null;
            try
            {
                //var document = this.documentStoreRepository.GetItem(item => item.UserId == userId && item.Id == documentId);
                var document = this.documentStoreRepository.GetItem(userId, documentId);
                DocumentDetail docDetail = new DocumentDetail();
                if (document != null)
                {
                    docDetail.SetValuesFrom(document);

                    var documentStream = GetFileStream(document);

                    using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(documentStream, true))
                    {
                        RemoveSelectedErrorType(excelDocument, detail);
                        excelDocument.WorkbookPart.Workbook.Save();
                    }

                    //destination = new MemoryStream();
                    //documentStream.CopyToStream(destination);
                    documentStream.Position = 0;
                    docDetail.Data.DataStream = documentStream;

                    // Document Save to the Repository.
                    if (docDetail.Data != null && docDetail.Data.DataStream != null)
                    {
                        UploadDetails uploadDetails = new UploadDetails();
                        uploadDetails.Data = docDetail.Data;
                        this.uploadService.UploadFile(uploadDetails, document);
                    }

                    this.documentStoreRepository.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                OperationStatus.CreateFailureStatus(ex.Message, ex);
            }
            finally
            {
                //destination.Dispose();
            }
        }

        public static void RemoveSelectedErrorType(SpreadsheetDocument document, ErrorDetails error)
        {
            try
            {
                switch(error.Error)
                {
                    case ErrorType.Comments:
                        FixExcel.XLRemoveComment(document, error.SheetName);
                        break;
                    case ErrorType.Charts:
                        FixExcel.XLRemoveChart(document, error.SheetName);
                        break;
                    case ErrorType.Shapes:
                        FixExcel.XLRemoveShape(document, error.SheetName);
                        break;
                    case ErrorType.MergedCell:
                        FixExcel.XLRemoveMergeCell(document, error.SheetName);
                        break;
                    case ErrorType.ColorCoded:
                        FixExcel.XLRemoveColorCell(document, error.SheetName);
                        break;
                    case ErrorType.Pictures:
                        FixExcel.XLRemovePictures(document, error.SheetName);
                        break;
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }
        }

        private Stream GetFileStream(DocumentStore document)
        {
            Stream dataStream = null;
            if (!string.IsNullOrWhiteSpace(document.FileId))
            {
                var blobDetails = this.blobDataRepository.GetBlobContent(document.FileId);
                dataStream = blobDetails.Data;
            }
            else
            {
                // TODO : Get the Stream of data from the 
                // documents.FileData
            }
            return dataStream;
        }
    }
}