void SaveXLSLocation(string filename, IGLNLocation iGLNLocation)
{
var stream = File.Open(filename, FileMode.Open, FileAccess.ReadWrite);
IExcelDataReader excelReader;
if (filename.Contains(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
stream.Close();
excelReader.IsFirstRowAsColumnNames = true;
var dataset = excelReader.AsDataSet();
var rowCollection = dataset.Tables[0].Rows;
foreach (DataRow row in rowCollection)
{
if (row[11].ToString().Contains(iGLNLocation.GLN))
{
var printed = (string)row[13];
printed = Regex.Replace(printed, "False", "True", RegexOptions.IgnoreCase);
row[13] = printed;
break;
}
}
try
{
if (File.Exists(filename))
{
File.Delete(filename);
}
stream = File.Open(filename, FileMode.Create, 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 + " :6", ex.ToString(), MethodBase.GetCurrentMethod().Name, ExceptionHelper.LineNumber(ex), GetType().Name);
}
}
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);
}