/// <summary>
/// Read FM file
/// </summary>
/// <param name="fmFilePath">The full path of the FM file.</param>
/// <returns>The main Ric list.</returns>
private List <string> GetMainRic(string fmFilePath)
{
List <string> ricList = new List <string>();
using (ExcelApp app = new ExcelApp(false, false))
{
if (!File.Exists(fmFilePath))
{
Logger.Log(string.Format("Can't find the FM file in the path {0} .", fmFilePath));
return(ricList);
}
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, fmFilePath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
string ric = null;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 3, ExcelLineWriter.Direction.Down))
{
ric = reader.ReadLineCellText();
while (!string.IsNullOrEmpty(ric))
{
ricList.Add(ric);
ric = reader.ReadLineCellText();
}
}
workbook.Close(false, workbook.FullName, false);
}
return(ricList);
}
public void UpdateTemplateV2File(Range A2Range, int lastRowContainInfo)
{
A2Range.Copy(Missing.Value);
string TemplateV2FilePath = BackupFiles(configObj.RIC_COWS_TEMPLATE_V2_FILE_PATH);
using (ExcelApp appTemplateV2 = new ExcelApp(false, false))
{
var workbookTemplateV2 = ExcelUtil.CreateOrOpenExcelFile(appTemplateV2, TemplateV2FilePath);
var worksheetTemplateV2 = ExcelUtil.GetWorksheet(configObj.TEMPLATE_V2_WORKSHEET_NAME, workbookTemplateV2);
if (worksheetTemplateV2 == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.TEMPLATE_V2_WORKSHEET_NAME, workbookTemplateV2.Name));
}
Range C3Range = ExcelUtil.GetRange(3, 3, lastRowContainInfo, 18, worksheetTemplateV2);
C3Range.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);
ExcelUtil.GetRange(1, 1, worksheetTemplateV2).Copy(Missing.Value);
SetCreateValue(worksheetTemplateV2, lastRowContainInfo);
//Run Macros
appTemplateV2.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
appTemplateV2.ExcelAppInstance,
new object[] { "FormatData" });
workbookTemplateV2.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbookTemplateV2.FullName), Path.GetFileName(configObj.RIC_COWS_TEMPLATE_V2_FILE_PATH)));
workbookTemplateV2.Close(false, workbookTemplateV2.FullName, false);
File.Delete(TemplateV2FilePath);
}
}
private void downloadAndParseIndexFile(string url)
{
string szseIndexFilePath = targetDownloadFileDir();
szseIndexFilePath += "\\SZSE";
if (!Directory.Exists(szseIndexFilePath))
{
Directory.CreateDirectory(szseIndexFilePath);
}
szseIndexFilePath += "\\Index.xls";
WebClientUtil.DownloadFile(url, 180000, szseIndexFilePath);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, szseIndexFilePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 2; i <= lastUsedRow; i++)
{
Range aRange = ExcelUtil.GetRange(i, 1, worksheet);
if (aRange != null && aRange.Text.ToString().Trim() != string.Empty)
{
ChinaIndex index = new ChinaIndex();
index.Chain = aRange.Text.ToString().Trim();
index.ChineseName = ExcelUtil.GetRange(i, 2, worksheet).Text.ToString();
szseIndexList.Add(index);
}
}
}
}
private void generateFile(List <ChinaIndex> indexList, string fileName)
{
if (indexList.Count == 0)
{
Logger.Log("No item in the index list.");
return;
}
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, string.Format("{0}\\{1}", configObj.TargetFileDir, fileName));
int sheetNum = (indexList.Count + configObj.ChainNumPerSheet - 1) / configObj.ChainNumPerSheet;
int startPos = 0;
if (sheetNum > workbook.Worksheets.Count)
{
workbook.Worksheets.Add(Missing.Value, Missing.Value, sheetNum - workbook.Worksheets.Count, Missing.Value);
}
for (int i = 0; i < sheetNum; i++)
{
var worksheet = workbook.Worksheets[i + 1] as Worksheet;
startPos = i * configObj.ChainNumPerSheet;
int endPos = indexList.Count < (startPos + configObj.ChainNumPerSheet) ? indexList.Count : (startPos + configObj.ChainNumPerSheet);
WriterWorksheet(worksheet, indexList, startPos, endPos);
}
TaskResultList.Add(new TaskResultEntry(fileName, "", workbook.FullName));
workbook.Save();
workbook.Close(true, workbook.FullName, true);
}
}
private void updateSZSEIndexListWithRic()
{
foreach (ChinaIndex index in szseIndexList)
{
//http://www.szse.cn/szseWeb/FrontController.szse?ACTIONID=8&CATALOGID=1747&TABKEY=tab1&ENCODE=1&ZSDM=399328
string url = string.Format("{0}/szseWeb/FrontController.szse?ACTIONID=8&CATALOGID=1747&TABKEY=tab1&ENCODE=1&ZSDM={1}", configObj.SzseBaseUri, index.Chain);
//string pageSource = WebClientUtil.GetPageSource(null, url, 180000, "", Encoding.GetEncoding("gb2312"));
string ricFilePath = targetDownloadFileDir() + "\\SZSE\\";
ricFilePath += string.Format("{0}.xls", index.Chain);
WebClientUtil.DownloadFile(url, 180000, ricFilePath);
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, ricFilePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 2; i <= lastUsedRow; i++)
{
Range aRange = ExcelUtil.GetRange(i, 1, worksheet);
if (aRange != null && !string.IsNullOrEmpty(aRange.Text.ToString().Trim()))
{
index.RicList.Add(generateRic(aRange.Text.ToString().Trim()));
}
}
}
}
}
private Dictionary <string, string> GetExlList()
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.RicListExcelPath);
Dictionary <string, string> exls = new Dictionary <string, string>();
for (int position = 1; position <= 3; position++)
{
int count = 0;
List <List <string> > values = workbook.ToList(position);
foreach (List <string> value in values)
{
if (count == 0)
{
count++;
continue;
}
if (!exls.ContainsKey(value[0].Trim()))
{
exls.Add(value[0].Trim(), value[2]);
}
}
}
return(exls);
}
/// <summary>
/// Read the CSV from path in parameters, parse it and return dictionary with wanted informations
/// </summary>
/// <param name="filename"></param>
/// <returns>A dictionary with the wanted
/// field name as key and their value as value</returns>
private Dictionary <string, string> ReadExcel(string filename)
{
var entry = new Dictionary <string, string>();
var excelValues = new List <List <string> >();
try
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, filename);
excelValues = workbook.ToList();
foreach (List <string> row in excelValues.Where(row => fieldList.Contains(row[0].Replace(" (NEW):", ":"))))
{
if (entry.ContainsKey(row[0].Replace(" (NEW):", ":")))
{
entry[row[0].Replace(" (NEW):", ":")] += row[1];
}
else
{
entry.Add(row[0], row[1]);
}
}
workbook.Close();
}
catch (Exception ex)
{
string msg = "Cannot read CSV file :" + ex;
Logger.Log(msg, Logger.LogType.Error);
CleanExit();
}
return(entry);
}
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 List <List <string> > GetDataFromFile(string path, int position)
{
try
{
if (!File.Exists(path))
{
Logger.Log(string.Format("file :{0} is not esist.", path), Logger.LogType.Error);
return(null);
}
ExcelApp app = new ExcelApp(false, false);
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.InputFilePath);
if (position > workbook.Sheets.Count)
{
Logger.Log(string.Format("Workbook sheet count:{0}, error file.", workbook.Sheets.Count.ToString()), Logger.LogType.Error);
return(null);
}
return(WorkbookExtension.ToList(workbook, position));
}
catch (Exception ex)
{
string msg = string.Format("\r\n ClassName: {0}\r\n MethodName: {1}\r\n Message: {2}",
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.ToString(),
System.Reflection.MethodBase.GetCurrentMethod().Name,
ex.Message);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
}
/// <summary>
/// Get worksheet with source information
/// </summary>
/// <returns>the worksheet</returns>
private Worksheet GetWorksheet(out Workbook workbook)
{
workbook = ExcelUtil.CreateOrOpenExcelFile(app, excelPath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
return(worksheet);
}
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();
}
}
/// <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 JpTRADGInfo printAndGetJpDailyTradingInfo(string filePath)
{
JpTRADGInfo tradgInfo = new JpTRADGInfo();
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
JapanShared.PrintWorksheet(worksheet, printerName, XlPageOrientation.xlPortrait);
tradgInfo.GSellSum = ExcelUtil.GetRange(7, 5, worksheet).Value2.ToString().Trim();
tradgInfo.GBuySum = ExcelUtil.GetRange(7, 10, worksheet).Value2.ToString().Trim();
tradgInfo.CurrentSellSum = ExcelUtil.GetRange(12, 5, worksheet).Value2.ToString().Trim();
tradgInfo.NextSellSum = ExcelUtil.GetRange(12, 6, worksheet).Value2.ToString().Trim();
tradgInfo.TotalSellSum = ExcelUtil.GetRange(12, 8, worksheet).Value2.ToString().Trim();
tradgInfo.CurrentNChg1 = ExcelUtil.GetRange(13, 5, worksheet).Value2.ToString();
tradgInfo.NextNChg1 = ExcelUtil.GetRange(13, 6, worksheet).Value2.ToString();
tradgInfo.TotalNchg1 = ExcelUtil.GetRange(13, 8, worksheet).Value2.ToString();
tradgInfo.CurrentBuySum = ExcelUtil.GetRange(12, 10, worksheet).Value2.ToString();
tradgInfo.NextBuySum = ExcelUtil.GetRange(12, 12, worksheet).Value2.ToString();
tradgInfo.TotalBuySum = ExcelUtil.GetRange(12, 14, worksheet).Value2.ToString();
tradgInfo.CurrentNChg2 = ExcelUtil.GetRange(13, 10, worksheet).Value2.ToString();
tradgInfo.NextNChg2 = ExcelUtil.GetRange(13, 12, worksheet).Value2.ToString();
tradgInfo.TotalNchg2 = ExcelUtil.GetRange(13, 14, worksheet).Value2.ToString().Trim();
tradgInfo.Date = GetDateTime(ExcelUtil.GetRange(33, 3, worksheet).Value2.ToString().Trim()).ToString("ddMMMyy");
int currentRow = 37;
while (ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Trim() != "-")
{
Transaction tran = new Transaction
{
CompanyInfo =
{
OriginalName =
ExcelUtil.GetRange(currentRow, 3, worksheet).Value2.ToString().Replace(" ", "")
}
};
if (nameDic.ContainsKey(tran.CompanyInfo.OriginalName))
{
tran.CompanyInfo.EnglishName = nameDic[tran.CompanyInfo.OriginalName].EnglistName;
tran.CompanyInfo.ShortEnglishName = nameDic[tran.CompanyInfo.OriginalName].ShortName;
tran.CompanyInfo.JapaneseName = nameDic[tran.CompanyInfo.OriginalName].JapaneseName;
}
else
{
Logger.Log(string.Format("There's no such name for {0}, please check the baknote file.", tran.CompanyInfo.OriginalName), Logger.LogType.Warning);
}
tran.SellSum = ExcelUtil.GetRange(currentRow, 7, worksheet).Value2.ToString();
tran.BuySum = ExcelUtil.GetRange(currentRow, 9, worksheet).Value2.ToString();
tran.TotalSum = ExcelUtil.GetRange(currentRow, 13, worksheet).Value2.ToString();
tradgInfo.TransactionList.Add(tran);
currentRow++;
}
tradgInfo.Top15SellSum = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString().Trim();
tradgInfo.Top15BuySum = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString().Trim();
tradgInfo.Top15TotalSum = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString().Trim();
tradgInfo.GTotalSum = ExcelUtil.GetRange(53, 13, worksheet).Value2.ToString().Trim();
workbook.Close(false, workbook.FullName, Missing.Value);
}
return(tradgInfo);
}
/// <summary>
/// Initialize the Workbook and worksheet depending the type
/// </summary>
/// <param name="workbook"></param>
/// <param name="worksheet"></param>
/// <param name="type"></param>
private void InitWorkbookAndWorksheet(out Workbook workbook, out Worksheet worksheet, string type)
{
string titleWork = configObj.ResultsFolder + type + "_" + DateTime.Now.ToString("dMMMyyyy") + ".csv";
workbook = ExcelUtil.CreateOrOpenExcelFile(app, titleWork);
worksheet = workbook.Worksheets[1] as Worksheet;
worksheet.Name = type;
}
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();
}
}
/// <summary>
/// Get nameMap from baknote.xls file
/// </summary>
/// <param name="bakNoteFilePath">baknote file path</param>
/// <param name="worksheetName"></param>
/// <returns></returns>
public static Dictionary <string, NameMap> GetNameMap(string bakNoteFilePath, string worksheetName)
{
Dictionary <string, NameMap> nameDic = new Dictionary <string, NameMap>();
try
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, bakNoteFilePath);
var worksheet = ExcelUtil.GetWorksheet(worksheetName, workbook);
if (worksheet == null)
{
throw new System.Exception(string.Format("There's no such worksheet {0}", worksheetName));
}
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 4; i <= lastUsedRow; i++)
{
string key = string.Empty;
if (ExcelUtil.GetRange(i, 4, worksheet).Value2 != null && ExcelUtil.GetRange(i, 4, worksheet).Value2.ToString() != string.Empty)
{
key = ExcelUtil.GetRange(i, 2, worksheet).Value2.ToString().Trim();
}
if (!nameDic.ContainsKey(key))
{
NameMap map = new NameMap();
if (ExcelUtil.GetRange(i, 4, worksheet).Value2 != null)
{
map.JapaneseName = ExcelUtil.GetRange(i, 4, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 3, worksheet).Value2 != null)
{
map.EnglistName = ExcelUtil.GetRange(i, 3, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 5, worksheet).Value2 != null)
{
map.Ric = ExcelUtil.GetRange(i, 5, worksheet).Value2.ToString().Trim();
}
if (ExcelUtil.GetRange(i, 6, worksheet).Value2 != null)
{
map.ShortName = ExcelUtil.GetRange(i, 6, worksheet).Value2.ToString().Trim();
}
nameDic.Add(key, map);
}
}
workbook.Close(false, workbook.FullName, Missing.Value);
}
}
catch (System.Exception)
{
throw new System.Exception("There's an error when getting name map from file baknote");
}
return(nameDic);
}
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 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);
}
}
public static void GenerateSecurityOptionTargetFile(string filePath, List <SecurityOptionSector> sectorList, string contentTitle, int rowNumEachSector, int startPageNum, int maxPageNum, string pageNamePre, DateTime lastBusinessDay)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = workbook.Worksheets[1] as Worksheet;
GenerateSecurityOptionTargetFile(worksheet, sectorList, contentTitle, rowNumEachSector, startPageNum, maxPageNum, pageNamePre, lastBusinessDay);
workbook.Save();
}
}
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 GenerateOneFile(string filename, string path, List <FID> list)
{
using (ExcelApp app = new ExcelApp(true, true))
{
string filepath = null;
if (path.EndsWith("\\"))
{
filepath = path + filename;
}
else
{
filepath = path + "\\" + filename;
}
if (app.ExcelAppInstance == null)
{
String msg = "Excel could not be started. Check that your office installation and project reference correct!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, filepath);
Worksheet worksheet = ExcelUtil.GetWorksheet("Sheet1", workbook);
if (worksheet == 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;
}
((Range)worksheet.Rows[1, Type.Missing]).Font.Bold = System.Drawing.FontStyle.Bold;
worksheet.Cells[1, 1] = "FileName";
worksheet.Cells[1, 2] = "LocalPath";
worksheet.Cells[1, 3] = "Weblink";
int startline = 2;
foreach (var item in list)
{
worksheet.Cells[startline, 1] = item.FileName;
worksheet.Cells[startline, 2] = item.LocalPath;
worksheet.Hyperlinks.Add(ExcelUtil.GetRange(startline, 3, worksheet), item.WebLink, Missing.Value, "Web Link for the file", Missing.Value);
startline++;
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbook.Save();
if (filename.Contains("CBBC"))
{
AddResult(filename, filepath, "Generate CBBC Reports File");
}
if (filename.Contains("Warrant"))
{
AddResult(filename, filepath, "Generate Warrant Reports File");
}
}
}
private void GenerateELWDropFmFile()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference correct!!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
if (string.IsNullOrEmpty(ELWDropELWFM1ELWFileBulkGenerate.filename))
{
ELWDropELWFM1ELWFileBulkGenerate.filename = "Korea FM for " + DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US")).Replace("-", " ") + " (Morning).xls";
}
string ipath = Path.Combine(configObj.FM, ELWDropELWFM1ELWFileBulkGenerate.filename); // "C:\\Korea_Auto\\ELW_FM\\ELW_Drop\\" + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = wSheet = (Worksheet)wBook.Worksheets[1];
if (wSheet == null)
{
string msg = "Error found in PrintFurtherIssueToExcel :(WorkSheet could not be created. Check that your office installation and project reference correct!!!)";
Logger.Log(msg, Logger.LogType.Error);
return;
}
int startLine = 5;
while (wSheet.get_Range("C" + startLine, Type.Missing).Value2 != null && wSheet.get_Range("C" + startLine, Type.Missing).Value2.ToString().Trim() != string.Empty)
{
startLine++;
}
GenerateExcelFileTitle(wSheet, startLine, "common");
startLine = startLine + 7;
AppendDataToFile(wSheet, startLine, "common");
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
//AddResult(Path.GetFileNameWithoutExtension(ipath),ipath,"");
}
catch (Exception ex)
{
string msg = "Error found in _print_ELWFMDroppTemplate : " + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
return;
}
finally
{
excelApp.Dispose();
}
}
protected override void Initialize()
{
base.Initialize();
configObj = Config as JapanNetChangeUpdateConfig;
app = new ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
Logger.Log("Excel cannot be started", Logger.LogType.Error);
}
template = ExcelUtil.CreateOrOpenExcelFile(app, configObj.TemplateFilePath);
}
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");
}
}
/// <summary>
/// Creating new CSV with requested companies (new IPOs)
/// </summary>
/// <param></param>
/// <returns></returns>
private void GenerateCSV()
{
var app = new ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !";
LogMessage(msg, Logger.LogType.Error);
return;
}
try
{
_resultFilename = _configObj.ResultsWorkbookPath.Replace(".csv", "_" + DateTime.Now.ToString("ddMMM_HH_mm_ss") + ".csv");
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, _resultFilename);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
for (int column = 0; column < _xlsTitle.Count; column++)
{
worksheet.Cells[1, column + 1] = _xlsTitle[column];
}
for (int line = 2; line <= ipoList.Count + 1; line++)
{
for (int column = 1; column <= _xlsTitle.Count; column++)
{
if (column == 5 || column == 6)
{
DateTime formatDate = DateTime.FromOADate(Convert.ToDouble(ipoList[line - 2][column - 1]));
worksheet.Cells[line, column] = formatDate.ToString("d");
}
else
{
worksheet.Cells[line, column] = ipoList[line - 2][column - 1];
}
}
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
AddResult("Result file", _resultFilename, "csv");
workbook.SaveAs(workbook.FullName, XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
LogMessage("Generated CSV file Successfully. Filepath is " + _resultFilename);
workbook.Close();
}
catch (Exception ex)
{
string msg = "Cannot generate CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
}
finally
{
app.Dispose();
File.Delete(_targetFilePath);
}
}
/// <summary>
/// Parsing CSV Finding new IPOs
/// </summary>
/// <param></param>
/// <returns></returns>
private void ReadCSV()
{
var app = new ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, _targetFilePath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
double yesterday = DateTime.Now.AddDays(-2).ToOADate();
double firstDate = DateTime.Parse("15-Apr-13").ToOADate();
string country = String.Empty;
for (int line = 2; worksheet.get_Range("C" + line, Type.Missing).Value2 != null; line++)
{
var newIpo = new List <string>();
double createStamp = Convert.ToDouble(worksheet.Range["E" + line, Type.Missing].Value2.ToString());
double updateStamp = Convert.ToDouble(worksheet.Range["F" + line, Type.Missing].Value2.ToString());
country = worksheet.Range["M" + line, Type.Missing].Value2.ToString();
if (((updateStamp >= yesterday && createStamp >= firstDate) || createStamp >= yesterday) &&
_countries.Contains(country))
{
if (!updatedCountries.Contains(country))
{
updatedCountries.Add(country);
}
for (int column = 0; column < _xlsTitle.Count; column++)
{
newIpo.Add(worksheet.Range[Alphabet.Substring(column, 1) + line, Type.Missing].Value2.ToString());
}
ipoList.Add(newIpo);
}
}
workbook.Close();
}
catch (Exception ex)
{
string msg = "Cannot read CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
}
finally
{
app.Dispose();
}
}
public void StartDelistingAndNameChangeJob()
{
cbbcList.Clear();
warrantList.Clear();
nameChangeList.Clear();
apartCbbcList.Clear();
apartWarrantList.Clear();
InitialHyphen();
int StartPosition = int.Parse(configObj.DELISTING_START_POSITION);
string sourceFolderPath = configObj.SOURCE_FILE_PATH; //Z:\Hong Kong\HK Circular
//Core coreObj = new Core();
//FindSourceFile(sourceFolderPath);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (File.Exists(configObj.SOURCE_FILE_PATH))
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.SOURCE_FILE_PATH);
var worksheet = ExcelUtil.GetWorksheet(configObj.SOURCE_FILE_WORKSHEET_NAME, workbook);
if (worksheet == null)
{
LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.SOURCE_FILE_WORKSHEET_NAME, configObj.SOURCE_FILE_PATH));
}
Range rangeCol6 = ExcelUtil.GetRange(StartPosition, 6, worksheet);
Range rangeCol3 = ExcelUtil.GetRange(StartPosition, 3, worksheet);
if (rangeCol6.get_Value(Missing.Value) != null && rangeCol3.get_Value(Missing.Value) != null)
{
int usedRange = worksheet.UsedRange.Count;
GetDelistingAndNameChangeData(usedRange, worksheet, StartPosition);
ApartList(apartCbbcList, cbbcList);
ApartList(apartWarrantList, warrantList);
}
else
{
LogMessage(" Check today's file in the folder Delisting!");
//coreObj.KillExcelProcess(xlApp);
}
workbook.Close(false, configObj.SOURCE_FILE_PATH, true);
WriteDataIntoFile(xlApp);
}
//coreObj.KillExcelProcess(xlApp);
}
else
{
LogMessage(string.Format(" Can't find today's delisting file {0}", configObj.SOURCE_FILE_PATH));
}
}
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);
}
}
}
}
/// <summary>
/// Filling the newly created Lot Size file with new entry
/// Create the file if doesn't exist yet
/// </summary>
/// <param name="content"></param>
/// <param name="index"></param>
private void FillLotSize(Dictionary <string, string> content, int index)
{
Workbook workbookNda;
Worksheet worksheetNda;
string titleWork = configObj.ResultsFolder + "LotSize_" + DateTime.Now.ToString("dMMMyyyy") + ".csv";
try
{
workbookNda = ExcelUtil.CreateOrOpenExcelFile(app, titleWork);
worksheetNda = workbookNda.Worksheets[1] as Worksheet;
worksheetNda.Name = "LotSize";
int firstLine = 2 + index * 3;
int ricNb = Int32.Parse(content["Official Code:"]);
// NDA Titles
if (index == 0)
{
worksheetNda.Cells[1, 1] = "RIC";
worksheetNda.Cells[1, 2] = "ROUND LOT SIZE";
}
// RIC
worksheetNda.Cells[firstLine, 1] = content["Official Code:"] + ".HK";
worksheetNda.Cells[firstLine + 1, 1] = content["Official Code:"] + "ta.HK";
worksheetNda.Cells[firstLine + 2, 1] = content["Official Code:"] + "stat.HK";
if (content.ContainsKey("Lot Size:"))
{
worksheetNda.Cells[firstLine, 2] = content["Lot Size:"];
}
else
{
worksheetNda.Cells[firstLine, 2] = content["Lot Size (NEW):"];
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbookNda.SaveAs(workbookNda.FullName, XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
AddResult(Path.GetFileNameWithoutExtension(workbookNda.FullName), workbookNda.FullName, "nda");
workbookNda.Close();
}
catch (Exception ex)
{
string msg = "Cannot generate CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
CleanExit();
}
}