OpenXML Spreadsheet, how to copy and insert specified rows

We usually use OpenXML spreadsheet to create excel document and report. For some document and report, sometimes we hope copy and insert some rows or columns automatically. For example , we want to output a list data into excel worksheet whose element has 3 formatted rows including some merge cells etc. If list’s size is 10, we hope copy 3 formatted rows of first element 10 times and insert copied rows below the first element rows automatically.

In OpenXML spreadsheet, when you copy some rows and insert copied rows into destination, there are many works to do. First, in the copied rows, maybe there are some merge cells, data validate list ( pull down cells), formulas, drawings, charts. You must change the row’s index number to the inserted rows. Then the row’s index number for objects below in the destination must be changed. These objects include normal cells, merge cells, data validate list, formulas, drawings, charts etc.

The following is part of code to explain how to implement copy and insert. About the complete examples, please Download ExcelTDF Package

        /// <summary>                                                      
        /// Copy the part of rows from srcRowFrom to srcRowTo between srcColFrom and srcColTo. 
        /// The row index, whose  column is between column srcColFrom and srcColTo, and whose
        /// row is below the destRowFrom, will be changed after cloned cells added into worksheet.
        /// </summary>
        /// <param name="workbookPart"><see cref="WorkbookPart"/></param>
        /// <param name="sheetIndex">worksheet index</param>
        /// <param name="srcRowFrom">source from row index</param>
        /// <param name="srcRowTo">source to row index</param>
        /// <param name="destRowFrom">destination from row index</param>
        /// <param name="copyTimes">the times to be copied</param>
        /// <param name="srcColFrom">limitation of column left</param>
        /// <param name="srcColTo">limitation of column right</param>
        public static void CopyRowRange(WorkbookPart workbookPart, int sheetIndex,
            int srcRowFrom, int srcRowTo, int destRowFrom, int copyTimes, int srcColFrom, int srcColTo)
            //only support copy down 
            if (copyTimes <= 0 || srcRowTo < srcRowFrom || destRowFrom < srcRowFrom) return;
            int destRowFromBase = destRowFrom;

            //Get the source sheet to be copied
            WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, sheetIndex);
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            //get cells to be cloned according to the specified rows and columns
            IList<Cell> cells = sheetData.Descendants<Cell>().Where(c =>
                GetRowIndex(c.CellReference) >= srcRowFrom &&
                GetRowIndex(c.CellReference) <= srcRowTo &&
                GetCellColIndex(c.CellReference) >= srcColFrom &&
                GetCellColIndex(c.CellReference) <= srcColTo).ToList<Cell>();

            //no cells to be cloned
            if (cells.Count() == 0) return;

            //after cloned, the index of rows from destRowFrom should be changed
            //diff rows between srcColFrom and srcColTo
            int copiedRowCount = srcRowTo - srcRowFrom + 1;

            //move row index between srcColFrom and srcColTo that the row index greater or equal to 
            MoveRowIndex(workbookPart, sheetIndex, destRowFrom - 1, copiedRowCount * copyTimes, srcColFrom, srcColTo);

            //temporary array of changed row index
            IList<int> changeRowIndexs = new List<int>();

            //cloned cells, row index/cells
            IDictionary<int, IList<Cell>> clonedCells = null;

            //formula cells in cloned cells
            IList<Cell> formulaCells = new List<Cell>();

            //cloned related rows for speed
            IList<Row> cloneRelatedRows = new List<Row>();

            for (int i = 0; i < copyTimes; i++)
                //next copy col index
                destRowFrom = destRowFromBase + copiedRowCount * i;
                // changed column index
                int changedRows = destRowFrom - srcRowFrom;

                //add change column index to list
                //clear formula cells

                //1: cloned cells, row index/cell list 
                clonedCells = new Dictionary<int, IList<Cell>>();

                foreach (Cell cell in cells)
                    Cell newCell = (Cell)cell.CloneNode(true);
                    int[] indexs = GetCellIndex(cell.CellReference);
                    //change row index of cloned cell
                    int rowIndex = indexs[1] + changedRows;
                    newCell.CellReference = GetColumnName(cell.CellReference) + rowIndex.ToString();

                    IList<Cell> rowCells = null;
                    if (clonedCells.ContainsKey(rowIndex))
                        rowCells = clonedCells[rowIndex];
                        rowCells = new List<Cell>();
                        clonedCells.Add(rowIndex, rowCells);

                    //if is formula cell
                    if (newCell.CellFormula != null && newCell.CellFormula.Text.Length > 0)

                //add cloned cell into row
                foreach (int rowIndex in clonedCells.Keys)
                    Row row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
                    //if no this row 
                    if (row == null)
                        row = new Row() { RowIndex = (uint)rowIndex };
                        //find insert position
                        Row refRow = sheetData.Elements<Row>().Where(r => r.RowIndex > rowIndex).OrderBy(r => r.RowIndex).FirstOrDefault();
                        if (refRow == null)
                            sheetData.InsertBefore<Row>(row, refRow);

                    //add into clone related rows

                //2: processing copied range's formula 
                ChangeFormulaRowNumber(formulaCells, changedRows);

                //3: copy drawings in range
                CopyDrawingsInRange(worksheetPart, srcRowFrom, srcRowTo, srcColFrom, srcColTo, destRowFrom, -1);

            foreach (Row row in cloneRelatedRows)
                // sort by column name
                IList<Cell> cs = row.Elements<Cell>().OrderBy(c => GetCellColIndex(c.CellReference.Value)).ToList<Cell>();
            //2: process merge cell in cloned rows
            MergeCells mcells = worksheetPart.Worksheet.GetFirstChild<MergeCells>();
            if (mcells != null)
                IList<MergeCell> newMergeCells = new List<MergeCell>();
                IEnumerable<MergeCell> clonedMergeCells = mcells.Elements<MergeCell>().
                    Where(m => MergeCellInRange(m, srcRowFrom, srcRowTo, srcColFrom, srcColTo)).ToList<MergeCell>();
                foreach (MergeCell cmCell in clonedMergeCells)
                    foreach (int changedRows in changeRowIndexs)
                        MergeCell newMergeCell = CreateChangedRowMergeCell(cmCell, changedRows);
                uint count = mcells.Count.Value;
                mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count);

            //3: process datavalidate list
            IDictionary<string, DataValidation> validates = GetDataValidatesInRange(worksheetPart, srcRowFrom, srcRowTo, srcColFrom, srcColTo);
            foreach (string cellname in validates.Keys)
                foreach (int changedRows in changeRowIndexs)
                    AddDataValidateRefItemOfChangedRow(cellname, changedRows, validates[cellname]);