Cacher is the code snippet organizer for pro developers

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace SGJ.Common.Utilities
{
    public class OpenXmlExcelUtilities
    {
        #region Worksheet
        public static WorksheetPart CopySheet(SpreadsheetDocument pobjDoc, string pstrSourceSheetName, string pstrClonedSheetName)
        {
            WorkbookPart workbookPart = pobjDoc.WorkbookPart;
            //Get the source sheet to be copied
            WorksheetPart sourceSheetPart = GetWorkSheetPartByName(workbookPart, pstrSourceSheetName);
            if (sourceSheetPart == null)
            {
                sourceSheetPart = GetFirstOrDefaultWorkSheetPart(workbookPart);
            }
            //------------------------------------------------------
            SpreadsheetDocument tempDoc = SpreadsheetDocument.Create(new MemoryStream(), pobjDoc.DocumentType);
            WorkbookPart tempWorkbookPart = tempDoc.AddWorkbookPart();
            WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
            //------------------------------------------------------
            //Add cloned sheet and all associated parts to workbook
            WorksheetPart clonedWorksheetPart = workbookPart.AddPart<WorksheetPart>(tempWorksheetPart);
            //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
            int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
            int tableId = numTableDefParts;
            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0)
            {
                FixupTableParts(clonedWorksheetPart, numTableDefParts);
            }
            //There should only be one sheet that has focus
            CleanView(clonedWorksheetPart);
            //Add new sheet to main workbook part
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            Sheet copiedSheet = new Sheet();
            copiedSheet.Name = pstrClonedSheetName;
            copiedSheet.Id = workbookPart.GetIdOfPart(clonedWorksheetPart);
            copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
            sheets.Append(copiedSheet);
            return clonedWorksheetPart;
        }

        public static void RemoveSheet(SpreadsheetDocument pobjDoc, string pstrSheetName)
        {
            WorkbookPart workbookPart = pobjDoc.WorkbookPart;
            //Get the source sheet to be copied
            var worksheetPart = GetWorkSheetPartByName(workbookPart, pstrSheetName);
            var sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name.Value.Equals(pstrSheetName)).First();
            if (sheet == null)
            {
                return;
            }
            else
            {
                sheet.Remove();
                workbookPart.DeletePart(worksheetPart);
            }
        }

        public static WorksheetPart GetFirstOrDefaultWorkSheetPart(WorkbookPart pobjWorkbookPart)
        {
            //Get the relationship id of the sheetname
            string relId = pobjWorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault().Id;
            return (WorksheetPart)pobjWorkbookPart.GetPartById(relId);
        }

        public static WorksheetPart GetWorkSheetPartByName(WorkbookPart pobjWorkbookPart, string pstrSheetName)
        {
            //Get the relationship id of the sheetname
            string relId = pobjWorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name.Value.Equals(pstrSheetName)).First().Id;
            return (WorksheetPart)pobjWorkbookPart.GetPartById(relId);
        }

        public static WorksheetPart GetWorkSheetPartById(WorkbookPart pobjWorkbookPart, string pstrId)
        {
            return (WorksheetPart)pobjWorkbookPart.GetPartById(pstrId);
        }

        public static void CleanView(WorksheetPart pobjWorksheetPart)
        {
            //There can only be one sheet that has focus
            SheetViews views = pobjWorksheetPart.Worksheet.GetFirstChild<SheetViews>();
            if (views != null)
            {
                views.Remove();
                pobjWorksheetPart.Worksheet.Save();
            }
        }

        public static void FixupTableParts(WorksheetPart pobjWorksheetPart, int pintTableDefParts)
        {
            //Every table needs a unique id and name
            foreach (TableDefinitionPart tableDefPart in pobjWorksheetPart.TableDefinitionParts)
            {
                pintTableDefParts++;
                tableDefPart.Table.Id = (uint)pintTableDefParts;
                tableDefPart.Table.DisplayName = "CopiedTable" + pintTableDefParts;
                tableDefPart.Table.Name = "CopiedTable" + pintTableDefParts;
                tableDefPart.Table.Save();
            }
        }

        #endregion

        #region Row
        public static int GetRowIndex(string cellReference)
        {
            var regex = new Regex("[0-9]+");
            var match = regex.Match(cellReference);
            return Int32.Parse(match.Value);
        }

        public static Row CreateOrGetRow(Worksheet pobjWorksheet, uint rowIndex)
        {
            Row row = pobjWorksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            if (row == null)
            {
                row = new Row { RowIndex = rowIndex };
            }
            return row;
        }

        #endregion

        #region Column

        public static string GetColumnName(string cellReference)
        {
            var regex = new Regex("[A-Za-z]+");
            var match = regex.Match(cellReference);
            return match.Value;
        }

        public static string ConvertColumnNumberToName(int intCol)
        {
            var intFirstLetter = ((intCol) / 676) + 64;
            var intSecondLetter = ((intCol % 676) / 26) + 64;
            var intThirdLetter = (intCol % 26) + 65;

            var firstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
            var secondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
            var thirdLetter = (char)intThirdLetter;

            return string.Concat(firstLetter, secondLetter, thirdLetter).Trim();
        }

        public static string ConvertColumnNumberToName1(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        public static int ConvertColumnNameToNumber(string columnName)
        {
            var alpha = new Regex("^[A-Z]+$");
            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();
            Array.Reverse(colLetters);

            var convertedValue = 0;
            for (int i = 0; i < colLetters.Length; i++)
            {
                char letter = colLetters[i];
                int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
                convertedValue += current * (int)Math.Pow(26, i);
            }
            return convertedValue;
        }

        #endregion

        #region Cell

        public static Cell GetCell(Row row, string columnName)
        {
            if (row != null)
            {
                return row.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + row.RowIndex, true) == 0).First();
            }
            else
            {
                return null;
            }
        }

        public static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            Row row = CreateOrGetRow(worksheet, rowIndex);
            if (row != null)
            {
                return row.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First();
            }
            else
            {
                return null;
            }
        }

        public static Cell CreateOrUpdateCell(Row row, string columnName, string text)
        {
            Cell cell = GetCell(row, columnName);
            if (cell != null)
            {
                cell.CellValue = new CellValue(text);
                cell.DataType = new EnumValue<CellValues>(CellValues.String);
            }
            else
            {
                cell = new Cell { DataType = CellValues.InlineString, CellReference = columnName + row.RowIndex };
                var inlineString = new InlineString();
                var t = new Text { Text = text };
                inlineString.AppendChild(t);
                cell.AppendChild(inlineString);
            }
            return cell;
        }

        public static Cell CreateOrUpdateCell(WorksheetPart pobjWorksheetPart, string columnName, uint rowIndex, string text)
        {
            if (pobjWorksheetPart != null)
            {
                Cell cell = GetCell(pobjWorksheetPart.Worksheet, columnName, rowIndex);
                if (cell != null)
                {
                    cell.CellValue = new CellValue(text);
                    cell.DataType = new EnumValue<CellValues>(CellValues.String);
                }
                else
                {
                    cell = new Cell { DataType = CellValues.InlineString, CellReference = columnName + rowIndex };
                    var inlineString = new InlineString();
                    var t = new Text { Text = text };
                    inlineString.AppendChild(t);
                    cell.AppendChild(inlineString);
                }
                return cell;
            }
            return null;
        }


        public static void MergeCells(WorksheetPart worksheetPart, Cell cell, int RowSpan)
        {
            Worksheet workSheet = worksheetPart.Worksheet;
            MergeCells mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
            if (mergeCells == null)
            {
                mergeCells = new MergeCells();
                workSheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
            }

            //mergeCells.Append(new MergeCell() { Reference = new StringValue("C1:F1") });
            string columnName = GetColumnName(cell.CellReference);
            int columnIndex = ConvertColumnNameToNumber(columnName);
            int rowIndex = GetRowIndex(cell.CellReference);
            string value = string.Format("{1}{0}:{2}{0}", rowIndex, ConvertColumnNumberToName(columnIndex), ConvertColumnNumberToName(columnIndex + RowSpan - 1));
            mergeCells.Append(new MergeCell() { Reference = new StringValue(value) });

        }

        #endregion

    }
}