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