/// <summary>
/// Deprecated Method for adding a new object to the ExchangeRates EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead.
/// </summary>
public void AddToExchangeRates(ExchangeRate exchangeRate)
{
base.AddObject("ExchangeRates", exchangeRate);
}
/// <summary>
/// Create a new ExchangeRate object.
/// </summary>
/// <param name="date">Initial value of the Date property.</param>
/// <param name="baseCurrencyCode">Initial value of the BaseCurrencyCode property.</param>
/// <param name="variableCurrencyCode">Initial value of the VariableCurrencyCode property.</param>
/// <param name="bidPrice">Initial value of the BidPrice property.</param>
/// <param name="askPrice">Initial value of the AskPrice property.</param>
public static ExchangeRate CreateExchangeRate(global::System.DateTime date, global::System.String baseCurrencyCode, global::System.String variableCurrencyCode, global::System.Decimal bidPrice, global::System.Decimal askPrice)
{
ExchangeRate exchangeRate = new ExchangeRate();
exchangeRate.Date = date;
exchangeRate.BaseCurrencyCode = baseCurrencyCode;
exchangeRate.VariableCurrencyCode = variableCurrencyCode;
exchangeRate.BidPrice = bidPrice;
exchangeRate.AskPrice = askPrice;
return exchangeRate;
}
public static void UploadExchangeBidAskRateExcel(string filePath)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
// DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
const int TOTAL_NUM_CURRENCY = 9;
excelReader.IsFirstRowAsColumnNames = false;
DataSet result = excelReader.AsDataSet();
DataTable dt = result.Tables[0];
DateTime datetime;
string baseCurrency;
string variableCurrency;
int targetFirstColumn;
List<ExchangeRate> exchangeRateList = new List<ExchangeRate>();
for(int i = 0; i < TOTAL_NUM_CURRENCY; i++){
targetFirstColumn = i * 4;
string currencyPair = dt.Rows[0][targetFirstColumn].ToString().Split(' ')[0];
baseCurrency = currencyPair.Substring(0, 3);
variableCurrency = currencyPair.Substring(3, 3);
foreach (DataRow dr in dt.Rows)
{
if (dt.Rows.IndexOf(dr) == 0 || dt.Rows.IndexOf(dr) == 1)
continue;
datetime = FromExcelSerialDate(Convert.ToInt32(dr[targetFirstColumn]));
exchangeRateList.Add(new FXEntities.ExchangeRate()
{
BaseCurrencyCode = baseCurrency,
VariableCurrencyCode = variableCurrency,
Date = datetime,
BidPrice = Convert.ToDecimal(Convert.ToDouble(dr[targetFirstColumn + 1])),
AskPrice = Convert.ToDecimal(Convert.ToDouble(dr[targetFirstColumn + 2]))
});
}
}
DateTime minDate = exchangeRateList.Select(e => e.Date).Min();
DateTime maxDate = exchangeRateList.Select(e => e.Date).Max();
var availableDates = Util.DateTimeHelper.GetWeekdaysDate(
minDate, maxDate );
foreach (var currency in exchangeRateList.Select(e => e.VariableCurrencyCode).Distinct().Where(c => c != "EUR").ToList())
{
var insertedDates = exchangeRateList
.Where(e => e.VariableCurrencyCode == currency).Select(e => e.Date);
var leftDays = availableDates.Except(insertedDates);
foreach (var leftDay in leftDays)
{
if (leftDay == minDate)
continue;
var lastRecordBeforeLeftDay = exchangeRateList
.Where(e => e.VariableCurrencyCode == currency &&
e.Date == insertedDates.Where(d => d < leftDay).Max()).FirstOrDefault();
if (lastRecordBeforeLeftDay == null)
throw new Exception("Cannot find record to replace");
else
{
ExchangeRate newExchangeRate = new ExchangeRate()
{
BaseCurrencyCode = lastRecordBeforeLeftDay.BaseCurrencyCode,
VariableCurrencyCode = lastRecordBeforeLeftDay.VariableCurrencyCode,
BidPrice = lastRecordBeforeLeftDay.BidPrice,
AskPrice = lastRecordBeforeLeftDay.AskPrice,
Date = leftDay
};
exchangeRateList.Add(newExchangeRate);
}
}
}
using (FXEntities.FXEntities fxEntities = new FXEntities.FXEntities())
{
exchangeRateList.ForEach(e => fxEntities.AddToExchangeRates(e));
fxEntities.SaveChanges();
}
}
stream.Close();
}
