Source

main / Source / Excel / Excel.BizLogic / Managers / ExcelManager.cs

Full commit
using System;
using System.Collections.Generic;
using DataUp.Excel.Common;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

namespace DataUp.Excel.BizLogic
{
    /// <summary>
    /// Implementation of IExcelManager.
    /// </summary>
    public class ExcelManager : IExcelManager
    {
        /// <summary>
        /// Gets or sets the workbook instance associated with the excel manager.
        /// </summary>
        public _Workbook Workbook { get; set; }

        /// <summary>
        /// Creates an instance of class ExcelManager.
        /// </summary>
        /// <param name="workbook">Workbook instance associated with this excel manager.</param>
        public ExcelManager(_Workbook workbook)
        {
            this.Workbook = workbook;
        }

        /// <summary>
        /// This function can be used to validate the excel for csv compatibility errors.
        /// </summary>
        /// <returns>List of all csv compatibility errors.</returns>
        public IEnumerable<ErrorDetail> Validate()
        {
            List<ErrorDetail> errors = new List<ErrorDetail>();

            try
            {
                //Comments
                var comments = this.Workbook.XLGetComments();
                if (comments.Count > 0)
                {
                    errors.AddRange(comments);
                }

                //charts
                var allCharts = this.Workbook.XLGetChart();
                if (allCharts != null && allCharts.Count > 0)
                {
                    errors.AddRange(allCharts);
                }

                //shapes
                var allShapes = this.Workbook.XLGetShapes();
                if (allShapes != null && allShapes.Count > 0)
                {
                    errors.AddRange(allShapes);
                }

                //Mixed type
                //var allMixedType = this.Workbook.XLGetMixedType();
                //if (allMixedType != null && allMixedType.Count > 0)
                //{
                //    errors.AddRange(allMixedType);
                //}

                //non contiguous cells
                var allnonContiguous = this.Workbook.XLGetNonContiguous();
                if (allnonContiguous != null && allnonContiguous.Count > 0)
                {
                    errors.AddRange(allnonContiguous);
                }

                // Commas, Merged Cells, blank cells, Color Coded Cells, Special Character
                var allCellRelatedErrors = this.Workbook.XLGetCellRelatedErrors();
                if (allCellRelatedErrors != null && allCellRelatedErrors.Count > 0)
                {
                    errors.AddRange(allCellRelatedErrors);
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }

            return errors;
        }

        /// <summary>
        /// This function can be used to fix all errors in the given workbook.
        /// </summary>
        /// <returns>True, if fix was successful; otherwise false.</returns>
        public void FixAll()
        {
            try
            {
                this.Workbook.XLRemoveComments();
                this.Workbook.XLRemoveCharts();
                this.Workbook.XLRemoveColourCells();
                this.Workbook.XLRemoveShapes();
                this.Workbook.XLRemoveMergedCells();
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }
        }

        /// <summary>
        /// This function can be used to fix the error in the given worksheet.
        /// </summary>
        /// <param name="typeOfError">Error type.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        public void FixErrors(ErrorType typeOfError, string sheetName)
        {
            try
            {
                switch (typeOfError)
                {
                    case ErrorType.Charts:
                    case ErrorType.Shapes:
                        this.Workbook.XLRemoveCharts(sheetName);
                        this.Workbook.XLRemoveShapes(sheetName);
                        break;
                    case ErrorType.Comments:
                        this.Workbook.XLRemoveComments(sheetName);
                        break;
                    case ErrorType.MergedCell:
                        this.Workbook.XLRemoveMergedCells(sheetName);
                        break;
                    case ErrorType.ColorCoded:
                        this.Workbook.XLRemoveColourCells(sheetName);
                        break;
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }
        }

        /// <summary>
        /// This function can be used to fix the error in the given workbook.
        /// </summary>
        /// <param name="error">Error which has to be fixed.</param>
        /// <returns>True, if fix was successful; otherwise false.</returns>
        public bool FixError(ErrorDetail error)
        {
            bool isRemoved = false;
            try
            {
                switch (error.Error)
                {
                    case ErrorType.Comments:
                        isRemoved = this.Workbook.XLRemoveComment(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Worksheet:
                        isRemoved = this.Workbook.XLRemoveSheet(error.ControlName);
                        break;
                    case ErrorType.Charts:
                        isRemoved = this.Workbook.XLRemoveChart(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Shapes:
                        isRemoved = this.Workbook.XLRemoveShape(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.MergedCell:
                        isRemoved = this.Workbook.XLRemoveRange(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.ColorCoded:
                        isRemoved = this.Workbook.XLRemoveColourCell(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Tables:
                    case ErrorType.MixedType:
                    case ErrorType.BlankCell:
                    case ErrorType.Commas:
                    case ErrorType.SpecialCharacter:
                        break;
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }

            return isRemoved;
        }

        /// <summary>
        /// This function is used to fix the list of errors in the given workbook.
        /// </summary>
        /// <param name="errors">List of errors to be fixed.</param>
        public void FixErrors(List<ErrorDetail> errors)
        {
            if (errors != null)
            {
                foreach (var item in errors)
                {
                    this.FixError(item);
                }
            }
        }

        /// <summary>
        /// This function is used to Select the error in the given workbook.
        /// </summary>
        /// <param name="error">Error which has to be selected.</param>
        public void Select(ErrorDetail error)
        {
            try
            {
                switch (error.Error)
                {
                    case ErrorType.Comments:
                        this.Workbook.XLSelectComment(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Names:
                    case ErrorType.Worksheet:
                        this.Workbook.XLSelectSheet(error.ControlName);
                        break;
                    case ErrorType.Charts:
                        this.Workbook.XLSelectChart(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Shapes:
                        this.Workbook.XLSelectShape(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.MergedCell:
                        this.Workbook.XLSelectRange(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Tables:
                        this.Workbook.XLSelectTables(error.ControlName, error.SheetName);
                        break;
                    case ErrorType.BlankCell:
                        this.Workbook.XLSelectBlank(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.Commas:
                        this.Workbook.XLSelectComma(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.SpecialCharacter:
                        this.Workbook.XLSelectSpecialChar(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.ColorCoded:
                        this.Workbook.XLSelectColourCoded(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.MixedType:
                        this.Workbook.XLSelectMixedType(error.ErrorAddress, error.SheetName);
                        break;
                    case ErrorType.NonContiguous:
                        this.Workbook.XLSelectNonContiguous(error.ErrorAddress, error.SheetName);
                        break;
                }
            }
            catch (Exception)
            {
                // TODO : Add event handling logic.
            }
        }

        /// <summary>
        /// Gets the default document properties.
        /// </summary>
        /// <returns>Default document properties list.</returns>
        public Dictionary<string, string> GetDocumentProperties()
        {
            Dictionary<string, string> documentProperties = new Dictionary<string, string>();

            DocumentProperties documentProperties1 = (DocumentProperties)this.Workbook.BuiltinDocumentProperties;

            for (int i = 1; i <= documentProperties1.Count; i++)
            {
                DocumentProperty dp = documentProperties1[i];
                try
                {
                    documentProperties.Add(dp.Name, dp.Value != null ? dp.Value.ToString() : string.Empty);
                }
                catch (Exception)
                {
                    // Ignore Any exception.
                }
            }

            return documentProperties;
        }

        /// <summary>
        /// Gets the workbook name.
        /// </summary>
        /// <returns>Name of the workbook.</returns>
        public string GetWorkbookName()
        {
            return this.Workbook.Name;
        }

        /// <summary>
        /// Gets list of all sheet names in the current workbook.
        /// </summary>
        /// <returns>List of workbook sheet names.</returns>
        public IDictionary<int, string> GetSheetNames()
        {
            return this.Workbook.GetSheetNames();
        }
    }
}