Template with data on Sheet1 and Chart on the Sheet1

Mar 18, 2013 at 11:08 AM
I have a template with two sheets,
Sheet1 has 2 columns with data.
Sheet2 has bar Chart corresponding to 2 columns on Sheet1.

Now I want to generate an excel in which when i update the data on Sheet1 manually the corresponding Chart on Sheet2 also gets updated.

I am able to do the same thing when the data and chart are on the same sheet. But i am not able to do it when the data and chart are on different sheets.

Here is my working code when data and chart are on same sheet :-

string connectionString = "mydbconnstring";
        FileInfo template = new FileInfo(Server.MapPath("GraphTemplate.xlsx"));
        using (ExcelPackage p = new ExcelPackage(template, true))
        {
            //Select worksheet and set the start rows for data
            ExcelWorksheet ws = p.Workbook.Worksheets[1];

            int startRow = 1;
            int row = startRow;

            //Connect to the database and fill the data
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY] FROM [Sales].[CurrencyRate]  where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate  ORDER BY CurrencyRateDate", sqlConn))
                {
                    using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                    {
                        // get the data and fill rows
                        int i = 1;
                        while (sqlReader.Read())
                        {
                            ws.Cells[row, 1].Value = "Flight " + i;
                            ws.Cells[row, 2].Value = sqlReader[1];
                            row++;
                            i++;
                            if (row > 40)
                                break;
                        }
                    }                        
                }
            }

            //Set the series for the chart. The series must exist in the template or the program will crash.
            ExcelBarChart chart = ((ExcelBarChart)wsGraph.Drawings["BarChart"]);

            chart.Series.Add(ExcelRange.GetAddress(1, 2, row - 1, 2), ExcelRange.GetAddress(1, 1, row - 1, 1));

The above code is working if i have the chart and data on sheet[1]. __I am not able to decipher how to do the same when the chart is on other sheet. __

Although i can get the reference of second sheet with graph in the following way:-
ExcelWorksheet wsGraph = p.Workbook.Worksheets[2];
But, how to add series from sheet1 to chart on sheet2?

Any suggestions will be highly appreciated.
Mar 18, 2013 at 5:23 PM
Edited Mar 18, 2013 at 5:23 PM
Here is an example of adding a series of data from a given worksheet to a chart.
scatter_chart.Series.Add(excel_worksheet_2.Cells["B2:B10"], excel_worksheet_2.Cells["A2:A10"]);
Mar 19, 2013 at 4:36 AM
Thanks a lot xav2075, it worked. Great.