private void startTradgTradhUpdating()
{
List <string> linkList = GetSourceFileLinks(tse_url);
nameDic = JapanShared.GetNameMap(baknoteFilePath, workSheetName);
// For tradgUpdate
try
{
dailySourceFilePath = DownloadFile(linkList[0]);
JpTRADGInfo tradgTradingInfo = printAndGetJpDailyTradingInfo(dailySourceFilePath);
GenerateTTradgTargetFile(targretFileDir, tradgTradingInfo);
}
catch (Exception ex)
{
Logger.Log("There's error during task: TRADG. Exception: " + ex.Message);
}
// For TRADH
try
{
if (linkList.Count == 2)
{
weeklySourceFilePath = DownloadFile(linkList[1]);
JpTRADHInfo tradhTradeInfo = PrintAndGetJpWeeklyTradingInfo(weeklySourceFilePath);
GenerateTradhTargetFile(targretFileDir, tradhTradeInfo);
}
}
catch (Exception ex)
{
Logger.Log("There's error during task: TRADH. Exception: " + ex.Message);
}
}
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);
}
//Generate TTRadgTarget File
private void GenerateTTradgTargetFile(string targetFileDir, JpTRADGInfo tradingInfo)
{
string fileName = "TTRADG";
fileName += MiscUtil.getFileExtension(targetFileVersion);
if (!Directory.Exists(targetFileDir))
{
Directory.CreateDirectory(targetFileDir);
}
fileName = Path.Combine(targetFileDir, fileName);
if (File.Exists(fileName))
{
File.Delete(fileName);
}
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, fileName);
var worksheet = workbook.Worksheets[1] as Worksheet;
((Range)worksheet.Columns["A", Type.Missing]).ColumnWidth = 8.43;
((Range)worksheet.Columns["B", Type.Missing]).ColumnWidth = 11.57;
((Range)worksheet.Columns["C", Type.Missing]).ColumnWidth = 14.57;
((Range)worksheet.Columns["D", Type.Missing]).ColumnWidth = 23.43;
((Range)worksheet.Columns["E", Type.Missing]).ColumnWidth = 16.57;
((Range)worksheet.Columns["F", Type.Missing]).ColumnWidth = 11.29;
((Range)worksheet.Columns["G", Type.Missing]).ColumnWidth = 15.29;
((Range)worksheet.Columns["A:G", Type.Missing]).Font.Name = "明朝";
worksheet.Cells[1, 1] = "ARBITRAGE TRANSACTION (T/TRADG)";
Range a1Range = ExcelUtil.GetRange(1, 1, worksheet);
a1Range.Font.Size = 16;
worksheet.Cells[1, 6] = tradingInfo.Date;
worksheet.Cells[1, 7] = "AS OF " + tradingInfo.Date.ToUpper();
Range f1Range = ExcelUtil.GetRange(1, 7, worksheet);
f1Range.Interior.Color = ColorTranslator.ToOle(Color.Violet);
worksheet.Cells[2, 7] = "↑飛ばす前に２営業日前になっているかどうか確認";
Range g2Range = ExcelUtil.GetRange(2, 7, worksheet);
g2Range.Font.Color = ColorTranslator.ToOle(Color.Red);
g2Range.Font.Size = 9;
int currentRow = 4;
for (int i = 0; i < 15; i++)
{
worksheet.Cells[currentRow + i, 1] = (i + 1).ToString();
}
worksheet.Cells[3, 2] = "SEUCURITIES";
worksheet.Cells[3, 3] = "ENGLISH";
worksheet.Cells[3, 4] = "JAPANESE";
worksheet.Cells[3, 5] = "SELL";
worksheet.Cells[3, 6] = "BUY";
worksheet.Cells[3, 7] = "TOTAL";
Range row3Range = ExcelUtil.GetRange(3, 2, 3, 7, worksheet);
row3Range.Font.Size = 10;
row3Range.Font.Bold = true;
row3Range.Borders.LineStyle = 1;
row3Range.Borders.Weight = XlBorderWeight.xlMedium;
currentRow = 4;
foreach (Transaction tran in tradingInfo.TransactionList)
{
worksheet.Cells[currentRow, 3] = tran.CompanyInfo.EnglishName;
worksheet.Cells[currentRow, 4] = tran.CompanyInfo.JapaneseName;
worksheet.Cells[currentRow, 5] = tran.SellSum;
worksheet.Cells[currentRow, 6] = tran.BuySum;
worksheet.Cells[currentRow, 7] = tran.TotalSum;
currentRow++;
}
Range F4g18Range = ExcelUtil.GetRange(4, 5, 18, 7, worksheet);
F4g18Range.Font.Bold = true;
F4g18Range.Font.Size = 12;
ExcelUtil.GetRange(4, 2, 18, 2, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
ExcelUtil.GetRange(4, 3, 18, 3, worksheet).Interior.Color = ColorTranslator.ToOle(Color.Yellow);
ExcelUtil.GetRange(4, 4, 18, 6, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
ExcelUtil.GetRange(19, 2, 20, 4, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
ExcelUtil.GetRange(23, 2, 24, 7, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
Range b4g18Range = ExcelUtil.GetRange(4, 2, 18, 7, worksheet);
b4g18Range.Borders.LineStyle = 1;
b4g18Range.Borders.Weight = XlBorderWeight.xlMedium;
worksheet.Cells[19, 2] = "TOP15";
worksheet.Cells[19, 5] = tradingInfo.Top15SellSum;
worksheet.Cells[19, 6] = tradingInfo.Top15BuySum;
worksheet.Cells[19, 7] = tradingInfo.Top15TotalSum;
worksheet.Cells[20, 2] = "G.TOTAL";
worksheet.Cells[20, 5] = tradingInfo.GSellSum;
worksheet.Cells[20, 6] = tradingInfo.GBuySum;
worksheet.Cells[20, 7] = tradingInfo.GTotalSum;
Range b19g20Range = ExcelUtil.GetRange(19, 2, 20, 7, worksheet);
b19g20Range.Font.Size = 12;
b19g20Range.Font.Bold = true;
b19g20Range.Borders.LineStyle = 1;
b19g20Range.Borders.Weight = XlBorderWeight.xlMedium;
worksheet.Cells[22, 2] = "SELL";
worksheet.Cells[22, 5] = "N.CHG";
worksheet.Cells[22, 6] = "BUY";
worksheet.Cells[22, 7] = "N.CHG";
Range row22Range = ExcelUtil.GetRange(22, 1, 22, 7, worksheet);
row22Range.Font.Bold = true;
row22Range.Font.Size = 11;
row22Range.Borders.LineStyle = 1;
row22Range.Borders.Weight = XlBorderWeight.xlMedium;
worksheet.Cells[21, 4] = "OK";
worksheet.Cells[23, 1] = "CURRENT MTH";
worksheet.Cells[23, 2] = tradingInfo.CurrentSellSum;
worksheet.Cells[23, 5] = tradingInfo.CurrentNChg1;
worksheet.Cells[23, 6] = tradingInfo.CurrentBuySum;
worksheet.Cells[23, 7] = tradingInfo.CurrentNChg2;
worksheet.Cells[24, 1] = "NEXT-M ONWRD";
worksheet.Cells[24, 2] = tradingInfo.NextSellSum;
worksheet.Cells[24, 5] = tradingInfo.NextNChg1;
worksheet.Cells[24, 6] = tradingInfo.NextBuySum;
worksheet.Cells[24, 7] = tradingInfo.NextNChg2;
worksheet.Cells[25, 1] = "TOTAL";
worksheet.Cells[25, 2] = tradingInfo.TotalSellSum;
worksheet.Cells[25, 5] = tradingInfo.TotalNchg1;
worksheet.Cells[25, 6] = tradingInfo.TotalBuySum;
worksheet.Cells[25, 7] = tradingInfo.TotalNchg2;
Range a23a25Range = ExcelUtil.GetRange(23, 1, 25, 1, worksheet);
a23a25Range.Borders.LineStyle = 1;
a23a25Range.Borders.Weight = XlBorderWeight.xlMedium;
Range b23g25Range = ExcelUtil.GetRange(23, 2, 25, 7, worksheet);
b23g25Range.Borders.LineStyle = 1;
b23g25Range.Borders.Weight = XlBorderWeight.xlMedium;
Range b22d25Range = ExcelUtil.GetRange(22, 2, 25, 4, worksheet);
b22d25Range.Merge(true);
workbook.Close(true, workbook.FullName, false);
AddResult("Target file", fileName, "file");
//TaskResultList.Add(new TaskResultEntry("Target File", "", fileName));
}
}
