private void OS225FUUJob() { string sourceFile = configObj.DownloadFilePath + "\\" + configObj.Date + "_index_futures_OI_by_participant.xls"; string targetFile = configObj.OS225FUUTemplateFile; OS225FUUOriginData data = new OS225FUUOriginData(); Workbook source = InitialExcelFile(sourceFile); Workbook target = InitialExcelFile(targetFile); if (source == null || target == null) { return; } try { GetOS225FUUData(source, ref data); GenerateOS225FUU(target, data); target.Save(); } catch (Exception ex) { Logger.Log("OS225FUUJob failed" + ex.Message); LogMessage("OS225FUUJob failed"); } }
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"); } }
private void GenerateOS225FUU(Workbook book, OS225FUUOriginData data) { OS225FUU.WriteOS225FUU(book, data); OS225FUU.WriteOS225FUX(book, data); OS225FUU.WriteOSTRADF(book, data); }
public static void WriteOS225FUX(Workbook book, OS225FUUOriginData data) { Worksheet sheet = book.Worksheets[2] as Worksheet; sheet.Cells[2, 1] = data.updateDate.ToString("yyyy"); sheet.Cells[2, 2] = data.updateDate.ToString("MM"); sheet.Cells[2, 3] = data.updateDate.ToString("dd"); sheet.Cells[2, 8] = data.updateDate.ToString("dd"); sheet.Cells[2, 9] = data.updateDate.ToString("MMM").ToUpper(); sheet.Cells[2, 10] = data.updateDate.ToString("yy"); sheet.Cells[3, 2] = data.OS225FUX.tradingDate1.ToString("MMM").ToUpper() + data.OS225FUX.tradingDate1.ToString("yy"); sheet.Cells[3, 3] = data.OS225FUX.tradingDate1.ToString("yy"); sheet.Cells[3, 5] = data.OS225FUX.tradingDate1.ToString("MM"); int currentRow = 5; int currentInfo = 0; foreach (OSETradeCompanyInfo item in data.OS225FUX.info1) { currentInfo++; if (currentInfo % 2 == 1) { sheet.Cells[currentRow, 2] = item.OriginalName; sheet.Cells[currentRow, 3] = item.EnglishName; sheet.Cells[currentRow, 4] = item.JapaneseName; sheet.Cells[currentRow, 5] = item.OPINT; } else { sheet.Cells[currentRow, 6] = item.OriginalName; sheet.Cells[currentRow, 7] = item.EnglishName; sheet.Cells[currentRow, 8] = item.JapaneseName; sheet.Cells[currentRow, 9] = item.OPINT; currentRow++; } } sheet.Cells[21, 2] = data.OS225FUX.tradingDate2.ToString("MMM").ToUpper() + data.OS225FUX.tradingDate2.ToString("yy"); sheet.Cells[21, 3] = data.OS225FUX.tradingDate2.ToString("yy"); sheet.Cells[21, 5] = data.OS225FUX.tradingDate2.ToString("MM"); currentRow = 23; currentInfo = 0; foreach (OSETradeCompanyInfo item in data.OS225FUX.info2) { currentInfo++; if (currentInfo % 2 == 1) { sheet.Cells[currentRow, 2] = item.OriginalName; sheet.Cells[currentRow, 3] = item.EnglishName; sheet.Cells[currentRow, 4] = item.JapaneseName; sheet.Cells[currentRow, 5] = item.OPINT; } else { sheet.Cells[currentRow, 6] = item.OriginalName; sheet.Cells[currentRow, 7] = item.EnglishName; sheet.Cells[currentRow, 8] = item.JapaneseName; sheet.Cells[currentRow, 9] = item.OPINT; currentRow++; } } }