public VMExcel ExcelToXml(HttpPostedFileBase file, string SheetName, string rootPath, string DirExcel, string DirXml) { VMExcel vMExcel = new VMExcel { OriginalExcelFileName = null, UploadedExcelFileName = null, Message = "You have not specified a excel file.", Status = PStatus.None }; if (file != null && file.ContentLength > 0) { try { string strdatetime = DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("HHmmss"); vMExcel.OriginalExcelFileName = Path.GetFileName(file.FileName); vMExcel.UploadedExcelFileName = strdatetime + "-" + vMExcel.OriginalExcelFileName; vMExcel.UploadedExcelFilePath = Path.Combine(rootPath, DirExcel, vMExcel.UploadedExcelFileName); file.SaveAs(vMExcel.UploadedExcelFilePath); DataTable dtexcel = new ExcelManagerByNetClasses().ReadExcel(vMExcel.UploadedExcelFilePath, SheetName); if (dtexcel.Rows.Count == 1) { vMExcel.Message = "Nothing in the excel sheet"; vMExcel.Status = PStatus.Failed; } else { List <string> PersonTag = new List <string>(new string[] { "<Persons>", "</Persons>", "<Person>", "</Person>" }); List <string> AddressTag = new List <string>(new string[] { "<Addresses>", "</Addresses>", "<Address>", "</Address>" }); List <string> ContactTag = new List <string>(new string[] { "<ContactInformation>", "</ContactInformation>", "<Contact>", "</Contact>" }); StringBuilder xml_fullcontent = new StringBuilder(); StringBuilder xml = new StringBuilder(); for (int row = 1; row < dtexcel.Rows.Count; row++) { // Assignment - starttag xml.Clear(); xml.Append(@"<?xml version=""1.0""?><Vendor_AssignmentRecord_v13 xmlns:xsd = ""http://www.w3.org/2001/XMLSchema"" xmlns:xsi = ""http://www.w3.org/2001/XMLSchema-instance"">"); #region Assignment Data // F1 - ClientName < CLIENTNAME ></ CLIENTNAME > xml.Append("<ClientName>" + Convert.ToString(dtexcel.Rows[row]["F1"]).Trim() + "</ClientName>"); // F2 - AssignmentTypeName < ASSIGNMENT_TYPE ></ ASSIGNMENT_TYPE > xml.Append("<AssignmentTypeName>" + Convert.ToString(dtexcel.Rows[row]["F2"]).Trim() + "</AssignmentTypeName>"); // F3 - AssignmentIdentity < ASSIGNMENTID ></ ASSIGNMENTID > string AssignmentIdentity = Convert.ToString(dtexcel.Rows[row]["F3"]).Trim(); xml.Append("<AssignmentIdentity>" + AssignmentIdentity + "</AssignmentIdentity>"); // F10 - AssignedCSR < CSR ></ CSR > //xml.Append("<AssignedCSR>" + Convert.ToString(dtexcel.Rows[row]["F10"]) + "</AssignedCSR>"); // F17 - ClientAccountNumber < CLIENTREFERENCENUMBER ></ CLIENTREFERENCENUMBER > xml.Append("<ClientAccountNumber>" + Convert.ToString(dtexcel.Rows[row]["F17"]).Trim() + "</ClientAccountNumber>"); // F18 - LoanNumber < CLIENTLOANNUMBER ></ CLIENTLOANNUMBER > xml.Append("<LoanNumber>" + Convert.ToString(dtexcel.Rows[row]["F18"]).Trim() + "</LoanNumber>"); // F34 - LienHolder < LienHolder ></ LienHolder > xml.Append("<LienHolder>" + Convert.ToString(dtexcel.Rows[row]["F34"]).Trim() + "</LienHolder>"); // F19 - Year < YEAR ></ YEAR > xml.Append("<Year>" + Convert.ToString(dtexcel.Rows[row]["F19"]).Trim() + "</Year>"); // F20 - Make < MAKE ></ MAKE > xml.Append("<Make>" + Convert.ToString(dtexcel.Rows[row]["F20"]).Trim() + "</Make>"); // F21 - Model < MODEL ></ MODEL > xml.Append("<Model>" + Convert.ToString(dtexcel.Rows[row]["F21"]).Trim() + "</Model>"); // F22 - Vin < VINSERIALNUM ></ VINSERIALNUM > xml.Append("<Vin>" + Convert.ToString(dtexcel.Rows[row]["F22"]).Trim() + "</Vin>"); // F31 - ClientContact < CLIENT_CONTACT ></ CLIENT_CONTACT > xml.Append("<ClientContact>" + Convert.ToString(dtexcel.Rows[row]["F31"]).Trim() + "</ClientContact>"); // F32 - CollateralType < COLLATERALNAME ></ COLLATERALNAME > xml.Append("<CollateralType>" + Convert.ToString(dtexcel.Rows[row]["F32"]).Trim() + "</CollateralType>"); // F33 - ClientCompanyID < CLIENTID ></ CLIENTID > xml.Append("<ClientCompanyID>" + Convert.ToString(dtexcel.Rows[row]["F33"]).Trim() + "</ClientCompanyID>"); #endregion #region Persons if (Convert.ToString(dtexcel.Rows[row]["F23"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F24"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), PersonTag[0])) { xml.Append(PersonTag[0]); } #region Maker // Maker - starttag xml.Append(PersonTag[2]); // F23 - FirstName < CUSTOMER ></ CUSTOMER > xml.Append("<First_Name>" + Convert.ToString(dtexcel.Rows[row]["F23"]).Trim() + "</First_Name>"); // F24 - SSN < Customer SSN ></ Customer SSN > xml.Append("<SSN>" + Convert.ToString(dtexcel.Rows[row]["F24"]).Trim() + "</SSN>"); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // Maker - endtag xml.Append(PersonTag[3]); #endregion } if (Convert.ToString(dtexcel.Rows[row]["F27"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F29"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), PersonTag[0])) { xml.Append(PersonTag[0]); } #region Co-Maker // Co-Maker - starttag xml.Append(PersonTag[2]); // F27 - FirstName < Comaker ></ Comaker > xml.Append("<First_Name>" + Convert.ToString(dtexcel.Rows[row]["F27"]).Trim() + "</First_Name>"); // F29 - SSN < Comaker SSN ></ Comaker SSN > xml.Append("<SSN>" + Convert.ToString(dtexcel.Rows[row]["F29"]).Trim() + "</SSN>"); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // Co-Maker - endtag xml.Append(PersonTag[3]); #endregion } if (StaticUtilities.IsSubstringValueExist(xml.ToString(), PersonTag[0])) { xml.Append(PersonTag[1]); } #endregion #region Addresses #region Maker Address // Maker Address 1 if (Convert.ToString(dtexcel.Rows[row]["F4"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F5"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F6"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F7"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F9"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Maker Address 1 // Maker Address 1 - starttag xml.Append(AddressTag[2]); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // F4 - AddressLine1 < maker_ADDRESS ></ maker_ADDRESS > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F4"]).Trim() + "</Address_Line_1>"); // F5 - City < maker_CITY ></ maker_CITY > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F5"]).Trim() + "</City>"); // F6 - State < maker_STATE ></ maker_STATE > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F6"]).Trim() + "</State>"); // F7 - ZipCode < maker_ZIP ></ maker_ZIP > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F7"]).Trim() + "</Zip_Code>"); // F9 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F9"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Address 1 - endtag xml.Append(AddressTag[3]); #endregion } // Maker Address 2 if (Convert.ToString(dtexcel.Rows[row]["F35"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F36"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F37"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F38"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F40"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Maker Address 2 // Maker Address 2 - starttag xml.Append(AddressTag[2]); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // F35 - AddressLine1 < maker_ADDRESS_2 ></ maker_ADDRESS_2 > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F35"]).Trim() + "</Address_Line_1>"); // F36 - City < maker_CITY_2 ></ maker_CITY_2 > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F36"]).Trim() + "</City>"); // F37 - State < maker_STATE_2 ></ maker_STATE_2 > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F37"]).Trim() + "</State>"); // F38 - ZipCode < maker_ZIP_2 ></ maker_ZIP_2 > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F38"]).Trim() + "</Zip_Code>"); // F40 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F40"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Address 2 - endtag xml.Append(AddressTag[3]); #endregion } #endregion #region Co-Maker Address // Co-Maker Address 1 if (Convert.ToString(dtexcel.Rows[row]["F11"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F12"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F13"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F14"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F16"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Co-Maker Address 1 // Co-Maker Address 1 - starttag xml.Append(AddressTag[2]); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // F11 - AddressLine1 < comaker_ADDRESS_1 ></ comaker_ADDRESS_1 > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F11"]).Trim() + "</Address_Line_1>"); // F12 - City < comaker_CITY_1 ></ comaker_CITY_1 > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F12"]).Trim() + "</City>"); // F13 - State < comaker_STATE_1 ></ comaker_STATE_1 > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F13"]).Trim() + "</State>"); // F14 - ZipCode < comaker_ZIP_1 ></ comaker_ZIP_1 > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F14"]).Trim() + "</Zip_Code>"); // F16 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F16"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Address 1 - endtag xml.Append(AddressTag[3]); #endregion } // Co-Maker Address 2 if (Convert.ToString(dtexcel.Rows[row]["F41"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F42"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F43"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F44"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F46"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Co-Maker Address 2 // Co-Maker Address 2 - starttag xml.Append(AddressTag[2]); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // F41 - AddressLine1 < comaker_ADDRESS_2 ></ comaker_ADDRESS_2 > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F41"]).Trim() + "</Address_Line_1>"); // F42 - City < comaker_CITY_2 ></ comaker_CITY_2 > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F42"]).Trim() + "</City>"); // F43 - State < comaker_STATE_2 ></ comaker_STATE_2 > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F43"]).Trim() + "</State>"); // F44 - ZipCode < comaker_ZIP_2 ></ comaker_ZIP_2 > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F44"]).Trim() + "</Zip_Code>"); // F46 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F46"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Address 2 - endtag xml.Append(AddressTag[3]); #endregion } #endregion #region Address without Person // Address 1 if (Convert.ToString(dtexcel.Rows[row]["F47"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F48"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F49"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F50"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F52"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Address 1 // Address 1 - starttag xml.Append(AddressTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F47 - AddressLine1 < ADDRESS_1 ></ ADDRESS_1 > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F47"]).Trim() + "</Address_Line_1>"); // F48 - City < CITY_1 ></ CITY_1 > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F48"]).Trim() + "</City>"); // F49 - State < STATE_1 ></ STATE_1 > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F49"]).Trim() + "</State>"); // F50 - ZipCode < ZIP_1 ></ ZIP_1 > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F50"]).Trim() + "</Zip_Code>"); // F52 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F52"]).Trim() + "</Debtor_Address_Type_Name>"); // Address 1 - endtag xml.Append(AddressTag[3]); #endregion } // Address 2 if (Convert.ToString(dtexcel.Rows[row]["F53"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F54"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F55"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F56"]).Trim() != "" || Convert.ToString(dtexcel.Rows[row]["F58"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[0]); } #region Address 2 // Address 2 - starttag xml.Append(AddressTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F53 - AddressLine1 < ADDRESS_2 ></ ADDRESS_2 > xml.Append("<Address_Line_1>" + Convert.ToString(dtexcel.Rows[row]["F53"]).Trim() + "</Address_Line_1>"); // F54 - City < CITY_2 ></ CITY_2 > xml.Append("<City>" + Convert.ToString(dtexcel.Rows[row]["F54"]).Trim() + "</City>"); // F55 - State < STATE_2 ></ STATE_2 > xml.Append("<State>" + Convert.ToString(dtexcel.Rows[row]["F55"]).Trim() + "</State>"); // F56 - ZipCode < ZIP_2 ></ ZIP_2 > xml.Append("<Zip_Code>" + Convert.ToString(dtexcel.Rows[row]["F56"]).Trim() + "</Zip_Code>"); // F58 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F58"]).Trim() + "</Debtor_Address_Type_Name>"); // Address 2 - endtag xml.Append(AddressTag[3]); #endregion } #endregion if (StaticUtilities.IsSubstringValueExist(xml.ToString(), AddressTag[0])) { xml.Append(AddressTag[1]); } #endregion #region Phone Numbers #region Maker Phone Number // Maker Phone Number 1 if (Convert.ToString(dtexcel.Rows[row]["F8"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Maker Phone Number 1 // Maker Phone Number 1 - starttag xml.Append(ContactTag[2]); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // F8 - PhoneNumber < maker Phone_1 ></< maker Phone_1 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F8"]).Trim() + "</Phone_Number>"); // F9 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F9"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Phone Number 1 - endtag xml.Append(ContactTag[3]); #endregion } // Maker Phone Number without Address if (Convert.ToString(dtexcel.Rows[row]["F26"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Maker Phone Number // Maker Phone Number - starttag xml.Append(ContactTag[2]); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // F26 - PhoneNumber < Maker_Phone_Number ></ Maker_Phone_Number > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F26"]).Trim() + "</Phone_Number>"); // DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name></Debtor_Address_Type_Name>"); // Maker Phone Number - endtag xml.Append(ContactTag[3]); #endregion } // Maker Phone Number 2 if (Convert.ToString(dtexcel.Rows[row]["F39"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Maker Phone Number 2 // Maker Phone Number 2 - starttag xml.Append(ContactTag[2]); // F25 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F25"]).Trim() + "</Debtor_Type_Name>"); // F39 - PhoneNumber < maker Phone_2 ></< maker Phone_2 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F39"]).Trim() + "</Phone_Number>"); // F40 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F40"]).Trim() + "</Debtor_Address_Type_Name>"); // Maker Phone Number 2 - endtag xml.Append(ContactTag[3]); #endregion } #endregion #region Co-Maker Phone Number // Co Maker Phone Number 1 if (Convert.ToString(dtexcel.Rows[row]["F15"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Co Maker Phone Number 1 // Co Maker Phone Number 1 - starttag xml.Append(ContactTag[2]); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // F15 - PhoneNumber < maker Phone_1 ></< maker Phone_1 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F15"]).Trim() + "</Phone_Number>"); // F16 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F16"]).Trim() + "</Debtor_Address_Type_Name>"); // Co Maker Phone Number 1 - endtag xml.Append(ContactTag[3]); #endregion } // Co Maker Phone Number without Address if (Convert.ToString(dtexcel.Rows[row]["F30"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Co Maker Phone Number // Co Maker Phone Number - starttag xml.Append(ContactTag[2]); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // F30 - PhoneNumber < CoMaker_Phone_Number ></ CoMaker_Phone_Number > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F30"]).Trim() + "</Phone_Number>"); // DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name></Debtor_Address_Type_Name>"); // Co Maker Phone Number - endtag xml.Append(ContactTag[3]); #endregion } // Co Maker Phone Number 2 if (Convert.ToString(dtexcel.Rows[row]["F45"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Co Maker Phone Number 2 // Co Maker Phone Number 2 - starttag xml.Append(ContactTag[2]); // F28 - DebtorTypeName < Person Type ></ Person Type > xml.Append("<Debtor_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F28"]).Trim() + "</Debtor_Type_Name>"); // F45 - PhoneNumber < maker Phone_2 ></< maker Phone_2 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F45"]).Trim() + "</Phone_Number>"); // F46 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F46"]).Trim() + "</Debtor_Address_Type_Name>"); // Co Maker Phone Number 2 - endtag xml.Append(ContactTag[3]); #endregion } #endregion #region Phone Number without Person // Phone Number 1 if (Convert.ToString(dtexcel.Rows[row]["F51"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Phone Number 1 // Phone Number 1 - starttag xml.Append(ContactTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F51 - PhoneNumber < maker Phone_1 ></< maker Phone_1 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F51"]).Trim() + "</Phone_Number>"); // F52 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F52"]).Trim() + "</Debtor_Address_Type_Name>"); // Phone Number 1 - endtag xml.Append(ContactTag[3]); #endregion } // Phone Number 2 if (Convert.ToString(dtexcel.Rows[row]["F57"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Phone Number 2 // Phone Number 2 - starttag xml.Append(ContactTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F57 - PhoneNumber < maker Phone_2 ></< maker Phone_2 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F57"]).Trim() + "</Phone_Number>"); // F58 - DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name>" + Convert.ToString(dtexcel.Rows[row]["F58"]).Trim() + "</Debtor_Address_Type_Name>"); // Phone Number 2 - endtag xml.Append(ContactTag[3]); #endregion } #endregion #region Phone Number without Person and also without Address // Phone Number 1 if (Convert.ToString(dtexcel.Rows[row]["F59"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Phone Number 1 // Phone Number 1 - starttag xml.Append(ContactTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F59 - PhoneNumber < maker Phone_1 ></< maker Phone_1 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F59"]).Trim() + "</Phone_Number>"); // DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name></Debtor_Address_Type_Name>"); // Phone Number 1 - endtag xml.Append(ContactTag[3]); #endregion } // Phone Number 2 if (Convert.ToString(dtexcel.Rows[row]["F60"]).Trim() != "") { if (!StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[0]); } #region Phone Number 2 // Phone Number 2 - starttag xml.Append(ContactTag[2]); // DebtorTypeName xml.Append("<Debtor_Type_Name></Debtor_Type_Name>"); // F60 - PhoneNumber < maker Phone_2 ></< maker Phone_2 > xml.Append("<Phone_Number>" + Convert.ToString(dtexcel.Rows[row]["F60"]).Trim() + "</Phone_Number>"); // DebtorAddressTypeName < Address Type ></ Address Type > xml.Append("<Debtor_Address_Type_Name></Debtor_Address_Type_Name>"); // Phone Number 2 - endtag xml.Append(ContactTag[3]); #endregion } #endregion if (StaticUtilities.IsSubstringValueExist(xml.ToString(), ContactTag[0])) { xml.Append(ContactTag[1]); } #endregion // Assignment - endtag xml.Append("</Vendor_AssignmentRecord_v13>"); xml_fullcontent.Append(xml.ToString()); // create the file vMExcel.ConvertedXmlFileName = DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("HHmmssfff") + "-" + new RandomGenerator().RandomGuid() + "-" + Path.GetFileNameWithoutExtension(vMExcel.OriginalExcelFileName) + ".xml"; string strxmlDir = Path.Combine(rootPath, DirXml, strdatetime); if (!Directory.Exists(strxmlDir)) { Directory.CreateDirectory(strxmlDir); } vMExcel.ConvertedXmlFilePath = Path.Combine(strxmlDir, vMExcel.ConvertedXmlFileName); if (!new XmlService().CreateXml(xml.ToString(), vMExcel.ConvertedXmlFilePath)) { vMExcel.Message = "Unable to convert. Please check excel content"; vMExcel.Status = PStatus.Failed; break; } } vMExcel.ConvertedXmlContent = xml_fullcontent.ToString(); //vMExcel.ConvertedXmlDownloadLink = Path.Combine(DirXml, vMExcel.ConvertedXmlFileName); if (vMExcel.Status == PStatus.None) { vMExcel.Message = "File successfully converted to xml"; vMExcel.Status = PStatus.Success; } } } catch (Exception ex) { vMExcel.Message = "ERROR:" + ex.Message.ToString(); vMExcel.Status = PStatus.Error; } } return(vMExcel); }