public static string GetSelectCommandText(ExcelSchema schema, ExcelOptions options)
{
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.Append("SELECT * FROM [");
commandBuilder.Append(getWorksheetName(options));
commandBuilder.Append(getDataRange(schema, options));
commandBuilder.Append("]");
return commandBuilder.ToString();
}
public static string GetSelectCommandText(ExcelSchema schema, ExcelOptions options)
{
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.Append("SELECT * FROM [");
commandBuilder.Append(getWorksheetName(options));
commandBuilder.Append(getDataRange(schema, options));
commandBuilder.Append("]");
return(commandBuilder.ToString());
}
private ExcelReader(string fileName, ExcelSchema schema, ExcelOptions options, bool hasSchema)
{
if (!File.Exists(fileName))
{
throw new FileNotFoundException(Resources.FileNotFound, fileName);
}
if (hasSchema && schema == null)
{
throw new ArgumentNullException("schema");
}
if (options == null)
{
throw new ArgumentNullException("options");
}
this.options = options;
string connectionString = ExcelHelpers.GetConnectionString(fileName);
connection = new OleDbConnection(connectionString);
connection.Open();
command = connection.CreateCommand();
command.CommandText = ExcelHelpers.GetSelectCommandText(schema, options);
dataReader = command.ExecuteReader();
if (hasSchema)
{
if (options.IsFirstRecordSchema)
{
dataReader.Read(); // skip header record
}
this.schema = schema;
}
else if (options.IsFirstRecordSchema && dataReader.Read())
{
object[] values = new object[dataReader.FieldCount];
dataReader.GetValues(values);
int startingIndex = ExcelHelpers.GetExcelColumnIndex(options.StartingColumn ?? "A");
this.schema = new ExcelSchema();
for (int valueIndex = 0; valueIndex != values.Length; ++valueIndex)
{
object value = values[valueIndex];
string columnName = getColumnName(startingIndex + valueIndex, value);
StringColumn column = new StringColumn(columnName);
this.schema.AddColumn(column);
}
}
}
private static string getEndingColumn(ExcelSchema schema, ExcelOptions options)
{
if (options.EndingColumn != null)
{
return(options.EndingColumn);
}
if (schema == null)
{
return(GetExcelColumnName(16384));
}
int fieldCount = schema.ColumnDefinitions.Count;
int startIndex = GetExcelColumnIndex(options.StartingColumn ?? "A");
int endIndex = startIndex + fieldCount - 1;
string endingColumn = GetExcelColumnName(endIndex);
return(endingColumn);
}
private static string getDataRange(ExcelSchema schema, ExcelOptions options)
{
StringBuilder builder = new StringBuilder();
if (options.StartingRow != null || options.EndingRow != null || options.StartingColumn != null || options.EndingColumn != null)
{
builder.Append(options.StartingColumn ?? "A");
builder.Append(options.StartingRow ?? 1);
builder.Append(":");
builder.Append(getEndingColumn(schema, options));
if (options.EndingRow != null)
{
builder.Append(options.EndingRow.Value);
}
}
return(builder.ToString());
}
private ExcelReader(string fileName, ExcelSchema schema, ExcelOptions options, bool hasSchema)
{
if (!File.Exists(fileName))
{
throw new FileNotFoundException(Resources.FileNotFound, fileName);
}
if (hasSchema && schema == null)
{
throw new ArgumentNullException("schema");
}
if (options == null)
{
throw new ArgumentNullException("options");
}
this.options = options;
string connectionString = ExcelHelpers.GetConnectionString(fileName);
connection = new OleDbConnection(connectionString);
connection.Open();
command = connection.CreateCommand();
command.CommandText = ExcelHelpers.GetSelectCommandText(schema, options);
dataReader = command.ExecuteReader();
if (hasSchema)
{
if (options.IsFirstRecordSchema)
{
dataReader.Read(); // skip header record
}
this.schema = schema;
}
else if (options.IsFirstRecordSchema && dataReader.Read())
{
object[] values = new object[dataReader.FieldCount];
dataReader.GetValues(values);
int startingIndex = ExcelHelpers.GetExcelColumnIndex(options.StartingColumn ?? "A");
this.schema = new ExcelSchema();
for (int valueIndex = 0; valueIndex != values.Length; ++valueIndex)
{
object value = values[valueIndex];
string columnName = getColumnName(startingIndex + valueIndex, value);
StringColumn column = new StringColumn(columnName);
this.schema.AddColumn(column);
}
}
}
private static string getWorksheetName(ExcelOptions options)
{
return(options.WorksheetName + "$");
}
private static string getDataRange(ExcelSchema schema, ExcelOptions options)
{
StringBuilder builder = new StringBuilder();
if (options.StartingRow != null || options.EndingRow != null || options.StartingColumn != null || options.EndingColumn != null)
{
builder.Append(options.StartingColumn ?? "A");
builder.Append(options.StartingRow ?? 1);
builder.Append(":");
builder.Append(getEndingColumn(schema, options));
if (options.EndingRow != null)
{
builder.Append(options.EndingRow.Value);
}
}
return builder.ToString();
}
private static string getWorksheetName(ExcelOptions options)
{
return options.WorksheetName + "$";
}
private static string getEndingColumn(ExcelSchema schema, ExcelOptions options)
{
if (options.EndingColumn != null)
{
return options.EndingColumn;
}
if (schema == null)
{
return GetExcelColumnName(16384);
}
int fieldCount = schema.ColumnDefinitions.Count;
int startIndex = GetExcelColumnIndex(options.StartingColumn ?? "A");
int endIndex = startIndex + fieldCount - 1;
string endingColumn = GetExcelColumnName(endIndex);
return endingColumn;
}
/// <summary>
/// Initializes a new instance of an ExcelReader.
/// </summary>
/// <param name="fileName">The name of the file to read.</param>
/// <param name="schema">The predefined schema for the records.</param>
/// <param name="options">The options for configuring the reader's behavior.</param>
public ExcelReader(string fileName, ExcelSchema schema, ExcelOptions options)
: this(fileName, schema, options, true)
{
}
/// <summary>
/// Initializes a new instance of an ExcelReader.
/// </summary>
/// <param name="fileName">The name of the file to read.</param>
/// <param name="options">The options for configuring the reader's behavior.</param>
public ExcelReader(string fileName, ExcelOptions options)
: this(fileName, null, options, false)
{
}
/// <summary>
/// Initializes a new instance of an ExcelReader.
/// </summary>
/// <param name="fileName">The name of the file to read.</param>
/// <param name="schema">The predefined schema for the records.</param>
/// <param name="options">The options for configuring the reader's behavior.</param>
public ExcelReader(string fileName, ExcelSchema schema, ExcelOptions options)
: this(fileName, schema, options, true)
{
}
/// <summary>
/// Initializes a new instance of an ExcelReader.
/// </summary>
/// <param name="fileName">The name of the file to read.</param>
/// <param name="options">The options for configuring the reader's behavior.</param>
public ExcelReader(string fileName, ExcelOptions options)
: this(fileName, null, options, false)
{
}