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 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));
}
}
/// <summary>
/// Write a sector for Security Option transaction
/// </summary>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="oseTransSector"></param>
public static void WriteTransCompanyInfosForSecurityOption(Worksheet worksheet, int startRow, int curCol, SecurityOptionSector securityOptionTransSector)
{
int currentRow = startRow;
if (securityOptionTransSector.SellCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, curCol + 1] = "UNQ";
worksheet.Cells[currentRow, curCol] = "出来ず";
}
if (securityOptionTransSector.BuyCompanyInfoList.Count == 0)
{
worksheet.Cells[currentRow, curCol + 4] = "UNQ";
worksheet.Cells[currentRow, curCol + 3] = "出来ず";
}
using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, currentRow, curCol, ExcelLineWriter.Direction.Right))
{
for (int i = 0; i < securityOptionTransSector.SellCompanyInfoList.Count; i++)
{
OSETransSingleCompanyInfo singleCompanyInfo = securityOptionTransSector.SellCompanyInfoList[i];
writeCompanyInfoForSecurityOption(worksheet, writer, currentRow, singleCompanyInfo);
writer.PlaceNext(startRow + i + 1, curCol);
}
writer.PlaceNext(startRow, curCol + 3);
for (int i = 0; i < securityOptionTransSector.BuyCompanyInfoList.Count; i++)
{
OSETransSingleCompanyInfo singleCompanyInfo = securityOptionTransSector.BuyCompanyInfoList[i];
writeCompanyInfoForSecurityOption(worksheet, writer, startRow, singleCompanyInfo);
writer.PlaceNext(startRow + i + 1, curCol + 3);
}
}
}
//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"));
}
}
}
}
}
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;
}
}
}
}
/// <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();
}
}
}
private void ReadFile(List <WrtQuaNotHK> list, string path)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, path);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 7, ExcelLineWriter.Direction.Right))
{
string code = string.Empty;
string listingDate = string.Empty;
string issuePrice = string.Empty;
string furtherIssueSize = string.Empty;
for (int i = 1; i <= lastUsedRow; i++)
{
listingDate = reader.ReadLineCellText();
if (listingDate != null && listingDate.Trim() != "")
{
if (NextWorkDay().ToString("M/d/yyyy").Equals(listingDate.Trim()))
{
reader.PlaceNext(reader.Row, 2);
code = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 8);
issuePrice = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 9);
furtherIssueSize = reader.ReadLineCellText().Replace(",", "");
if (code == null || issuePrice == null || furtherIssueSize == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
WrtQuaNotHK wrt = new WrtQuaNotHK();
wrt.LogicalKey = list.Count + 1;
wrt.SecondaryID = code.Trim();
wrt.ISIN = GetISIN(wrt.SecondaryID);
wrt.SecondaryIDType = "HONG KONG CODE";
wrt.EHIssueQuantity = "N";
wrt.IssueQuantity = GetIssueQuantity(wrt.SecondaryID, furtherIssueSize);
wrt.Action = "I";
wrt.Note1Type = "O";
wrt.TranchePrice = issuePrice.Replace("HKD", "").Trim();
wrt.Note1 = string.Format("Further issue with issue price HKD {0} on {1}.", issuePrice.Replace("HKD", "").Trim(), NextWorkDay().ToString("dd-MMM-yyyy"));
wrt.TrancheListingDate = DateTime.Parse(listingDate).ToString("dd-MMM-yyyy");
list.Add(wrt);
}
}
reader.PlaceNext(reader.Row + 1, 7);
}
}
}
}
private List <FMELWEntity> ReadFile(string filePath, List <FMELWEntity> listFMELW)
{
List <FMELWEntity> list = new List <FMELWEntity>();
if (!File.Exists(filePath))
{
string msg = string.Format("the file [{0}] is not exist.", filePath);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
try
{
using (ExcelApp eApp = new ExcelApp(false, false))
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(eApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Worksheet could not be created. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
//wSheet.Name = "FM";
int lastUsedRow = wSheet.UsedRange.Row + wSheet.UsedRange.Rows.Count - 1;//193=1+193-1
using (ExcelLineWriter reader = new ExcelLineWriter(wSheet, 2, 2, ExcelLineWriter.Direction.Right))
{
while (reader.Row <= lastUsedRow)
{
FMELWEntity fm = new FMELWEntity();
fm.ISIN = reader.ReadLineCellText();//web.StandardCongenial
reader.PlaceNext(reader.Row, 4);
fm.IssuingAuthority = reader.ReadLineValue2();
reader.PlaceNext(reader.Row + 1, 2);
if (!string.IsNullOrEmpty(fm.ISIN.Trim()) && !string.IsNullOrEmpty(fm.IssuingAuthority.Trim()))
{
list.Add(fm);
}
}
}
}
return(list);
}
catch (Exception ex)
{
string msg = string.Format("read xls file error :{0}", ex.ToString());
Logger.Log(msg);
return(null);
}
}
private void FillInListTemplate(List <IssueAssetAddTemplate> listIAATemplate)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkQAAddFileName);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right))
{
string ric = string.Empty;
string warrantIssueQuantity = string.Empty; //18
string tranche = string.Empty; //17
string trancheListingAate = string.Empty; //16
for (int i = 1; i <= lastUsedRow; i++)
{
ric = reader.ReadLineCellText();
if (ric == null || ric.Trim() == "")
{
continue;
}
foreach (var item in listIAATemplate)
{
if (!(item.HongKongCode.Trim() + ".HK").Equals(ric.Trim()))
{
continue;
}
reader.PlaceNext(reader.Row, 16);
trancheListingAate = DateTime.Parse(reader.ReadLineCellText()).ToString("dd-MMM-yyyy");
reader.PlaceNext(reader.Row, 17);
tranche = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 18);
warrantIssueQuantity = reader.ReadLineValue2();
if (trancheListingAate == null || tranche == null || warrantIssueQuantity == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
item.TrancheListingDate = trancheListingAate;
item.TranchePrice = tranche;
item.WarrantIssueQuantity = warrantIssueQuantity;
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
}
private void GetHongKongCodeToList(List <IssueAssetAddTemplate> listIAATemplate)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkIAAddFileName);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right))
{
string ric = string.Empty;
string type = string.Empty;
string category = string.Empty;
string rcsAssrtClass = string.Empty;
string warrantIssue = string.Empty;
for (int i = 1; i <= lastUsedRow; i++)
{
ric = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 2);
type = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 3);
category = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 4);
rcsAssrtClass = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 5);
warrantIssue = reader.ReadLineCellText();
if (ric == null || type == null || category == null || rcsAssrtClass == null || warrantIssue == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
//if (string.IsNullOrWhiteSpace(ric))
// continue;
IssueAssetAddTemplate iaat = new IssueAssetAddTemplate();
iaat.HongKongCode = ric;
iaat.Type = type;
iaat.Category = category;
iaat.RcsAssetClass = rcsAssrtClass;
iaat.WarrantIssuer = warrantIssue;
listIAATemplate.Add(iaat);
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
}
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);
}
}
//Update BN underlying column: (Removing the first number redundant "0", 00005.HK --> 0005.HK, 00038.HK --> 0038.HK, and changes "HSC" to "HSCE"
//There's a bug in the macro, this method is a work around
public void UpdateBNColumn(Worksheet worksheet)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 66, ExcelLineWriter.Direction.Down))
{
while (reader.Row <= lastUsedRow)
{
var cellValue = ExcelUtil.GetRange(reader.Row, reader.Col, worksheet).Text;
if (cellValue != null)
{
string text = cellValue.ToString().Trim().ToUpper();
if (text.Contains(".HSC"))
{
reader.WriteInPlace(text.Replace(".HSC", ".HSCE"));
}
//00005.HK
else if (text.Contains(".HK"))
{
string underlyingCode = text.Substring(0, text.IndexOf('.'));
if (underlyingCode.Length == 5 && underlyingCode.StartsWith("0"))
{
reader.WriteInPlace(text.Remove(0, 1));
}
}
}
reader.PlaceNext(reader.Row + 1, reader.Col);
}
}
}
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);
}
}
}
}
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 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);
}
}
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 getNameMap()
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, NAME_MAP_FILE_PATH);
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.Right))
{
while (reader.Row <= lastUsedRow)
{
string englishOrigName = reader.ReadLineCellText().ToUpper();
if (!englishNameMap.ContainsKey(englishOrigName))
{
englishNameMap.Add(englishOrigName, new HKBrokerPageNameMap());
englishNameMap[englishOrigName].longName = reader.ReadLineCellText().ToUpper();
englishNameMap[englishOrigName].shortName = reader.ReadLineCellText().ToUpper();
}
else
{
reader.PlaceNext(reader.Row, reader.Col + 2);
}
reader.MoveNext();
string chineseOrigName = reader.ReadLineCellText().ToUpper();
if (!chineseNameMap.ContainsKey(chineseOrigName))
{
chineseNameMap.Add(chineseOrigName, new HKBrokerPageNameMap());
chineseNameMap[chineseOrigName].longName = reader.ReadLineCellText().ToUpper();
chineseNameMap[chineseOrigName].shortName = reader.ReadLineCellText().ToUpper();
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
workbook.Close(false, workbook.FullName, false);
}
}
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 generateTXTFile(Workbook warGuideBWorkbook)
{
Worksheet insertV3Sheet = ExcelUtil.GetWorksheet(configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook);
if (insertV3Sheet == null)
{
LogMessage(string.Format("There's no worksheet {0} in workbook {1}", configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook.FullName));
}
int lastUsedRow = insertV3Sheet.UsedRange.Row + insertV3Sheet.UsedRange.Rows.Count - 1;
var sb = new StringBuilder();
sb.AppendLine("HKSE;29-JUN-29;7:1;TPS;");
sb.AppendLine("ROW80_5;ROW80_6;ROW80_7;ROW80_8;ROW80_9;ROW80_10;ROW80_11;ROW80_12;ROW80_13;ROW80_14;ROW80_15;ROW80_16;ROW80_17;ROW80_18;ROW80_19;ROW80_20;ROW80_21;ROW80_22;ROW80_23;ROW80_24;ROW80_25;");
sb.Append("HK/WTS06;");
using (var reader = new ExcelLineWriter(insertV3Sheet, 86, 1, ExcelLineWriter.Direction.Right))
{
string line = string.Empty;
int pageNum = -1;
while (reader.Row <= lastUsedRow)
{
line += reader.ReadLineCellText().PadRight(9);
line += reader.ReadLineCellText().PadRight(16);
line += reader.ReadLineCellText().PadLeft(9);
line += reader.ReadLineCellText().PadLeft(8);
line += reader.ReadLineCellText().PadRight(10);
line += reader.ReadLineCellText().PadLeft(9);
line += reader.ReadLineCellText().PadLeft(8);
line += ";";
sb.Append(line);
line = string.Empty;
if (reader.Row % 20 == 5)
{
pageNum = reader.Row / 20 + 1;
sb.AppendLine(string.Format(" PREV <HK/WTS{0}> / NEXT <HK/WTS{1}>;", (pageNum - 1).ToString("D2"), (pageNum + 1).ToString("D2")));
line = string.Format("HK/WTS{0};", (pageNum + 1).ToString("D2"));
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
using (var sw = new StreamWriter(Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), false, Encoding.UTF8))
{
sw.WriteLine(sb.ToString());
sw.AutoFlush = true;
sw.Close();
AddResult("war_guideB.txt", Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), "file");
}
}
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 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 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);
}
}
}
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;
}
}
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 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;
}
}
}
/// <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 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);
}
}
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);
}
}
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);
}