private void AddBugChart( ExcelInterop.Worksheet sheet, int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol, string bugDataSource, string chartTitle) { ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]]; ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects; Utility.AddNativieResource(charts); ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height); Utility.AddNativieResource(bugChartObject); ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。 Utility.AddNativieResource(bugChart); ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是:"B14:B25","F14:F25" Utility.AddNativieResource(datasource); bugChart.SetSourceData(datasource); bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered; //bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing); bugChart.ApplyDataLabels();//图形上面显示具体的值 //将图表移到数据区域之下。 bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20; bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20; bugChartObject.Locked = false; bugChartObject.Select(); bugChartObject.Activate(); }
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file. public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries) { // The top level log directory. string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\"; // The graph image directory. string imageDir = directory + @"graphs\"; // Check if the image directory exists. if (!File.Exists(imageDir)) { // If not, create it. Directory.CreateDirectory(imageDir); } // This is needed for a lot of the Excel initialization tasks. object misValue = System.Reflection.Missing.Value; // Create an Excel application object. Excel.Application xl = new Excel.Application(); // Create an Excel work sheet variable. Excel.Worksheet xlWorkSheet; // Create an Excel work books variable and point it at the application object's work books.. Excel.Workbooks xlWorkBooks = xl.Workbooks; // Make the Excel application visible? Not sure, it's magic. xl.Visible = true; // Open the summary CSV file in the Excel work book. xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); // Grab the work sheet that represents the CSV file. xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1); // Loop through every summary element, excluding the depth count which should be first. for (int summary = 1; summary < summaries[0].Count; summary++) { // Create a new Excel chart holder in the work sheet. Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); // Create a new Excel chart and position it below the table and other charts. Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210); // Select the chart object's chart. Don't ask me, this Excel interface is strange. Excel.Chart chartPage = myChart.Chart; // Make the chart object active. myChart.Select(); // Set the chart's style. 227 should be a white background with a blue line. chartPage.ChartStyle = 227; // Set the type of chart. We are using a line chart. chartPage.ChartType = Excel.XlChartType.xlLine; // Turn the legend off. chartPage.HasLegend = false; // Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb. Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(); // Create a new series. Excel.Series series1 = seriesCollection.NewSeries(); // Select the chart's X values. These should be the depth counts. series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth]; // Select the chart's Y values. These should be the current summary element data. series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth]; // Name the chart according to the current summary element. series1.Name = summaries[0][summary].First; // Export the current chart as a PNG image. chartPage.Export(imageDir + series1.Name + ".png", "PNG", false); } // Save the current work book as an XLS file. xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); // Close the current work book. xlWorkBooks[1].Close(true, misValue, misValue); // Quit the Excel application. xl.Quit(); // Do some garbage collection? Not sure, all examples I saw had this. releaseObject(xlWorkSheet); releaseObject(xlWorkBooks); releaseObject(xl); }