private void CopyRange(ref CellRangeRef sourceRange, SheetData sheetData, ref CellRef target)
{
CellRef source = sourceRange.Start;
CellRef offset = target.CalculateOffset(source);
var cellsToCopy = FindCellsByRange(sourceRange);
foreach (var rowGroup in cellsToCopy)
{
Row keyRow = rowGroup.Key;
Row targetRow = new Row()
{
RowIndex = (UInt32)(keyRow.RowIndex + offset.Row)
};
MoveCurrentRow((int)targetRow.RowIndex.Value);
sheetData.InsertBefore(targetRow, currentRow);
foreach (Cell cellToCopy in rowGroup)
{
Cell targetCell = (Cell)cellToCopy.Clone();
targetCell.CellReference = CellRef.OffsetIt(targetCell.CellReference, offset);
targetRow.Append(targetCell);
}
}
}
public void SetDefinedNameRange(string name, CellRangeRef value)
{
DefinedName definedName = (
from item in document.WorkbookPart.Workbook.DefinedNames.Elements <DefinedName>()
where item.Name == name
select item).Single();
definedName.Text = value.ToString();
}
public CellRangeRef FindDefinedNameRange(string name)
{
DefinedName definedName = (
from item in document.WorkbookPart.Workbook.DefinedNames.Elements <DefinedName>()
where item.Name == name
select item).Single();
CellRangeRef range = new CellRangeRef(definedName.Text);
return(range);
}
public void InsertRange(CellRangeRef range)
{
CopyRange(ref range, currentSheetData, ref currentPosition);
if (direction == DirectionType.TOP_TO_DOWN)
{
currentPosition.Row = currentPosition.Row + range.Height + 1;
}
else
{
currentPosition.Column = currentPosition.Column + range.Width + 1;
}
}
private IEnumerable <IGrouping <Row, Cell> > FindCellsByRange(CellRangeRef range)
{
Sheet sheet = FindSheetByName(range.SheetName);
WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
SheetData sheetData = workSheetPart.Worksheet.GetFirstChild <SheetData>();
var result = from row in sheetData.Elements <Row>()
where range.Start.Row <= row.RowIndex && row.RowIndex <= range.End.Row
let cells = row.Elements <Cell>()
from cell in cells
where range.Contains(new CellRef(cell.CellReference))
group cell by row into cellsInRange
select cellsInRange;
return(result);
}
public CellRangeTemplate CreateCellRangeTemplate(CellRangeRef range)
{
CellRangeTemplate result = new CellRangeTemplate(range);
var cells = FindCellsByRange(range);
foreach (var rowGroup in cells)
{
foreach (Cell cell in rowGroup)
{
if (cell.DataType != null && cell.CellValue != null)
{
string stringValue = null;
switch (cell.DataType.Value)
{
case CellValues.SharedString:
{
int index = int.Parse(cell.CellValue.Text);
SharedStringItem stringItem = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index);
stringValue = stringItem.InnerText;
break;
}
case CellValues.String:
{
stringValue = cell.CellValue.Text;
break;
}
}
if (PARAM_PATTERN.IsMatch(stringValue))
{
var match = PARAM_PATTERN.Match(stringValue);
var paramName = match.Groups[1].Value;
result.AddCell(paramName, cell);
}
}
}
}
return(result);
}
public CellRangeTemplate(CellRangeRef range)
{
this.range = range;
}
public CellRangeTemplate CreateCellRangeTemplate(string name)
{
CellRangeRef range = FindDefinedNameRange(name);
return(CreateCellRangeTemplate(range));
}
public void SetCurrentPositionByName(string definedName)
{
CellRangeRef range = FindDefinedNameRange(definedName);
CurrentPosition = range.Start;
}
public void InsertRange(string name)
{
CellRangeRef range = FindDefinedNameRange(name);
InsertRange(range);
}
