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