/// <summary>
/// Validate a Excel Template file
/// </summary>
/// <remarks>Only when excel template is in use</remarks>
/// <seealso cref=""/>
/// <param name="file">File as stream</param>
/// <param name="fileName">Name of the file</param>
/// <param name="sds">StructuredDataStructure of a dataset</param>
/// <param name="datasetId">Datasetid of a dataset</param>
public void ValidateFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId)
{
this.FileStream = file;
this.FileName = fileName;
this.StructuredDataStructure = sds;
//this.Info = efri;
this.DatasetId = datasetId;
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false);
// 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();
if (this._areaOfData == null)
{
this.ErrorMessages.Add(new Error(ErrorType.Other, "Data area is not defined in the excel template."));
}
// Select variable area
this._areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();
if (this._areaOfVariables == null)
{
this.ErrorMessages.Add(new Error(ErrorType.Other, "VariableIdentifiers area is not defined in the excel template."));
}
// 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;
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData);
//worksheet = worksheetPart.Worksheet;
// get styleSheet
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
if (this.ErrorMessages.Count == 0)
{
if (ValidateDatastructure(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow))
{
ValidateRows(worksheetPart, this._areaOfData.StartRow, this._areaOfData.EndRow);
}
}
// close fehlt
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="file"></param>
/// <param name="fileName"></param>
/// <param name="sds"></param>
/// <param name="datasetId"></param>
/// <param name="variableList"></param>
/// <param name="packageSize"></param>
/// <returns></returns>
public List <List <string> > ReadValuesFromFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId, List <long> variableList, int packageSize)
{
List <List <string> > listOfSelectedvalues = new List <List <string> >();
this.FileStream = file;
this.FileName = fileName;
this.StructuredDataStructure = sds;
//this.Info = efri;
this.DatasetId = datasetId;
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false);
// 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;
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData);
//worksheet = worksheetPart.Worksheet;
// get styleSheet
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
if (Position == 1)
{
Position = this._areaOfData.StartRow;
}
if (GetSubmitedVariableIdentifier(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow) != null)
{
listOfSelectedvalues = GetValuesFromRows(worksheetPart, variableList, Position, Position + packageSize);
Position += packageSize;
}
return(listOfSelectedvalues);
}
/// <summary>
/// Return the worksheet which include a specific area
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="workbookPart">Workbook part</param>
/// <param name="definedName">Object with informations about a area</param>
/// <returns>Worksheet where the area is inside</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>
/// Return the worksheet which include a specific area
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="workbookPart">Workbook part</param>
/// <param name="definedName">Object with informations about a area</param>
/// <returns>Worksheet where the area is inside</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>
/// Validate a Excel Template file
/// </summary>
/// <remarks>Only when excel template is in use</remarks>
/// <seealso cref=""/>
/// <param name="file">File as stream</param>
/// <param name="fileName">Name of the file</param>
/// <param name="sds">StructuredDataStructure of a dataset</param>
/// <param name="datasetId">Datasetid of a dataset</param>
public void ValidateFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId)
{
this.FileStream = file;
this.FileName = fileName;
this.StructuredDataStructure = sds;
//this.Info = efri;
this.DatasetId = datasetId;
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false);
// 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();
if(this._areaOfData == null) this.ErrorMessages.Add(new Error(ErrorType.Other,"Data area is not defined in the excel template."));
// Select variable area
this._areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();
if(this._areaOfVariables == null) this.ErrorMessages.Add(new Error(ErrorType.Other,"VariableIdentifiers area is not defined in the excel template."));
// 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;
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData);
//worksheet = worksheetPart.Worksheet;
// get styleSheet
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();
if (this.ErrorMessages.Count == 0)
{
if (ValidateDatastructure(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow))
{
ValidateRows(worksheetPart, this._areaOfData.StartRow, this._areaOfData.EndRow);
}
}
// close fehlt
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="file"></param>
/// <param name="fileName"></param>
/// <param name="sds"></param>
/// <param name="datasetId"></param>
/// <param name="variableList"></param>
/// <param name="packageSize"></param>
/// <returns></returns>
public List<List<string>> ReadValuesFromFile(Stream file, string fileName, StructuredDataStructure sds, long datasetId, List<long> variableList, int packageSize)
{
List<List<string>> listOfSelectedvalues = new List<List<string>>();
this.FileStream = file;
this.FileName = fileName;
this.StructuredDataStructure = sds;
//this.Info = efri;
this.DatasetId = datasetId;
// open excel file
spreadsheetDocument = SpreadsheetDocument.Open(this.FileStream, false);
// 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;
// select worksheetpart by selected defined name area like data in sheet
// sheet where data area is inside
WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, this._areaOfData);
//worksheet = worksheetPart.Worksheet;
// get styleSheet
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
// Get shared strings
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();
if (Position == 1)
{
Position = this._areaOfData.StartRow;
}
if (GetSubmitedVariableIdentifier(worksheetPart, this._areaOfVariables.StartRow, this._areaOfVariables.EndRow) != null)
{
listOfSelectedvalues= GetValuesFromRows(worksheetPart,variableList, Position, Position + packageSize);
Position += packageSize;
}
return listOfSelectedvalues;
}
