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)); } }