//Update page Id from HKBKC to HKBK for English version broker3h file.
public void UpdatePageId(Worksheet worksheet)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 4, 5, ExcelLineWriter.Direction.Down))
{
while (writer.Row <= lastUsedRow)
{
Range r = ExcelUtil.GetRange(writer.Row, 5, worksheet);
while (r.Text != null && r.Text.ToString().Contains("HKBKC"))
{
writer.WriteLine(r.Text.ToString().Replace("HKBKC", "HKBK"));
writer.PlaceNext(writer.Row + 15, 5);
writer.WriteLine(ExcelUtil.GetRange(writer.Row, 5, worksheet).Text.ToString().Replace("HKBKC", "HKBK"));
}
break;
}
for (int i = 0; i < 3; i++)
{
writer.PlaceNext(2, 9 + i * 3);
while (ExcelUtil.GetRange(writer.Row, 7, worksheet).Text != null && !ExcelUtil.GetRange(writer.Row, 7, worksheet).Text.ToString().Contains("REFER"))
{
Range referRange = ExcelUtil.GetRange(writer.Row, writer.Col, worksheet);
if (referRange.Text != null)
{
writer.WriteLine(referRange.Text.ToString().Replace("HKBKC", "HKBK"));
}
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="results"></param>
private void WriteResultsInCsv(IEnumerable <string> results)
{
using (ExcelApp app = new ExcelApp(false, false))
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.WorkingFolder + "result.csv");
try
{
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
ExcelLineWriter lineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right);
lineWriter.PlaceNext(1, 1);
lineWriter.WriteLine("RIC");
lineWriter.WriteLine("RETIRE DATE");
int line = 2;
foreach (string entry in results)
{
lineWriter.PlaceNext(line, 1);
lineWriter.WriteLine(entry);
lineWriter.WriteLine(DateTime.Now.ToString("dd-MMM-yyyy"));
line++;
}
}
catch (Exception ex)
{
LogMessage("Error while creating CSV", Logger.LogType.Error);
throw new Exception("Error when creating result CSV: " + ex.Message);
}
finally
{
AddResult("TW IO Drop results", configObj.WorkingFolder + "result.csv", "file");
workbook.SaveAs(configObj.WorkingFolder + "result.csv", XlFileFormat.xlCSV);
workbook.Close();
}
}
}
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 static void writeCompanyInfoForSecurityOption(Worksheet worksheet, ExcelLineWriter writer, int startRow, OSETransSingleCompanyInfo singleCompanyInfo)
{
if (singleCompanyInfo != null && singleCompanyInfo.CompanyInfo != null)
{
if (!string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.JapaneseName))
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.JapaneseName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
else
{
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;
}
if (!string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.EnglishName))
{
writer.WriteLine(singleCompanyInfo.CompanyInfo.EnglishName);
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
else
{
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;
}
writer.WriteLine(singleCompanyInfo.Volume_OP_INT.Replace(",", ""));
ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
writer.PlaceNext(writer.Row + 1, writer.Col);
}
}
public void GenerateISINFile(List <RicISINInfo> ricInfoList)
{
using (ExcelApp app = new ExcelApp(false, false))
{
string targetFilePath = NewTargetFilePath(Path.Combine(Path.GetDirectoryName(configObj.SourceFilePath), configObj.TargetIsinFileName));
if (File.Exists(targetFilePath))
{
File.Delete(targetFilePath);
}
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, targetFilePath);
var worksheet = (Worksheet)workbook.Worksheets[1];
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
writer.WriteLine("Ric");
writer.WriteLine("Official Code");
writer.WriteLine("ISIN");
writer.WriteLine("Name");
writer.WriteLine("Type");
writer.PlaceNext(writer.Row + 1, 1);
foreach (RicISINInfo ricISINInfo in ricInfoList)
{
writer.WriteLine(ricISINInfo.ric);
ExcelUtil.GetRange(writer.Row, writer.Col, worksheet).NumberFormat = "@";
writer.WriteLine(ricISINInfo.OfficialCode);
writer.WriteLine(ricISINInfo.ISIN);
writer.WriteLine(ricISINInfo.Name);
writer.WriteLine(ricISINInfo.type);
writer.PlaceNext(writer.Row + 1, 1);
}
}
workbook.Save();
TaskResultList.Add(new TaskResultEntry("Newly Generated ISIN File", "The file contains all the newly generated ISIN.", targetFilePath));
}
}
private void WriteResultsInCsv()
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.ResultFileName);
try
{
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
ExcelLineWriter lineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down);
int col = 1;
bool skip = false;
foreach (List <string> updateEntry in toUpdate)
{
List <string> tmp = updateEntry;
tmp.Sort();
skip = false;
lineWriter.PlaceNext(1, col);
string test = tmp[0];
if (ricExl.ContainsKey("0#" + tmp[0]))
{
lineWriter.WriteLine(ricExl["0#" + tmp[0]]);
}
foreach (string rowEntry in tmp)
{
if (rowEntry.StartsWith("1") || rowEntry.StartsWith("5"))
{
skip = true;
}
string suffix = "";
if (rowEntry.StartsWith("6"))
{
suffix = ".SS";
}
else if ((rowEntry.StartsWith("0") && !rowEntry.StartsWith("0#")) || rowEntry.StartsWith("2") || rowEntry.StartsWith("3"))
{
suffix = ".SZ";
}
if (!rowEntry.StartsWith("1"))
{
lineWriter.WriteLine(rowEntry + suffix);
}
}
if (skip == false)
{
col++;
}
}
}
catch (Exception ex)
{
throw new Exception("Error when creating result CSV: " + ex.Message);
}
finally
{
workbook.SaveAs(configObj.ResultFileName, XlFileFormat.xlCSV);
workbook.Close();
}
}
private void WriterWorksheet(Worksheet worksheet, List <ChinaIndex> indexList, int startPos, int endPos)
{
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
for (int i = startPos; i < endPos; i++)
{
writer.WriteLine(indexList[i].Chain);
writer.WriteLine(indexList[i].ChineseName);
foreach (string ric in indexList[i].RicList)
{
writer.WriteLine(ric);
}
writer.PlaceNext(1, writer.Col + 1);
}
}
}
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 InsertNewDeleteChangeInfo(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> brokerChangeList, Worksheet worksheet)
{
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), brokerChangeList.Count);
if (brokerChangeList.Count == 0)
{
return;
}
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
foreach (BrokerChangeInfo broker in brokerChangeList)
{
writer.PlaceNext(rowPos++, 1);
writer.WriteLine(broker.Name);
writer.WriteLine(broker.Ric);
writer.WriteLine(broker.Type);
writer.WriteLine(broker.Date);
if (IsIPos(broker.Ric))
{
//Mark as yellow
ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0;
}
else
{
ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777215.0;
}
}
ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 4, rowPos - 1, 4, worksheet).NumberFormat = "MM/dd/yyyy";
if (brokerChangeList[0].Type == "delete")
{
if (ParseDateTime(brokerChangeList[0].Date) == GetNextBusinessDay(DateTime.Now, configObj.HOLIDAY_COUNT))
{
//Mark as red
ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 255.0;
}
else
{
ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 0.0;
}
}
else
{
ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 0.0;
}
}
public void WriterWorksheet(Worksheet worksheet, Dictionary <string, List <string> > indicesRicDic, int startPos, int endPos)
{
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
for (int i = startPos; i < endPos; i++)
{
string key = indicesRicDic.Keys.ToList()[i];
writer.WriteLine("0#" + key);
writer.WriteLine(key);
foreach (string ric in indicesRicDic[key])
{
writer.WriteLine(ric);
}
writer.PlaceNext(1, writer.Col + 1);
}
}
}
private void WriteTransactionInfo(IEnumerable <Transaction> transactionList, Worksheet worksheet, int currentRow, int currentCol)
{
for (int i = currentRow; i < currentRow + 15; i++)
{
worksheet.Cells[i, 1] = (i - currentRow + 1).ToString();
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, currentRow, currentCol + 1, ExcelLineWriter.Direction.Right))
{
foreach (Transaction transaction in transactionList)
{
writer.WriteLine(transaction.CompanyInfo.EnglishName);
writer.WriteLine(transaction.CompanyInfo.JapaneseName);
writer.WriteLine(transaction.SellSum);
writer.WriteLine(transaction.BuySum);
writer.WriteLine(transaction.TotalSum);
writer.PlaceNext(writer.Row + 1, currentCol + 1);
}
}
}
public void StartOptionRicGeneratorJob()
{
List <StockOption> stockOptionList = new List <StockOption>();
stockOptionList = GetStockOptionList();
using (ExcelApp app = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, Path.Combine(configObj.RIC_GENERATE_FILE_DIR, NewFileName()));
var worksheet = workbook.Worksheets[1] as Worksheet;
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
foreach (StockOption stockOption in stockOptionList)
{
for (int i = 0; i < stockOption.PutRicList.Count; i++)
{
writer.WriteLine(stockOption.CallRicList[i]);
writer.WriteLine("D" + stockOption.CallRicList[i]);
writer.WriteLine("/" + stockOption.CallRicList[i]);
writer.PlaceNext(writer.Row + 1, 1);
writer.WriteLine(stockOption.PutRicList[i]);
writer.WriteLine("D" + stockOption.PutRicList[i]);
writer.WriteLine("/" + stockOption.PutRicList[i]);
writer.PlaceNext(writer.Row + 1, 1);
}
}
}
workbook.Save();
}
}
public void InsertBordSizeChangeInfo(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> brokerChangeList, List <BordLotSizeChangeInfo> bordLotSizeChangeInfoList, Worksheet worksheet)
{
ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), brokerChangeList.Count);
if (brokerChangeList.Count == 0)
{
return;
}
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
foreach (BrokerChangeInfo broker in brokerChangeList)
{
string type = string.Empty;
writer.PlaceNext(rowPos++, 1);
writer.WriteLine(broker.Name);
writer.WriteLine(broker.Ric);
foreach (BordLotSizeChangeInfo bordLotSizeChangeInfo in bordLotSizeChangeInfoList)
{
if (bordLotSizeChangeInfo.Name == broker.Name)
{
type = string.Format("Bord Lot Size Change to {0}", bordLotSizeChangeInfo.BoardLotSize);
break;
}
else
{
continue;
}
}
writer.WriteLine(type);
writer.WriteLine(broker.Date);
if (IsIPos(broker.Ric))
{
ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0;
}
else
{
ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777215.0;
}
}
}
//Update C column value as the English version name
public void UpdateCColAsEnglish(List <BrokerInfo> brokerList, Worksheet worksheet)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
ExcelUtil.GetRange(4, 2, lastUsedRow, 4, worksheet).Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.White);
using (ExcelLineWriter EnglishWriter = new ExcelLineWriter(worksheet, 4, 3, ExcelLineWriter.Direction.Down))
{
foreach (BrokerInfo broker in brokerList)
{
if (!string.IsNullOrEmpty(broker.longName))
{
EnglishWriter.WriteLine(broker.longName);
}
else
{
EnglishWriter.WriteLine(broker.originalName);
ExcelUtil.GetRange(EnglishWriter.Row, EnglishWriter.Col, worksheet).Interior.Color = 255.0;
}
}
}
}
//Update Ration column
public void UpdateRation(Worksheet worksheetInput)
{
List <BullBearInfo> bullBearInfoList = GetBullBearInfo();
using (ExcelLineWriter writer = new ExcelLineWriter(worksheetInput, 4, 11, ExcelLineWriter.Direction.Down))
{
while ((ExcelUtil.GetRange(writer.Row, 1, worksheetInput).Text != null) && (ExcelUtil.GetRange(writer.Row, 1, worksheetInput).Text.ToString().Trim() != "0"))
{
foreach (BullBearInfo bullBear in bullBearInfoList.Where(bullBear => ExcelUtil.GetRange(writer.Row, 1, worksheetInput).Text.ToString() == bullBear.Code))
{
writer.WriteLine(bullBear.Ratio);
break;
}
}
}
}
//Set A col value as "Create" for TemplateV2
public void SetCreateValue(Worksheet worksheet, int lastRowNum)
{
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Down))
{
while (ExcelUtil.GetRange(writer.Row, 1, worksheet) != null)
{
string value = ExcelUtil.GetRange(writer.Row, 3, worksheet).Value2.ToString().ToLower();
if (value.Contains("add") || value.Contains("delete") || value.Contains("change"))
{
writer.WriteLine("Create");
}
else
{
break;
}
}
}
}
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 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>
/// 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);
}
}
}
private void updateTemplateV2File()
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH);
Worksheet worksheet = ExcelUtil.GetWorksheet(configObj.WORKSHEET_NAME_TEMPLATE, workbook);
if (worksheet == null)
{
LogMessage(string.Format("There's no worksheet {0} in the file {1}", configObj.WORKSHEET_NAME_TEMPLATE, workbook.Name));
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right))
{
foreach (string key in addRicDic.Keys)
{
writer.WriteLine("Revise"); //EventAction
writer.WriteLine(key.Remove(key.IndexOf('.'))); //Ric SeqId
writer.WriteLine("Add"); //Change Type
writer.WriteLine(DateTime.Parse(addRicDic[key].EffectiveDate).ToString("ddMMMyy")); // Date
writer.WriteLine(""); //Description Was
writer.WriteLine(addRicDic[key].DescriptionNow); //Description Now
writer.WriteLine(""); //Ric Was
writer.WriteLine(addRicDic[key].RicNow); //RICNow
writer.WriteLine(""); //ISINWas
writer.WriteLine(addRicDic[key].ISIN); //ISINNow
writer.WriteLine("Official Code"); //2ndId
writer.WriteLine(""); //2ndWas
writer.WriteLine(addRicDic[key].SecondMarketNow); //2ndNow
writer.WriteLine(""); //ThomsonWas
writer.WriteLine(""); //ThomsonNow
writer.WriteLine("");
writer.WriteLine(addRicDic[key].Exchanges); //Exchange
writer.WriteLine(addRicDic[key].AssetClass); //Asset
writer.PlaceNext(writer.Row + 1, 1);
}
}
//Run Macro
worksheet.Activate();
app.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
app.ExcelAppInstance,
new object[] { "FormatData" });
string targetFileName = Path.Combine(Path.GetDirectoryName(configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH), configObj.DATE);
targetFileName += Path.GetFileName(configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH);
workbook.SaveCopyAs(targetFileName);
workbook.Close(false, workbook.FullName, false);
AddResult("Target file ", targetFileName, "The file has been updated with ISIN");
}
}
public void GenerateSCBFile(SCBChangeInfo changeInfo)
{
using (ExcelApp app = new ExcelApp(false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.GENERATED_SCB_FILE_PATH);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1;
ExcelUtil.GetRange(1, 1, lastUsedRow, lastUsedCol, worksheet).ClearContents();
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right))
{
writer.WriteLine("EventType");
writer.WriteLine("Function");
writer.WriteLine("FILE_CODE");
writer.WriteLine("ISIN");
writer.WriteLine("SEDOL");
writer.WriteLine("OLD TICKER");
writer.WriteLine("NEW TICKER");
writer.WriteLine("Old RIC");
writer.WriteLine("New RIC");
writer.WriteLine("MIC");
writer.WriteLine("EXCHANGE_CODE");
writer.WriteLine("EXCHANGE_NAME");
writer.WriteLine("OLD COMPANY_NAME");
writer.WriteLine("NEW COMPANY_NAME");
writer.WriteLine("ANNOUNCEMENT DATE");
writer.WriteLine("EFFECTIVE DATE");
writer.PlaceNext(2, 1);
writer.WriteLine(changeInfo.EventType);
writer.WriteLine(changeInfo.Function);
writer.WriteLine(changeInfo.FileCode);
writer.WriteLine(changeInfo.Isin);
writer.WriteLine(changeInfo.Sedol);
writer.WriteLine(changeInfo.OldTicker);
writer.WriteLine(changeInfo.NewTicker);
writer.WriteLine(changeInfo.OldRic);
writer.WriteLine(changeInfo.NewRic);
writer.WriteLine(changeInfo.Mic);
writer.WriteLine(changeInfo.ExchangeCode);
writer.WriteLine(changeInfo.ExchangeName);
writer.WriteLine(changeInfo.OldCompName);
writer.WriteLine(changeInfo.NewCompName);
writer.WriteLine(changeInfo.AnnouncementDate);
writer.WriteLine(changeInfo.EffectiveDate);
}
workbook.SaveCopyAs(GetSCBGenerateFilePath(configObj.GENERATED_SCB_FILE_PATH));
workbook.Close(false, workbook.FullName, false);
}
}
private void UpdateAddDropFile(string templateFilePath, List <AddDropChangeInfo> addDropEventList)
{
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 (AddDropChangeInfo changeInfo in addDropEventList)
{
writer.WriteLine(changeInfo.EventAction);
writer.WriteLine(changeInfo.RicSeqId);
writer.WriteLine(changeInfo.ChangeType);
writer.WriteLine(changeInfo.Date.ToString("ddMMMyy"));
writer.WriteLine(changeInfo.DescriptionWas);
writer.WriteLine(changeInfo.DescriptionNow);
writer.WriteLine(changeInfo.RicWas);
writer.WriteLine(changeInfo.RicNow);
writer.WriteLine(changeInfo.ISINWas);
writer.WriteLine(changeInfo.ISINNow);
writer.WriteLine(changeInfo.SecondID);
writer.WriteLine(changeInfo.SecondWas);
writer.WriteLine(changeInfo.SecondNow);
writer.WriteLine(changeInfo.ThomsonWas);
writer.WriteLine(changeInfo.ThomsonNow);
writer.WriteLine("");
writer.WriteLine(changeInfo.Exchange);
writer.WriteLine(changeInfo.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.Save();
workbook.Close(false, templateFilePath, false);
}
}
//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 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);
}
}
//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();
}
}
}
/// <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);
}
}
}
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);
}
}