Click here to Skip to main content
15,897,704 members
Search within:


Articles / Programming Languages / SQL
 

Excel to SQL without JET or OLE (Version 2)

Rate me:
Please Sign up or sign in to vote.
4.29/5 (5 votes)
5 Oct 2010CPOL4 min read 61.5K   4.9K   48  
Import an Excel Workbook ".xls" or ."xlsx" into SQL without the use of JET or OLE
 
<?xml version="1.0"?>
<doc>
    <assembly>
        <name>ExcelToDB</name>
    </assembly>
    <members>
        <member name="T:ExcelToDB.ExcError_EventArgs">
            <summary>
            Error Message Event Argument
            Used to transfer an error message via an event
            </summary>
        </member>
        <member name="M:ExcelToDB.ExcError_EventArgs.#ctor(System.Exception)">
            <summary>
            Creates and instance of the ExcError_EventArgs class
            </summary>
            <param name="Error">The Exception error message to transfer</param>
        </member>
        <member name="M:ExcelToDB.ExcError_EventArgs.ToString">
            <summary>
            Creates and returns a string representation of the current ExcError_EventArgs
            </summary>
            <returns>The Message Field of the Exception</returns>
        </member>
        <member name="P:ExcelToDB.ExcError_EventArgs.Error">
            <summary>
            The Exception error message to transfer
            </summary>
        </member>
        <member name="T:ExcelToDB.SvrDbInfo">
            <summary>
            Information for the Connection to the 
            SQL Server, the Database, and the Table
            </summary>
        </member>
        <member name="M:ExcelToDB.SvrDbInfo.#ctor(System.String,System.String,System.String)">
            <summary>
            Create a New Instance of the DatabaseInfo Class
            </summary>
            <param name="SVRName">The Server Name</param>
            <param name="DBName">The Database Name</param>
            <param name="TBLName">The Table Name</param>
        </member>
        <member name="P:ExcelToDB.SvrDbInfo.SVRName">
            <summary>
            The Server Name
            </summary>
        </member>
        <member name="P:ExcelToDB.SvrDbInfo.DBName">
            <summary>
            The Database Name
            </summary>
        </member>
        <member name="P:ExcelToDB.SvrDbInfo.TBLName">
            <summary>
            The Table Name
            </summary>
        </member>
        <member name="T:ExcelToDB.ExcInfo_EventArgs">
            <summary>
            Information Message Event Argument
            Used to transfer a message via an event
            </summary>
        </member>
        <member name="M:ExcelToDB.ExcInfo_EventArgs.#ctor(System.String)">
            <summary>
            Creates an instance of the ExcInfo_EventArgs class.
            </summary>
            <param name="Message">The Message to transfer</param>
        </member>
        <member name="M:ExcelToDB.ExcInfo_EventArgs.ToString">
            <summary>
            Returns a System.String that represents the current ExcInfo_EventArgs
            </summary>
            <returns>The ExcInfo_EventArgs instance Message property</returns>
        </member>
        <member name="P:ExcelToDB.ExcInfo_EventArgs.Message">
            <summary>
            The Message to transfer
            </summary>
        </member>
        <member name="T:ExcelToDB.ConnectionString">
            <summary>
            The Connection information for the Server
            </summary>
        </member>
        <member name="M:ExcelToDB.ConnectionString.#ctor(ExcelToDB.SvrDbInfo)">
            <summary>
            Create a new instance of the ConnectionString class
            </summary>
            <param name="D_Info">The Database Info for the Server</param>
        </member>
        <member name="M:ExcelToDB.ConnectionString.#ctor(ExcelToDB.SvrDbInfo,System.Int32)">
            <summary>
            Create a new instance of the ConnectionString class
            </summary>
            <param name="D_Info">The Database Info for the Server</param>
            <param name="Timeout">The Timeout value for the Command</param>
        </member>
        <member name="M:ExcelToDB.ConnectionString.#ctor(ExcelToDB.SvrDbInfo,System.Int32,System.String,System.String)">
            <summary>
            Create a new instance of the ConnectionString class
            </summary>
            <param name="D_Info">The Database Info for the Server</param>
            <param name="Timeout">The Timeout value for the Command</param>
            <param name="Username">The SQL Username</param>
            <param name="Password">The SQL Password</param>
        </member>
        <member name="M:ExcelToDB.ConnectionString.ToString">
            <summary>
            Returns the Connection string required for the SqlConnection class
            </summary>
            <returns>The SQL Connection string</returns>
        </member>
        <member name="P:ExcelToDB.ConnectionString.D_Info">
            <summary>
            The Database Info for the Server
            </summary>
        </member>
        <member name="P:ExcelToDB.ConnectionString.UserName">
            <summary>
            The SQL Username
            </summary>
        </member>
        <member name="P:ExcelToDB.ConnectionString.Password">
            <summary>
            The SQL Password
            </summary>
        </member>
        <member name="P:ExcelToDB.ConnectionString.Timeout">
            <summary>
            The Timeout value for the SQL Command (seconds)
            </summary>
        </member>
        <member name="P:ExcelToDB.ConnectionString.TrustedConnection">
            <summary>
            Is a Trusted Connection
            </summary>
        </member>
        <member name="M:ExcelToDB.SQLHandler.GetSQLTableName(System.String)">
            <summary>
            Get the Table value to pass to the SQL Command
            </summary>
            <param name="TableName">the string value passed to the program</param>
            <returns>The correct format for the sql tablename i.e [MySchema].[Date]</returns>
        </member>
        <member name="M:ExcelToDB.SQLHandler.SQL_Get_TableStructure">
            <summary>
            Gets the structure of the Tables from the Database and Table specified.
            </summary>
            <returns>The DataTable representation of the SQL Table used for import</returns>
        </member>
        <member name="M:ExcelToDB.SQLHandler.TruncateSQLTable">
            <summary>
            Delete the previous information from the SQL Table.
            </summary>
        </member>
        <member name="M:ExcelToDB.SQLHandler.ReadExcelIntoDatabase(System.Data.DataTable)">
            <summary>
            Read the Excel Document and Write the Information into the SQL Table.
            This will use the Internal ConnectionString Class to obtain connection details
            </summary>
            <param name="Table">The DataTable to write to the SQL Server.</param>
        </member>
        <member name="M:ExcelToDB.SQLHandler.RowsFromCollection(System.Data.DataRowCollection)">
            <summary>
            Return a DataRow array from the DataRowCollection
            </summary>
            <param name="Collection">The DataRow Collection to retrieve the array from</param>
            <returns></returns>
        </member>
        <member name="M:ExcelToDB.IExcel.TableFromExcelWorksheet">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.IExcel.TableFromExcelWorksheet(System.Int32)">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <param name="SheetNumIndex">The index value of the Worksheet required</param>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.IExcel.TableFromExcelWorksheet(System.Data.DataTable)">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.IExcel.TableFromExcelWorksheet(System.Data.DataTable,System.Int32)">
            <summary>
            Gets the Specified Worksheet from Excel into a DataTable.
            </summary>
            <param name="tblStructure">The Structure of the DataTable</param>
            <param name="SheetNumIndex">The index value of the Worksheet required</param>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.IExcel.TablesFromExcelWorksheets(System.Data.DataSet)">
            <summary>
            Gets the Worksheets from Excel into a DataSet.
            </summary>
            <param name="dsStructures">The initial dataset containing the table structure for each Worksheet</param>
            <returns></returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.TableFromExcelWorksheet">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.TableFromExcelWorksheet(System.Int32)">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <param name="SheetNumIndex">The index value of the Worksheet required</param>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.TableFromExcelWorksheet(System.Data.DataTable)">
            <summary>
            Gets the First Worksheet from Excel into a DataTable
            </summary>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.TableFromExcelWorksheet(System.Data.DataTable,System.Int32)">
            <summary>
            Gets the Specified Worksheet from Excel into a DataTable.
            </summary>
            <param name="tblStructure">The Structure of the DataTable</param>
            <param name="SheetNumIndex">The index value of the Worksheet required</param>
            <returns>The Excel information as a DataTable</returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.TablesFromExcelWorksheets(System.Data.DataSet)">
            <summary>
            Gets the Worksheets from Excel into a DataSet.
            </summary>
            <param name="dsStructures">The initial dataset containing the table structure for each Worksheet</param>
            <returns></returns>
        </member>
        <member name="M:ExcelToDB.ExcelBaseHandler.ReadExcelIntoDatabase(System.Data.DataTable)">
            <summary>
            Read the Excel Document and Write the Information into the SQL Table.
            This will use the Internal ConnectionString Class to obtain connection details
            </summary>
            <param name="ExcelTable">DataTable to write to the Database.</param>
        </member>
        <member name="P:ExcelToDB.ExcelBaseHandler.X_StartRow">
            <summary>
            The Start Row for the Excel Worksheet
            </summary>
        </member>
        <member name="P:ExcelToDB.ExcelBaseHandler.X_EndRow">
            <summary>
            The End Row for the Excel Worksheet
            </summary>
        </member>
        <member name="P:ExcelToDB.ExcelBaseHandler.FileName">
            <summary>
            Excel File Location to Import from
            </summary>
        </member>
        <member name="P:ExcelToDB.ExcelBaseHandler.TrnctTBL">
            <summary>
            Truncate the Table prior to adding the Excel Document
            </summary>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel2007UpHandler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.Excel97to2003Handler.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="T:ExcelToDB.ExcelDB">
            <summary>
            The class used to handle all reading and input of data
            from and Excel spreadsheet to a SQL Server database.
            </summary>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Boolean,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="XHeader">The 1st row of the Excel Document is a Header.</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.#ctor(System.String,ExcelToDB.ConnectionString,System.Boolean,System.Int32,System.Int32)">
            <summary>
            Creates an instance of the ExcelDB Class
            </summary>
            <param name="FileName">The Excel document File Location</param>
            <param name="ConnInfo">The ConnectionString used to connect to the Database</param>
            <param name="TrunctTBL">Delete all prior information in the table</param>
            <param name="X_StartRow">The row from which to start Reading</param>
            <param name="X_EndRow">The row to end Reading</param>
        </member>
        <member name="M:ExcelToDB.ExcelDB.ReadExcelIntoDatabase(System.Int32)">
            <summary>
            Read the Excel Document and Write the Information into the SQL Table.
            This will use the Internal ConnectionString Class to obtain connection details
            </summary>
            <param name="SheetNumber">The Sheet Number of the Excel Workbook to Read</param>
        </member>
        <member name="P:ExcelToDB.ExcelDB.SupportedFileTypes">
            <summary>
            The string value for the Supported File Types of the current 
            ExcelDB Class.
            The values are returned in the values of 
            *.xxx;*.xxx
            This is so that they can be directly added to a Dialog filter.
            </summary>
        </member>
        <member name="E:ExcelToDB.ExcelDB.MessageEvnt">
            <summary>
            Occurs when information messages are produced
            </summary>
        </member>
        <member name="E:ExcelToDB.ExcelDB.ExceptionEvnt">
            <summary>
            Occurs when exception messages are thrown
            </summary>
        </member>
        <member name="T:ExcelToDB.ExcelDB.ExceptionDelegate">
            <summary>
            The Event Handler for Exception Messages thrown by the import procedure
            </summary>
            <param name="args"></param>
        </member>
        <member name="T:ExcelToDB.ExcelDB.MessageDelegate">
            <summary>
            The Event Handler for Information Messages produced by the Import procedure
            </summary>
            <param name="args"></param>
        </member>
    </members>
</doc>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Kelvin Armstrong
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 5 Oct 2010

Article Copyright 2010 by Kelvin Armstrong
Everything else Copyright © CodeProject, 1999-2024

Web03 2.8:2024-04-02:1