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);
}
}
// Print Source File and Get Weekly TradingInfo
private JpTRADHInfo PrintAndGetJpWeeklyTradingInfo(string filePath)
{
JpTRADHInfo tradhInfo = new JpTRADHInfo();
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
JapanShared.PrintWorksheet(worksheet, printerName, XlPageOrientation.xlPortrait);
worksheet.UsedRange.UnMerge();
JpTRADHInfoPart1 part1 = new JpTRADHInfoPart1();
DateTime[] part1DateArr = ParseToGetTimeDuration(ExcelUtil.GetRange(33, 3, worksheet).Value2.ToString());
part1.StartDate = part1DateArr[0];
part1.EndDate = part1DateArr[1];
part1.TransactionList = GetTransactionList(37, 3, worksheet);
part1.Top15SellSum = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString();
part1.Top15BuySum = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString();
part1.Top15TotalSum = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString();
part1.GTotalSellSum = ExcelUtil.GetRange(52, 7, worksheet).Value2.ToString();
part1.GTotalBuySum = ExcelUtil.GetRange(52, 9, worksheet).Value2.ToString();
part1.GTotalTotalSum = ExcelUtil.GetRange(52, 13, worksheet).Value2.ToString();
JpTRADHInfoPart2 part2 = new JpTRADHInfoPart2();
string part2SourceTitle = ExcelUtil.GetRange(5, 3, worksheet).Value2.ToString().Trim();
part2.Title = part2SourceTitle.Split('（')[0].Trim();
DateTime[] part2DateArr = ParseToGetTimeDuration(part2SourceTitle.Split('（')[1].Trim());
part2.StartDate = part2DateArr[0];
part2.EndDate = part2DateArr[1];
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(9, i, worksheet).Value2 != null && ExcelUtil.GetRange(9, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part2.ShareSumList.Add(ExcelUtil.GetRange(9, i, worksheet).Value2.ToString().Trim());
}
}
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(10, i, worksheet).Value2 != null && ExcelUtil.GetRange(10, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part2.TradeVolList.Add(ExcelUtil.GetRange(10, i, worksheet).Value2.ToString().Trim());
}
}
JpTRADHInfoPart3 part3 = new JpTRADHInfoPart3();
string part3SourceTitle = ExcelUtil.GetRange(12, 3, worksheet).Value2.ToString();
part3.Title = part3SourceTitle.Split('（')[0].Trim();
part3.Date = JapanShared.TransferJpDate(part3SourceTitle.Split('（')[1].Trim());
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(15, i, worksheet).Value2 != null && ExcelUtil.GetRange(15, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part3.ShareSumList.Add(ExcelUtil.GetRange(15, i, worksheet).Value2.ToString().Trim());
}
}
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(16, i, worksheet).Value2 != null && ExcelUtil.GetRange(16, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part3.ShareIncrementList.Add(ExcelUtil.GetRange(16, i, worksheet).Value2.ToString().Trim());
}
}
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(17, i, worksheet).Value2 != null && ExcelUtil.GetRange(17, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part3.TradeVolSumList.Add(ExcelUtil.GetRange(17, i, worksheet).Value2.ToString().Trim());
}
}
for (int i = 5; i < 16; i++)
{
if (ExcelUtil.GetRange(18, i, worksheet).Value2 != null && ExcelUtil.GetRange(18, i, worksheet).Value2.ToString().Trim() != string.Empty)
{
part3.TradeVolIncrementList.Add(ExcelUtil.GetRange(18, i, worksheet).Value2.ToString().Trim());
}
}
tradhInfo.Part1 = part1;
tradhInfo.Part2 = part2;
tradhInfo.Part3 = part3;
}
return(tradhInfo);
}
private void GenerateTradhTargetFile(string targetFileDir, JpTRADHInfo tradeInfo)
{
string fileName = "Weekly_TTRADH";
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:I", Missing.Value]).ColumnWidth = 15;
((Range)worksheet.Columns["A:I", Type.Missing]).Font.Name = "明朝";
worksheet.Cells[1, 1] = "ARBITRAGE TRANSACTION (T/TRADH)";
//Part 1:
worksheet.Cells[2, 6] = string.Format("{0}-{1}", tradeInfo.Part1.StartDate.ToString("MMMdd", new CultureInfo("en-US")), tradeInfo.Part1.EndDate.ToString("MMMdd", new CultureInfo("en-US"))).ToUpper();
ExcelUtil.GetRange(2, 6, worksheet).Interior.Color = ColorTranslator.ToOle(Color.Violet);
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";
ExcelUtil.GetRange(3, 2, 3, 7, worksheet).Font.Bold = true;
WriteTransactionInfo(tradeInfo.Part1.TransactionList, worksheet, 4, 2);
worksheet.Cells[19, 2] = "TOP15";
worksheet.Cells[20, 2] = "G.TOTAL";
ExcelUtil.GetRange(19, 2, 20, 2, worksheet).Font.Bold = true;
worksheet.Cells[19, 5] = tradeInfo.Part1.Top15SellSum;
worksheet.Cells[19, 6] = tradeInfo.Part1.Top15BuySum;
worksheet.Cells[19, 7] = tradeInfo.Part1.Top15TotalSum;
worksheet.Cells[20, 5] = tradeInfo.Part1.GTotalSellSum;
worksheet.Cells[20, 6] = tradeInfo.Part1.GTotalBuySum;
worksheet.Cells[20, 7] = tradeInfo.Part1.GTotalTotalSum;
ExcelUtil.GetRange(4, 5, 20, 7, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
ExcelUtil.GetRange(3, 1, 20, 7, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightGray);
//Part 2:
worksheet.Cells[22, 1] = tradeInfo.Part2.Title;
worksheet.Cells[22, 6] = string.Format("{0}-{1}", tradeInfo.Part2.StartDate.ToString("MMMdd", new CultureInfo("en-US")), tradeInfo.Part2.EndDate.ToString("MMMdd", new CultureInfo("en-US"))).ToUpper();
ExcelUtil.GetRange(22, 6, worksheet).Interior.Color = ColorTranslator.ToOle(Color.Violet);
worksheet.Cells[23, 2] = "ﾌﾟﾛｸﾞﾗﾑ売買";
worksheet.Cells[24, 6] = "裁定取引に係る売買";
worksheet.Cells[24, 8] = "裁定取引以外の売買";
worksheet.Cells[25, 2] = "売り付け";
worksheet.Cells[25, 5] = "買い付け";
worksheet.Cells[25, 6] = "売り付け";
worksheet.Cells[25, 7] = "買い付け";
worksheet.Cells[25, 8] = "売り付け";
worksheet.Cells[25, 9] = "買い付け";
worksheet.Cells[26, 1] = "株数";
worksheet.Cells[27, 1] = "金額";
WriteShareOrIncreaseSumList(tradeInfo.Part2.ShareSumList, worksheet, 26, 2);
WriteShareOrIncreaseSumList(tradeInfo.Part2.TradeVolList, worksheet, 27, 2);
ExcelUtil.GetRange(26, 2, 27, 9, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
ExcelUtil.GetRange(23, 1, 27, 9, worksheet).Interior.Color = ColorTranslator.ToOle(Color.LightSlateGray);
//Part 3:
worksheet.Cells[29, 1] = tradeInfo.Part3.Title;
ExcelUtil.GetRange(29, 6, worksheet).NumberFormat = "@";
worksheet.Cells[29, 6] = tradeInfo.Part3.Date.ToString("MMMdd", new CultureInfo("en-US")).ToUpper();
ExcelUtil.GetRange(29, 6, worksheet).Interior.Color = ColorTranslator.ToOle(Color.Violet);
worksheet.Cells[30, 2] = "' 売りﾎﾟｼｼｮﾝ";
worksheet.Cells[30, 7] = "' 買ﾎﾟｼﾞｼｮﾝ";
worksheet.Cells[31, 2] = " 当限";
worksheet.Cells[31, 5] = "翌限以降";
worksheet.Cells[31, 6] = " 合計 ";
worksheet.Cells[31, 7] = " 当限";
worksheet.Cells[31, 8] = "翌限以降";
worksheet.Cells[31, 9] = " 合計 ";
worksheet.Cells[32, 1] = "株数";
worksheet.Cells[33, 1] = "前週末比";
worksheet.Cells[34, 1] = "金額";
worksheet.Cells[35, 1] = "前週末比";
WriteShareOrIncreaseSumList(tradeInfo.Part3.ShareSumList, worksheet, 32, 2);
WriteShareOrIncreaseSumList(tradeInfo.Part3.ShareIncrementList, worksheet, 33, 2);
WriteShareOrIncreaseSumList(tradeInfo.Part3.TradeVolSumList, worksheet, 34, 2);
WriteShareOrIncreaseSumList(tradeInfo.Part3.TradeVolIncrementList, worksheet, 35, 2);
ExcelUtil.GetRange(32, 2, 35, 9, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight;
ExcelUtil.GetRange(30, 1, 35, 9, worksheet).Interior.Color = ColorTranslator.ToOle(Color.Olive);
worksheet.UsedRange.NumberFormat = "@";
workbook.Save();
workbook.Close(false, workbook.FullName, false);
AddResult("Weekly_TRADH", fileName, "file");
//TaskResultList.Add(new TaskResultEntry("Weekly_TRADH", "Weekly Task Result File", fileName));
}
}