/// <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; }