XDocument TransformXLSToXML(string filename)
        {
            XDocument xDoc = null;

            try
            {
                var stream = File.Open(filename, FileMode.Open, FileAccess.ReadWrite);
                IExcelDataReader excelReader;

                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                // excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                stream.Close();

                excelReader.IsFirstRowAsColumnNames = true;
                var dataset = excelReader.AsDataSet();
                var builder = new StringBuilder();

                List <object> tableCollection = new List <object>();
                foreach (DataTable dt in dataset.Tables)
                {
                    tableCollection.Add(dt);
                }


                var table       = dataset.Tables[0];
                var hasPrintCol = true;
                // if (table.Columns.Count == 13)
                if (table.Columns.Count == 8)
                {
                    table.Columns.Add("Printed", typeof(string), "False");
                    hasPrintCol = false;
                }

                foreach (DataRow row in dataset.Tables[0].Rows)
                {
                    var temp = string.Empty;

                    if (row.ItemArray[1].ToString() != "Region")
                    {
                        var glnlocation = new GLNLocation()
                        {
                            Region   = row.ItemArray[0].ToString(),
                            Site     = row.ItemArray[1].ToString(),
                            Building = row.ItemArray[2].ToString(),
                            Floor    = row.ItemArray[3].ToString(),
                            Room     = row.ItemArray[4].ToString(),
                            Code     = row.ItemArray[5].ToString(),
                            GLN      = row.ItemArray[6].ToString(),
                            Date     = Convert.ToDateTime(row.ItemArray[7])
                        };
                        if (hasPrintCol)
                        {
                            glnlocation.Printed = Convert.ToBoolean(row.ItemArray[8]);
                        }

                        // glnlocation.Region = row.ItemArray[1].ToString();
                        // glnlocation.Site = row.ItemArray[3].ToString();
                        // glnlocation.Building = row.ItemArray[5].ToString();
                        // glnlocation.Floor = row.ItemArray[7].ToString();
                        // glnlocation.Room = row.ItemArray[9].ToString();
                        // glnlocation.Code = row.ItemArray[10].ToString();
                        // glnlocation.GLN = row.ItemArray[11].ToString();
                        // glnlocation.Date = Convert.ToDateTime(row.ItemArray[12]);
                        // if (hasPrintCol)
                        //    glnlocation.Printed = Convert.ToBoolean(row.ItemArray[13]);

                        // clean up site for commas etc
                        glnlocation.Site = glnlocation.Site.TrimStart(' ', '"');
                        glnlocation.Site = glnlocation.Site.TrimEnd('"');
                        glnlocation.Site = glnlocation.Site.Replace(",", " ");

                        temp = glnlocation.Value();

                        // remove unnecessary spaces from the end of the string
                        while (temp.EndsWith(" "))
                        {
                            temp = temp.Remove(temp.LastIndexOf(' '), 1);
                        }


                        // remove unnecessary commas from the end of the string
                        while (temp.EndsWith(","))
                        {
                            temp = temp.Remove(temp.LastIndexOf(','), 1);
                        }


                        // add the printed true/false flag to each now
                        if (!temp.EndsWith("True", StringComparison.CurrentCultureIgnoreCase) &&
                            !temp.EndsWith("False", StringComparison.CurrentCultureIgnoreCase))
                        {
                            temp = temp + ",False\r\n";
                        }

                        builder.AppendLine(temp);
                    }
                    else
                    {
                        try
                        {
                            row["Printed"] = "Printed";
                        }
                        catch (Exception ex)
                        {
                            // call LogFile method and pass argument as Exception message, event name, control name, error line number, current form name
                            // LogFile(ex.Message+" :1", ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), GetType().Name);
                        }
                    }
                }

                dataset.Tables.Clear();
                dataset.Tables.Add(table);
                tableCollection.Remove(table);
                foreach (DataTable dt in tableCollection)
                {
                    dataset.Tables.Add(dt);
                }


                var location = new XElement("Root",
                                            from str in builder.ToString().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
                                            let fields = str.Split(',')

                                                         select new XElement("GLNLocation",
                                                                             new XElement("Region", fields[0]),
                                                                             new XElement("Site", fields[1]),
                                                                             new XElement("Building", fields[2]),
                                                                             new XElement("Floor", fields[3]),
                                                                             new XElement("Room", fields[4]),
                                                                             new XElement("Code", fields[5]),
                                                                             new XElement("GLN", fields[6]),
                                                                             new XElement("GLNCreationDate", fields[7]),
                                                                             new XElement("Printed", fields[8])));

                var schemaSet = AddXMLSchema();
                xDoc = XDocument.Parse(location.ToString());
                if (xDoc == null | xDoc.Root == null)
                {
                    throw new ApplicationException("xml error: the referenced stream is not xml.");
                }

                xDoc.Validate(schemaSet, (o, e) =>
                {
                    throw new ApplicationException("xsd validation error: xml file has structural problems");
                });

                try
                {
                    if (File.Exists(filename))
                    {
                        File.Delete(filename);
                    }

                    stream = File.Open(filename, FileMode.CreateNew, FileAccess.ReadWrite);
                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                        ExcelFunctions.WriteExcelFile(dataset, document);


                    stream.Close();
                }
                catch (Exception ex)
                {
                    // call LogFile method and pass argument as Exception message, event name, control name, error line number, current form name
                    // LogFile(ex.Message + " :2", ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), GetType().Name);
                }
            }
            catch (IndexOutOfRangeException ex)
            {
                // call LogFile method and pass argument as Exception message, event name, control name, error line number, current form name
                // LogFile(ex.Message + " :3", ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), GetType().Name);
            }

            return(xDoc);
        }
        public async void LoadFile(string filename)
        {
            var    xmlString = string.Empty;
            object res       = null;

            try
            {
                AndHUD.Shared.Show(this, "Loading...", -1, MaskType.Black);
                Func <string> function = new Func <string>(() => LoadLocations(filename));
                res = await Task.Factory.StartNew <string>(function);

                AndHUD.Shared.Dismiss(this);
                res = LoadLocations(filename);

                var xDoc = XDocument.Parse((string)res);
                if (xDoc != null)
                {
                    var xName = XName.Get("GLNLocation");

                    foreach (XElement xElem in xDoc.Descendants("GLNLocation"))
                    {
                        IGLNLocation location = new GLNLocation()
                        {
                            Region   = xElem.Element("Region").Value,
                            Site     = xElem.Element("Site").Value,
                            Building = xElem.Element("Building").Value,
                            Floor    = xElem.Element("Floor").Value,
                            Room     = xElem.Element("Room").Value,
                            Code     = xElem.Element("Code").Value,
                            GLN      = xElem.Element("GLN").Value,
                            Date     = Convert.ToDateTime(xElem.Element("GLNCreationDate").Value),
                            Printed  = Convert.ToBoolean(xElem.Element("Printed").Value),
                            ToPrint  = false
                        };
                        if (!locationList.Contains(location))
                        {
                            locationList.Add(location);
                        }
                    }

                    try
                    {
                        locationsView.Adapter = new CheckListCustomArrayAdapter(Android.App.Application.Context, Android.Resource.Layout.SimpleListItemChecked, locationList);
                        if (((CheckListCustomArrayAdapter)locationsView.Adapter).CheckForItemsToPrint())
                        {
                            FindViewById <Button>(GLNLabelPrint.Resource.Id.PrintButton).Enabled = true;
                        }
                    }
                    catch (Exception ex)
                    {
                        // call LogFile method and pass argument as Exception message, event name, control name, error line number, current form name
                        fileUtility.LogFile(ex.Message, ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), Class.SimpleName);
                    }
                }
                else
                {
                    var dialogBuilder = new AlertDialog.Builder(this);

                    dialogBuilder.SetTitle("File Error");
                    dialogBuilder.SetMessage("There was a problem loading this file. Please choose another file, or correct the error and try again.");
                    dialogBuilder.SetIcon(Android.Resource.Drawable.IcDialogAlert);
                    dialogBuilder.SetPositiveButton(Android.Resource.String.Ok, delegate { });
                    dialogBuilder.Show();
                }
            }
            catch (Exception ex)
            {//call LogFile method and pass argument as Exception message, event name, control name, error line number, current form name
                fileUtility.LogFile(ex.Message, ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), GetType().Name);
            }
        }