/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="workbookPart"></param>
/// <param name="definedName"></param>
/// <returns></returns>
private static WorksheetPart getWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName)
{
//get worksheet based on defined name
string relId = workbookPart.Workbook.Descendants <Sheet>()
.Where(s => definedName.SheetName.Equals(s.Name))
.First()
.Id;
return((WorksheetPart)workbookPart.GetPartById(relId));
}
/// <summary>
/// prepare existing excel file to append data
/// </summary>
/// <param name="filePath"></param>
/// <param name="dataStructureId"></param>
protected override void Init(string filePath, long dataStructureId)
{
// loading datastructure
dataStructure = GetDataStructure(dataStructureId);
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
// get workbookpart
workbookPart = spreadsheetDocument.WorkbookPart;
// get all the defined area
List <DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart);
// select data area
this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault();
// set starting row number
rowIndex = areaOfData.EndRow;
// Select variable area
this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();
// Get integers for reading data
startColumn = getColumnNumber(this.areaOfData.StartColumn);
endColumn = getColumnNumber(this.areaOfData.EndColumn);
numOfColumns = (endColumn - startColumn) + 1;
offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1;
// generate Style for cell types
generateStyle(spreadsheetDocument);
// get styleSheet
stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData);
// Get VariableIndentifiers
VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow);
// get sheetData object for adding data to
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
}
/// <summary>
/// Add Datatuples to a Excel Template file
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="dataTuples"> Datatuples to add</param>
/// <param name="filePath">Path of the excel template file</param>
/// <param name="dataStructureId">Id of datastructure</param>
/// <returns>List of Errors or null</returns>
public List<Error> AddDataTuplesToTemplate(DatasetManager datasetManager, List<long> dataTuplesIds, string filePath, long dataStructureId )
{
if (File.Exists(filePath))
{
//Stream file = Open(filePath);
//_dataTuples = dataTuples;
// loading datastructure
dataStructure = GetDataStructure(dataStructureId);
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// get all the defined area
List<DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart);
// select data area
this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault();
// Select variable area
this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();
// Get intergers for reading data
startColumn = getColumnNumber(this.areaOfData.StartColumn);
endColumn = getColumnNumber(this.areaOfData.EndColumn);
numOfColumns = (endColumn - startColumn) + 1;
offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1;
// gerneat Style for cell types
generateStyle(spreadsheetDocument);
// get styleSheet
stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData);
// Get VarioableIndentifiers
this.VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow);
AddRows(worksheetPart, this.areaOfData.StartRow, this.areaOfData.EndRow, dataTuplesIds, datasetManager);
// set data area
foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>())
{
if (name.Name == "Data")
{
string[] tempArr = name.InnerText.Split('$');
string temp = "";
//$A$10:$C$15
tempArr[tempArr.Count() - 1] = numOfDataRows.ToString();
foreach (string t in tempArr)
{
if (t == tempArr.First())
{
temp = temp + t;
}
else
{
temp = temp + "$" + t;
}
}
name.Text = temp;
}
}
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
return ErrorMessages;
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="workbookPart"></param>
/// <param name="definedName"></param>
/// <returns></returns>
private static WorksheetPart getWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName)
{
//get worksheet based on defined name
string relId = workbookPart.Workbook.Descendants<Sheet>()
.Where(s => definedName.SheetName.Equals(s.Name))
.First()
.Id;
return (WorksheetPart)workbookPart.GetPartById(relId);
}