private static bool CreateExcel(MarketValueInfo marketValue)
{
bool bRtnVal = false;
Application excelApp = null;
Workbook workBook = null;
Worksheet workSheet = null;
try
{
var fileName = String.Format("{0}{1}.xlsx", PortfoliExcelPath, excelFileName);
if (File.Exists(fileName))
{
File.Delete(fileName);
}
excelApp = new Application();
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
workSheet = workBook.Sheets[1];
PropertyInfo[] properties = marketValue.GetType().GetProperties();
int ncol = 1;
foreach (PropertyInfo property in properties)
{
workSheet.Cells[1, ncol] = property.Name;
workSheet.Cells[1, ncol].Font.Bold = true;
ncol++;
}
//To freeze first rwo
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
excelApp.DisplayAlerts = false;
workBook.Save();
excelApp.ActiveWorkbook.SaveAs(fileName);
workBook.Close(0);
excelApp.Quit();
ApplicationLog.Instance.WriteInfo(String.Format("Excel file creation completed at '{0}'", DateTime.Now));
bRtnVal = true;
}
catch (Exception ex)
{
ApplicationLog.Instance.WriteException(ex);
}
finally
{
if (workSheet != null)
{
Marshal.ReleaseComObject(workSheet);
}
if (workBook != null)
{
Marshal.ReleaseComObject(workBook);
}
if (excelApp != null)
{
Marshal.ReleaseComObject(excelApp);
}
}
return(bRtnVal);
}
private static MarketValueInfo GetTickerInfo(StockRequestInfo stockRequestInfo)
{
MarketValueInfo rtnVal = null;
try
{
HttpWebRequest req = HttpWebRequest.Create(stockRequestInfo.URL) as HttpWebRequest;
req.Accept = "*/*";
req.Host = "nseindia.com";
req.UserAgent = "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0";
req.Method = "GET";
req.KeepAlive = true;
String strResult = String.Empty;
using (HttpWebResponse res = req.GetResponse() as HttpWebResponse)
{
using (Stream s = res.GetResponseStream())
{
using (StreamReader sr = new StreamReader(s))
{
strResult = sr.ReadToEnd().ToString().Trim();
strResult = strResult.Substring(strResult.IndexOf("futLink"));
int nt = strResult.IndexOf("</div>");
strResult = strResult.Substring(0, nt);
strResult = strResult.Substring(strResult.IndexOf("[{"));
nt = strResult.IndexOf("}]");
strResult = strResult.Substring(2, nt);
var jsonLikeString = strResult.Split(new String[] { "\",\"" }, StringSplitOptions.None);
Dictionary <String, String> dTemp = new Dictionary <string, string>();
foreach (String str in jsonLikeString)
{
String str1 = str.Replace("\\", String.Empty);
str1 = str1.Replace("\"", String.Empty);
dTemp.Add(str1.Split(':')[0].Trim().ToUpper(), str1.Split(':')[1].Trim());
}
if (dTemp.Count > 0)
{
rtnVal = new MarketValueInfo();
PropertyInfo[] props = rtnVal.GetType().GetProperties();
foreach (PropertyInfo prop in props)
{
try
{
String strValueToConvert = String.Empty;
if (dTemp.ContainsKey(prop.Name))
{
strValueToConvert = numbersOnlyRegEx.Replace(dTemp[prop.Name], String.Empty);
}
Object propValue = null;
TypeConverter typeConverter = TypeDescriptor.GetConverter(prop.PropertyType);
try
{
propValue = typeConverter.ConvertFrom(strValueToConvert);
}
catch
{
if (typeConverter.CanConvertTo(prop.PropertyType) && dTemp.ContainsKey(prop.Name))
{
if (typeConverter.IsValid(strValueToConvert))
{
propValue = typeConverter.ConvertTo(null, System.Globalization.CultureInfo.CurrentCulture, strValueToConvert, prop.PropertyType);
}
}
}
rtnVal.GetType().GetProperty(prop.Name).SetValue(rtnVal, propValue, null);
}
catch (Exception ex)
{
ApplicationLog.Instance.WriteException(ex);
}
}
#region keeping for ref only
//String previousClose = dTemp["previousClose"];
//String open = dTemp["open"];
//String dayHigh = dTemp["dayHigh"];
//String dayLow = dTemp["dayLow"];
//String lastPrice = dTemp["lastPrice"];
//String closePrice = dTemp["closePrice"];
//String averagePrice = dTemp["averagePrice"];
//String quantityTraded = dTemp["quantityTraded"];
#endregion
}
else
{
throw new Exception(String.Format("Webrequest failed for symbol: {0}", stockRequestInfo.Ticker));
}
}
}
}
ApplicationLog.Instance.WriteDebug(String.Format("Symbol read done: '{0}'", stockRequestInfo.Ticker));
}
catch (Exception ex)
{
ApplicationLog.Instance.WriteError(String.Format("Error in ticker : '{0}'", stockRequestInfo.Ticker));
ApplicationLog.Instance.WriteException(ex);
}
return(rtnVal);
}
public bool RunApplication()
{
bool bRtnVal = false;
try
{
if (!File.Exists(requestFilePath) || StockMarketRunnerStatus.CreateRequestFileBasedOnStatus())
{
CreateRequestFile();
}
StockRequestInfos stockInfosFromFile = SerializationHelper.LoadXML <StockRequestInfos>(requestFilePath);
if (stockInfosFromFile != null)
{
List <StockRequestInfo> listOfStockInfos = stockInfosFromFile.ListOfStockRequests;
List <MarketValueInfo> lstMarketValues = new List <MarketValueInfo>();
foreach (StockRequestInfo stockRequestInfo in listOfStockInfos)
{
MarketValueInfo marketValue = GetTickerInfo(stockRequestInfo);
if (marketValue != null)
{
lstMarketValues.Add(marketValue);
}
}
if (lstMarketValues != null && lstMarketValues.Count > 0)
{
CreateExcel(new MarketValueInfo());
UpdateExcel(lstMarketValues);
List <EmailUtil> lst = new List <EmailUtil>()
{
new EmailUtil()
{
ToEmail = "*****@*****.**", ToName = "Sunita Gaikwad"
},
new EmailUtil()
{
ToEmail = "*****@*****.**", ToName = "Pratik outlook"
},
//new EmailUtil() { ToEmail = "*****@*****.**", ToName = "Mayur Gaikwad" }
};
foreach (EmailUtil u in lst)
{
u.Subject = String.Format("Portfolio Excel for - '{0}'", DateTime.Today.Date.ToString("MM-dd-yyyy"));
u.Body = "Please find attached portfolio excel sheet for today.";
bool bSendEMail = EmailUtil.Instance.SendEmail(u, strAttchmentFileName: String.Format("{0}{1}.xlsx", PortfoliExcelPath, excelFileName));
if (bSendEMail)
{
ApplicationLog.Instance.WriteInfo(String.Format("Email sent to '{0}' at '{1}'", u.ToEmail, DateTime.Now));
}
else
{
ApplicationLog.Instance.WriteWarning(String.Format("Email NOT sent to '{0}'", u.ToEmail));
}
}
}
bRtnVal = true;
}
else
{
ApplicationLog.Instance.WriteError("Request file not in correct format.");
}
}
catch (Exception ex)
{
bRtnVal = false;
ApplicationLog.Instance.WriteException(ex);
}
return(bRtnVal);
}