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