How to set Excel chart area range based on GridView using C#?

Question

I have an Excel Sheet divided into two sides. On the left side I export data from ASP.NET application. On the right side is a chart which is configured to populate based on the data exported.

The problem is that I have reserved certain rows and columns for the left side data in the sheet. For example, I am assuming 10 columns and 31 rows for all reports. Reports can be of type Daily/Weekly/Monthly. For daily report, the first column is dates from 1 to 31. I therefore reserved left side of the sheet as max 31 rows and 10 columns.

The data is correctly exported from my application to this sheet area and chart is also populated. But the problem is that exported data may be of 5 rows and 3 columns only, but because the chart area is configured for 31 rows and 10 columns, the chart bars visible are very thin in width.

Is there a way to programatically control the chart area based on the GridView rows and columns?

In my application, I am first displaying results on a GridView. When user clicks Export button, this GridView data is exported to the left-side reserved area of the Excel sheet.

How to programatically set the range of the chart area from C# code of the current excel sheet?

Answer 1

I believe there are two ways to solve your problem

1.) The first would be to plot the chart dynamically.That is When you get the Dataset you can create the chart at runtime and allocate your Datarange that is your Chart Datasource

A Simple example is as Follows

Excel.Application XlApp = null;
Excel.Workbook workbook = null;
Excel.Worksheet Ws = null;
Excel.Range Range1 = null;
Excel.RangeDataRange = null;

XlApp = new Excel.Application();
XlApp.Visible = true;
workbook = XlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Ws = (Excel.Worksheet)workbook.Worksheets[1];//Lets say you have your graph at 1 sheet
XlApp.WindowState = XlWindowState.xlMaximized;

Ws.Cells.Clear();//Clear your sheet of any existing data

ChartObjs = null;
ChartObj = null;
xlChart = null;
Range1 = Ws.get_Range(Ws.Cells[39, 6], Ws.Cells[48, 10]);
ChartObjects WsChartObjs = (ChartObjects)Ws.ChartObjects(Type.Missing);
ChartObj = WsChartObjs.Add((double)Range1.Left + 115, (double)Range1.Top - 1,  (double)Range1.Width - 25, (double)Range1.Height + 5);
xlChart = ChartObj.Chart;

DataRange = null;

 if (MyDataset.Tables[0].Rows.Count > 10)
 {
   DataRange = Ws.get_Range(Ws.Cells[12, 14], Ws.Cells[12 + 

   MyDataset.Tables[0].Rows.Count, 15]);//or whatever your range may be
 }


  xlChart.SetSourceData(DataRange, System.Type.Missing);
  xlChart.ChartType = XlChartType.xlBarClustered;
  xlChart.ChartArea.Fill.Visible =            
  Microsoft.Office.Core.MsoTriState.msoFalse;
  xlChart.ChartArea.Border.Color = 
  System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 255));
  xlChart.PlotArea.Interior.Color =  
  System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 255));
  xlChart.PlotArea.Border.Color = System.Drawing.ColorTranslator.ToOle(255, 255, 255));

This is the first apporach

2.) Second approach is that in which you select the existing chart in excel and change its datasource

Simple example would be

Excel.ChartObject MyChartObject = (Excel.ChartObject)Ws.ChartObjects(1);//You can run 
//macro and get your chart number and pass that instead of "1"
MyChartObject .Activate();

xlChart = MyChartObject .Chart;
xlChart.SetSourceData(DataRange, System.Type.Missing);

I guess thats all

Hope this helps