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