//A method for copying a row and insert it: //Copy an existing row and insert it //We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us public static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData) { uint newRowIndex; var newRow = (Row)refRow.CloneNode(true); // Loop through all the rows in the worksheet with higher row // index values than the one you just added. For each one, // increment the existing row index. IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= rowIndex); foreach (Row row in rows) { newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1); foreach (Cell cell in row.Elements <Cell>()) { // Update the references for reserved cells. string cellReference = cell.CellReference.Value; cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString())); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); } // Update the row index. row.RowIndex = new UInt32Value(newRowIndex); } sheetData.InsertBefore(newRow, refRow); return(newRow); }
private void CopyRange(ref CellRangeRef sourceRange, SheetData sheetData, ref CellRef target) { CellRef source = sourceRange.Start; CellRef offset = target.CalculateOffset(source); var cellsToCopy = FindCellsByRange(sourceRange); foreach (var rowGroup in cellsToCopy) { Row keyRow = rowGroup.Key; Row targetRow = new Row() { RowIndex = (UInt32)(keyRow.RowIndex + offset.Row) }; MoveCurrentRow((int)targetRow.RowIndex.Value); sheetData.InsertBefore(targetRow, currentRow); foreach (Cell cellToCopy in rowGroup) { Cell targetCell = (Cell)cellToCopy.Clone(); targetCell.CellReference = CellRef.OffsetIt(targetCell.CellReference, offset); targetRow.Append(targetCell); } } }
private static bool CreateOrUpdateRowAt(SheetData sheetData, Row newRow, uint rowNo) { bool res = false; //create row and cell. append or insert. if (sheetData != null && newRow != null) { //是否存在row,1.存在,删除.2.现在是否存在大于row,存在,在前插入.不存在.直接附加. var rows = sheetData.Elements <Row>().Where(x => x.RowIndex == rowNo); if (rows.Count() > 0) { sheetData.RemoveChild(rows.First()); } var biggerrows = sheetData.Elements <Row>().Where(x => x.RowIndex > rowNo); if (biggerrows.Count() <= 0) { sheetData.Append(newRow);//todo:装载数据7/10的时间耗费在这里。需要优化!如果是大数据插入,应该创建大量row,再使用一次append或其他插入函数。 } else { sheetData.InsertBefore(newRow, biggerrows.First()); } res = true; } return(res); }
//根据行索引值获取列Row private Row getRow(int rowIndex) { int rowcount = sheetData.Elements <Row>().Count(); Row row = sheetData.Elements <Row>().FirstOrDefault(r => r != null && r.RowIndex != null && r.RowIndex == rowIndex); if (row == null) { row = new Row() { RowIndex = (uint)rowIndex }; Row lastRow = sheetData.Elements <Row>().LastOrDefault(); if ((lastRow != null && lastRow.RowIndex != null && lastRow.RowIndex < rowIndex) || lastRow == null) { sheetData.AppendChild(row); } else { Row refRow = sheetData.Elements <Row>().FirstOrDefault(r => r != null && r.RowIndex != null && r.RowIndex > rowIndex); sheetData.InsertBefore(row, refRow); } } return(row); }
public void InsertRowAt(DataRow rowToInsert, DataColumnCollection columns, int refRow, WorksheetPart worksheetPart) { CellFormat format = new CellFormat(); format.BorderId = InsertBorder(GenerateBorder()); uint styleIndex = InsertCellFormat(_workbookPart, format); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); Row newRow = new Row { RowIndex = Convert.ToUInt32(refRow) }; foreach (DataColumn col in columns) { Cell cell = new Cell { DataType = CellValues.String, CellValue = new CellValue(" \n" + rowToInsert[col].ToString() + " s") }; cell.StyleIndex = styleIndex; newRow.AppendChild(cell); } Row refrenceRow = null;//sheetData.Descendants<Row>().Where(x => x.RowIndex != null && x.RowIndex.Value == refRow).First(); IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(x => x.RowIndex != null && x.RowIndex >= refRow); foreach (Row row in rows) { if (row.Elements <Cell>().Any(x => x.CellReference != null)) { if (refrenceRow == null) { refrenceRow = row; } uint newIndex = row.RowIndex.Value + 1; foreach (Cell cell in row.Elements <Cell>()) { string cellReference = cell.CellReference.Value; cell.CellReference = cellReference.Replace(row.RowIndex.Value.ToString(), newIndex.ToString()); } row.RowIndex = new UInt32Value(newIndex); } } if (refrenceRow != null) { sheetData.InsertBefore(newRow, refrenceRow); } else { sheetData.Append(newRow); } worksheetPart.Worksheet.Save(); }
/// <summary> /// Метод для получения или создания ячейки и вставки данных в эту ячейку /// </summary> /// <param name="columnName">Имя столбца в формате как в Excel [A-Z...]</param> /// <param name="rowIndex">Индекс строки</param> /// <param name="worksheetPart"></param> /// <returns>Ячейка</returns> private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string cellReference = columnName + rowIndex; // Если рабочий лист не содержит строки с указанным индексом строки, то просто добавляем ее. Row row; if (sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).First(); } else { // Обязательно необходимо соблюсти последовательность индексов Row nextRow = sheetData.Elements <Row>().OrderBy(r => r.RowIndex).FirstOrDefault(r => r.RowIndex > rowIndex); row = new Row() { RowIndex = rowIndex }; sheetData.InsertBefore(row, nextRow); } // Если ячейки с указанным именем столбца нет, то добавляем. if (row.Elements <Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return(row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference).First()); } else { // Ячейки должны располагаться в последовательном порядке согласно CellReference. // Необходимо определить, куда вставить новую ячейку. Cell refCell = null; foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference.Value.Length == cellReference.Length) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } } Cell newCell = new Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); worksheet.Save(); return(newCell); } }
/// <summary> /// Inserts new rows at index. It adds multiple rows and multiple columns for each row. /// </summary> /// <param name="rowIndex">Index of the row that will be the first of the inserted rows</param> /// <param name="sheetData"></param> /// <param name="howManyRows">How many rows to insert</param> /// <param name="howManyColumns">How many columns to insert for each row</param> private static void InsertNewRows(uint rowIndex, SheetData sheetData, int howManyRows, int howManyColumns) { Row RefRow = GetRow(sheetData.Parent as Worksheet, rowIndex); var styleIndex = RefRow.Elements <Cell>().First().StyleIndex; List <Row> RowsToAdd = new List <Row>(); for (int i = 0; i < howManyRows; ++i) { int newRowIndex = (int)rowIndex + i; Row rowToAdd = new Row() { RowIndex = new UInt32Value((uint)newRowIndex) }; for (uint cellColumnId = 0; cellColumnId <= howManyColumns; ++cellColumnId) { Cell cellToAdd = new Cell() { CellReference = new StringValue(GetExcelColumnName(cellColumnId) + newRowIndex), CellFormula = new CellFormula(), DataType = CellValues.String, StyleIndex = styleIndex }; rowToAdd.Append(cellToAdd); } RowsToAdd.Add(rowToAdd); } // Insert 1st row above given row index. Insert all others below that. for (int i = 0; i < RowsToAdd.Count; ++i) { Row row = RowsToAdd[i]; if (i == 0) { sheetData.InsertBefore(row, RefRow); } else { sheetData.InsertAfter(row, RowsToAdd[i - 1]); } } // Row that had previously row index of the 1st row inserted must now update its cell references and row index. uint newIndexForFirstRow = RefRow.RowIndex.Value + (uint)howManyRows; foreach (Cell cell in RefRow.Elements <Cell>()) { cell.CellReference = new StringValue(cell.CellReference.Value.Replace(RefRow.RowIndex.Value.ToString(), newIndexForFirstRow.ToString())); } RefRow.RowIndex = new UInt32Value(newIndexForFirstRow); }
/// <summary> /// Iterates over all row and cell nodes to check and add (if missing) RowIndex and CellReference attributes. /// Those values make it easier to set error style and add comments. /// </summary> /// <param name="document">File being modified.</param> /// <param name="worksheet">Current sheet that is going to be changed.</param> public static void StructurateExcelSheetDataXmlAttributes(SpreadsheetDocument document, Worksheet worksheet) { //Row and Cell nodes are contained within a sheetData node, there is one sheetData node per worksheet. SheetData sheetData = worksheet.GetFirstChild <SheetData>(); bool firstRow = true; int currentRow = 0; int currentRowIndex = 0; foreach (Row rowItem in sheetData) { if (rowItem.RowIndex == null) { if (firstRow) { currentRow = 1; currentRowIndex = 1; } else { currentRow++; currentRowIndex++; } rowItem.RowIndex = (uint)currentRowIndex; firstRow = false; } else { currentRow++; firstRow = false; currentRowIndex = (int)rowItem.RowIndex.Value; while ((currentRow) < currentRowIndex) { sheetData.InsertBefore(new Row() { RowIndex = (uint)(currentRow) }, rowItem); currentRow++; } } StructurateCellsInRow(rowItem); } if (document.FileOpenAccess != FileAccess.Read) { document.Save(); } }
internal static void InsertRow(Worksheet worksheet, Row refRow, Row insertedRow) { SheetData sheetData = worksheet.GetFirstChild <SheetData>(); uint rowIndex = refRow.RowIndex; MoveRowsDown(worksheet, rowIndex); Row newRow = sheetData.InsertBefore(insertedRow, refRow); string newIndex = rowIndex.ToString(); ReplaceRowIndex(newRow, newRow.RowIndex, newIndex.ToString()); newRow.RowIndex = rowIndex; }
public static Row GetRow(SheetData sheetData, uint line, bool createIfDoesntExists = false) { var row = sheetData?.Elements <Row>().FirstOrDefault(r => r.RowIndex == line); // Se existir a linha retorna. if (row != null) { row.Spans = null; return(row); } else if (!createIfDoesntExists) { return(null); } // Senao cria uma nova linha. row = new Row { RowIndex = line }; var rows = sheetData.Elements <Row>().ToList(); // Caso nao exista linhas pode simplesmente inserir if (!rows.Any()) { sheetData.AppendChild(row); return(row); } // Insere a linha na order correta. var before = rows.Where(w => w.RowIndex < line).OrderBy(o => o.RowIndex.Value).LastOrDefault(); if (before != null) // Existem linhas anteriores a que sera inserida. { sheetData.InsertAfter(row, before); } else // Nao existem nenhuma linha anterior a que sera inserida. { var after = rows.Where(w => w.RowIndex > line).OrderBy(o => o.RowIndex.Value).FirstOrDefault(); // Insere antes do primeiro. sheetData.InsertBefore(row, after); } return(row); }
/// <summary> /// Получить строку. /// Создается новый элемент строки, если строка еще не существует. /// </summary> /// <param name="sheetData">Данные листа в котором находится требуемая строка</param> /// <param name="rowNum">Номер запрашиваемой строки</param> /// <returns>Объект строки</returns> public static Row GetRow(this SheetData sheetData, uint rowNum) { var row = sheetData .Elements <Row>() .Where(r => r.RowIndex.Value >= rowNum) .OrderBy(r => r.RowIndex.Value).FirstOrDefault(); if (row != null && row.RowIndex == rowNum) { return(row); } var newRow = new Row(); newRow.RowIndex = rowNum; sheetData.InsertBefore(newRow, row); return(newRow); }
private static Row GetRow(SheetData sheetData, int rowIndex) { Row newRow = null; foreach (Row current in sheetData.Elements <Row>()) { if (current.RowIndex >= rowIndex) { if (current.RowIndex == rowIndex) { return(current); } newRow = GetNewRow(rowIndex); sheetData.InsertBefore <Row>(newRow, current); return(newRow); } } newRow = GetNewRow(rowIndex); sheetData.Append(newRow); return(newRow); }
private void CopyRange(ref CellRangeTemplate sourceRange, SheetData sheetData, Worksheet worksheet, ref CellPosition target, double?rowHeight = null) { #region Sheet sheetTemplate = sourceRange.CellRange.SheetTemplate; var workbookPartTemplate = sourceRange.CellRange.WorksheetPart; MergeCells mergeCellsTemplate = sourceRange.CellRange.MergeCells; MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>(); if (false && workbookPartTemplate.DrawingsPart != null && worksheet.WorksheetPart.DrawingsPart == null) { var drawingsPart = worksheet.WorksheetPart.AddPart <DrawingsPart>(workbookPartTemplate.DrawingsPart); drawingsPart = worksheet.WorksheetPart.DrawingsPart; if (!worksheet.WorksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheet.WorksheetPart.Worksheet.Append(new Drawing { Id = worksheet.WorksheetPart.GetIdOfPart(drawingsPart) }); } } Dictionary <string, MergeCell> mergeCellTemplateDic = sourceRange.CellRange.MergeCellsDic; #endregion CellPosition source = sourceRange.CellRange.Start; CellPosition offset = target.CalculateOffset(source); var cellsToCopy = document.FindCellsByRange(sourceRange.CellRange); for (int i = 0; i < cellsToCopy.Count(); i++) { var rowGroup = cellsToCopy.ElementAt(i); Row keyRow = rowGroup.Key; Row targetRow = new Row() { RowIndex = (UInt32)(keyRow.RowIndex + offset.Row), Height = (short)-1, }; if (rowHeight != null) { targetRow.Height = rowHeight; targetRow.CustomHeight = true; } MoveCurrentRow((int)targetRow.RowIndex.Value); sheetData.InsertBefore(targetRow, currentRow); foreach (Cell cellToCopy in rowGroup) { Cell targetCell = (Cell)cellToCopy.Clone(); targetCell.CellReference = CellPosition.OffsetIt(targetCell.CellReference, offset); targetRow.Append(targetCell); MergeCell _findMerge; if (mergeCellTemplateDic != null && mergeCellTemplateDic.TryGetValue(cellToCopy.CellReference.Value.ToUpper(), out _findMerge)) { var positionParent = _findMerge.Reference.Value.Split(':'); CellPosition offsetStart = new CellPosition(positionParent[0]); CellPosition offsetEnd = new CellPosition(positionParent[1]); var celRefNew = new CellPosition(targetCell.CellReference); if (mergeCells == null) { var a = new MergeCells(); worksheet.InsertAfter(a, sheetData); mergeCells = worksheet.GetFirstChild <MergeCells>(); } var mergeCell = new MergeCell(); mergeCell.Reference = celRefNew.ToString() + ":" + new CellPosition(celRefNew.Row + (offsetEnd.Row - offsetStart.Row), celRefNew.Column + (offsetEnd.Column - offsetStart.Column)).ToString(); mergeCells.Append(mergeCell); mergeCells.Count = (mergeCells.Count ?? 0) + 1; } } } }
public static Row CreateRow(this Worksheet worksheet, Row refRow) { SheetData sheetData = worksheet.GetFirstChild <SheetData>(); uint newRowIndex = 0; var newRow = new Row() { RowIndex = refRow.RowIndex.Value }; var cells = refRow.Elements <Cell>(); newRow.Height = new DoubleValue(refRow.Height); newRow.CustomHeight = new BooleanValue(refRow.CustomHeight); foreach (Cell cell in cells) { Cell newCell = (Cell)cell.CloneNode(true); newCell.StyleIndex = new UInt32Value(cell.StyleIndex); newRow.Append(newCell); } IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value); foreach (Row row in rows) { newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1); foreach (var cell in row.Elements <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 <MergeCells>(); if (mcells != null) { //处理所有动态行以下的merg var clonedMergeCells = mcells.Elements <MergeCell>(). Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) >= newRow.RowIndex.Value).ToList <MergeCell>(); foreach (var cmCell in clonedMergeCells) { cmCell.Reference.Value = Utility.AddRow(cmCell.Reference.Value); } //增加新的merg var newMergeCells = new List <MergeCell>(); var rowMergeCells = mcells.Elements <MergeCell>(). Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) == refRow.RowIndex).ToList <MergeCell>(); foreach (var mc in rowMergeCells) { newMergeCells.Add(new 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); }
private static bool SetOrUpdateCellValue(SheetData sheetData, uint rowNumber, uint columnNumber, Type datatype, object value, DefaultCellStyle defaultCellStyle, uint customStyle = 0) { if (sheetData != null && value != null && datatype != null) { //创建cell. //0.不存在row,建立row,插入cell. //0.row存在1.删除cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在row后面. string cellRefrence = GetCellReference(rowNumber, columnNumber); IEnumerable <Row> equalOrbiggerRows = sheetData.Elements <Row>().Where(x => x.RowIndex >= rowNumber); Row equalRow = null, biggerRow = null; if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex == rowNumber) { equalRow = equalOrbiggerRows.First(); } else if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex > rowNumber) { biggerRow = equalOrbiggerRows.First(); } if (equalRow != null)//存在row. 1.是否存在cell,存在跟新,不存在建立新cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在Row后面. { IEnumerable <Cell> equalOrbiggerCells = equalRow.Elements <Cell>().Where(x => x.CellReference >= new StringValue(cellRefrence)); Cell equalCell = null; Cell biggerCell = null; if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference == cellRefrence) { equalCell = equalOrbiggerCells.First(); } else if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference > new StringValue(cellRefrence)) { biggerCell = equalOrbiggerCells.First(); } Cell newCell = createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle); if (equalCell != null) { equalOrbiggerRows.First().ReplaceChild(newCell, equalCell); } else { if (biggerCell != null) { equalOrbiggerRows.First().InsertBefore(newCell, biggerCell); } else { equalOrbiggerRows.First().Append(newCell); } } } else//不存在.新建row and cell. { Row newrow = new Row(); newrow.RowIndex = rowNumber; Cell theCell = IncOpenExcel.createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle); if (theCell != null) { newrow.Append(theCell); } if (biggerRow != null) { sheetData.InsertBefore(newrow, equalOrbiggerRows.First());//插入的行不是最大的,插到比它大的前面. } else { sheetData.Append(newrow);; //插入的行是最大的,直接附加到最后 } } } return(true); }
//Copy an existing row and insert it //We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us // private void CopyToLine(Row refRow, uint rowIndex, SheetData sheetData) private void CopyToLine(Row refRow, SheetData sheetData) { uint newRowIndex; var newRow = (Row)refRow.CloneNode(true); sheetData.InsertBefore(newRow, refRow); //newRowIndex = System.Convert.ToUInt32(refRow.RowIndex.Value + 1); //refRow.RowIndex.Value = newRowIndex; //IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value); //int posicao = (int)refRow.RowIndex.Value; //foreach (Row row in rows) //{ // newRowIndex = System.Convert.ToUInt32(posicao + 1);// System.Convert.ToUInt32(row.RowIndex.Value + 1); // foreach (Cell cell in row.Elements<Cell>()) // { // // Update the references for reserved cells. // string cellReference = getCOL(cell.CellReference.Value) + newRowIndex.ToString(); // cell.CellReference = new StringValue(cellReference); // } // // Update the row index. // row.RowIndex = new UInt32Value(newRowIndex); // posicao += 1; //} // Loop through all the rows in the worksheet with higher row // index values than the one you just added. For each one, // increment the existing row index. // IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > newRow.RowIndex.Value); // IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex); //int primeiro = 0; //foreach (Row row in rows) //{ // if (primeiro == 0) // { // newRowIndex = row.RowIndex.Value; // primeiro = 1; // } // else // newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1); // foreach (Cell cell in row.Elements<Cell>()) // { // // Update the references for reserved cells. // string cellReference = cell.CellReference.Value; // cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString())); // } // // Update the row index. // row.RowIndex = new UInt32Value(newRowIndex); //} //foreach (Cell c in newRow.Descendants<Cell>()) //{ // string coluna = getCOL(c.CellReference.Value); // Cell refCell = refRow.Elements<Cell>().Where(cc => string.Compare(cc.CellReference.Value, coluna + (rowIndex - 1).ToString(), true) == 0).First(); // c.StyleIndex = refCell.StyleIndex; //} }
public static Row InsertRow(uint rowIndex, WorksheetPart worksheetPart, Row insertRow, bool isNewLastRow = false) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); Row retRow = !isNewLastRow?sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == rowIndex) : null; // If the worksheet does not contain a row with the specified row index, insert one. if (retRow != null) { // if retRow is not null and we are inserting a new row, then move all existing rows down. if (insertRow != null) { UpdateRowIndexes(worksheetPart, rowIndex, false); UpdateMergedCellReferences(worksheetPart, rowIndex, false); UpdateHyperlinkReferences(worksheetPart, rowIndex, false); // actually insert the new row into the sheet retRow = sheetData.InsertBefore(insertRow, retRow); // at this point, retRow still points to the row that had the insert rowIndex string curIndex = rowIndex.ToString(); string newIndex = rowIndex.ToString(); foreach (Cell cell in retRow.Elements <Cell>()) { // Update the references for the rows cells. cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex)); } // Update the row index. retRow.RowIndex = rowIndex; } } else { // Row doesn't exist yet, shifting not needed. // Rows must be in sequential order according to RowIndex. Determine where to insert the new row. Row refRow = !isNewLastRow?sheetData.Elements <Row>().FirstOrDefault(row => row.RowIndex > rowIndex) : null; // use the insert row if it exists retRow = insertRow ?? new Row() { RowIndex = rowIndex }; IEnumerable <Cell> _cellsInRow = retRow.Elements <Cell>().ToList(); if (_cellsInRow.Any()) { string curIndex = retRow.RowIndex.ToString(); string newIndex = rowIndex.ToString(); foreach (Cell cell in _cellsInRow) { // Update the references for the rows cells. cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex)); } // Update the row index. retRow.RowIndex = rowIndex; } sheetData.InsertBefore(retRow, refRow); } return(retRow); }
private static Cell RetrieveCellReference(Worksheet worksheet, string addressName) { //Use regular expressions to get the row number and column name. //If the paramenter wasn't well formed this code will fail Regex regex = new Regex("^(?<col>\\D+)(?<row>\\d+)"); Match match = regex.Match(addressName); uint rowNumber = uint.Parse(match.Result("${row}")); string colName = match.Result("${col"); //Retrieve reference to the sheet's data. SheetData sheetData = worksheet.GetFirstChild <SheetData>(); //If the worksheet does not contain a row //with the specified row index, insert one. var rows = sheetData.Elements <Row>(); var theRow = rows.FirstOrDefault(r => r.RowIndex.Value == rowNumber); if (theRow == null) { //Rows dosen't exist, so we create a new one. theRow = new Row(); theRow.RowIndex = rowNumber; //Must insert row in the appropiate location. Row refRow = null; foreach (Row row in rows) { if (row.RowIndex > rowNumber) { refRow = row; break; } } //If refRow is null, InsertBefore appends. sheetData.InsertBefore(theRow, refRow); } //At this point, theRow refers to the row to contain the cell value. //The cell may or may not exist. //If the cell you need already exists, return it. //If there is not a cell with the specified address name, insert one. var cells = theRow.Elements <Cell>(); Cell theCell = cells.FirstOrDefault(c => c.CellReference.Value == addressName); if (theCell == null) { //Cell dosen't exist, so create one. theCell = new Cell(); theCell.CellReference = addressName; //Must insert cell in the appropiate location. Cell refCell = null; foreach (Cell cell in cells) { if (string.Compare(cell.CellReference.Value, addressName, true) > 0) { refCell = cell; break; } } //If refCell is null, InsertBefore appends. theRow.InsertBefore(theCell, refCell); } return(theCell); }