main / Source / Excel / Excel.BizLogic / Extensions / WorksheetExtensions.cs

using System.Collections.Generic;
using System.Data;
using DataUp.Excel.Common;
using Microsoft.Office.Interop.Excel;
using Tools = Microsoft.Office.Tools.Excel;

namespace DataUp.Excel.BizLogic
{
    /// <summary>
    /// Extensions for Worksheet class.
    /// </summary>
    public static class WorksheetExtensions
    {
        /// <summary>
        /// Updates the metadata in the given worksheet.
        /// </summary>
        /// <param name="workSheet">Current worksheet instance.</param>
        /// <param name="metadata">Metadata list.</param>
        /// <param name="Factory">ApplicationFactory instance.</param>
        public static void UpdateMetadata(this _Worksheet workSheet, List<MetadataDetail> metadata, Tools.ApplicationFactory Factory)
        {
            if (workSheet != null)
            {
                Tools.ListObject metadataTool = null;
                try
                {
                    var metadataList = workSheet.GetMetadataTable();
                    if (metadataList != null)
                    {
                        metadataTool = Factory.GetVstoObject(metadataList);
                        metadataTool.Delete();
                    }

                    Tools.Worksheet sheet = Factory.GetVstoObject(workSheet);
                    metadataTool = sheet.CreateMetadataTable();

                    metadataTool.Resize(workSheet.Range["A1", "B" + metadata.Count]);
                    metadataTool.AutoSetDataBoundColumnHeaders = true;
                    metadataTool.TableStyle = Common.Constants.MetadataTableStyleName;

                    System.Data.DataTable table = GetDataTable(metadata);
                    metadataTool.SetDataBinding(table);

                    //Point to metadata sheet from metadata-->save
                    sheet.Activate();

                    workSheet.UsedRange.Columns.AutoFit();
                }
                catch
                {
                }
                finally
                {
                    if (metadataTool != null)
                    {
                        metadataTool.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// Updates the list of parameter metadata in the given worksheet
        /// </summary>
        /// <param name="workSheet">Current worksheet instance.</param>
        /// <param name="metadata">Parameter metadata list.</param>
        /// <param name="Factory">ApplicationFactory instance.</param>
        public static void UpdateparaMetadata(this _Worksheet workSheet, List<ParameterDetail> metadata, Tools.ApplicationFactory Factory)
        {
            if (workSheet != null && metadata != null)
            {
                Tools.ListObject metadataTool = null;
                try
                {
                    var metadataList = workSheet.GetParaMetadataTable();
                    if (metadataList != null)
                    {
                        metadataTool = Factory.GetVstoObject(metadataList);
                        metadataTool.Delete();
                    }

                    var unitsRange = workSheet.get_Range("$M$1").EntireColumn;
                    unitsRange.Clear();

                    if (metadata.Count > 0)
                    {
                        Tools.Worksheet sheet = Factory.GetVstoObject(workSheet);
                        var standardUnits = Helper.GetStandardUnits();
                        for (int rowIndex = 0; rowIndex < standardUnits.Count; rowIndex++)
                        {
                            sheet.Cells[rowIndex + 1, unitsRange.Column] = standardUnits[rowIndex];
                        }

                        metadataTool = sheet.CreateParaMetadataTable();

                        metadataTool.Resize(workSheet.Range["D1", "I" + metadata.Count]);
                        metadataTool.AutoSetDataBoundColumnHeaders = true;
                        metadataTool.TableStyle = Common.Constants.MetadataTableStyleName;

                        System.Data.DataTable table = GetParaDataTable(metadata);
                        metadataTool.SetDataBinding(table);

                        metadataList = workSheet.GetParaMetadataTable();
                        if (metadataList != null)
                        {
                            for (int rCnt = 2; rCnt <= metadataList.Range.Rows.Count; rCnt++)
                            {
                                var typeCell = (Range)metadataList.Range.Cells[rCnt, 5];

                                typeCell.Validation.Delete();
                                typeCell.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, Common.Constants.ParameterMetadataType);
                                typeCell.Validation.InCellDropdown = true;
                            }
                        }
                        
                    }

                    workSheet.UsedRange.Columns.AutoFit();

                    workSheet.get_Range("$M$1").EntireColumn.Hidden = true;
                }
                catch
                {
                }
                finally
                {
                    if (metadataTool != null)
                    {
                        metadataTool.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// Clear parameter metadata table.
        /// </summary>
        /// <param name="workSheet">Current worksheet instance.</param>
        /// <param name="Factory">ApplicationFactory instance.</param>
        public static void ClearParameter(this _Worksheet workSheet, Tools.ApplicationFactory Factory)
        {
            if (workSheet != null)
            {
                ListObject obj = workSheet.GetParaMetadataTable();
                foreach (Range r in obj.Range.Rows)
                {

                    r.Rows.Clear();
                    workSheet.Activate();
                }
            }
        }

        /// <summary>
        /// Gets the metadata list from the given worksheet.
        /// </summary>
        /// <param name="workSheet">Current worksheet instance.</param>
        /// <param name="Factory">ApplicationFactory instance.</param>
        /// <returns>List of metadata.</returns>
        public static Dictionary<string, string> GetExistingMetadata(this _Worksheet workSheet, Tools.ApplicationFactory Factory)
        {
            Dictionary<string, string> metadataExisitingList = new Dictionary<string, string>();
            if (workSheet != null)
            {
                Tools.ListObject metadataTool;

                var metadataTable = workSheet.GetMetadataTable();
                if (metadataTable != null)
                {
                    metadataTool = Factory.GetVstoObject(metadataTable);
                    var existingData = metadataTool.DataSource as System.Data.DataTable;
                    if (existingData != null)
                    {
                        foreach (DataRow item in existingData.Rows)
                        {
                            metadataExisitingList.Add(item["Name"].ToString(), item["Value"].ToString());
                        }
                    }
                    else
                    {
                        var range = metadataTable.Range;
                        for (int row = 1; row <= range.Rows.Count; row++)
                        {
                            if (range.Cells[row, 2].Value != null)
                            {
                                metadataExisitingList.Add(range.Cells[row, 1].Value, range.Cells[row, 2].Value.ToString());
                            }
                        }
                    }
                }
                else
                {
                    // If Metadata Table is not available then get the value from the first two Columns of the metadata sheet.
                    var range = workSheet.UsedRange;
                    for (int row = 1; row <= range.Rows.Count; row++)
                    {
                        // Do not consider Nu, or empty string value in the First/Second column.
                        if (range.Cells[row, 1].Value != null && range.Cells[row, 2].Value != null)
                        {
                            metadataExisitingList.Add(range.Cells[row, 1].Value, range.Cells[row, 2].Value.ToString());
                        }
                    }
                }
            }

            return metadataExisitingList;
        }

        /// <summary>
        /// Gets the header row of the given range.
        /// </summary>
        /// <param name="range">Current Range instance.</param>
        /// <returns>List of object present in the header row.</returns>
        public static List<object> GetHeaderRow(this Range range)
        {
            List<object> obj = new List<object>();
            if (range.Value != null)
            {
                for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                {
                    bool flag = false;
                    for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                    {
                        var val = (range.Cells[rCnt, cCnt] as Range).Value;
                        if (val != null)
                        {
                            obj.Add(val);
                        }

                        if (cCnt == range.Columns.Count)
                        {
                            flag = true;
                        }
                    }
                    if (flag == true)
                        break;
                }
            }

            return obj;
        }

        private static Tools.ListObject CreateMetadataTable(this Tools.Worksheet sheet)
        {
            Tools.ListObject metadataTable = null;
            if (sheet != null)
            {
                if (metadataTable == null)
                {
                    metadataTable = sheet.Controls.AddListObject(sheet.Range["A1", "B44"], Excel.Common.Constants.MetadataRangeName);
                }
            }

            return metadataTable;
        }

        private static Tools.ListObject CreateParaMetadataTable(this Tools.Worksheet sheet)
        {
            Tools.ListObject ParametadataTable = null;
            if (sheet != null)
            {
                if (ParametadataTable == null)
                {
                    ParametadataTable = sheet.Controls.AddListObject(sheet.Range["D1", "I1"], Excel.Common.Constants.ParaMetadataRangeName);
                }
            }

            return ParametadataTable;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "We are disposing the datatable")]
        private static System.Data.DataTable GetDataTable(List<MetadataDetail> metadata)
        {
            System.Data.DataTable table = new System.Data.DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Value");

            DataRow row;
            foreach (var item in metadata)
            {
                row = table.NewRow();
                row["Name"] = item.Name;
                row["Value"] = item.Value;

                table.Rows.Add(row);
            }
            return table;
        }

        //for parameter metadata
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "We are disposing the datatable")]
        private static System.Data.DataTable GetParaDataTable(List<ParameterDetail> metadata)
        {
            System.Data.DataTable table = new System.Data.DataTable();
            table.Columns.Add("EntityName");
            table.Columns.Add("EntityDescription");
            table.Columns.Add("Name");
            table.Columns.Add("Description");
            table.Columns.Add("Type");
            table.Columns.Add("Units");

            DataRow row;
            foreach (var item in metadata)
            {
                row = table.NewRow();
                row["EntityName"] = item.EntityName;
                row["EntityDescription"] = item.EntityDescription;
                row["Name"] = item.Name;
                row["Description"] = item.Description;
                row["Type"] = item.Type;
                row["Units"] = item.Units;
                table.Rows.Add(row);
            }
            return table;
        }
    }
}
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.