private void mnbDownload_Click(object sender, RibbonControlEventArgs e) { //Check MNB service if (_mnbService.State.ToString() != "Created") { _mnbService = new MNBArfolyamServiceSoapClient(); _mnbService.Open(); } //Log to the database LogDatabaseDataSet.LogRow newLogRow = _logDatabaseDataSet.Log.NewLogRow(); newLogRow.Név = Environment.UserName; newLogRow.TimeStamp = DateTime.Now; _logDatabaseDataSet.Log.Rows.Add(newLogRow); _logTableAdapter.Update(_logDatabaseDataSet.Log); //Get currencies xml and map it to its model GetCurrenciesResponseBody currenciesXML = _mnbService.GetCurrencies(new GetCurrenciesRequestBody()); MNBCurrencies currenciesModel = (MNBCurrencies)_operatorService.XmlToModel <MNBCurrencies>(currenciesXML.GetCurrenciesResult); //Exchange rates query config GetExchangeRatesRequestBody getExchangeRatesRequestBody = new GetExchangeRatesRequestBody { startDate = "2015.01.01.", endDate = "2020.04.01.", currencyNames = string.Join(",", currenciesModel.Currencies) }; //Get exchange rates xml and map it to its model GetExchangeRatesResponseBody exchangeRatesXML = _mnbService.GetExchangeRates(getExchangeRatesRequestBody); MNBExchangeRates exchangeRatesModel = (MNBExchangeRates)_operatorService.XmlToModel <MNBExchangeRates>(exchangeRatesXML.GetExchangeRatesResult); //Create datatable from model and import it to Excel then save it DataTable dataTable = _operatorService.ModelToDataTable(exchangeRatesModel, currenciesModel); DataSet dataSet = new DataSet(); dataSet.Tables.Add(dataTable); _operatorService.DataSetToExcel(dataSet); //Format worksheet and save the workbook to the user's documents Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet; activeWorksheet.Range["A2:CV2"].NumberFormatLocal = ""; activeWorksheet.Range["B3:CV10000"].NumberFormatLocal = "0"; activeWorksheet.Range["A3:A10000"].NumberFormatLocal = "éééé\\.hh\\.nn\\."; string savePath = Directory.GetCurrentDirectory() + "\\arfolyam-letoltes.xlsx"; Globals.ThisAddIn.Application.ActiveWorkbook.SaveCopyAs(savePath); _mnbService.Close(); }
public DataTable ModelToDataTable(MNBExchangeRates exchangeRatesModel, MNBCurrencies currenciesModel) { DataTable exchangeRatesDataTable = new DataTable("exchangeRatesDataTable"); exchangeRatesDataTable.Clear(); //Columns exchangeRatesDataTable.Columns.Add("Dátum/ISO"); foreach (string currency in currenciesModel.Currencies) { exchangeRatesDataTable.Columns.Add(currency); } //First row (units) DataRow FirstRow = exchangeRatesDataTable.NewRow(); FirstRow["Dátum/ISO"] = "Egység"; foreach (MNBExchangeRatesDayRate rate in exchangeRatesModel.Day[0].Rate) { FirstRow[rate.curr] = rate.unit; } exchangeRatesDataTable.Rows.Add(FirstRow); //Rate rows foreach (MNBExchangeRatesDay day in exchangeRatesModel.Day) { DataRow RateRow = exchangeRatesDataTable.NewRow(); RateRow["Dátum/ISO"] = day.date.ToShortDateString(); foreach (MNBExchangeRatesDayRate rate in day.Rate) { RateRow[rate.curr] = rate.Value; } exchangeRatesDataTable.Rows.Add(RateRow); } return(exchangeRatesDataTable); }