public void UpdateSheet5(Worksheet worksheet5, List <BordLotSizeChangeInfo> bordLotSizeChangeList)
{
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet5, 2, 2, ExcelLineWriter.Direction.Down))
{
while (true)
{
Range nameCell = ExcelUtil.GetRange(writer.Row, 1, worksheet5);
if (nameCell == null || nameCell.Text.ToString().Trim() == "0")
{
break;
}
else
{
if (bordLotSizeChangeList != null && bordLotSizeChangeList.Count != 0)
{
if (ExcelUtil.GetRange(writer.Row, 3, worksheet5).Text.ToString().Trim() == "#N/A")
{
writer.PlaceNextAndWriteLine(writer.Row, 3, "CHANGE");
writer.PlaceNext(writer.Row - 1, writer.Col);
}
}
Range r = ExcelUtil.GetRange(writer.Row, 2, worksheet5);
string line = r.Value2.ToString().Trim(new char[] { '>', '<' });
writer.PlaceNextAndWriteLine(writer.Row, 2, line);
}
}
}
}
public void UpdateAddDropSheet(Worksheet worksheetAddDrop, List <BordLotSizeChangeInfo> bordLotSizeChangeList)
{
using (ExcelLineWriter writer = new ExcelLineWriter(worksheetAddDrop, 2, 1, ExcelLineWriter.Direction.Down))
{
while (true)
{
Range changeTypeRange = ExcelUtil.GetRange(writer.Row, 1, worksheetAddDrop);
if (changeTypeRange == null || changeTypeRange.Text.ToString().Trim() == "#N/A")
{
break;
}
else
{
if (changeTypeRange != null && changeTypeRange.Text.ToString().Trim() == "FALSE")
{
writer.WriteLine("CHANGE");
writer.PlaceNextAndWriteLine(writer.Row, 3, ExcelUtil.GetRange(writer.Row, 4, worksheetAddDrop).Text.ToString());
}
else if (changeTypeRange != null && changeTypeRange.Text.ToString().Trim() == "Change")
{
writer.PlaceNextAndWriteLine(writer.Row, 3, ExcelUtil.GetRange(writer.Row, 4, worksheetAddDrop).Text.ToString());
}
else
{
writer.MoveNext();
}
}
}
}
}
public void InsertNameChange(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> nameChangeList, Worksheet worksheet)
{
if (nameChangeList == null || nameChangeList.Count == 0)
{
return;
}
else
{
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), 2 * nameChangeList.Count);
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
foreach (BrokerChangeInfo nameChange in nameChangeList)
{
writer.PlaceNext(rowPos++, 2);
writer.WriteLine(nameChange.Ric);
writer.WriteLine(nameChange.Type);
writer.WriteLine(nameChange.Date);
writer.PlaceNext(rowPos++, 1);
writer.WriteLine(nameChange.Name);
writer.PlaceNextAndWriteLine(rowPos, 4, nameChange.Date);
if (IsIPos(nameChange.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 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;
}
}
}
}
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>
/// 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();
}
}
}