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