ジャナ・ビジネス・コンサルティング
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 //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); } 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]); } } }