Click here to Skip to main content
15,893,904 members
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.4K   4.9K   48  
Import an Excel Workbook ".xls" or ."xlsx" into SQL without the use of JET or OLE
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SettingData
{
    public class ExcelSheetInfo
    {
        /// <summary>
        /// Indicates if the 1st Row is a Header in the Sheet
        /// </summary>
        public bool HasHeader { get; set; }
        /// <summary>
        /// The Start Row of the Sheet
        /// </summary>
        public int StartRow { get; set; }
        /// <summary>
        /// The End Row of the Sheet.
        /// A -1 will indicate read to end
        /// </summary>
        public int EndRow { get; set; }
        /// <summary>
        /// The Sheet Number within the Excel Document
        /// </summary>
        public int SheetNumber { get; set; }

        /// <summary>
        /// Creates an instance of the SheetInfo Class
        /// </summary>
        /// <param name="HasHeader">Indicates if the 1st Row is a Header in the Sheet</param>
        public ExcelSheetInfo(bool HasHeader, int SheetNumber) : this(HasHeader, (HasHeader ? 2 : 1), -1, SheetNumber) { }
        /// <summary>
        /// Creates an instance of the SheetInfo Class
        /// </summary>
        /// <param name="StartRow">The 1st Row to Start Reading From</param>
        /// <param name="EndRow">The Last Row to Read up to. A -1 will indicate read all</param>
        public ExcelSheetInfo(int StartRow, int EndRow, int SheetNumber) : this(false, StartRow, EndRow, SheetNumber) { }
        /// <summary>
        /// Creates an instance of the SheetInfo Class
        /// </summary>
        /// <param name="HasHeader">Indicates if the 1st Row is a Header in the Sheet</param>
        /// <param name="StartRow">The 1st Row to Start Reading From</param>
        /// <param name="EndRow">The Last Row to Read up to. A -1 will indicate read all</param>
        private ExcelSheetInfo(bool HasHeader, int StartRow, int EndRow, int SheetNumber)
        {
            this.HasHeader = HasHeader;
            this.StartRow = StartRow;
            this.EndRow = EndRow;
            this.SheetNumber = SheetNumber;
        }

        public System.Xml.XmlNode ToXml(System.Xml.XmlDocument Doc)
        {
            System.Xml.XmlNode Node = Doc.CreateElement("ExcelInfo");

            System.Xml.XmlNode HeaderNode = Doc.CreateElement("Header");
            HeaderNode.AppendChild(Doc.CreateTextNode(this.HasHeader.ToString()));

            System.Xml.XmlNode StartNode = Doc.CreateElement("StartRow");
            StartNode.AppendChild(Doc.CreateTextNode(this.StartRow.ToString()));

            System.Xml.XmlNode EndNode = Doc.CreateElement("EndRow");
            EndNode.AppendChild(Doc.CreateTextNode(this.EndRow.ToString()));

            System.Xml.XmlNode SheetNode = Doc.CreateElement("SheetNumber");
            SheetNode.AppendChild(Doc.CreateTextNode(this.SheetNumber.ToString()));

            Node.AppendChild(HeaderNode);
            Node.AppendChild(StartNode);
            Node.AppendChild(EndNode);
            Node.AppendChild(SheetNode);

            return Node;
        }
    }
}

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
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