/// <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 //destRowFrom 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 changeRowIndexs.Add(changedRows); //clear formula cells formulaCells.Clear(); //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]; else { rowCells = new List<Cell>(); clonedCells.Add(rowIndex, rowCells); } rowCells.Add(newCell); //if is formula cell if (newCell.CellFormula != null && newCell.CellFormula.Text.Length > 0) { formulaCells.Add(newCell); } } //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.AppendChild<Row>(row); else sheetData.InsertBefore<Row>(row, refRow); } row.Append(clonedCells[rowIndex].ToArray()); //add into clone related rows cloneRelatedRows.Add(row); } //2: processing copied range's formula ChangeFormulaRowNumber(formulaCells, changedRows); //3: copy drawings in range CopyDrawingsInRange(worksheetPart, srcRowFrom, srcRowTo, srcColFrom, srcColTo, destRowFrom, -1); } //sort cells in clone-related rows, SORT IS MOST TIME-SPEND foreach (Row row in cloneRelatedRows) { // sort by column name IList<Cell> cs = row.Elements<Cell>().OrderBy(c => GetCellColIndex(c.CellReference.Value)).ToList<Cell>(); row.RemoveAllChildren(); row.Append(cs.ToArray()); } //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); newMergeCells.Add(newMergeCell); } } uint count = mcells.Count.Value; mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count); mcells.Append(newMergeCells.ToArray()); } //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]); } } }
Hi,
Where can we find the implementations of the methods MergeCellInRange() and CreateChangedRowMergeCell() used in the above example?
is it possible to share the body of the above mentioned two methods ?
Thanks and Regards,
YKK Reddy