public static void LoadExcelToClientsJson() { BBDataExtractClass.Client cl = new BBDataExtractClass.Client(); cl.Policies = new List <BBDataExtractClass.Policies>(); BBDataExtractClass.Config con = new BBDataExtractClass.Config(); con.BenefitOrder = new List <string>(); con.CategorySortOrder = new List <string>(); string collectionID = System.Configuration.ConfigurationSettings.AppSettings["collectionID"]; cl._id = @"ObjectId(""test"")"; foreach (string excelFile in System.Configuration.ConfigurationSettings.AppSettings["excelFile"].Split(';')) { if (string.IsNullOrEmpty(excelFile)) { continue; } try { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(System.Configuration.ConfigurationSettings.AppSettings["excelPath"] + excelFile, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; List <BBDataExtractClass.BenefitCards> benLst = new List <BBDataExtractClass.BenefitCards>(); BBDataExtractClass.Policies _pol = new BBDataExtractClass.Policies(); _pol.PolicyID = (getValue(xlWorksheet, 2, 2)); _pol.PolicyName = getValue(xlWorksheet, 1, 2); Console.WriteLine(string.Format("Policy id: {0}", getValue(xlWorksheet, 2, 2))); Console.WriteLine(string.Format("Policy Name: {0}", getValue(xlWorksheet, 1, 2))); Console.WriteLine(string.Format("Policy Points: {0}", getValue(xlWorksheet, 2, 6))); for (int i = 5; i <= 125; i++) { if (String.IsNullOrEmpty(getValue(xlWorksheet, i, 1))) { continue; } if ("ZZZCash Out" == getValue(xlWorksheet, i, 1)) { Console.Write("wait"); } if ("5731498185E" == getValue(xlWorksheet, i, 1)) { Console.Write(""); } Console.WriteLine(string.Format("Benefit ID: {0}", getValue(xlWorksheet, i, 1))); Console.WriteLine(string.Format("Benefit Card Image: {0}", getValue(xlWorksheet, i, 2))); Console.WriteLine(string.Format("Default value: {0}", getValue(xlWorksheet, i, 4))); Console.WriteLine(string.Format("Service Type: {0}", getValue(xlWorksheet, i, 5))); Console.WriteLine(string.Format("Card category: {0}", getValue(xlWorksheet, i, 6))); Console.WriteLine(string.Format("Card order: {0}", getValue(xlWorksheet, i, 7))); Console.WriteLine(string.Format("Card Title: {0}", getValue(xlWorksheet, i, 8))); Console.WriteLine(string.Format("Card Sequence: {0}", getValue(xlWorksheet, i, 9))); Console.WriteLine(string.Format("Card text/PDF content: {0}", getValue(xlWorksheet, i, 10))); Console.WriteLine(string.Format("Product Name: {0}", getValue(xlWorksheet, i, 20))); Console.WriteLine(string.Format("Sub Product Name: {0}", getValue(xlWorksheet, i, 21))); List <string> orBenefits = null; if (!String.IsNullOrEmpty(getValue(xlWorksheet, i, 11)) && getValue(xlWorksheet, i, 11).ToLowerInvariant() != "na") { orBenefits = new List <string>(); orBenefits.AddRange(getValue(xlWorksheet, i, 11).Split(';').ToList()); } List <string> andBenefits = null; if (!String.IsNullOrEmpty(getValue(xlWorksheet, i, 13)) && getValue(xlWorksheet, i, 13).ToLowerInvariant() != "na") { andBenefits = new List <string>(); andBenefits.AddRange(getValue(xlWorksheet, i, 13).Split(';').ToList()); } benLst.Add(new BBDataExtractClass.BenefitCards() { BenefitID = getValue(xlWorksheet, i, 1), Attribute = "", CashOutValue = Math.Round(0.0, 0), Category = getValue(xlWorksheet, i, 6), ServiceType = getValue(xlWorksheet, i, 5), ClientBenefitDesc = getValue(xlWorksheet, i, 10), ClientBenefitTitle = getValue(xlWorksheet, i, 8), ClientNo = getValue(xlWorksheet, i, 1).Substring(0, 4), HasQuantity = false, ImageURL = getValue(xlWorksheet, i, 2) + (getValue(xlWorksheet, i, 2).IndexOf(".") > 1 ? "" : ".png"), IsRecommended = false, LastUpdatedBy = "", OrBenefits = orBenefits, AndBenefits = andBenefits, Points = getValue(xlWorksheet, i, 4), ProdName = getValue(xlWorksheet, i, 31), SubProdName = getValue(xlWorksheet, i, 32), ProductNo = "0", SubProductNo = "0", cardSequence = Int32.Parse(getValue(xlWorksheet, i, 9)), ConsultantOnly = (getValue(xlWorksheet, i, 25).ToLowerInvariant() == "true" ? true : false), Hide = (getValue(xlWorksheet, i, 25).ToLowerInvariant() == "true" ? true : false) }); Console.WriteLine("\n\nNext \n\n"); } ///Add Cash out benefit /// //benLst.Add(new BBDataExtractClass.BenefitCards() //{ // BenefitID = benLst.FirstOrDefault().ClientNo + "99999999", // Attribute = "", // CashOutValue = 0, // Category = "Cash Out", // ClientBenefitDesc = "You have the option to convert some or all of your Flex points to cash. While a full cash-out of all points is generally not recommended, this offers the flexibility to gain access to additional discretionary funds.", // ClientBenefitTitle = "Trade Points for Cash", // ClientNo = benLst.FirstOrDefault().ClientNo, // HasQuantity = false, // ImageURL = "33.png", // IsRecommended = false, // LastUpdatedBy = "", // OrBenefits = new List<string>(), // AndBenefits = new List<string>(), // Points = "0", // ProdName = "0", // ProductNo = "0", // SubProductNo = "0", // cardSequence = benLst.Count //}); benLst = benLst.OrderBy(x => x.cardSequence).ToList(); _pol.Benefits = benLst; cl.ClientNo = benLst.FirstOrDefault().ClientNo; con.DisableBeforeWelcomeCall = true; con.BenefitOrder.AddRange(benLst.Select(i => i.BenefitID).ToList()); con.CategorySortOrder.AddRange(benLst.Select(i => i.Category).Distinct().ToList()); _pol.CashOut = new BBDataExtractClass.CashOut() { BasedOn = new List <string>() { "PointsCashedOut" }, CashOutRules = new List <BBDataExtractClass.CashOutRules>() { new BBDataExtractClass.CashOutRules() { // Amount = 1000, // Currency = "USD", JobCode = new List <int>(), PointsCashedOut = new List <double>() //{ 1,2,3,4,5,6,7,8} } } }; cl.Config = con; cl.Policies.Add(_pol); _pol.PDFSalutation = System.Configuration.ConfigurationSettings.AppSettings["PDFSalutation"]; xlWorkbook.Close(); xlApp.Quit(); } catch (Exception ex) { } } cl.Config.BenefitOrder = cl.Config.BenefitOrder.Distinct().ToList(); cl.Config.CategorySortOrder = cl.Config.CategorySortOrder.Distinct().ToList(); string outputStr = Newtonsoft.Json.JsonConvert.SerializeObject(cl, Newtonsoft.Json.Formatting.Indented, new DefaultJsonSerializer()); outputStr = outputStr.Replace("{\"_id\":\"ObjectId", "{\"_id\":ObjectId"); outputStr = outputStr.Replace("\\\"test\\\")\",", '"' + collectionID + '"' + "),"); WriteToAFileWithStreamWriter(outputStr); }
public static void LoadClientsJsonToExcel() { using (StreamReader r = new StreamReader(System.Configuration.ConfigurationSettings.AppSettings["JsonPath"] + System.Configuration.ConfigurationSettings.AppSettings["clientFile"])) { string json = r.ReadToEnd(); //BBDataExtractClass.Client cll = // Newtonsoft.Json.JsonConvert.DeserializeObject<BBDataExtractClass.Client>(json); //dynamic jsond = JObject.FromObject(cll); //jsond.id = cll._id; //var document = await Client.ReplaceDocumentAsync(cll.SelfLink, json); Regex rx = new Regex(@"NumberInt\((\d+)\)", RegexOptions.Multiline); String output = rx.Replace(json, "$1"); try { BBDataExtractClass.Client items = Newtonsoft.Json.JsonConvert.DeserializeObject <BBDataExtractClass.Client>(output); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.DisplayAlerts = false; //open excel file foreach (BBDataExtractClass.Policies pr in items.Policies) { Console.WriteLine(string.Format("Policy ID: {0}, Policy Name : {1}", pr.PolicyID, pr.PolicyName)); Microsoft.Office.Interop.Excel.Workbook workbk = excel.Workbooks.Open(System.Configuration.ConfigurationSettings.AppSettings["JsonPath"] + System.Configuration.ConfigurationSettings.AppSettings["clientExcelFile"], 0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, System.Reflection.Missing.Value, false, false, false); Microsoft.Office.Interop.Excel.Sheets xlsheets = workbk.Sheets; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsheets["Homeowner"]; //setValue(excelWorksheet, 3, 1, items.ClientNo); setValue(excelWorksheet, 2, 2, pr.PolicyID.ToString()); setValue(excelWorksheet, 1, 2, pr.PolicyName); int rowCount = 5; foreach (BBDataExtractClass.BenefitCards bc in pr.Benefits) { setValue(excelWorksheet, rowCount, 1, bc.BenefitID.ToString()); setValue(excelWorksheet, rowCount, 2, bc.ImageURL.ToString()); setValue(excelWorksheet, rowCount, 3, "Single Selection"); setValue(excelWorksheet, rowCount, 4, bc.Points.ToString()); setValue(excelWorksheet, rowCount, 5, bc.ServiceType.ToString()); setValue(excelWorksheet, rowCount, 6, bc.Category.ToString()); setValue(excelWorksheet, rowCount, 8, bc.ClientBenefitTitle); setValue(excelWorksheet, rowCount, 9, bc.cardSequence.ToString()); setValue(excelWorksheet, rowCount, 10, bc.ClientBenefitDesc.ToString()); setValue(excelWorksheet, rowCount, 11, string.Join(";", bc.OrBenefits).ToString()); setValue(excelWorksheet, rowCount, 13, string.Join(";", bc.AndBenefits).ToString()); setValue(excelWorksheet, rowCount, 31, bc.ProdName.ToString()); setValue(excelWorksheet, rowCount, 32, bc.SubProdName.ToString()); rowCount++; } excel.DisplayAlerts = false; workbk.SaveAs(System.Configuration.ConfigurationSettings.AppSettings["JsonPath"] + @"\Client_" + items.ClientNo + "_Policy_" + pr.PolicyID + ".xlsm", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing); // save workbk.Close(); excel.Quit();// close. dont forget this } } catch (Exception ex) { // throw; } } }