Exemplo n.º 1
0
 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();
 }
Exemplo n.º 2
0
        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());
        }
Exemplo n.º 3
0
        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);
                }
            }
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
        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());
        }
Exemplo n.º 6
0
        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);
                }
            }
        }
Exemplo n.º 7
0
 private static string getWorksheetName(ExcelOptions options)
 {
     return(options.WorksheetName + "$");
 }
Exemplo n.º 8
0
 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();
 }
Exemplo n.º 9
0
 private static string getWorksheetName(ExcelOptions options)
 {
     return options.WorksheetName + "$";
 }
Exemplo n.º 10
0
 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;
 }
Exemplo n.º 11
0
 /// <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)
 {
 }
Exemplo n.º 12
0
 /// <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)
 {
 }
Exemplo n.º 13
0
 /// <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)
 {
 }
Exemplo n.º 14
0
 /// <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)
 {
 }