private void GenerateMAINSSExcelAndXmlFile(string xlsFilePath, TradingInfo tradingInfo)
{
xlsFilePath = MiscUtil.BackUpFileWithDateFolder(xlsFilePath, false); //MiscUtil.BackupFileWithNewName(xlsFilePath);
List <string> linesToWrite = new List <string>();
foreach (StockInfo stockInfo in tradingInfo.StockList)
{
linesToWrite.Add(stockInfo.ToSingleLine());
}
linesToWrite.Add("");
if (!string.IsNullOrEmpty(tradingInfo.DesignatedSecuritiesRecordingSum))
{
linesToWrite.Add(tradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
}
if (!string.IsNullOrEmpty(tradingInfo.DesignatedSharesShortSoldSum))
{
linesToWrite.Add(tradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
}
if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverShares))
{
linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength));
}
if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverValue))
{
linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength));
}
if (!string.IsNullOrEmpty(tradingInfo.HKDTurnoverValue))
{
linesToWrite.Add(tradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength));
}
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, xlsFilePath);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAINSS_CONFIG.WORKSHEET_NAME, workbook);
if (worksheet == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_CONFIG.WORKSHEET_NAME, workbook.Name));
}
//Generate MAINSSExcelFile
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
// Allocate spaces
int startOffset = (configObj.HKMAINSS_CONFIG.START_PAGE - 1) * configObj.HKMAINSS_CONFIG.LINE_NUM;
int startRow = startOffset + 1;
int curLineNum = 0;
int insertBlankPageNum = (linesToWrite.Count - 1) / configObj.HKMAINSS_CONFIG.LINE_EACH_PAGE_TO_COPY + 1;
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(startRow, 1, worksheet), insertBlankPageNum * 24);
writer.PlaceNext(startRow, 1);
// Form 1st line and write
string line = string.Empty;
List <string> headerLineList = new List <string>();
headerLineList.Add(tradingInfo.DateStr);
line = "RIC STOCK NAME SHARES TURNOVER ($)";
headerLineList.Add(line);
line = "========= ==================== =============== ===============";
headerLineList.Add(line);
for (int i = 0; i < insertBlankPageNum; i++)
{
int curStartRow = startOffset + i * configObj.HKMAINSS_CONFIG.LINE_NUM + 1;
int nextStartRow = curStartRow + 24;
writer.WriteLine(headerLineList[0]);
writer.WriteLine(headerLineList[1]);
writer.WriteLine(headerLineList[2]);
//keep a line without text.
writer.MoveNext();
while (writer.Row < nextStartRow && curLineNum < linesToWrite.Count)
{
writer.WriteLine(linesToWrite[curLineNum]);
curLineNum++;
}
writer.PlaceNext(nextStartRow, 1);
}
// Fill designated securities summary with page no. and background color.
for (int page = configObj.HKMAINSS_CONFIG.START_PAGE; page <= configObj.HKMAINSS_CONFIG.TOTAL_PAGE_NUM; page++)
{
string id = "HK/MAINSS" + page.ToString("D2");
int upRow = configObj.HKMAINSS_CONFIG.LINE_NUM * (page - 1) + 1;
int downRow = upRow + (configObj.HKMAINSS_CONFIG.LINE_NUM - 1);
writer.PlaceNextAndWriteLine(upRow, 2, id);
writer.PlaceNextAndWriteLine(downRow, 2, id);
Range pageRange = ExcelUtil.GetRange(upRow, 2, downRow, 2, worksheet);
if (page % 2 == 0)
{
pageRange.Interior.Color = 16777164.0;
}
else
{
pageRange.Interior.Color = 10092543.0;
}
}
Range rangToRemove = ExcelUtil.GetRange(writer.Row, 1, writer.Row + 24 * insertBlankPageNum, 2, worksheet);
rangToRemove.Clear();
}
//Generate MAINSSXmlFile
ProductionXmlFileTemplate xmlFileTem = GetHKMAINSSXmlFileContent(worksheet);
ConfigUtil.WriteXml(configObj.HKMAINSS_CONFIG.XML_FILE_PATH, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HKMAINSS", "", configObj.HKMAINSS_CONFIG.XML_FILE_PATH));
if (File.Exists(configObj.HKMAINSS_CONFIG.WORKBOOK_PATH))
{
File.Delete(configObj.HKMAINSS_CONFIG.WORKBOOK_PATH);
}
workbook.SaveCopyAs(configObj.HKMAINSS_CONFIG.WORKBOOK_PATH);
TaskResultList.Add(new TaskResultEntry("HKMAINSS", "", configObj.HKMAINSS_CONFIG.WORKBOOK_PATH));
workbook.Close(false, xlsFilePath, true);
}
}
/// <summary>
/// Generate HK-MAINSS file by copying contents from HK-MAIN01-10.xls .
/// </summary>
public void Start_HK_MAINSS()
{
TradingInfo tradingInfo = GetTradingInfoFromMain01_10File(configObj.HKMAINSS_CONFIG.PAGE_NUM_TO_COPY, configObj.HKMAINSS_CONFIG.LINE_EACH_PAGE_TO_COPY);
GenerateMAINSSExcelAndXmlFile(configObj.HKMAINSS_CONFIG.WORKBOOK_PATH, tradingInfo);
}
//
/// <summary>
/// Get trading information from the HK-MAIN01-10.xls file.
/// </summary>
/// <param name="pageNum">the page number to copy.</param>
/// <param name="lineNumEachPage">the line number of each page to copy.</param>
/// <returns>the Main01_10 trading News Information.</returns>
public TradingInfo GetTradingInfoFromMain01_10File(int pageNum, int lineNumEachPage)
{
TradingInfo tradingInfo = new TradingInfo();
tradingInfo.StockList = new List <StockInfo>();
//Open HK-MAIN01-10.xls
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook);
if (worksheet == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook.Name));
}
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
tradingInfo.DateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString();
reader.PlaceNext(4, 1);
//Range range = ExcelUtil.GetRange(reader.Row, 1, worksheet);
while ((reader.Row < (pageNum * lineNumEachPage) + 11))
{
if (ExcelUtil.GetRange(reader.Row, 1, worksheet).Text != null)
{
string firstColText = ExcelUtil.GetRange(reader.Row, 1, worksheet).Text.ToString().Trim();
if (firstColText.StartsWith("<"))
{
StockInfo stockInfo = new StockInfo();
stockInfo.Ric = reader.ReadLineCellText();
stockInfo.StockName = reader.ReadLineCellText();
stockInfo.Shares = reader.ReadLineCellText();
stockInfo.Turnover = reader.ReadLineCellText();
tradingInfo.StockList.Add(stockInfo);
reader.PlaceNext(reader.Row + 1, 1);
}
else
{
if (firstColText != string.Empty)
{
tradingInfo.DesignatedSecuritiesRecordingSum = ExcelUtil.GetRange(reader.Row, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedSharesShortSoldSum = ExcelUtil.GetRange(reader.Row + 1, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedShortSellTurnoverShares = ExcelUtil.GetRange(reader.Row + 2, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedShortSellTurnoverValue = ExcelUtil.GetRange(reader.Row + 3, 1, worksheet).Text.ToString().Trim();
if (ExcelUtil.GetRange(reader.Row + 4, 1, worksheet).Text != null)
{
tradingInfo.HKDTurnoverValue = ExcelUtil.GetRange(reader.Row + 4, 1, worksheet).Text.ToString().Trim();
}
reader.PlaceNext(reader.Row + 5, 1);
}
else
{
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
else
{
reader.PlaceNext(reader.Row + 1, 1);
}
}
workbook.Close(false, configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH, false);
}
}
return(tradingInfo);
}
/// <summary>
/// Generate the HKMAIN01_10Excel file and the HKMAIN01_10Xml file.
/// </summary>
/// <param name="main01_10TradingInfo">the Main01_10 trading News Info.</param>
public void GenerateMain01_10ExcelAndXMLFile(TradingInfo main01_10TradingInfo)
{
string main01_10FilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH, false); //MiscUtil.BackupFileWithNewName(configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH);
using (ExcelApp app = new ExcelApp(true, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, main01_10FilePath);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook);
if (worksheet == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook.Name));
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
// Update the date
string line = string.Empty;
writer.WriteLine(main01_10TradingInfo.DateStr);
//Copy from line 4
writer.PlaceNext(4, 1);
foreach (StockInfo stock in main01_10TradingInfo.StockList)
{
writer.WriteLine(stock.Ric);
writer.WriteLine(stock.StockName);
writer.WriteLine(stock.Shares);
writer.WriteLine(stock.Turnover);
writer.PlaceNext(writer.Row + 1, 1);
}
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
ExcelUtil.GetRange(writer.Row, writer.Col, lastUsedRow, writer.Col + 4, worksheet).Clear();
writer.PlaceNext(writer.Row + 2, 1);
writer.WriteLine(main01_10TradingInfo.DesignatedSecuritiesRecordingSum);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(main01_10TradingInfo.DesignatedSharesShortSoldSum);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverShares);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverValue);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(main01_10TradingInfo.HKDTurnoverValue);
ExcelUtil.GetRange(1, 1, writer.Row - 1, 4, worksheet).Borders.LineStyle = 0;
}
ProductionXmlFileTemplate xmlFileTem = GetHKMAINXmlFileContent(worksheet);
ConfigUtil.WriteXml(configObj.HKMAIN01_10_CONFIG.XML_FILE_PATH, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HK-MAIN01-10", "", configObj.HKMAIN01_10_CONFIG.XML_FILE_PATH));
//Save the HK-MAIN01-10 file as a copy
if (File.Exists(configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH))
{
File.Delete(configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH);
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbook.SaveCopyAs(configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH);
TaskResultList.Add(new TaskResultEntry("HKMAINSS01-10", "", configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH));
workbook.Close(false, main01_10FilePath, true);
}
}
/// <summary>
/// Get trading information about "(Main Board) up to day" .
/// </summary>
/// <returns>a instance of the Class TradingInfo which is about the Main01_10 trading News Info.</returns>
public TradingInfo GetMain01_10TradingInfo()
{
string url = configObj.MainBoard_URI;
// "/eng/stat/smstat/ssturnover/ncms/ASHTMAIN.HTM";
// url = MiscUtil.UrlCombine(configObj.BASE_URI, url);
TradingInfo main01_10TradingInfo = new TradingInfo();
main01_10TradingInfo.StockList = new List <StockInfo>();
List <string> valueList = new List <string>();
string tradingNewsInfo = string.Empty;
string dateStr = DateTime.Now.ToString("dddd dd/MM/yyyy");
main01_10TradingInfo.DateStr = string.Format("Recorded as of {0} 04:00 pm :-", dateStr);
var htmlDoc = WebClientUtil.GetHtmlDocument(url, 180000);
tradingNewsInfo = htmlDoc.DocumentNode.SelectSingleNode("//body/pre/font").InnerText;
using (StringReader sr = new StringReader(tradingNewsInfo))
{
string line;
while ((line = sr.ReadLine()) != null)
{
//Parse the stock info
string[] frags = line.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
int ric = 0;
if (frags.Length == 4 && int.TryParse(frags[0].Trim(), out ric))
{
StockInfo stockInfo = new StockInfo();
stockInfo.Ric = string.Format("<{0}.HK>", ric.ToString("D4"));
stockInfo.StockName = frags[1].Trim();
stockInfo.Shares = frags[2].Trim();
stockInfo.Turnover = frags[3].Trim();
main01_10TradingInfo.StockList.Add(stockInfo);
continue;
}
else if (frags.Length == 4 && !int.TryParse(frags[0].Trim(), out ric))
{
Regex regExpression = new Regex("[0-9]+");
Match match = regExpression.Match(frags[0].Trim());
if (match.Success)
{
StockInfo stockInfo = new StockInfo();
stockInfo.Ric = string.Format("<{0}.HK>", match.Value);
stockInfo.StockName = frags[1].Trim();
stockInfo.Shares = frags[2].Trim();
stockInfo.Turnover = frags[3].Trim();
main01_10TradingInfo.StockList.Add(stockInfo);
continue;
}
}
frags = line.Split(':');
if (frags.Length == 2 && frags[1].Trim() != "")
{
valueList.Add(line.Trim());
}
}
//Parse to get the summary info of Main Board
if (valueList != null && valueList.Count > 0)
{
main01_10TradingInfo.DesignatedSecuritiesRecordingSum = valueList[1];
main01_10TradingInfo.DesignatedSharesShortSoldSum = valueList[2];
main01_10TradingInfo.DesignatedShortSellTurnoverShares = valueList[3];
main01_10TradingInfo.DesignatedShortSellTurnoverValue = valueList[4];
main01_10TradingInfo.HKDTurnoverValue = valueList[5];
}
}
return(main01_10TradingInfo);
}
/// <summary>
/// GeneratingHKMAIN01_10 File.
/// </summary>
public void Start_HKMAIN01_10()
{
TradingInfo main01_10TradingInfo = GetMain01_10TradingInfo();
GenerateMain01_10ExcelAndXMLFile(main01_10TradingInfo);
}
/// <summary>
/// Update HK-GEMSS.xls file and create HK-GEMSS.xml
/// </summary>
/// <param name="gemTradingInfo">the GEM trading News Info.</param>
public void UpdateGemssFileAndGenerateXMLFile(TradingInfo gemTradingInfo)
{
// string gemssFilePath = MiscUtil.BackupFileWithNewName(configObj.HKGEMSS_CONFIG.WORKBOOK_PATH);
string gemssFilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKGEMSS_CONFIG.WORKBOOK_PATH, false);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, gemssFilePath);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKGEMSS_CONFIG.WORKSHEET_NAME, workbook);
if (worksheet == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_CONFIG.WORKSHEET_NAME, workbook.Name));
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
// Allocate spaces
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange("A1", worksheet), 24);
writer.PlaceNext(1, 1);
// Form 1st line and write
string line = string.Empty;
writer.WriteLine(gemTradingInfo.DateStr);
// Copy 2nd and 3rd line from line 26 and line 27
writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]);
writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]);
if (gemTradingInfo.StockList == null)
{
writer.WriteLine("NIL");
while (writer.Row < 19)
{
writer.WriteLine(" ");
}
}
else
{
// Fill stock info
foreach (StockInfo stockInfo in gemTradingInfo.StockList)
{
StringBuilder sb = new StringBuilder();
sb.Append(stockInfo.Ric.PadRight(12));
sb.Append(stockInfo.StockName.PadRight(27));
sb.Append(stockInfo.Shares.PadLeft(15));
sb.Append(stockInfo.Turnover.PadLeft(20));
line = sb.ToString();
writer.WriteLine(line);
}
while (writer.Row < 19)
{
writer.WriteLine(" ");
}
//Fill non-designated securities summary if non-designated securities exist
writer.PlaceNext(19, 1);
if (!string.IsNullOrEmpty(gemTradingInfo.NonDesignatedSecuritiesRecordingSum) && gemTradingInfo.NonDesignatedSecuritiesRecordingSum != "0")
{
writer.WriteLine(gemTradingInfo.NonDesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
writer.WriteLine(gemTradingInfo.NonDesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
writer.WriteLine(gemTradingInfo.NonDesignatedShortSellTransactionSum.PadLeft(recordAlignrightLength));
}
// Fill designated securities summary
writer.WriteLine(gemTradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength));
writer.WriteLine(gemTradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength));
writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength));
writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength));
if (!string.IsNullOrEmpty(gemTradingInfo.HKDTurnoverValue))
{
writer.WriteLine(gemTradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength));
}
}
for (int page = 1; page <= 25; page++)
{
string id = "HK/GEMSS" + page.ToString("D2");
int upRow = 24 * (page - 1) + 1;
int downRow = upRow + 23;
writer.PlaceNextAndWriteLine(upRow, 3, id);
writer.PlaceNextAndWriteLine(downRow, 3, id);
}
//Fill color for C columns
Range range = ExcelUtil.GetRange(1, 3, 24, 3, worksheet);
range.Interior.Color = ExcelUtil.GetRange(49, 3, worksheet).Interior.Color;
ProductionXmlFileTemplate xmlFileTem = GetGemssXmlFileContent(worksheet);
ConfigUtil.WriteXml(configObj.HKGEMSS_CONFIG.XML_FILE_PATH, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HK-GEMSS", "", configObj.HKGEMSS_CONFIG.XML_FILE_PATH));
if (File.Exists(configObj.HKGEMSS_CONFIG.WORKBOOK_PATH))
{
File.Delete(configObj.HKGEMSS_CONFIG.WORKBOOK_PATH);
}
//Save files as a copy
workbook.SaveCopyAs(configObj.HKGEMSS_CONFIG.WORKBOOK_PATH);
TaskResultList.Add(new TaskResultEntry("HK-GEMSS", "", configObj.HKGEMSS_CONFIG.WORKBOOK_PATH));
//Close current opend excel file
workbook.Close(false, gemssFilePath, false);
}
}
}
/// <summary>
/// Get GEM trading News Info.
/// </summary>
/// <param name="uri">the uri of the source data which is a html page.</param>
/// <returns>a instance of the Class TradingInfo which is about the GEM trading News Info.</returns>
public TradingInfo GetGemTradingInfo(string uri)
{
HtmlAgilityPack.HtmlDocument htmlDoc = null;
htmlDoc = WebClientUtil.GetHtmlDocument(uri, 180000);
TradingInfo gemTradingInfo = new TradingInfo();
gemTradingInfo.StockList = new List <StockInfo>();
List <string> valueList = new List <string>();
string dateStr = DateTime.Now.ToString("dddd dd/MM/yyyy");
gemTradingInfo.DateStr = string.Format("Recorded as of {0} 04:00 pm :-", dateStr);
//Get the trading news information
string gemTradingNewsInfo = htmlDoc.DocumentNode.SelectSingleNode("//body/pre/font").InnerText;
//Parse and get the required information
if (!IsNewsExist(gemTradingNewsInfo))
{
gemTradingInfo.StockList = null;
}
using (StringReader sr = new StringReader(gemTradingNewsInfo))
{
string line;
while ((line = sr.ReadLine()) != null)
{
string[] frags = line.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
int ric = 0;
if (frags.Length == 4 && int.TryParse(frags[0].Trim(), out ric))
{
StockInfo stockInfo = new StockInfo();
stockInfo.Ric = string.Format("<{0}.HK>", ric.ToString("D4"));
stockInfo.StockName = frags[1].Trim();
stockInfo.Shares = frags[2].Trim();
stockInfo.Turnover = frags[3].Trim();
gemTradingInfo.StockList.Add(stockInfo);
continue;
}
frags = line.Split(':');
if (frags.Length == 2 && frags[1].Trim() != "")
{
valueList.Add(line.Trim());
}
}
}
//Parse to get the summary info of Main Board
if (valueList != null && valueList.Count > 0)
{
gemTradingInfo.DesignatedSecuritiesRecordingSum = valueList[1];
gemTradingInfo.DesignatedSharesShortSoldSum = valueList[2];
gemTradingInfo.DesignatedShortSellTurnoverShares = valueList[3];
gemTradingInfo.DesignatedShortSellTurnoverValue = valueList[4];
if (valueList[5].Contains("Short Selling Turnover Total Value ($) : HKD "))
{
gemTradingInfo.HKDTurnoverValue = valueList[5];
}
}
return(gemTradingInfo);
}