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;
}
}
}
}
