//17 lines each page
private void GenerateEnglishVersionXmlFile(List <BrokerInfo> brokerList, string xmlFilePath)
{
int lineNumEachPage = 22;
int lastAddedRicNum = 0;
ProductionXmlFileTemplate template = new ProductionXmlFileTemplate();
for (int i = 0; i < lineNumEachPage * 34; i++)
{
if (i % lineNumEachPage == 0)
{
Core.Ric ric = new Core.Ric();
ric.Name = string.Format("HKBK{0}", ((i / lineNumEachPage) + 2).ToString("D2"));
template.rics.rics.Add(ric);
}
Fid fid = new Fid();
fid.Id = 317 + i % lineNumEachPage;
if (i < brokerList.Count)
{
fid.Value = getFidValueForEnglishVersionFile(brokerList[i]);
}
else
{
fid.Value = "\"\"";
}
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fid);
}
ConfigUtil.WriteXml(xmlFilePath, template);
TaskResultList.Add(new TaskResultEntry("English Version XML Format File", "", xmlFilePath));
}
private ProductionXmlFileTemplate getSpeedGuideXmlFileContent(Workbook warGuideBWorkbook)
{
var template = new ProductionXmlFileTemplate();
Worksheet insertV3Sheet = ExcelUtil.GetWorksheet(configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook);
if (insertV3Sheet == null)
{
LogMessage(string.Format("There's no worksheet {0} in workbook {1}", configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook.FullName));
}
int lastAddedRicNum;
for (int i = 86; i < 1966; i++)
{
if (i % 20 == 6)
{
Core.Ric ric = new Core.Ric {
Name = string.Format("HK/WTS{0}", (i / 20 + 2).ToString("D2"))
};
template.rics.rics.Add(ric);
}
Fid fid = new Fid {
Id = ((i - 86) % 20) + 319, Value = getFidValue(insertV3Sheet, i)
};
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fid);
}
return(template);
}
private void generateXMLFile(Workbook warGuideBook)
{
ProductionXmlFileTemplate xmlObj = getSpeedGuideXmlFileContent(warGuideBook);
ConfigUtil.WriteXml(Path.Combine(configObj.WarGuideBFolderPath, "War_guideB.xml"), xmlObj);
AddResult("war_guideB.xml (for FTP)", Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.xml"), "file");
}
private ProductionXmlFileTemplate GetHKMAINSSXmlFileContent(Worksheet worksheet)
{
ProductionXmlFileTemplate template = new ProductionXmlFileTemplate();
int lastAddedRicNum = 0;
// the default id value of the first fid node in each ric node
int lastStartId = 316;
// the start row of the range which will be copied from the HKMAINSSExcel file to the HKMAINSSXml file
int startRow = (configObj.HKMAINSS_CONFIG_StartPage - 1) * configObj.HKMAINSS_CONFIG_LineNum + 1;
// the end row of the range which will be copied from the HKMAINSSExcel file to the HKMAINSSXml file
int endRow = configObj.HKMAINSS_CONFIG_TotalPage * configObj.HKMAINSS_CONFIG_LineNum;
//copy the records whose Range is from startRow to endRow in the HKMAINSSExcel file
for (int i = startRow; i <= endRow; i++)
{
if ((i - startRow) % configObj.HKMAINSS_CONFIG_LineNum == 0)
{
lastAddedRicNum = template.rics.rics.Count - 1;
Core.Ric ric = new Core.Ric();
ric.Name = string.Format("HK/MAINSS{0}", (((i - startRow) / configObj.HKMAINSS_CONFIG_LineNum) + configObj.HKMAINSS_CONFIG_StartPage).ToString("D2"));
template.rics.rics.Add(ric);
if (lastAddedRicNum >= 0)
{
Fid lastFid = new Fid();
lastFid.Id = 339;
string continuedPageDescription = "Continued on <" + template.rics.rics[lastAddedRicNum + 1].Name + ">";
continuedPageDescription = continuedPageDescription.PadLeft(recordTotalLength);
lastFid.Value = string.Format("\"{0}\"", continuedPageDescription);
template.rics.rics[lastAddedRicNum].fids.Add(lastFid);
}
}
if ((i - startRow) % configObj.HKMAINSS_CONFIG_LineNum == 3)
{
lastStartId = 315;
continue;
}
Fid fid = new Fid();
fid.Id = lastStartId + (i - startRow) % configObj.HKMAINSS_CONFIG_LineNum;
string colValue = GetFormatedValue(i, worksheet);
fid.Value = string.Format("\"{0}\"", colValue);
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fid);
if ((i - startRow) % configObj.HKMAINSS_CONFIG_LineNum == (configObj.HKMAINSS_CONFIG_LineNum - 1))
{
lastStartId = 316;
}
}
return(template);
}
private ProductionXmlFileTemplate GetGemssXmlFileContent(Worksheet gemssWorksheet)
{
ProductionXmlFileTemplate template = new ProductionXmlFileTemplate();
int lastAddedRicNum = 0;
for (int i = 1; i <= 240; i++)
{
if (i % 24 == 1)
{
lastAddedRicNum = template.rics.rics.Count - 1;
if (lastAddedRicNum > 0)
{
Fid lastFid = new Fid();
lastFid.Id = 339;
lastFid.Value = string.Format("\"Continued on <{0}>\"", template.rics.rics[lastAddedRicNum].Name);
}
Core.Ric ric = new Core.Ric();
ric.Name = string.Format("HK/GEMSS{0}", (i / 24 + 1).ToString("D2"));
template.rics.rics.Add(ric);
}
if (i % 24 == 0)
{
continue;
}
Fid fid = new Fid();
fid.Id = 316 + (i - 1) % 24;
if (ExcelUtil.GetRange(i, 1, gemssWorksheet).Text != null)
{
fid.Value = string.Format("\"{0}\"", ExcelUtil.GetRange(i, 1, gemssWorksheet).Text.ToString());
if (fid.Value == "\" \"")
{
fid.Value = " ".PadLeft(recordTotalLength);
fid.Value = "\"" + fid.Value + "\"";
}
}
else
{
fid.Value = string.Format("\"{0}\"", " ".PadLeft(recordAlignrightLength));
}
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fid);
}
return(template);
}
private ProductionXmlFileTemplate GetHKMAINXmlFileContent(Worksheet worksheet)
{
ProductionXmlFileTemplate template = new ProductionXmlFileTemplate();
int lastAddedRicNum = 0;
string dateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString();
string titleLine = "RIC STOCK NAME SHARES TURNOVER ($)";
string partingLine = "========= ==================== =============== ===============";
for (int i = 4; i <= 503; i++)
{
if (i % 20 == 4)
{
Ric.Core.Ric ric = new Ric.Core.Ric();
ric.Name = string.Format("HK/MAINSS{0}", (i / 20 + 1).ToString("D2"));
template.rics.rics.Add(ric);
Fid fidDate = new Fid();
fidDate.Id = 316;
fidDate.Value = string.Format("\"{0}\"", dateStr);
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fidDate);
Fid fidTitleLine = new Fid();
fidTitleLine.Id = 317;
fidTitleLine.Value = string.Format("\"{0}\"", titleLine);
template.rics.rics[lastAddedRicNum].fids.Add(fidTitleLine);
Fid fidPartingLine = new Fid();
fidPartingLine.Id = 318;
fidPartingLine.Value = string.Format("\"{0}\"", partingLine);
template.rics.rics[lastAddedRicNum].fids.Add(fidPartingLine);
}
Fid fid2 = new Fid();
fid2.Id = (i - 4) % 20 + 319;
fid2.Value = GetFidValue(worksheet, i);
lastAddedRicNum = template.rics.rics.Count - 1;
template.rics.rics[lastAddedRicNum].fids.Add(fid2);
}
return(template);
}
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, workbook);
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, workbook.Name));
}
//Generate MAINSSExcelFile
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
// Allocate spaces
int startOffset = (configObj.HKMAINSS_CONFIG_StartPage - 1) * configObj.HKMAINSS_CONFIG_LineNum;
int startRow = startOffset + 1;
int curLineNum = 0;
int insertBlankPageNum = (linesToWrite.Count - 1) / configObj.HKMAINSS_CONFIG_LineEach + 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_LineNum + 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_StartPage; page <= configObj.HKMAINSS_CONFIG_TotalPage; page++)
{
string id = "HK/MAINSS" + page.ToString("D2");
int upRow = configObj.HKMAINSS_CONFIG_LineNum * (page - 1) + 1;
int downRow = upRow + (configObj.HKMAINSS_CONFIG_LineNum - 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_XmlPath, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HKMAINSS", "", configObj.HKMAINSS_CONFIG_XmlPath));
if (File.Exists(configObj.HKMAINSS_CONFIG_Workbook))
{
File.Delete(configObj.HKMAINSS_CONFIG_Workbook);
}
workbook.SaveCopyAs(configObj.HKMAINSS_CONFIG_Workbook);
TaskResultList.Add(new TaskResultEntry("HKMAINSS", "", configObj.HKMAINSS_CONFIG_Workbook));
workbook.Close(false, xlsFilePath, true);
}
}
/// <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_Workbook, false); //MiscUtil.BackupFileWithNewName(configObj.HKMAIN01_10_Workbook);
using (ExcelApp app = new ExcelApp(true, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, main01_10FilePath);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_Worksheet, workbook);
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_Worksheet, 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_XmlPath, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HK-MAIN01-10", "", configObj.HKMAIN01_10_XmlPath));
//Save the HK-MAIN01-10 file as a copy
if (File.Exists(configObj.HKMAIN01_10_Workbook))
{
File.Delete(configObj.HKMAIN01_10_Workbook);
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbook.SaveCopyAs(configObj.HKMAIN01_10_Workbook);
TaskResultList.Add(new TaskResultEntry("HKMAINSS01-10", "", configObj.HKMAIN01_10_Workbook));
workbook.Close(false, main01_10FilePath, true);
}
}
/// <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.BackUpFileWithDateFolder(configObj.HKGEMSS_Workbook, false);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, gemssFilePath);
var worksheet = ExcelUtil.GetWorksheet(configObj.HKGEMSS_Worksheet, workbook);
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, 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_XmlPath, xmlFileTem);
TaskResultList.Add(new TaskResultEntry("XML file for HK-GEMSS", "", configObj.HKGEMSS_XmlPath));
if (File.Exists(configObj.HKGEMSS_Workbook))
{
File.Delete(configObj.HKGEMSS_Workbook);
}
//Save files as a copy
workbook.SaveCopyAs(configObj.HKGEMSS_Workbook);
TaskResultList.Add(new TaskResultEntry("HK-GEMSS", "", configObj.HKGEMSS_Workbook));
//Close current opend excel file
workbook.Close(false, gemssFilePath, false);
}
}
}
