public void exportVariety(string varietyName, int agreement, DateTime startingDate, DateTime endingDate)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1");
setColumnWidth(sheet);
initStyle(workbook);
//第一行，品种、起止时间
XSSFRow row0 = (XSSFRow)sheet.CreateRow(0);
setRowHeight(row0);
XSSFCell cell = (XSSFCell)row0.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(varietyName + Variety.getAgreementName(agreement));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
cell = (XSSFCell)row0.CreateCell(2);
cell.CellStyle = headerStyle;
cell = (XSSFCell)row0.CreateCell(1);
cell.CellStyle = headerStyle;
cell.SetCellValue(Utils.formatDate(startingDate) + "-" + Utils.formatDate(endingDate));
excelFilePath = Utils.getExportDir() + varietyName + "_" + Variety.getAgreementName(agreement) + "_" + Utils.getDate() + "_回测" + "_" + Utils.getTimeMillisecond() + ".xlsx";
FileStream file = new FileStream(excelFilePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
//TODO 边框、单元格宽度、高度
public void exportExcel(VarietyReport varietyReport)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1");
setColumnWidth(sheet);
initStyle(workbook);
//第一行，品种、起止时间
XSSFRow row0 = (XSSFRow)sheet.CreateRow(0);
setRowHeight(row0);
XSSFCell cell = (XSSFCell)row0.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(varietyReport.varietyName + Variety.getAgreementName(varietyReport.agreement));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
cell = (XSSFCell)row0.CreateCell(1);
cell.CellStyle = headerStyle;
cell.SetCellValue(Utils.formatDate(varietyReport.startingDate) + "-" + Utils.formatDate(varietyReport.endingDate));
List <CycleReport> cycleReports = varietyReport.cycleReports;
if (cycleReports != null)
{
for (int i = 0; i < cycleReports.Count; i++)
{
if (i % 2 == 0)
{
int colStartIndex = 0;
CycleReport cycleReport = cycleReports[i];
if (cycleReport != null)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(leftRowIndex);
setRowHeight(row);
leftRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
cell.CellStyle = headerStyle;
cell.SetCellValue(cycleReport.cycleName);
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算开始时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算结束时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号个数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = titleStyle;
cell.SetCellValue("最终权益");
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = titleStyle;
cell.SetCellValue("夏普比率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤");
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤比");
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = titleStyle;
cell.SetCellValue("风险率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手平均盈亏");
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = titleStyle;
cell.SetCellValue("胜率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = titleStyle;
cell.SetCellValue("模型得分");
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大盈利");
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续盈利次数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续亏损次数");
List <ModelReport> modelReports = cycleReport.modelReports;
if (modelReports != null)
{
foreach (ModelReport modelReport in modelReports)
{
row = (XSSFRow)sheet.CreateRow(leftRowIndex);
setRowHeight(row);
leftRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
if (modelReport.warning)
{
cell.CellStyle = warningStyle;
}
else
{
cell.CellStyle = commonStyle;
}
cell.SetCellValue(modelReport.modelName);
//期望模型名称和实际模型名称不一致，则不导出数据
bool isErrorData = false;
if (!modelReport.modelName.Equals(modelReport.realisticModelName))
{
isErrorData = true;
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.startingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.endingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.signalNumber);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.lastInterest);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.sharpeRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracement);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracementRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.hazardRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.avgProfitLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.winRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.score);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxProfit);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLoss);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousProfitabilityTimes);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousLossesTimes);
}
}
}
}
}
else
{
int colStartIndex = 18;
CycleReport cycleReport = cycleReports[i];
if (cycleReport != null)
{
XSSFRow row = (XSSFRow)sheet.GetRow(rightRowIndex);
rightRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
cell.CellStyle = headerStyle;
cell.SetCellValue(cycleReport.cycleName);
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算开始时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算结束时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号个数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = titleStyle;
cell.SetCellValue("最终权益");
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = titleStyle;
cell.SetCellValue("夏普比率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤");
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤比");
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = titleStyle;
cell.SetCellValue("风险率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手平均盈亏");
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = titleStyle;
cell.SetCellValue("胜率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = titleStyle;
cell.SetCellValue("模型得分");
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大盈利");
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续盈利次数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续亏损次数");
List <ModelReport> modelReports = cycleReport.modelReports;
if (modelReports != null)
{
foreach (ModelReport modelReport in modelReports)
{
row = (XSSFRow)sheet.GetRow(rightRowIndex);
rightRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
if (modelReport.warning)
{
cell.CellStyle = warningStyle;
}
else
{
cell.CellStyle = commonStyle;
}
cell.SetCellValue(modelReport.modelName);
//期望模型名称和实际模型名称不一致，则不导出数据
bool isErrorData = false;
if (!modelReport.modelName.Equals(modelReport.realisticModelName))
{
isErrorData = true;
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.startingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.endingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.signalNumber);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.lastInterest);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.sharpeRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracement);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracementRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.hazardRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.avgProfitLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.winRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.score);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxProfit);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLoss);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousProfitabilityTimes);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousLossesTimes);
}
}
}
}
}
}
}
String filePath = Utils.getExportDir() + varietyReport.varietyName + "_" + Variety.getAgreementName(varietyReport.agreement) + "_" + Utils.getDate() + "_回测" + "_" + Utils.getTimeMillisecond() + ".xlsx";
FileStream file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
