public void WorksheetDeleteRowsTest() { MemoryStream stream = SpreadsheetReader.Create(); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1"); WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart); writer.PasteText("D3", "Row 3"); writer.PasteText("D4", "Row 4"); writer.PasteText("D5", "Row 5"); //Delete row 3 writer.DeleteRows(3, 2); //Save to the memory stream, and then to a file SpreadsheetWriter.Save(doc); SpreadsheetWriter.StreamToFile(string.Format("{0}\\deleterows.xlsx", GetOutputFolder()), stream); }
private void DeleteRows(SpreadsheetDocument RecultDoc, WorksheetWriter RecultWriter, uint rowIndex, uint count) { RecultWriter.DeleteRows(rowIndex, count); if (RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().Count() > 0) { MergeCells mergeCells = RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().First(); //Удаление обединенных ячеек попавших в облать удаления for (int i = mergeCells.Elements <MergeCell>().Count() - 1; i >= 0; i--) { string Range = mergeCells.Elements <MergeCell>().ElementAt(i).Reference.Value; UInt32 startRow = SpreadsheetReader.RowFromReference(FirstRefFromRange(Range)); UInt32 endRow = SpreadsheetReader.RowFromReference(LastRefFromRange(Range)); if (startRow >= rowIndex && endRow <= (rowIndex + count - 1)) { mergeCells.Elements <MergeCell>().ElementAt(i).Remove(); } } //Обновления ссылок всех ниже стоящих обединенных ячеек foreach (MergeCell mCell in mergeCells.Elements <MergeCell>()) { string Range = mCell.Reference.Value; string StartRef = FirstRefFromRange(Range); string EndRef = LastRefFromRange(Range); UInt32 startRow = SpreadsheetReader.RowFromReference(StartRef); UInt32 endRow = SpreadsheetReader.RowFromReference(EndRef); if (startRow > (rowIndex + count - 1)) { string newRangeFirst = SpreadsheetReader.ColumnFromReference(StartRef) + (SpreadsheetReader.RowFromReference(StartRef) - count); string newRangeEnd = SpreadsheetReader.ColumnFromReference(EndRef) + (SpreadsheetReader.RowFromReference(EndRef) - count); mCell.Reference.Value = newRangeFirst + ":" + newRangeEnd; } } } }