public void InsertRicChange(int rowPos, ExcelLineWriter writer, List <StockNameRicChange> ricChangeList, Worksheet worksheet)
{
if (ricChangeList == null || ricChangeList.Count == 0)
{
return;
}
else
{
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), 2 * ricChangeList.Count);
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
foreach (StockNameRicChange ricChange in ricChangeList)
{
writer.PlaceNext(rowPos++, 1);
writer.WriteLine(ricChange.Name);
writer.WriteLine(ricChange.OldValue);
writer.WriteLine(ricChange.Type);
writer.WriteLine(ricChange.Date);
writer.PlaceNext(rowPos++, 2);
writer.WriteLine(ricChange.NewValue);
if (IsUpgrade(ricChange))
{
writer.WriteLine("UPGRADE");
}
writer.PlaceNextAndWriteLine(rowPos, 4, ricChange.Date);
if (IsIPos(ricChange.Ric))
{
ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0;
}
else
{
ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777164.0;
}
}
}
}
public void UpdateHKSEAFile(ChangeInfo ChangedBrokerInfo, List <BordLotSizeChangeInfo> bordLotSizeChangeList)
{
using (ExcelApp appSEA = new ExcelApp(false, false))
{
string SEAFilePath = BackupFiles(configObj.HKSEA_Z2_FILE_PATH);
var workbookSEA = ExcelUtil.CreateOrOpenExcelFile(appSEA, SEAFilePath);
var worksheetSEA = ExcelUtil.GetWorksheet("Sheet2", workbookSEA);
int rowPos = 1;
using (ExcelLineWriter writer = new ExcelLineWriter(worksheetSEA, 1, 1, ExcelLineWriter.Direction.Right))
{
int curRowPos = worksheetSEA.UsedRange.Row + worksheetSEA.UsedRange.Rows.Count - 1;
for (int i = ChangedBrokerInfo.DifDayChangeInfo.Count - 1; i > -1; i--)
{
SameDayChangeInfo sameDayChangedInfo = ChangedBrokerInfo.DifDayChangeInfo[i];
rowPos = curRowPos;
while (rowPos > 0)
{
if (ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2 == null || string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2.ToString()))
{
rowPos--;
continue;
}
else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Text.ToString()))
{
if (rowPos == curRowPos)
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else if (ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA) == null || (string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString())))
{
rowPos--;
}
else if (!DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString()))
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else
{
rowPos--;
continue;
}
}
else
{
if (rowPos == 1)
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else if (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA) == null || (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text == null))
{
rowPos--;
}
else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text.ToString()))
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else
{
rowPos--;
continue;
}
}
}
}
}
//workbookSEA.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbookSEA.FullName), Path.GetFileName(configObj.HKSEA_Z2_FILE_PATH)));
workbookSEA.SaveCopyAs(configObj.HKSEA_Z2_FILE_PATH);
workbookSEA.Close(false, workbookSEA.FullName, false);
//File.Delete(SEAFilePath);
}
}
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);
}
}
private void GeneratePageUpdateFile(string templateFilePath, System.Data.DataTable dt)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, templateFilePath);
var worksheet = ExcelUtil.GetWorksheet("INPUT SHEET", workbook);
if (worksheet == null)
{
Logger.LogErrorAndRaiseException(string.Format("There's no worksheet: {0}", worksheet.Name));
}
((Range)worksheet.Columns["D"]).NumberFormat = "@";
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right))
{
foreach (DataRow dr in dt.Rows)
{
writer.WriteLine("Create");
writer.WriteLine("");
writer.WriteLine(Convert.ToString(dr["ChangeType"]));
string effectiveDate = Convert.ToDateTime(dr["EffectiveDate"]).ToString("ddMMMyy");
writer.WriteLine(effectiveDate);
writer.WriteLine(Convert.ToString(dr["DescriptionWas"]));
writer.WriteLine(Convert.ToString(dr["DescriptionNow"]));
string ricWas = (Convert.ToString(dr["RICWas"])).Replace("D^", "").Replace("^", "");
writer.WriteLine(ricWas);
writer.WriteLine(Convert.ToString(dr["RICNow"]));
writer.WriteLine(Convert.ToString(dr["ISINWas"]));
writer.WriteLine(Convert.ToString(dr["ISINNow"]));
writer.WriteLine("Official Code");
writer.WriteLine(Convert.ToString(dr["SecondWas"]));
writer.WriteLine(Convert.ToString(dr["SecondNow"]));
writer.WriteLine("");
writer.WriteLine("");
writer.WriteLine("");
//Exchange
string exchange = Convert.ToString(dr["RICNow"]);
if (string.IsNullOrEmpty(exchange))
{
exchange = Convert.ToString(dr["RICWas"]);
}
if (exchange.Contains(".KS"))
{
exchange = "KSC";
}
else if (exchange.Contains(".KQ"))
{
exchange = "KOE";
}
else if (exchange.Contains(".KN"))
{
exchange = "KNX";
}
writer.WriteLine(exchange);
string asset = Convert.ToString(dr["Asset"]);
if (asset.Equals("KDR"))
{
asset = "DRC";
}
writer.WriteLine(asset);
writer.PlaceNext(writer.Row + 1, 1);
}
}
//Run Macros
app.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
app.ExcelAppInstance,
new object[] { "FormatData" });
string targetFilePath = Path.GetDirectoryName(templateFilePath);
targetFilePath = Path.Combine(targetFilePath, DateTime.Today.ToString("yyyy-MM-dd"));
if (!Directory.Exists(targetFilePath))
{
Directory.CreateDirectory(targetFilePath);
}
targetFilePath += "\\Result_Add_Drop_Upload_File.xls";
workbook.SaveCopyAs(targetFilePath);
AddResult("Result File", targetFilePath, "");
workbook.Close(false, templateFilePath, false);
}
}
/// <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);
}
}
public TradingInfo GetTradingInfoFromMain01_10File(int pageNum, int lineNumEachPage)
{
TradingInfo tradingInfo = new TradingInfo();
tradingInfo.StockList = new List <StockInfo>();
using (ExcelApp excelApp = new ExcelApp(false, false))
{
Workbook orOpenExcelFile = ExcelUtil.CreateOrOpenExcelFile(excelApp,
configObj.HKMAIN01_10_Workbook);
Worksheet worksheet = ExcelUtil.GetWorksheet(this.configObj.HKMAIN01_10_Worksheet,
orOpenExcelFile);
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}",
(object)configObj.HKMAIN01_10_Worksheet, (object)orOpenExcelFile.Name));
}
using (
ExcelLineWriter excelLineWriter = new ExcelLineWriter(worksheet, 1, 1,
ExcelLineWriter.Direction.Right))
{
tradingInfo.DateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString();
excelLineWriter.PlaceNext(4, 1);
while (excelLineWriter.Row < pageNum * lineNumEachPage + 4)
{
if (ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text != null)
{
string str = ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim();
if (str.StartsWith("<"))
{
tradingInfo.StockList.Add(new StockInfo()
{
Ric = excelLineWriter.ReadLineCellText(),
StockName = excelLineWriter.ReadLineCellText(),
Shares = excelLineWriter.ReadLineCellText(),
Turnover = excelLineWriter.ReadLineCellText()
});
excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1);
}
else if (str != string.Empty)
{
tradingInfo.DesignatedSecuritiesRecordingSum =
ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedSharesShortSoldSum =
ExcelUtil.GetRange(excelLineWriter.Row + 1, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedShortSellTurnoverShares =
ExcelUtil.GetRange(excelLineWriter.Row + 2, 1, worksheet).Text.ToString().Trim();
tradingInfo.DesignatedShortSellTurnoverValue =
ExcelUtil.GetRange(excelLineWriter.Row + 3, 1, worksheet).Text.ToString().Trim();
if (ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text != null)
{
tradingInfo.HKDTurnoverValue =
ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text.ToString().Trim();
}
excelLineWriter.PlaceNext(excelLineWriter.Row + 5, 1);
}
else
{
excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1);
}
}
else
{
excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1);
}
}
orOpenExcelFile.Close((object)false, (object)configObj.HKMAIN01_10_Workbook,
(object)false);
}
}
return(tradingInfo);
}
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.");
}
/// <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);
}
}
}
//Generate insert sheet
public void GenerateInsertSheet(Workbook workbook, int brokerRowsSum, List <BrokerInfo> brokerList)
{
var worksheetInsert = ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook);
if (worksheetInsert == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.INSERT_WORKSHEET_NAME, workbook.Name));
}
Range copyRange = GenerateMasterSheet(workbook, brokerList, out brokerRowsSum);
using (ExcelLineWriter writerInsert = new ExcelLineWriter(worksheetInsert, 4, 1, ExcelLineWriter.Direction.Right))
{
Range r = ExcelUtil.GetRange(4, 1, brokerRowsSum, 4, worksheetInsert);
copyRange.Copy(r);
int lastUsedRowInsert = worksheetInsert.UsedRange.Row + worksheetInsert.UsedRange.Rows.Count - 1;
if (lastUsedRowInsert > (brokerRowsSum))
{
ExcelUtil.GetRange(brokerRowsSum + 1, 1, lastUsedRowInsert, 4, worksheetInsert).Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
}
ExcelUtil.GetRange(4, 5, lastUsedRowInsert, 5, worksheetInsert).ClearContents();
int pageSum = ((brokerRowsSum - 4)) / 17 + 1;
List <PageInfo> pageList = new List <PageInfo>();
for (int i = 0; i < pageSum; i++)
{
PageInfo pageInfo = new PageInfo();
string id = "HKBKC" + (i + 2).ToString("D2");
int upRow = 17 * i + 4;
int downRow = upRow + 16;
writerInsert.PlaceNextAndWriteLine(upRow, 5, id);
writerInsert.PlaceNextAndWriteLine(downRow, 5, id);
pageInfo.pageNumberChineseStr = "<" + id + ">";
pageInfo.pageNumberStr = pageInfo.pageNumberChineseStr.Replace("HKBKC", "HKBK");
pageInfo.minNumberStr = GetMinMaxValue(ExcelUtil.GetRange(upRow, 1, worksheetInsert).Text.ToString() + "," + ExcelUtil.GetRange(upRow, 2, worksheetInsert).Text.ToString())[0];
if (downRow < (brokerRowsSum))
{
pageInfo.maxNumberStr = GetMinMaxValue(ExcelUtil.GetRange(downRow, 1, worksheetInsert).Text.ToString() + "," + ExcelUtil.GetRange(downRow, 2, worksheetInsert).Text.ToString())[1];
}
else
{
pageInfo.maxNumberStr = GetMinMaxValue(ExcelUtil.GetRange(brokerRowsSum, 1, worksheetInsert).Text.ToString() + ExcelUtil.GetRange(brokerRowsSum, 2, worksheetInsert).Text.ToString())[1];
}
pageList.Add(pageInfo);
}
writerInsert.PlaceNext(2, 7);
int referStartRow = 0;
int referLastRow = 0;
while ((ExcelUtil.GetRange(writerInsert.Row + 1, 7, worksheetInsert).Text.ToString() != "") && (ExcelUtil.GetRange(writerInsert.Row + 2, 7, worksheetInsert).Text.ToString() != ""))
{
if (ExcelUtil.GetRange(writerInsert.Row, 7, worksheetInsert).Text.ToString() == "REFER <HKBK01>")
{
referStartRow = writerInsert.Row;
}
writerInsert.PlaceNext(writerInsert.Row + 1, 7);
}
referLastRow = writerInsert.Row + 2;
Range referRange = ExcelUtil.GetRange(referStartRow, 7, referLastRow, 15, worksheetInsert);
Range bakRange = ExcelUtil.GetRange(referLastRow + 1, 7, referLastRow + 1 + (referLastRow - referStartRow), 15, worksheetInsert);
referRange.Copy(bakRange);
ExcelUtil.GetRange(2, 7, referStartRow - 1, 15, worksheetInsert).Clear();
writerInsert.PlaceNext(1, 7);
for (int j = 0; j < pageList.Count; j++)
{
if (j % 3 == 0)
{
writerInsert.PlaceNext(writerInsert.Row + 1, 7);
}
else
{
}
writerInsert.WriteLine(pageList[j].minNumberStr);
writerInsert.WriteLine(pageList[j].maxNumberStr);
writerInsert.WriteLine(pageList[j].pageNumberChineseStr);
}
ExcelUtil.GetRange(2, 7, writerInsert.Row, 15, worksheetInsert).Font.FontStyle = ExcelUtil.GetRange(4, 1, worksheetInsert).Font.FontStyle;
ExcelUtil.GetRange(2, 7, writerInsert.Row, 15, worksheetInsert).Font.Size = ExcelUtil.GetRange(4, 1, worksheetInsert).Font.Size;
ExcelUtil.GetRange(2, 7, writerInsert.Row, 9, worksheetInsert).Interior.Color = 16777164.0;
ExcelUtil.GetRange(2, 10, writerInsert.Row, 12, worksheetInsert).Interior.Color = 13409484.0;
ExcelUtil.GetRange(2, 13, writerInsert.Row, 15, worksheetInsert).Interior.Color = 10092543.0;
Range referR = ExcelUtil.GetRange(writerInsert.Row + 1, 7, writerInsert.Row + (referLastRow - referStartRow) + 1, 15, worksheetInsert);
bakRange.Copy(referR);
if ((referLastRow - referStartRow) > (writerInsert.Row - referStartRow))
{
ExcelUtil.GetRange(writerInsert.Row + (referLastRow - referStartRow) + 3, 7, referLastRow + 1 + (referLastRow - referStartRow), 15, worksheetInsert).Clear();
}
}
}
//Get all the broker information from the current csv file
public List <BrokerInfo> GetBrokerList(string currentCSVFilePath)
{
List <BrokerInfo> brokerList = new List <BrokerInfo>();
using (ExcelApp app = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, currentCSVFilePath);
Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", Path.GetFileNameWithoutExtension(currentCSVFilePath), workbook.Name));
}
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Down))
{
while (reader.Row < lastUsedRow)
{
try
{
if (!string.IsNullOrEmpty(ExcelUtil.GetRange(reader.Row, 1, worksheet).Text.ToString()))
{
string brokerNumbers = ExcelUtil.GetRange(reader.Row, 2, worksheet).Text.ToString();
if ((brokerNumbers != null) && (brokerNumbers != ""))
{
string originalName = ExcelUtil.GetRange(reader.Row, 3, worksheet).Text.ToString().Trim();
string ChineseName = ExcelUtil.GetRange(reader.Row, 4, worksheet).Text.ToString().Trim();
if (brokerList.Count == 0 || originalName != brokerList[brokerList.Count - 1].originalName)
{
if (brokerList.Count != 0 && brokerNumbers == brokerList[brokerList.Count - 1].brokerNumbers)
{
reader.PlaceNext(reader.Row + 1, 1);
continue;
}
Dictionary <int, BrokerInfo> brokerGroupMap = new Dictionary <int, BrokerInfo>();
foreach (string id in brokerNumbers.Split(','))
{
int curId = int.Parse(id.Trim());
int part1 = curId / 10;
int part2 = curId % 10;
if (brokerGroupMap.ContainsKey(part1))
{
brokerGroupMap[part1].IdList.Add(part2);
}
else
{
BrokerInfo brokerInfo = new BrokerInfo();
brokerInfo.ChineseName = ChineseName.Trim();
brokerInfo.originalName = originalName;
brokerInfo.brokerNumbers = brokerNumbers;
brokerInfo.GetBrokerLongAndShortName();
if (brokerInfo.err != string.Empty)
{
Logger.Log(brokerInfo.err);
brokerInfo.err = string.Empty;
}
brokerInfo.GetBrokerChineseLongAndShortName();
if (brokerInfo.err != string.Empty)
{
Logger.Log(brokerInfo.err);
brokerInfo.err = string.Empty;
}
brokerInfo.GroupId = part1;
brokerInfo.IdList = new List <int>();
brokerInfo.IdList.Add(part2);
brokerGroupMap.Add(part1, brokerInfo);
}
}
foreach (BrokerInfo broker in brokerGroupMap.Values)
{
broker.hall = broker.GetHallInfo();
broker.terminal = broker.GetTerminalInfo();
if (!brokerList.Contains(broker))
{
brokerList.Add(broker);
}
}
}
}
reader.PlaceNext(reader.Row + 1, 1);
}
else
{
break;
}
}
catch (Exception ex)
{
LogMessage(string.Format("msg:{0}", ex.Message));
}
}
}
workbook.Close(false, workbook.FullName, false);
}
brokerList.Sort();
return(brokerList);
}
//Generate master sheet
public Range GenerateMasterSheet(Workbook workbook, List <BrokerInfo> brokerList, out int brokerRowsSum)
{
var worksheetMaster = ExcelUtil.GetWorksheet(configObj.MASTER_WORKSHEET_NAME, workbook);
if (worksheetMaster == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.MASTER_WORKSHEET_NAME, workbook.Name));
}
int lastUsedRow = worksheetMaster.UsedRange.Row + worksheetMaster.UsedRange.Rows.Count - 1;
ExcelUtil.GetRange(1, 2, lastUsedRow, 4, worksheetMaster).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.White);
Range copyRange = null;
using (ExcelLineWriter writerMaster = new ExcelLineWriter(worksheetMaster, 4, 1, ExcelLineWriter.Direction.Right))
{
foreach (BrokerInfo brokerInfo in brokerList)
{
writerMaster.WriteLine(brokerInfo.hall);
writerMaster.WriteLine(brokerInfo.terminal);
if (!string.IsNullOrEmpty(brokerInfo.ChineseName))
{
if (!string.IsNullOrEmpty(brokerInfo.ChineseLongName))
{
writerMaster.WriteLine(brokerInfo.ChineseLongName);
writerMaster.WriteLine(brokerInfo.ChineseShortName);
}
if (string.IsNullOrEmpty(brokerInfo.ChineseLongName))
{
writerMaster.WriteLine(brokerInfo.ChineseName);
writerMaster.WriteLine(brokerInfo.ChineseName);
ExcelUtil.GetRange(writerMaster.Row, writerMaster.Col - 1, writerMaster.Row, writerMaster.Col, worksheetMaster).Interior.Color = 255;//Highlight as red
}
}
else
{
if (!string.IsNullOrEmpty(brokerInfo.longName))
{
writerMaster.WriteLine(brokerInfo.longName);
writerMaster.WriteLine(brokerInfo.shortName);
}
else
{
writerMaster.WriteLine(brokerInfo.originalName);
writerMaster.WriteLine(brokerInfo.originalName);
ExcelUtil.GetRange(writerMaster.Row, writerMaster.Col - 1, writerMaster.Row, writerMaster.Col, worksheetMaster).Interior.Color = 255;
}
}
writerMaster.PlaceNext(writerMaster.Row + 1, 1);
}
Range r = ExcelUtil.GetRange(4, 1, writerMaster.Row - 1, 1, worksheetMaster);
r.Interior.Color = 13409484.0;
brokerRowsSum = writerMaster.Row - 1;
if (lastUsedRow > writerMaster.Row)
{
Range range = ExcelUtil.GetRange(writerMaster.Row, 1, lastUsedRow, 4, worksheetMaster);
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
}
writerMaster.WriteLine("STOP");
writerMaster.WriteLine("STOP");
writerMaster.WriteLine("STOP");
writerMaster.WriteLine("STOP");
writerMaster.PlaceNext(writerMaster.Row + 1, 2);
writerMaster.WriteLine("STANDARD SECURITIES ASIA LTD");
copyRange = ExcelUtil.GetRange(4, 1, brokerRowsSum, 4, worksheetMaster);
}
return(copyRange);
}
private List <string> GetRicFromFile(List <string> list)
{
List <string> listRic = new List <string>();
string filePath = string.Empty;
if (list == null || list.Count == 0)
{
string msg = string.Format("no download file in this email .");
Logger.Log(msg, Logger.LogType.Warning);
return(null);
}
foreach (var item in list)
{
filePath = item;
break;
}
using (Ric.Util.ExcelApp app = new Ric.Util.ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = (Worksheet)workbook.Worksheets[1];
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
while (reader.Row <= lastUsedRow)
{
string key = reader.ReadLineCellText();
if (key.Equals("CHANGE"))
{
reader.PlaceNext(reader.Row + 11, reader.Col);
}
if (key.Equals("DROP"))
{
reader.PlaceNext(reader.Row + 8, reader.Col);
}
if (key.Equals("RIC"))
{
int lastUsedCol = worksheet.UsedRange.Columns.Count;
string value = string.Empty;
string ricSS = string.Empty;
using (ExcelLineWriter readerCol = new ExcelLineWriter(worksheet, reader.Row - 1, reader.Col + 1, ExcelLineWriter.Direction.Right))
{
while (readerCol.Col <= lastUsedCol)
{
value = readerCol.ReadLineCellText();
if (!string.IsNullOrEmpty(value) && !listRic.Contains(value))
{
listRic.Add(value);
}
if (value.EndsWith(".SS") && value.StartsWith("6"))
{
ricSS = string.Format("{0}.SH", value.Substring(0, value.Length - 3));
if (!string.IsNullOrEmpty(ricSS) && !listRic.Contains(ricSS))
{
listRic.Add(ricSS);
}
}
}
}
}
}
reader.PlaceNext(reader.Row, 1);
}
workbook.Close(false, workbook.FullName, false);
}
return(listRic);
}
private void updateRicAddDicFromDownloadFile()
{
string downloadFilePath = string.Empty;
if (Directory.Exists(configObj.DOWNLOAD_FILE_DIR))
{
string[] files = Directory.GetFiles(configObj.DOWNLOAD_FILE_DIR, "DownloadRicChangeEvents*.xls", SearchOption.TopDirectoryOnly);
if (files.Length != 0)
{
if (files.Length > 1)
{
LogMessage("More than one downloadRicChangeEvents*.xls file found, please have a check, there should be only one download file. ");
}
else
{
downloadFilePath = files[0];
}
//foreach (string file in files)
//{
// string[] arr = file.Split('_');
// if (DateTime.ParseExact(arr[1],"yyyyMMdd",null).Equals(DateTime.Parse(configObj.DATE)))
// {
// downloadFilePath = file;
// }
//}
}
}
if (downloadFilePath == string.Empty)
{
LogMessage("There's no \"DownloadRicChangeEvents\" file under foler " + configObj.DOWNLOAD_FILE_DIR);
}
else
{
using (ExcelApp downloadApp = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(downloadApp, downloadFilePath);
var worksheet = (Worksheet)workbook.Worksheets[2];
if (worksheet == null)
{
LogMessage("There's no sheet2 in the excel file " + workbook.FullName);
}
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right))
{
while (reader.Row <= lastUsedRow)
{
Range changeTypeRange = ExcelUtil.GetRange(reader.Row, 4, worksheet);
Range ricNowRange = ExcelUtil.GetRange(reader.Row, 3, worksheet);
if (changeTypeRange.Text != null && changeTypeRange.Text.ToString().Trim().ToUpper() == "ADD" && ricNowRange.Text != null)
{
string ricNow = ricNowRange.Text.ToString().Trim();
if (addRicDic.ContainsKey(ricNow))
{
addRicDic[ricNow].EffectiveDate = reader.ReadLineCellText(); //Effective Date
addRicDic[ricNow].RicWas = reader.ReadLineCellText(); //Ric Was
addRicDic[ricNow].RicNow = reader.ReadLineCellText(); //Ric Now
addRicDic[ricNow].ChangeType = reader.ReadLineCellText(); //Change Type
addRicDic[ricNow].Country = reader.ReadLineCellText(); //Country
addRicDic[ricNow].Exchanges = reader.ReadLineCellText(); //Exchanges
addRicDic[ricNow].AssetClass = reader.ReadLineCellText(); //Asset Class
addRicDic[ricNow].DescriptionWas = reader.ReadLineCellText();
addRicDic[ricNow].DescriptionNow = reader.ReadLineCellText();
addRicDic[ricNow].SummaryOfChange = reader.ReadLineCellText();
addRicDic[ricNow].ISINWas = reader.ReadLineCellText();
//addRicDic[ricNow].ISINNow = reader.GetCellText();
reader.ReadLineCellText();
addRicDic[ricNow].SecondMarketId = reader.ReadLineCellText();
addRicDic[ricNow].SecondMarketWas = reader.ReadLineCellText();
addRicDic[ricNow].SecondMarketNow = reader.ReadLineCellText();
addRicDic[ricNow].RicSeqId = reader.ReadLineCellText();
}
//reader.PlaceNext(reader.Row + 1, 1);
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
}
}
/// <summary>
/// Write a sector for OSE transaction
/// </summary>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="oseTransSector"></param>
public static void WriteTransCompanyInfoSector(Worksheet worksheet, int startRow, OSETransSector oseTransSector, bool isFuture)
{
int currentRow = startRow;
if (!isFuture)
{
if (oseTransSector.SellCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, 2] = "出来ず";
worksheet.Cells[currentRow, 3] = "UNQ";
}
if (oseTransSector.BuyCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, 5] = "出来ず";
worksheet.Cells[currentRow, 6] = "UNQ";
}
}
else
{
if (oseTransSector.SellCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, 3] = "UNQ";
worksheet.Cells[currentRow, 4] = "出来ず";
}
if (oseTransSector.BuyCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, 7] = "UNQ";
worksheet.Cells[currentRow, 8] = "出来ず";
}
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, currentRow, 2, ExcelLineWriter.Direction.Right))
{
foreach (OSETransSingleCompanyInfo singleCompanyInfo in oseTransSector.SellCompanyInfoList)
{
if (singleCompanyInfo != null && singleCompanyInfo.CompanyInfo != null)
{
if (!string.IsNullOrEmpty(singleCompanyInfo.TransSum))
{
writer.WriteLine(singleCompanyInfo.TransSum.Replace(",", ""));
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
}
else
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
if (string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.EnglishName))
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, writer.Row, writer.Col, worksheet).Font.Color = ColorTranslator.ToOle(Color.Red);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
else
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.EnglishName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
writer.WriteLine(singleCompanyInfo.CompanyInfo.JapaneseName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
writer.WriteLine(singleCompanyInfo.Volume_OP_INT.Replace(",", ""));
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
writer.PlaceNext(writer.Row + 1, 2);
}
ExcelUtil.GetRange(startRow, 5, startRow + oseTransSector.SellCompanyInfoList.Count, 9, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
}
writer.PlaceNext(currentRow, 6);
foreach (OSETransSingleCompanyInfo singleCompanyInfo in oseTransSector.BuyCompanyInfoList.Where(singleCompanyInfo => singleCompanyInfo != null && singleCompanyInfo.CompanyInfo != null))
{
if (!string.IsNullOrEmpty(singleCompanyInfo.TransSum))
{
writer.WriteLine(singleCompanyInfo.TransSum.Replace(",", ""));
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
}
else
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
if (string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.EnglishName))
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, writer.Row, writer.Col, worksheet).Font.Color = ColorTranslator.ToOle(Color.Red);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
else
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.EnglishName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
writer.WriteLine(singleCompanyInfo.CompanyInfo.JapaneseName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
writer.WriteLine(singleCompanyInfo.Volume_OP_INT.Replace(",", ""));
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
writer.PlaceNext(writer.Row + 1, 6);
}
}
}
//
/// <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);
}
