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); } }