public static Cell FindCell(string reference, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string columnName = SpreadsheetReader.ColumnFromReference(reference);
uint rowIndex = SpreadsheetReader.RowFromReference(reference);
string cellReference = (columnName + rowIndex.ToString());
int columnIndex = SpreadsheetReader.GetColumnIndex(columnName);
Row row = null;
var match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex);
if (match.Count() != 0)
{
row = match.First();
IEnumerable <Cell> cells = row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference);
if ((cells.Count() > 0))
{
return(cells.First());
}
else
{
return(null);
}
}
else
{
return(null);
}
}
public static List <Cell> FindCells(string Range, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
Range = Range.Replace("$", "");
string FirstReference = Range.Split(':')[0], LastReference = Range.Split(':')[1];
uint FirstrowIndex = SpreadsheetReader.RowFromReference(FirstReference);
uint LastrowIndex = SpreadsheetReader.RowFromReference(LastReference);
int FirstcolumnIndex = SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(FirstReference));
int LastcolumnIndex = SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(LastReference));
IEnumerable <Row> match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value >= FirstrowIndex && r.RowIndex.Value <= LastrowIndex);
List <Cell> listCells = new List <Cell>();
foreach (Row row in match)
{
foreach (Cell cell in row)
{
if (SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(cell.CellReference.Value)) >= FirstcolumnIndex && SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(cell.CellReference.Value)) <= LastcolumnIndex)
{
if (cell.CellValue != null)
{
double d;
if (cell.DataType != null)
{
if (cell.DataType.Value == CellValues.Number || cell.DataType.Value == CellValues.SharedString)
{
listCells.Add(cell);
}
if (cell.DataType.Value == CellValues.String)
{
if (double.TryParse(cell.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d))
{
listCells.Add(cell);
}
}
}
else if (double.TryParse(cell.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d))
{
listCells.Add(cell);
}
}
else if (cell.CellFormula != null)
{
listCells.Add(cell);
}
}
}
}
return(listCells);
}
private UInt32 AppendRows(DataRow dataRow, IEnumerable <Row> ListRowInRange, IEnumerable <MergeCell> ListMergeCells, WorksheetPart RecultWorkSheetPart, UInt32 Offset)
{
// String RangeName = "";
// if (dataRow != null) RangeName = dataRow.Table.TableName;
UInt32 CountRangeData = Convert.ToUInt32(ListRowInRange.Count());
SheetData sheetData = RecultWorkSheetPart.Worksheet.Elements <SheetData>().First();
foreach (Row RangeRow in ListRowInRange) //Добавляем строки выбраные по указаному DefinedName
{
Row r = (Row)RangeRow.Clone();
r.RowIndex = r.RowIndex + Offset;
foreach (Cell cell in r.Elements <Cell>())
{
cell.CellReference = SpreadsheetReader.ColumnFromReference(cell.CellReference) + r.RowIndex.ToString();
if (dataRow != null)
{
// ConvertMathFormulaToCellValue(dataRow.Table.DataSet, dataRow.Table.Columns, dataRow, cell);
// ConvertFormulaToValue(dataRow.Table.DataSet, dataRow.Table.Columns, dataRow, cell);
SetValueToCellFormula(dataRow.Table.DataSet, dataRow, cell);
}
}
sheetData.Append(r);
}
if (ListMergeCells.Count() > 0)
{
foreach (MergeCell mCell in ListMergeCells) //Добавление обединенных ячеек присутствующих в ListMergeCells
{
string StartRef = FirstRefFromRange(mCell.Reference.Value);
string EndRef = LastRefFromRange(mCell.Reference.Value);
string newRangeFirst = SpreadsheetReader.ColumnFromReference(StartRef) + (SpreadsheetReader.RowFromReference(StartRef) + Offset);
string newRangeEnd = SpreadsheetReader.ColumnFromReference(EndRef) + (SpreadsheetReader.RowFromReference(EndRef) + Offset);
MergeCell mergeCell = new MergeCell()
{
Reference = new StringValue(newRangeFirst + ":" + newRangeEnd)
};
RecultWorkSheetPart.Worksheet.GetFirstChild <MergeCells>().Append(mergeCell);
}
}
return(CountRangeData);
}
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;
}
}
}
}
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);
}