Source

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

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

namespace DataUp.Excel.Common
{
    /// <summary>
    /// Extensions for Worksheet class.
    /// </summary>
    public static class WorksheetExtensions
    {
        /// <summary>
        /// Activates the sheet.
        /// </summary>
        /// <param name="sheet">Current worksheet instance.</param>
        /// <param name="workbook">Current workbook instance.</param>
        public static void ActivateSheet(this _Worksheet sheet, _Workbook workbook)
        {
            // Activate the sheet in which the range is present.
            if (sheet != workbook.ActiveSheet)
            {
                sheet.Activate();
            }
        }

        /// <summary>
        /// Gets the metadata table.
        /// </summary>
        /// <param name="sheet">Current worksheet instance.</param>
        /// <returns>Metadata table.</returns>
        public static ListObject GetMetadataTable(this _Worksheet sheet)
        {
            ListObject metadataTable = null;
            if (sheet != null)
            {
                foreach (ListObject obj in sheet.ListObjects)
                {
                    if (string.Compare(obj.Name, Constants.MetadataRangeName, true) == 0)
                    {
                        metadataTable = obj;
                        break;
                    }
                }
            }

            return metadataTable;
        }

        /// <summary>
        /// Gets parameter metadata table.
        /// </summary>
        /// <param name="sheet">Current worksheet instance.</param>
        /// <returns>Parameter metadata table.</returns>
        public static ListObject GetParaMetadataTable(this _Worksheet sheet)
        {
            ListObject parametadataTable = null;
            if (sheet != null)
            {
                foreach (ListObject obj in sheet.ListObjects)
                {
                    if (string.Compare(obj.Name, Constants.ParaMetadataRangeName, true) == 0)
                    {
                        parametadataTable = obj;
                        break;
                    }
                }
            }

            return parametadataTable;
        }

        /// <summary>
        /// Removes the metadata table from the current worksheet.
        /// </summary>
        /// <param name="sheet">Current worksheet instance.</param>
        public static void RemoveMetadataTable(this _Worksheet sheet)
        {
            if (sheet != null)
            {
                foreach (ListObject obj in sheet.ListObjects)
                {
                    if (string.Compare(obj.Name, Constants.MetadataRangeName, true) == 0)
                    {
                        obj.Range.Delete();
                        break;
                    }
                }
            }
        }

        /// <summary>
        /// Remove all the comments in a worksheet.
        /// </summary>
        public static void XLRemoveComments(this _Worksheet worksheet)
        {
            List<Comment> comments = new List<Comment>();
            if (worksheet != null)
            {
                for (int i = 1; i <= worksheet.Comments.Count; i++)
                {
                    var com = worksheet.Comments[i] as Comment;
                    if (com != null)
                    {
                        comments.Add(com);
                    }
                }

                foreach (var item in comments)
                {
                    item.Delete();
                }
            }
        }

        /// <summary>
        /// Remove all the colour coded cells in a workbook.
        /// </summary>
        public static void XLRemoveColourCells(this _Worksheet worksheet)
        {
            List<Range> range = new List<Range>();

            if (worksheet != null)
            {
                worksheet.UsedRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                worksheet.UsedRange.Interior.Color = -4142;
            }
        }

        /// <summary>
        /// Remove all the merged cells in a worksheet.
        /// </summary>
        public static void XLRemoveMergedCells(this _Worksheet worksheet)
        {
            if (worksheet != null)
            {
                // Check Merged Cell
                if (worksheet.UsedRange.MergeCells is System.DBNull || (bool)worksheet.UsedRange.MergeCells)
                {
                    worksheet.UsedRange.UnMerge();
                }
            }
        }

        /// <summary>
        /// Remove all the charts in a worksheet.
        /// </summary>
        public static void XLRemoveCharts(this _Worksheet worksheet)
        {
            // Given an Excel worksheet, and the title of a chart to export, create a 
            // new xml file containing the chart. Although the chart won't be loadable
            // in Excel, you could extract the chart from it at a later date.

            List<ChartObject> charts = new List<ChartObject>();

            if (worksheet != null)
            {
                ChartObjects chartObjects = (ChartObjects)(worksheet.ChartObjects());

                for (int i = 1; i <= chartObjects.Count; i++)
                {
                    ChartObject chtObj = (ChartObject)(chartObjects.Item(i));

                    if (chtObj != null)
                    {
                        charts.Add(chtObj);
                    }
                }
            }

            foreach (var item in charts)
            {
                item.Delete();
            }
        }

        /// <summary>
        /// Remove all the tables in a worksheet.
        /// </summary>
        public static void XLRemoveTables(this _Worksheet worksheet)
        {
            if (worksheet != null)
            {
                foreach (ListObject obj in worksheet.ListObjects)
                {
                    obj.Delete();
                }
            }
        }

        /// <summary>
        /// Remove all the shapes in a worksheet.
        /// </summary>
        public static void XLRemoveShapes(this _Worksheet worksheet)
        {
            List<Microsoft.Office.Interop.Excel.Shape> shapeList = new List<Microsoft.Office.Interop.Excel.Shape>();
            if (worksheet != null)
            {
                var shapes = worksheet.Shapes;

                for (int i = 1; i <= shapes.Count; i++)
                {
                    shapeList.Add(shapes.Item(i));
                }
            }

            foreach (var shape in shapeList)
            {
                if ((shape != null)
                    && (shape.Name != null)
                    && shape.HasChart != Microsoft.Office.Core.MsoTriState.msoTrue
                    && shape.Visible == Microsoft.Office.Core.MsoTriState.msoTrue
                    && shape.Type != Microsoft.Office.Core.MsoShapeType.msoComment)
                {
                    shape.Delete();
                }
            }
        }

        /// <summary>
        /// This function can be used to retrieve the data for the selected range in array format.
        /// </summary>
        /// <param name="range">
        /// Excel range which is in scope.
        /// </param>
        /// <param name="getHeaderOnly">
        /// True to return only the first row in the range(header), false to return the entire range
        /// </param>
        /// <returns>
        /// Data in two dimensional array format.
        /// </returns>
        public static object[,] GetDataArray(this Range range, bool getHeaderOnly)
        {
            object[,] obj = null;
            if (range != null)
            {
                if (range.Cells.Count == 1)
                {
                    // Initialize an object array with the lower bound from 1.
                    obj = (object[,])Array.CreateInstance(typeof(object), new int[] { 1, 1 }, new int[] { 1, 1 });
                    obj[1, 1] = range.Value;
                }
                else
                {
                    if (getHeaderOnly)
                    {
                        obj = (object[,])Array.CreateInstance(typeof(object), new int[] { 1, range.EntireColumn.Count }, new int[] { 1, 1 });
                        Range headerRow = range.Rows[1];
                        if (range.EntireColumn.Count == 1)
                        {
                            obj[1, 1] = headerRow.Value;
                        }
                        else
                        {
                            obj = headerRow.Value;
                        }
                    }
                    else
                    {
                        obj = range.Value;
                    }
                }
            }

            return obj;
        }

    }
}