private UInt32 SetValueFromDataTable(DataTable dataTable, SpreadsheetDocument RecultDoc, SpreadsheetDocument TemleytDoc, UInt32 CurOffset) { UInt32 newOffset = 0; string RangeName = dataTable.TableName; // SpreadsheetDocument TemleytDoc = SpreadsheetDocument.Open(Temleyt, true); DefinedName DefName = SpreadsheetReader.GetDefinedName(TemleytDoc, RangeName); if (DefName != null) { if (DefName.Text.IndexOf("#REF!") < 0) { string definedName = DefName.Text; string WorkSheetName = definedName.Substring(0, definedName.IndexOf("!")).TrimEnd('\'').TrimStart('\''); string Range = definedName.Substring(definedName.IndexOf("!") + 1); UInt32 startRowInRange = FirstRowInRange(Range); UInt32 endRowInRange = LastRowInRange(Range); UInt32 serviceRow = 1; UInt32 CountRangeData = (endRowInRange - startRowInRange + 1) - serviceRow; WorksheetPart TemleytWorkSheet = SpreadsheetReader.GetWorksheetPartByName(TemleytDoc, WorkSheetName); List <Row> ListRowInRange = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>() .Where(r => r.RowIndex >= startRowInRange && r.RowIndex < endRowInRange).ToList(); List <Row> ListRowBottom = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>() .Where(r => r.RowIndex > endRowInRange).ToList(); List <Row> ServiceRow = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>() .Where(r => r.RowIndex == endRowInRange).ToList(); List <MergeCell> ListMCellInRange = new List <MergeCell>(); List <MergeCell> ListMCellBottom = new List <MergeCell>(); MergeCells mergeCells = TemleytWorkSheet.Worksheet.GetFirstChild <MergeCells>(); if (mergeCells != null) { ListMCellInRange = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>() .Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) >= startRowInRange && SpreadsheetReader.RowFromReference(LastRefFromRange(r.Reference.Value)) <= endRowInRange).ToList(); ListMCellBottom = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>() .Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) > endRowInRange).ToList(); } UInt32 NewStartRow = startRowInRange + CurOffset; WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, WorkSheetName); WorksheetWriter RecultWriter = new WorksheetWriter(RecultDoc, RecultWorkSheetPart); DeleteRows(RecultDoc, RecultWriter, NewStartRow, CountRangeData + serviceRow); if (ListRowBottom.Count() > 0) { DeleteRows(RecultDoc, RecultWriter, NewStartRow, ListRowBottom.Last().RowIndex.Value + CurOffset - NewStartRow + 1); } MaximumCount = dataTable.Rows.Count; UInt32 InsideOffset = NewStartRow; foreach (DataRow dataRow in dataTable.Rows) { UInt32 Offset = (InsideOffset - NewStartRow) + CurOffset; if (dataRow.RowState.ToString() != "Deleted") { AppendRows(dataRow, ListRowInRange, ListMCellInRange, RecultWorkSheetPart, Offset); InsideOffset += CountRangeData; } ++ProgressValue; } newOffset = (InsideOffset - NewStartRow - CountRangeData) + CurOffset; AppendRows(null, ListRowBottom, ListMCellBottom, RecultWorkSheetPart, newOffset); DefName = SpreadsheetReader.GetDefinedName(RecultDoc, RangeName); definedName = DefName.Text; DefName.Text = definedName.Substring(0, definedName.LastIndexOf('$') + 1) + InsideOffset.ToString(); // DefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartClone.ToString(); //Обновляем ссилки начала и конца рабочих областей DefinedNames foreach (DefinedName CurdefName in RecultDoc.WorkbookPart.Workbook.DefinedNames) { if (CurdefName.Text.IndexOf("#REF!") < 0) { // if (CurdefName.Text == definedName)//Текущая облать DefinedName // { // CurdefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartOffset.ToString(); // } // else //Все остальные DefinedName текущего листа Sheet // { string strDefName = CurdefName.Text; string sheetName = strDefName.Substring(0, strDefName.IndexOf("!")).Trim('\''); string range = strDefName.Substring(strDefName.IndexOf("!") + 1); if (sheetName == WorkSheetName) { string firstRefInRange = FirstRefFromRange(range); string lastRefInRange = LastRefFromRange(range); UInt32 firstRow = SpreadsheetReader.RowFromReference(firstRefInRange); UInt32 lastRow = SpreadsheetReader.RowFromReference(lastRefInRange); if (firstRow + newOffset > InsideOffset + CurOffset) { CurdefName.Text = strDefName.Substring(0, strDefName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(firstRefInRange) + '$' + (firstRow + newOffset).ToString() + ":$" + SpreadsheetReader.ColumnFromReference(lastRefInRange) + '$' + (lastRow + newOffset).ToString(); } } // } } } } } // TemleytDoc.Close(); return(newOffset); }