//Download csv file from page http://www.hkex.com.hk/eng/plw/plw_search.asp
public void DownLoadSCVFile()
{
string uri = GetCSVFileSrc();
Thread.Sleep(2000);
if (File.Exists(configObj.CURRENT_SEHKFILE_PATH))
{
MiscUtil.BackUpFile(configObj.CURRENT_SEHKFILE_PATH);
File.Delete(configObj.CURRENT_SEHKFILE_PATH);
}
WebClientUtil.DownloadFile(uri, 180000, configObj.CURRENT_SEHKFILE_PATH);
}
private void GenerateComparedAndValidationFile(List <ChainRicRecord> chainRicList)
{
Logger.Log("Start to generate the compared file and the validation file.");
using (ExcelApp xlapp = new ExcelApp(false, false))
{
MiscUtil.BackUpFile(configObj.FMFilePath);
var workbook = ExcelUtil.CreateOrOpenExcelFile(xlapp, configObj.FMFilePath);
var worksheet = ExcelUtil.GetWorksheet("Sheet1", workbook);
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
int[] addedColumnOrder = new int[] { 6, 8, 10, 12, 15, 17, 22, 26, 28 };
string[] addedColumnTitle = new string[] { "DSPLY_NAME", "OFFC_CODE2", "OFFCL_CODE", "BCAST_REF", "MATUR_DATE", "STRIKE_PRC", "WNT_RATIO", "GV2_DATE", "LONGLINK1" };
for (int i = 0; i < addedColumnOrder.Length; i++)
{
ExcelUtil.InsertBlankCols(ExcelUtil.GetRange(1, addedColumnOrder[i], worksheet), 1);
ExcelUtil.GetRange(4, addedColumnOrder[i], worksheet).Value2 = addedColumnTitle[i];
ExcelUtil.GetRange(4, addedColumnOrder[i], worksheet).Interior.Color = System.Drawing.Color.FromArgb(146, 208, 80).ToArgb();
}
writer.PlaceNext(5, 6);
foreach (ChainRicRecord chainRicRecord in chainRicList)
{
if ("1".Equals(chainRicRecord.MainRic.HaveFields))
{
for (int i = 0; i < addedColumnOrder.Length; i++)
{
ExcelUtil.GetRange(writer.Row, addedColumnOrder[i], worksheet).Value2 = chainRicRecord.MainRic.FieldsList[i];
string valueInFM = ExcelUtil.GetRange(writer.Row, addedColumnOrder[i] - 1, worksheet).Text.ToString();
string valueInGATS = ExcelUtil.GetRange(writer.Row, addedColumnOrder[i], worksheet).Text.ToString();
if (i == 4 || i == 7)
{
string[] dateArray = valueInFM.Split(new char[] { '-' });
if (dateArray.Length == 3)
{
string formatedDate = string.Format("{0} {1} 20{2}", dateArray[0], dateArray[1].ToUpper(), dateArray[2]);
if (!formatedDate.Equals(chainRicRecord.MainRic.FieldsList[i]))
{
ExcelUtil.GetRange(writer.Row, addedColumnOrder[i] - 1, worksheet).Interior.Color = System.Drawing.Color.FromArgb(149, 179, 215).ToArgb();
}
}
else
{
if (!valueInFM.Equals(chainRicRecord.MainRic.FieldsList[i]))
{
ExcelUtil.GetRange(writer.Row, addedColumnOrder[i] - 1, worksheet).Interior.Color = System.Drawing.Color.FromArgb(149, 179, 215).ToArgb();
}
}
}
else
{
if (!valueInFM.Equals(valueInGATS))
{
ExcelUtil.GetRange(writer.Row, addedColumnOrder[i] - 1, worksheet).Interior.Color = System.Drawing.Color.FromArgb(149, 179, 215).ToArgb();
}
}
}
}
writer.PlaceNext(writer.Row + 1, 6);
}
}
workbook.Save();
workbook.Close(false, workbook.FullName, false);
var workbookValidation = ExcelUtil.CreateOrOpenExcelFile(xlapp, configObj.ValidationResultFilePath);
var worksheet1 = ExcelUtil.GetWorksheet("Sheet1", workbookValidation);
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet1, 1, 1, ExcelLineWriter.Direction.Right))
{
string[] sheetTitle = new string[] { "MainRic", "Have Data(1 means Yes, 0 means No)", " Have Rics(1 means Yes, 0 means No)" };
for (int i = 0; i < sheetTitle.Length; i++)
{
writer.WriteLine(sheetTitle[i]);
}
writer.PlaceNext(2, 1);
int maxRicCount = 0;
foreach (ChainRicRecord chainRicRecord in chainRicList)
{
writer.WriteLine(chainRicRecord.MainRic.Ric);
writer.WriteLine(chainRicRecord.MainRic.HaveFields);
if (chainRicRecord.RicList.Count > 0)
{
writer.WriteLine("1");
chainRicRecord.RicList.RemoveAt(0);
maxRicCount = chainRicRecord.RicList.Count;
foreach (RicRecord ricRecord in chainRicRecord.RicList)
{
writer.WriteLine(string.Format("{0} : {1}", ricRecord.Ric, ricRecord.HaveFields));
if ("0".Equals(ricRecord.HaveFields))
{
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet1).Interior.Color = System.Drawing.Color.FromArgb(149, 179, 215).ToArgb();
}
}
}
else
{
writer.WriteLine("0");
}
writer.PlaceNext(writer.Row + 1, 1);
}
writer.PlaceNext(1, 4);
for (int num = 1; num <= maxRicCount; num++)
{
writer.WriteLine(string.Format("Ric{0}(1 means having data, 0 means not)", num));
}
}
workbookValidation.Save();
workbookValidation.Close(false, workbookValidation.FullName, false);
}
Logger.Log("Finished generating the compared file and the validation file.");
}