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