C# Class BExIS.IO.Transform.Input.ExcelReader

http://blogs.msdn.com/b/brian_jones/archive/2010/05/27/parsing-and-reading-large-excel-files-with-the-open-xml-sdk.aspx this class is used to read and validate excel files

Inheritance: DataReader

Show file Open project: BEXIS2/Core Class Usage Examples

Public Methods

Method	Description
ExcelReader ( ) : System
IsTemplate ( Stream file ) : bool	return true if the Excel file based on a template
ReadFile ( Stream file, string fileName, FileReaderInfo fri, StructuredDataStructure sds, long datasetId ) : List	Read a Excel row by row Convert the rows into a datatuple based on the datastructure. Return a list of datatuples
ReadFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId ) : List	Read a Excel Template file Convert the rows into a datatuple based on the datastructure. Return a list of datatuples Only when excel template is in use
ReadFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId, int packageSize ) : List	Read a Excel Template file wth package size Convert the rows into a datatuple based on the datastructure. Return a list of datatuples Only when excel template is in use
ReadValuesFromFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId, List variableList, int packageSize ) : List>
ValidateFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId ) : void	Validate a Excel Template file Only when excel template is in use

Protected Methods

Method	Description
ReadRows ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : void	Read rows from worksheetPart starts from a startrow and ends on the endrow
ValidateDatastructure ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : bool	Validate Datastructure from file true = valid false = not valid
ValidateRows ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : void	Validate rows from file

Private Methods

Method	Description
BuildDefinedNamesTable ( DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart ) : List	generate a list of DefinedNameVal which includes all information about areas in a Excel template file like Data and VariableIdentifiers from the excel file
GetColumnName ( string cellName ) : string	Get the cloumn name from a cell
GetColumnNumber ( string columnName ) : int	Get Column number based on a column name
GetRowNumber ( string cellName ) : int	Get the row number from a cell
GetSubmitedVariableIdentifier ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : List	Generate a list of VariableIdentifiers from the excel file
GetValuesFromRows ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, List variableList, int startRow, int endRow ) : List>
GetWorkSheetPart ( DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, DefinedNameVal definedName ) : DocumentFormat.OpenXml.Packaging.WorksheetPart	Return the worksheet which include a specific area
IsEmpty ( Row row ) : bool
RowToList ( Row r ) : List	Convert a excel row to a list of strings Every cell is one value
RowToList ( Row r, List varIds ) : List	Convert a excel row to a list of strings Every cell is one value

Method Details

ExcelReader() public method

public ExcelReader ( ) : System
return	System

IsTemplate() public method

return true if the Excel file based on a template

public IsTemplate ( Stream file ) : bool
file	Stream	file as stream
return	bool

ReadFile() public method

Read a Excel row by row Convert the rows into a datatuple based on the datastructure. Return a list of datatuples

public ReadFile ( Stream file, string fileName, FileReaderInfo fri, StructuredDataStructure sds, long datasetId ) : List
file	Stream	File as stream
fileName	string	Name of the file
fri	FileReaderInfo	FileReaderInfo (ExcelFileReaderInfo) for additional Informations to read the file
sds	BExIS.Dlm.Entities.DataStructure.StructuredDataStructure	StructuredDataStructure of a dataset
datasetId	long	Datasetid of a dataset
return	List

ReadFile() public method

Read a Excel Template file Convert the rows into a datatuple based on the datastructure. Return a list of datatuples

Only when excel template is in use

public ReadFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId ) : List
file	Stream	File as stream
fileName	string	Name of the file
sds	BExIS.Dlm.Entities.DataStructure.StructuredDataStructure	StructuredDataStructure of a dataset
datasetId	long	Datasetid of a dataset
return	List

ReadFile() public method

Read a Excel Template file wth package size Convert the rows into a datatuple based on the datastructure. Return a list of datatuples

Only when excel template is in use

public ReadFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId, int packageSize ) : List
file	Stream	File as stream
fileName	string	Name of the file
sds	BExIS.Dlm.Entities.DataStructure.StructuredDataStructure	StructuredDataStructure of a dataset
datasetId	long	Datasetid of a dataset
packageSize	int
return	List

ReadRows() protected method

Read rows from worksheetPart starts from a startrow and ends on the endrow

protected ReadRows ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : void
worksheetPart	DocumentFormat.OpenXml.Packaging.WorksheetPart	part of a excel worksheet
startRow	int	start row
endRow	int	end row
return	void

ReadValuesFromFile() public method

public ReadValuesFromFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId, List variableList, int packageSize ) : List>
file	Stream
fileName	string
sds	BExIS.Dlm.Entities.DataStructure.StructuredDataStructure
datasetId	long
variableList	List
packageSize	int
return	List>

ValidateDatastructure() protected method

Validate Datastructure from file true = valid false = not valid

protected ValidateDatastructure ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : bool
worksheetPart	DocumentFormat.OpenXml.Packaging.WorksheetPart	Part of a excel worksheet where the datastructure is located
startRow	int	Row where the DataStructure is starting
endRow	int	Row where the DataStructure is ending
return	bool

ValidateFile() public method

Validate a Excel Template file

Only when excel template is in use

public ValidateFile ( Stream file, string fileName, StructuredDataStructure sds, long datasetId ) : void
file	Stream	File as stream
fileName	string	Name of the file
sds	BExIS.Dlm.Entities.DataStructure.StructuredDataStructure	StructuredDataStructure of a dataset
datasetId	long	Datasetid of a dataset
return	void

ValidateRows() protected method

Validate rows from file

protected ValidateRows ( DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, int startRow, int endRow ) : void
worksheetPart	DocumentFormat.OpenXml.Packaging.WorksheetPart	Part of a excel worksheet where the datastructure is located
startRow	int	Rows starting
endRow	int	Rows ending
return	void