private DateTime[] ParseToGetTimeDuration(string sourceTimeDuration) { //平成24年3月5日~3月9日 string[] timeDuration = sourceTimeDuration.Split('~'); DateTime[] dateArr = { DateTime.Today, DateTime.Today }; dateArr[0] = JapanShared.TransferJpDate(timeDuration[0]); dateArr[1] = JapanShared.TransferJpDate(timeDuration[1]); return(dateArr); }
public static void GetOS225OPZData(Workbook book, ref OS225OPZOriginData data) { if (book == null || data == null) { return; } Worksheet sheet = book.Worksheets[1] as Worksheet; if (sheet == null) { return; } string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString(); data.updateDate = JapanShared.TransferJpDate(updateDate); string putTradingDate = ExcelUtil.GetRange("B4", sheet).Text; putTradingDate = putTradingDate.Replace("プット", ""); data.putTradingDate = JapanShared.TransferJpDate(putTradingDate); string callTradingDate = ExcelUtil.GetRange("I4", sheet).Text; callTradingDate = callTradingDate.Replace("コール", ""); data.callTradingDate = JapanShared.TransferJpDate(callTradingDate); int offset = 17; for (int i = 0; i < 5; i++) { string exchangeCount = ExcelUtil.GetRange(6 + i * offset, 1, sheet).Text.ToString(); exchangeCount = exchangeCount.Replace("(", "").Replace(")", "").Replace("円", "").Replace(",", "").Trim(); data.putExchangeCount.Add(exchangeCount); exchangeCount = ExcelUtil.GetRange(6 + i * offset, 8, sheet).Text.ToString(); exchangeCount = exchangeCount.Replace("(", "").Replace(")", "").Replace("円", "").Replace(",", "").Trim(); data.callExchangeCount.Add(exchangeCount); for (int j = 0; j < 15; j++) { OSETradeCompanyInfo Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 2); data.putInfo.Add(Info); Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 4); data.putInfo.Add(Info); Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 9); data.callInfo.Add(Info); Info = OS225FUU.GetTradingInfo(sheet, 8 + i * offset + j, 11); data.callInfo.Add(Info); } } }
public static void GetOSOPT35_46Data(Workbook book, ref OSOPT35_46OriginData data, Dictionary <string, NameMap> nameDic) { if (book == null || data == null) { return; } Worksheet sheet = book.Worksheets[1] as Worksheet; if (sheet == null) { return; } string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString(); data.updateDate = JapanShared.TransferJpDate(updateDate); int offset = 19; for (int i = 0; i < 10; i++) { string count = ExcelUtil.GetRange(6 + i * offset, 3, sheet).Text.ToString(); data.code.Add(count); count = ExcelUtil.GetRange(6 + i * offset, 12, sheet).Text.ToString(); data.code.Add(count); count = GetExchangeCount2(sheet, 7 + i * offset, 7); data.exchangeCount2.Add(count); count = GetExchangeCount2(sheet, 7 + i * offset, 16); data.exchangeCount2.Add(count); string tmp = GetPutOrCall(sheet, 6 + i * offset, 7); data.putOrCall.Add(tmp); tmp = GetPutOrCall(sheet, 6 + i * offset, 16); data.putOrCall.Add(tmp); OSETradeCompanyInfo exchangeName = GetExchangeName(sheet, 6 + i * offset, 3); updateCompanyInfo(exchangeName, nameDic); data.exchangeName.Add(exchangeName); exchangeName = GetExchangeName(sheet, 6 + i * offset, 12); updateCompanyInfo(exchangeName, nameDic); data.exchangeName.Add(exchangeName); string exchangDate = ExcelUtil.GetRange(6 + i * offset, 6, sheet).Text.ToString(); data.exchangeDate.Add(JapanShared.TransferJpDate(exchangDate)); exchangDate = ExcelUtil.GetRange(6 + i * offset, 15, sheet).Text.ToString(); data.exchangeDate.Add(JapanShared.TransferJpDate(exchangDate)); for (int j = 9; j <= 23; j++) { OSETradeCompanyInfo info = GetTradingInfo(sheet, j + i * offset, 3, 2); updateCompanyInfo(info, nameDic); data.info.Add(info); info = GetTradingInfo(sheet, j + i * offset, 6, 1); updateCompanyInfo(info, nameDic); data.info.Add(info); info = GetTradingInfo(sheet, j + i * offset, 12, 2); updateCompanyInfo(info, nameDic); data.info.Add(info); info = GetTradingInfo(sheet, j + i * offset, 15, 1); updateCompanyInfo(info, nameDic); data.info.Add(info); } } }
// 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 GetOS225FUUData(Workbook book, ref OS225FUUOriginData data) { if (book == null || data == null) { return; } try { Worksheet sheet = book.Worksheets[1] as Worksheet; if (sheet == null) { return; } string updateDate = ExcelUtil.GetRange("A2", sheet).Text.ToString(); data.updateDate = JapanShared.TransferJpDate(updateDate); int lastUsedRow = sheet.UsedRange.Row + sheet.UsedRange.Rows.Count - 1; int currentRow = 1; while (currentRow <= lastUsedRow) { if (ExcelUtil.GetRange(currentRow, 1, sheet).Value2 != null && ExcelUtil.GetRange(currentRow, 1, sheet).Value2.ToString() != string.Empty) { string text = ExcelUtil.GetRange(currentRow, 1, sheet).Value2.ToString(); if (text.Contains("日経225先物")) { string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString(); data.OS225FUU.tradingDate1 = JapanShared.TransferJpDate(date); date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString(); data.OS225FUU.tradingDate2 = JapanShared.TransferJpDate(date); for (int i = 0; i < 15; i++) { OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2); updateCompanyInfo(companyInfo, nameDic); data.OS225FUU.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4); updateCompanyInfo(companyInfo, nameDic); data.OS225FUU.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9); updateCompanyInfo(companyInfo, nameDic); data.OS225FUU.info2.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11); updateCompanyInfo(companyInfo, nameDic); data.OS225FUU.info2.Add(companyInfo); } currentRow += 20; } else if (text.Contains("日経225mini")) { string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString(); data.OS225FUX.tradingDate1 = JapanShared.TransferJpDate(date); date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString(); data.OS225FUX.tradingDate2 = JapanShared.TransferJpDate(date); for (int i = 0; i < 15; i++) { OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2); updateCompanyInfo(companyInfo, nameDic); data.OS225FUX.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4); updateCompanyInfo(companyInfo, nameDic); data.OS225FUX.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9); updateCompanyInfo(companyInfo, nameDic); data.OS225FUX.info2.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11); updateCompanyInfo(companyInfo, nameDic); data.OS225FUX.info2.Add(companyInfo); } currentRow += 20; } else if (text.Contains("TOPIX先物")) { string date = ExcelUtil.GetRange(currentRow + 1, 2, sheet).Text.ToString(); data.OSTRADF.tradingDate1 = JapanShared.TransferJpDate(date); date = ExcelUtil.GetRange(currentRow + 1, 9, sheet).Text.ToString(); data.OSTRADF.tradingDate2 = JapanShared.TransferJpDate(date); for (int i = 0; i < 15; i++) { OSETradeCompanyInfo companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 2); updateCompanyInfo(companyInfo, nameDic); data.OSTRADF.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 4); updateCompanyInfo(companyInfo, nameDic); data.OSTRADF.info1.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 9); updateCompanyInfo(companyInfo, nameDic); data.OSTRADF.info2.Add(companyInfo); companyInfo = OS225FUU.GetTradingInfo(sheet, currentRow + 4 + i, 11); updateCompanyInfo(companyInfo, nameDic); data.OSTRADF.info2.Add(companyInfo); } currentRow += 20; } else { currentRow++; } } else { currentRow++; } } } catch (Exception ex) { Logger.Log("GetOS225FUUData failed" + ex.Message); LogMessage("GetOS225FUUData failed"); } }