clsExcel to xml class

 public class clsExcelToXML
    {
        #region Variable Declaration
        private OleDbConnection cn;
        private OleDbDataAdapter daAdapter;

        private string ExcelCon = @"Provider=Microsoft.Jet.OLEDB.4.0;";
        private string strConnectionString;

        private string strParseError = "";
        private string SheetName, Range;
        #endregion

        #region Constructors
        ///

        /// Initialize ExcelXML component using the sepecifed File name, By default HDR property will be false.
        ///

        ///
        public clsExcelToXML(string strFileName)
        {
            strConnectionString = ExcelCon + "Data Source=" + strFileName + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;HDR=No" + Convert.ToChar(34).ToString();
            cn = new OleDbConnection();
            cn.ConnectionString = strConnectionString;
        }

        ///

        /// Initialize ExcelXML component using the specified File name, you can specify HDR value using _blnHeaders
        ///

        ///
        ///
        public clsExcelToXML(string strFileName, Boolean _blnHeaders)
        {
            if (_blnHeaders)
                strConnectionString = ExcelCon + "Data Source=" + strFileName + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;HDR=Yes" + Convert.ToChar(34).ToString();
            else
                strConnectionString = ExcelCon + "Data Source=" + strFileName + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;HDR=No" + Convert.ToChar(34).ToString();

            cn = new OleDbConnection();
            cn.ConnectionString = strConnectionString;
        }

        #endregion

        #region Functionality

        #region XML Functionality
        public string GetXML(string strSheetName, Boolean _blnSchema)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(this.GetDataTable(strSheetName));

            if (_blnSchema)
                return ds.GetXmlSchema() + ds.GetXml();
            else
                return ds.GetXml();
        }
        public string GetXMLSchema(string strSheetName)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(this.GetDataTable(strSheetName));
            return ds.GetXmlSchema();
        }

        public string[] GetAllXML()
        {
            string[] excelSheet = GetExcelSheetNames();

            DataSet dsExcelData = new DataSet();
            DataTable dt = new DataTable();
            foreach (string strSheetName in excelSheet)
            {
                dsExcelData.Tables.Add(this.GetDataTable(strSheetName));
            }
            string[] xml = new string[2];
            xml[0] = dsExcelData.GetXmlSchema();
            xml[1] = dsExcelData.GetXml();
            return xml;
        }

        #endregion

        #region Excel File Info
        public String[] GetExcelSheetNames()
        {

            System.Data.DataTable dt = null;

            try
            {
                cn.Open();

                // Get the data table containing the schema
                dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {

                    //string strSheetTableName = row["TABLE_NAME"].ToString();
                    string strSheetTableName = "Barcode Sequence$";
                    strSheetTableName.Trim(new char[] { '\\' });
                    excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length);
                    i++;
                }


                return excelSheets;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                // Clean up.
                cn.Close();
            }
        }
        public DataTable GetDataTableForCase(string strSheetName)
        {

            try
            {

                string strComand;
                if (strSheetName.IndexOf("|") > 0)
                {
                    SheetName = strSheetName.Substring(0, strSheetName.IndexOf("|"));
                    Range = strSheetName.Substring(strSheetName.IndexOf("|") + 1);
                    strComand = "select * from [" + SheetName + "$" + Range + "]";
                }
                else
                {
                    strSheetName = strSheetName.Substring(0, strSheetName.Length);
                    strComand = "select DISTINCT F5,F1 from [" + strSheetName + "]";


                }


                daAdapter = new OleDbDataAdapter(strComand, cn);
                DataTable dt = new DataTable(strSheetName);

                daAdapter.FillSchema(dt, SchemaType.Source);

                daAdapter.Fill(dt);
                dt.Rows.RemoveAt(0);
                cn.Close();

                 return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
     
        public DataTable GetDataTable(string strSheetName)
        {

            try
            {

                string strComand;
                if (strSheetName.IndexOf("|") > 0)
                {
                    SheetName = strSheetName.Substring(0, strSheetName.IndexOf("|"));
                    Range = strSheetName.Substring(strSheetName.IndexOf("|") + 1);
                    strComand = "select * from [" + SheetName + "$" + Range + "]";
                }
                else
                {
                    strSheetName = strSheetName.Substring(0, strSheetName.Length);
                    strComand = "select * from [" + strSheetName + "]  " + " 'EXCEPT SELECT TOP 1 * from " + strSheetName + "'";


                }


                daAdapter = new OleDbDataAdapter(strComand, cn);
                DataTable dt = new DataTable(strSheetName);
               
                daAdapter.FillSchema(dt, SchemaType.Source);
               
                daAdapter.Fill(dt);
                dt.Rows.RemoveAt(0);
                cn.Close();

                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region Save Functionality
        public void SaveSheetXML(string strFileName, string strSheetName, Boolean _blnSchema)
        {
            try
            {
                string strFile = strFileName.Substring(strFileName.LastIndexOf("\\") + 1);
                string path = strFileName.Substring(0, strFileName.LastIndexOf("\\"));
                strFile = strFile.Remove(strFile.IndexOf("."), 4);
                SaveFile(path, strFile, strSheetName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private void SaveFile(string path, string strFile, string strSheetName)
        {
            FileStream file = new FileStream(path + "\\" + strFile + ".xml", FileMode.Create);
            StreamWriter wr = new StreamWriter(file);
            wr.Write("" + this.GetXML(strSheetName, false));
            wr.Close();
            file.Close();
            file = new FileStream(path + "\\" + strFile + ".xsd", FileMode.Create);
            wr = new StreamWriter(file);
            wr.Write(this.GetXMLSchema(strSheetName).Replace("utf-16", "utf-8"));
            wr.Close();
            file.Close();
        }
        public void SaveXslXml(string strFileName, Boolean _blnSchema, Boolean _blnMulti)
        {
            string[] excelSheet = GetExcelSheetNames();
            string strFile = strFileName.Substring(strFileName.LastIndexOf("\\") + 1);
            string path = strFileName.Substring(0, strFileName.LastIndexOf("\\"));
            strFile = strFile.Remove(strFile.IndexOf("."), 4);

            if (_blnMulti)
            {
                foreach (string strSheetName in excelSheet)
                {
                    this.SaveFile(path, strFile + "_" + strSheetName, strSheetName);

                }
            }
            else
            {
                string[] xml = this.GetAllXML();
                FileStream file = new FileStream(path + "\\" + strFile + ".xml", FileMode.Create);
                StreamWriter wr = new StreamWriter(file);
                wr.Write("" + xml[1]);
                wr.Close();
                file.Close();
                file = new FileStream(path + "\\" + strFile + ".xsd", FileMode.Create);
                wr = new StreamWriter(file);
                wr.Write(xml[0].Replace("utf-16", "utf-8"));
                wr.Close();
                file.Close();
            }

        }

        #endregion

        #region Validations
        public string ValidateXML(string strSchemaFile)
        {
            strParseError = "";

            XmlParserContext context = new XmlParserContext(null, new XmlNamespaceManager(new NameTable()), null, XmlSpace.None);
            XmlTextReader xmlReader = new XmlTextReader(this.GetAllXML()[1], XmlNodeType.Element, context);
            XmlValidatingReader objValidator = new XmlValidatingReader(xmlReader);

            //set the validation type to use an XSD schema
            objValidator.ValidationType = ValidationType.Schema;
            XmlSchemaCollection objSchemaCol = new XmlSchemaCollection();
            objSchemaCol.Add("", strSchemaFile);
            objValidator.Schemas.Add(objSchemaCol);
            objValidator.ValidationEventHandler += new ValidationEventHandler(ShowCompileErrors);

            try
            {
                while (objValidator.Read()) { }
                //xmlInfo.InnerHtml += "* XML was validated - " + intValidErrors + " error(s) found";
            }
            catch (Exception objError)
            {
                throw new Exception(objError.Message);
                //xmlInfo.InnerHtml += "* Read/Parser error: " + objError.Message + "
";
            }
            finally
            {
                xmlReader.Close();
            }
            return strParseError;
        }

        public string ValidateXML(string strSchemaFile, string WorkSheet)
        {

            strParseError = "";

            XmlParserContext context = new XmlParserContext(null, new XmlNamespaceManager(new NameTable()), null, XmlSpace.None);
            XmlTextReader xmlReader = new XmlTextReader(this.GetXML(WorkSheet, false), XmlNodeType.Element, context);
            XmlValidatingReader objValidator = new XmlValidatingReader(xmlReader);

            //set the validation type to use an XSD schema
            objValidator.ValidationType = ValidationType.Schema;
            XmlSchemaCollection objSchemaCol = new XmlSchemaCollection();
            objSchemaCol.Add("", strSchemaFile);
            objValidator.Schemas.Add(objSchemaCol);
            objValidator.ValidationEventHandler += new ValidationEventHandler(ShowCompileErrors);

            try
            {
                while (objValidator.Read()) { }
                //xmlInfo.InnerHtml += "* XML was validated - " + intValidErrors + " error(s) found";
            }
            catch (Exception objError)
            {
                throw new Exception(objError.Message);
                //xmlInfo.InnerHtml += "* Read/Parser error: " + objError.Message + "
";
            }
            finally
            {
                xmlReader.Close();
            }
            return strParseError;
        }

        private void ShowCompileErrors(object sender, ValidationEventArgs args)
        {
            strParseError += "::" + args.Message + "\r\n";
        }

        #endregion

        #region Batch Function
        public static void BatchXMLConvert(string[] Files, Boolean _blnMulti)
        {
            try
            {
                foreach (string FileName in Files)
                {
                    clsExcelToXML obj = new clsExcelToXML(FileName);
                    obj.SaveXslXml(FileName, true, _blnMulti);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #endregion


     
    }