using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Text.RegularExpressions;using System.Threading.Tasks;using DocumentFormat.OpenXml.Packaging;using System.Drawing;using System.Xml.Linq;using DocumentFormat.OpenXml.Spreadsheet;namespace ExportExcel{    internal class Utility    {        /// <summary>        /// Given a cell name, parses the specified cell to get the row index.        /// </summary>        /// <param name="cellReference">Address of the cell (ie. B2)</param>        /// <returns>Row Index (ie. 2)</returns>        public static int GetRowIndex(string cellReference)        {            // Create a regular expression to match the row index portion the cell name.            Regex regex = new Regex(@"\d+");            Match match = regex.Match(cellReference);            return int.Parse(match.Value);        }        /// <summary>        /// Given a cell name, parses the specified cell to get the column index.        /// </summary>        /// <param name="cellReference">Address of the cell (ie. C2)</param>        /// <returns>Row Index (ie. 3)</returns>        public static int GetColumnIndex(string cellReference)        {            // Create a regular expression to match the column name portion of the cell name.            Regex regex = new Regex("[A-Za-z]+");            Match match = regex.Match(cellReference);            string name = match.Value.ToUpper();            int number = 0;            int pow = 1;            for (int i = name.Length - 1; i >= 0; i--)            {                number += (name[i] - ‘A‘ + 1) * pow;                pow *= 26;            }            return number;        }        /// <summary>        /// Given a cell name, parses the specified cell to get the column index.        /// </summary>        /// <param name="cellReference">Address of the cell (ie. C2)</param>        /// <returns>Row Letter (ie. C)</returns>        public static string GetColumnLetter(string cellReference)        {            // Create a regular expression to match the column name portion of the cell name.            Regex regex = new Regex("[A-Za-z]+");            Match match = regex.Match(cellReference);            string name = match.Value.ToUpper();            return name;        }        /// <summary>        /// 将column从12345 转换成 ABCDE        /// </summary>        /// <param name="colindex"></param>        /// <returns></returns>        public static string ToColumnLetter(int colindex)        {            int quotient =colindex/26;            if(quotient>0)            {               return  ToColumnLetter(quotient)+ Char.ConvertFromUtf32((colindex % 26)+64).ToString();            }            else                return char.ConvertFromUtf32(colindex+64).ToString();        }        /// <summary>        /// 得到一个cell区域的开始 如sheet1!A1:B3,返回{A1,B3} 或者 Sheet3!$B$3:$F$3 返回{B3,F3}        /// </summary>        /// <param name="celladdr">一个单元格的地址</param>        /// <returns></returns>        public static string[] GetCellAddr(string celladdr)        {            if (string.IsNullOrEmpty(celladdr)) return null;            int i = celladdr.IndexOf("!");            if (i > 0) celladdr = celladdr.Substring(i + 1);            return celladdr.Replace("$", "").Trim().Split(‘:‘);        }        public static string GetSheetName(string celladdr)        {            if (string.IsNullOrEmpty(celladdr)) return null;            int i = celladdr.IndexOf("!");            if (i > 0)            {                if (celladdr.StartsWith("‘"))                    return celladdr.Substring(1, i - 2);                else                    return celladdr.Substring(0, i);            }            return string.Empty;        }         /// <summary>        /// 增加新的行        /// </summary>        /// <param name="address"></param>        /// <returns></returns>        public static string AddRow(string address)        {            var ad = address.Split(‘:‘);            string startAddress = ad[0];            string endAddress = ad[1];            int start = GetRowIndex(startAddress);            int end = GetRowIndex(startAddress);            return startAddress.Replace(start.ToString(), (start + 1).ToString()) + ":" + endAddress.Replace(end.ToString(), (end + 1).ToString());        }    }    }

using System;using System.Collections.Generic;using System.Linq;using System.Web;using DocumentFormat.OpenXml;using DocumentFormat.OpenXml.Packaging;using Excel = DocumentFormat.OpenXml.Spreadsheet;namespace ExportExcel{    public static class WorkSheetExtend    {        // Given a worksheet, a column name, and a row index,         // gets the cell at the specified column and         private static Excel.Cell GetCell(this  Excel.Worksheet worksheet,                     string columnName, uint rowIndex)        {            Excel.Row row = GetRow(worksheet, rowIndex);            if (row == null)                return null;            return row.Elements<Excel.Cell>().Where(c => string.Compare                   (c.CellReference.Value, columnName +                   rowIndex, true) == 0).First();        }        // Given a worksheet and a row index, return the row.        public static Excel.Row GetRow(this Excel.Worksheet worksheet, uint rowIndex)        {            return worksheet.GetFirstChild<Excel.SheetData>().              Elements<Excel.Row>().Where(r => r.RowIndex == rowIndex).First();        }        public static void UpdateCellNumber(this   Excel.Worksheet worksheet, string col, uint row, string text)        {            Excel.Cell cell = GetCell(worksheet, col, row);            cell.CellValue = new Excel.CellValue(text);            cell.DataType =                new EnumValue<Excel.CellValues>(Excel.CellValues.Number);        }        public static void UpdateCell(this   Excel.Worksheet worksheet, string col, uint row, string text)        {            var cell = GetCell(worksheet, col, row);            cell.CellValue = new Excel.CellValue(text);            cell.DataType =                new EnumValue<Excel.CellValues>(Excel.CellValues.String);        }        public static void UpdateCell(this   Excel.Row row, string col, string text )        {            var cell = row.Elements<Excel.Cell>().Where(c => string.Compare                (c.CellReference.Value, col +                row.RowIndex, true) == 0).First();            cell.CellValue = new Excel.CellValue(text);            cell.DataType =                new EnumValue<Excel.CellValues>(Excel.CellValues.String);        }        public static void UpdateCellNumber(this   Excel.Row row, string col, string text)        {            var cell = row.Elements<Excel.Cell>().Where(c => string.Compare                   (c.CellReference.Value, col +                   row.RowIndex, true) == 0).First();            cell.CellValue = new Excel.CellValue(text);            cell.DataType =                new EnumValue<Excel.CellValues>(Excel.CellValues.Number);        }        public static WorksheetPart GetWorksheetPartByName(this SpreadsheetDocument document, string sheetName)        {            IEnumerable<Excel.Sheet> sheets =               document.WorkbookPart.Workbook.GetFirstChild<Excel.Sheets>().               Elements<Excel.Sheet>().Where(s => s.Name == sheetName);            if (sheets.Count() == 0)            {                // The specified worksheet does not exist.                return null;            }            string relationshipId = sheets.First().Id.Value;            WorksheetPart worksheetPart = (WorksheetPart)                 document.WorkbookPart.GetPartById(relationshipId);            return worksheetPart;        }        public static Excel.Row CreateRow(this  Excel.Worksheet worksheet, Excel.Row refRow)        {            Excel.SheetData sheetData = worksheet.GetFirstChild<Excel.SheetData>();            uint newRowIndex = 0;            var newRow = new Excel.Row() { RowIndex = refRow.RowIndex.Value };            var cells = refRow.Elements<Excel.Cell>();            newRow.Height = new DoubleValue(refRow.Height);            newRow.CustomHeight = new BooleanValue(refRow.CustomHeight);            foreach (Excel.Cell cell in cells)            {                Excel.Cell newCell = (Excel.Cell)cell.CloneNode(true);                newCell.StyleIndex = new UInt32Value(cell.StyleIndex);                newRow.Append(newCell);            }            IEnumerable<Excel.Row> rows = sheetData.Descendants<Excel.Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value);            foreach (Excel.Row row in rows)            {                newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);                foreach (var cell in row.Elements<Excel.Cell>())                {                    // Update the references for reserved cells.                    string cellReference = cell.CellReference.Value;                    cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));                }                row.RowIndex = new UInt32Value(newRowIndex);            }            sheetData.InsertBefore(newRow, refRow);            // process merge cell in cloned rows            var mcells = worksheet.GetFirstChild<Excel.MergeCells>();            if (mcells != null)            {                //处理所有动态行以下的merg                var clonedMergeCells = mcells.Elements<Excel.MergeCell>().                     Where(m => Utility.GetRowIndex(m.Reference.Value.Split(‘:‘)[0]) >= newRow.RowIndex.Value).ToList<Excel.MergeCell>();                foreach (var cmCell in clonedMergeCells)                {                    cmCell.Reference.Value = Utility.AddRow(cmCell.Reference.Value);                }                //增加新的merg                var newMergeCells = new List<Excel.MergeCell>();                var rowMergeCells = mcells.Elements<Excel.MergeCell>().                    Where(m => Utility.GetRowIndex(m.Reference.Value.Split(‘:‘)[0]) == refRow.RowIndex).ToList<Excel.MergeCell>();                foreach (var mc in rowMergeCells)                {                    newMergeCells.Add(new Excel.MergeCell() { Reference = mc.Reference.Value.Replace(refRow.RowIndex.Value.ToString(), (newRow.RowIndex.Value).ToString()) });                }                uint count = mcells.Count.Value;                mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count);                mcells.Append(newMergeCells.ToArray());            }            return newRow;        }    }}