private static Cell GetCell(WorksheetPart wsPart, DefinedNameData dn, object cellValue)
{
Cell cell = wsPart.Worksheet.Descendants <Cell>().FirstOrDefault(c => c.CellReference == dn.Reference);
if (cell != null)
{
return(cell);
}
Worksheet ws = wsPart.Worksheet;
SheetData sheetData = ws.GetFirstChild <SheetData>();
Row row = sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == dn.RowIndex);
if (row == null)
{
row = new Row()
{
RowIndex = dn.RowIndex
};
sheetData.Append(row);
}
Type cellValueType = cellValue == null ? typeof(int) : cellValue.GetType();
cell = new Cell()
{
CellReference = dn.Reference, DataType = GetCellDataType(cellValueType)
};
Cell refCell = null;
IEnumerable <Cell> existingCells = row.Elements <Cell>();
foreach (Cell cellInRow in existingCells)
{
if (string.Compare(cellInRow.CellReference.Value, dn.Reference) > 0)
{
refCell = cellInRow;
break;
}
}
if (refCell != null)
{
row.InsertBefore(cell, refCell);
}
else if (existingCells.Count() > 0)
{
row.InsertAfter(cell, existingCells.Last());
}
else
{
row.InsertAt(cell, 0);
}
return(cell);
}
private static List <DefinedNameData> GetDefinedNames(WorkbookPart wbPart)
{
DefinedNames definedNames = wbPart.Workbook.DefinedNames;
List <DefinedNameData> definedNameDataSet = new List <DefinedNameData>(definedNames.Count());
DefinedNameData definedNameData;
string sheetName, columnName;
uint rowIndex;
string[] referenceSegments;
foreach (DefinedName dn in definedNames)
{
// assume none of these defined names are cell range (e.g. "A1", not "A1:B1").
referenceSegments = dn.Text.Split('!');
sheetName = referenceSegments[0].Trim('\'');
referenceSegments = referenceSegments[1].Split('$');
columnName = referenceSegments[1];
rowIndex = uint.Parse(referenceSegments[2]);
definedNameData = new DefinedNameData(dn.Name, sheetName, columnName, rowIndex);
definedNameDataSet.Add(definedNameData);
}
return(definedNameDataSet);
}