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