New Post

hgfh

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


     
    }

Excel to xml Conversion

public partial class frmXmlConversion : Form
    {
        private string strFileName;
        private clsExcelToXML obj;
        private string strSheetNameRange;
        private DataSet dsFile;
        string filepath;
        string Newxmlfilename;
        string fileNameWithoutex;

        public frmXmlConversion()
        {
            InitializeComponent();
        }

        private void frmXmlConversion_Load(object sender, EventArgs e)
        {
            this.FormBorderStyle = FormBorderStyle.None;
            this.Activate();
            Application.DoEvents();
        }

        private void frmXmlConversion_Activated(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            this.FormBorderStyle = FormBorderStyle.None;
        }

        private void btnBro_Click(object sender, EventArgs e)
        {
            try
            {
                dlgOpenFile.Filter = "Excel File(*.xls)|*.xls";
                dlgOpenFile.ShowDialog();
                strFileName = dlgOpenFile.FileName;
                obj = new clsExcelToXML(strFileName, false);
                txtFileName.Text = strFileName;
                string[] sheetnames = obj.GetExcelSheetNames();
                cboSheetName.Items.Clear();
                cboSheetName.Items.AddRange(sheetnames);
                cboSheetName.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnConvert_Click(object sender, EventArgs e)
        {
            CreateXmlForBarcode();
            CreateXmlForCase();
        }

        private void CreateXmlForCase()
        {
            try
            {
                dsFile = new DataSet();
                DataTable dt = new DataTable();
                strSheetNameRange = cboSheetName.Text.ToString();

                dt = obj.GetDataTable(strSheetNameRange);

                if (dt.Rows.Count > 0)
                {
                    XmlWriterSettings contentTypesSettings = new XmlWriterSettings();
                    contentTypesSettings.Indent = true;

                    filepath = txtFileName.Text;
                    fileNameWithoutex = Path.Combine(Path.GetDirectoryName(filepath), Path.GetFileNameWithoutExtension(filepath));
                    Newxmlfilename = fileNameWithoutex + "_Case.xml";
                    XmlWriter xmlWriter = XmlWriter.Create(Newxmlfilename);

                    xmlWriter.WriteStartDocument(true);
                    xmlWriter.WriteStartElement("UNMAPPED_DATASET");

                    string chk = string.Empty;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (chk != dr["F1"].ToString())
                        {
                            if (chk != string.Empty)
                                chk = dr["F1"].ToString();

                            xmlWriter.WriteStartElement("CASE");
                            xmlWriter.WriteAttributeString("GTIN", dr["F1"].ToString());
                            xmlWriter.WriteAttributeString("ETN", dr["F5"].ToString());
                            xmlWriter.WriteEndElement();
                        }
                    }
                    if (xmlWriter != null)
                        xmlWriter.Flush();

                    xmlWriter.Close();

                }
                Functions.setMessageBox(Functions.gApp, "Both files exported Sucessfully", 1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void CreateXmlForBarcode()
        {
            try
            {
                dsFile = new DataSet();
                DataTable dt = new DataTable();
                strSheetNameRange = cboSheetName.Text.ToString();

                dt = obj.GetDataTable(strSheetNameRange);

                if (dt.Rows.Count > 0)
                {
                    XmlWriterSettings contentTypesSettings = new XmlWriterSettings();
                    contentTypesSettings.Indent = true;

                    filepath = txtFileName.Text;
                    fileNameWithoutex = Path.Combine(Path.GetDirectoryName(filepath), Path.GetFileNameWithoutExtension(filepath));
                    Newxmlfilename = fileNameWithoutex + "_Barcode.xml";
                    XmlWriter xmlWriter = XmlWriter.Create(Newxmlfilename);

                    xmlWriter.WriteStartDocument(true);
                    xmlWriter.WriteStartElement("UNMAPPED_DATASET");
                   
                    string chk = string.Empty;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (chk != dr["F6"].ToString())
                        {
                            if (chk != string.Empty)
                            chk = dr["F6"].ToString();
                          
                            xmlWriter.WriteStartElement("BOTTLE");
                            xmlWriter.WriteAttributeString("BARCODE", dr["F6"].ToString());
                            xmlWriter.WriteEndElement();
                        }
                    }
                    if (xmlWriter != null)
                        xmlWriter.Flush();

                    xmlWriter.Close();

                }
              
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }