Excel slow/crashing when I generate a graph using C#

[kevin.cochrane]

Hi Guys

I am using C# to make an app that will read an excel file, edit the data and then generate a graph for me using said data.

The program works well, but when I open the graph in excel it is incredibly slow and sometimes crashes excel.

I think the problem is in the code I wrote to generate the graph but I have no idea where. Any help would be fantastic. Here is the code I use to make the graph:

private void DrawChart()
     {
         int i7 = i4 - 1;
         string minValue, testValue;

         minValue = "C" + i7.ToString();



         nInLastRow2 = oSheet.Cells.Find("*", System.Reflection.Missing.Value,                     //Finds which cell has the last value in it
                 System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                 Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false,
                 System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;


         Excel.ChartObjects charts = (Excel.ChartObjects)oSheet.ChartObjects(Missing.Value);
         Excel.ChartObject chartObj = charts.Add(500, 500, 200, 200);                                            //Setting Height, width, top, bottom of chart.    


         chartObj.Chart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;                               //Defines the chart type- Line chart with smoothed lines (No Markers)

         Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObj.Chart.SeriesCollection(Missing.Value);
         Excel.Series series1 = seriesCollection.NewSeries();                                                //Draws the graph in the current worksheet
         series1.Values = oSheet.get_Range("C2", "C" + nInLastRow2);
         series1.XValues = oSheet.get_Range("B2", "B" + nInLastRow2);                                         //Defines the ranges to be used in the graph 


         x = (Excel.Axis)chartObj.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);        //Defines X-Axis Properties

         x.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
         x.HasTitle = true;
         x.AxisTitle.Text = "Time";


         y = (Excel.Axis)chartObj.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);            //Defines Y-Axis Properties

         y.HasTitle = true;
         y.AxisTitle.Text = "Potential";
         y.ReversePlotOrder = true;
         y.MaximumScale = -0.3;

 chartObj.Chart.HasLegend = false;                                                                    //Defines whether there is a legend or not
         chartObj.Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

         chartObj.Chart.Location(Excel.XlChartLocation.xlLocationAsNewSheet, "Chart");                       //Moves the Chart to a new Worksheet

Thanks.
Kevin

[ddanbe]

Is the excel sheet also slow after you close and reopen it?

[kevin.cochrane]

Yes it is.

I have a feeling the problem is actually that I'm saving the files as a .csv (which is the original format of the data source for the graph) instead of an .xls. I didn't realise the program was doing this, I thought I had changed the extension. I'm busy working on moving the data out of the csv and into a new xls before I graph it. Any idea if that would solve the problem?

Thanks.

[kevin.cochrane]

Solved this problem. For some reason excel wasn't loving me changing the maximum value in the y axis. Comment that out and it works like a charm.