locked
copying chart from excel RRS feed

  • Question

  • I have an excel file that contains a chart within it.  I would like to use this chart as a template for charts that I generate.  This means that charts based on the template chart will inheirate the template charts type, fonts, colors, etc...  I created a C# program that would read the excel file, extract the chart as an Excel.ChartObject, create a new spreadsheet, and copy the Excel.ChartObject into a Excel.ChartObjects.  Unfortunatly when I try to do this in C# I either end up with a runtime error or an empty chart.  The code for the C# program is below.  Do you know what is wrong with my code?

    Thanks

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Text;
    using System.Xml;
    using System.Collections;
    using Excel;
    using System.Reflection;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Ouput Excel and start a new workbook.
                Excel.Application objApp = new Excel.Application();
                Excel.Workbooks objBooks = objApp.Workbooks;
                Excel.Workbook objBook = objBooks.Add(Missing.Value);
                Excel.Sheets objSheets = objBook.Worksheets;
                Excel.Worksheet objSheet = (Excel.Worksheet)objSheets.get_Item(1);

                // Open Template Excel file
                Excel.Application iApp = new Excel.Application();
                Excel.Workbooks iWorkbooks = iApp.Workbooks;
                Excel.Workbook iWorkbook = iWorkbooks.Open("H:\\restool\\graph_templ.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Sheets iSheets = iWorkbook.Worksheets;
                Excel.Worksheet iSheet = (Excel.Worksheet)iSheets.get_Item(1);
                Excel.ChartObject iChartObj = (Excel.ChartObject)iSheet.ChartObjects(1);

                //add data
                objSheet.Cells[1, 1] = 10;
                objSheet.Cells[2, 1] = 20;
                objSheet.Cells[3, 1] = 30;
                objSheet.Cells[1, 2] = 10;
                objSheet.Cells[2, 2] = 20;
                objSheet.Cells[3, 2] = 30;

                //draw chart
                Excel.ChartObjects oChartObjs = (Excel.ChartObjects)objSheet.ChartObjects(Type.Missing);
                Excel.ChartObject oChartObj = oChartObjs.Add(0, 0, 400, 255);
                oChartObj = (Excel.ChartObject)iChartObj.Duplicate();
                Excel.Chart oChart = oChartObj.Chart;
                Excel.Chart iChart = iChartObj.Chart;
                oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");

                Excel.SeriesCollection xlSeriesCollection = (Excel.SeriesCollection)oChart.SeriesCollection(Type.Missing);
                Excel.Range rng;

                rng = objSheet.get_Range("A1", "A3");
                oChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
                Excel.Series xlSeries = xlSeriesCollection.Item(xlSeriesCollection.Count);
                xlSeries.Name = "Series 1";
                xlSeries.XValues = rng;
                rng = objSheet.get_Range("B1", "B3");
                xlSeries.Values = rng;
                oChart.ChartTitle.Text = "Chart Title";

    //            THIS WORKS if I comment out "oChartObj = (Excel.ChartObject)iChartObj.Duplicate();"
    //            oChart.ChartType = iChart.ChartType;
    //            oChart.HasTitle = iChart.HasTitle;
    //            oChart.HasLegend = iChart.HasLegend;

                Excel.Axis oAxisX = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
                Excel.Axis iAxisX = (Excel.Axis)iChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
                oAxisX.HasTitle = iAxisX.HasTitle;
                oAxisX.AxisTitle.Text = "x axis title";

                Excel.Axis oAxisY = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
                Excel.Axis iAxisY = (Excel.Axis)iChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
                oAxisY.HasTitle = iAxisY.HasTitle;
                oAxisY.AxisTitle.Text = "y axis title";

                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
                iApp.Workbooks.Close();
            }
        }
    }


    Friday, March 23, 2007 6:42 PM

Answers

  • Figured out a solution.  I rewrote the code to use the clipboard to transfer the chart to the new excel document.  See below for my solution.

    Thanks

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Text;
    using System.Xml;
    using System.Collections;
    using Excel;
    using System.Reflection;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Ouput Excel and start a new workbook.
                Excel.Application objApp = new Excel.Application();
                Excel.Workbooks objBooks = objApp.Workbooks;
                Excel.Workbook objBook = objBooks.Add(Missing.Value);
                Excel.Sheets objSheets = objBook.Worksheets;
                Excel.Worksheet objSheet = (Excel.Worksheet)objSheets.get_Item(1);

                // Open Template Excel file
                Excel.Application iApp = new Excel.Application();
                Excel.Workbooks iBooks = iApp.Workbooks;
                Excel.Workbook iBook = iBooks.Open("H:\\restool\\graph_templ.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Sheets iSheets = iBook.Worksheets;
                Excel.Worksheet iSheet = (Excel.Worksheet)iSheets.get_Item(1);

                //add data
                objSheet.Cells[1, 1] = 10;
                objSheet.Cells[2, 1] = 20;
                objSheet.Cells[3, 1] = 30;
                objSheet.Cells[1, 2] = 10;
                objSheet.Cells[2, 2] = 20;
                objSheet.Cells[3, 2] = 30;


                Excel.ChartObjects iChartObjs = (Excel.ChartObjects)iSheet.ChartObjects(Type.Missing);
                Excel.ChartObject iChartObj = (Excel.ChartObject)iChartObjs.Item(1);
                iChartObj.Copy();
                objSheet.Paste(objSheet.get_Range("D1", Missing.Value), false);
                iBook.Close(false, null, null);

                //draw chart
                Excel.ChartObjects oChartObjs = (Excel.ChartObjects)objSheet.ChartObjects(Type.Missing);
                Excel.ChartObject oChartObj = (Excel.ChartObject)oChartObjs.Item(1);
                Excel.Chart oChart = oChartObj.Chart;

                //oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");

                Excel.SeriesCollection xlSeriesCollection = (Excel.SeriesCollection)oChart.SeriesCollection(Type.Missing);
                Excel.Range rng;

                rng = objSheet.get_Range("A1", "A3");
                oChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
                Excel.Series xlSeries = xlSeriesCollection.Item(xlSeriesCollection.Count);
                xlSeries.Name = "Series 1";
                xlSeries.XValues = rng;
                rng = objSheet.get_Range("B1", "B3");
                xlSeries.Values = rng;
                oChart.ChartTitle.Text = "Chart Title";


                Excel.Axis oAxisX = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
                oAxisX.HasTitle = true;
                oAxisX.AxisTitle.Text = "x axis title";

                Excel.Axis oAxisY = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
                oAxisY.HasTitle = true;
                oAxisY.AxisTitle.Text = "y axis title";

                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
            }
        }
    }

    Friday, March 23, 2007 9:16 PM

All replies

  • What I would suggest is to copy the whole sheet (use the whole sheet as your template) instead of copying chart, then update the datasource and chart details as needed. As you are creating a new sheet everytime anyway, this may save you some trouble.
    Friday, March 23, 2007 6:51 PM
  • I tried making a copy of a worksheet and working with that but that does not seem to work either.  I am now left with an empty worksheet in excel.  I couldn't find a duplicate or clone function so I used the = operator.  Any more suggestions?  The code I used is as follows:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Text;
    using System.Xml;
    using System.Collections;
    using Excel;
    using System.Reflection;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Ouput Excel and start a new workbook.
                Excel.Application objApp = new Excel.Application();
                Excel.Workbooks objBooks = objApp.Workbooks;
                Excel.Workbook objBook = objBooks.Add(Missing.Value);
                Excel.Sheets objSheets = objBook.Worksheets;
                Excel.Worksheet objSheet = (Excel.Worksheet)objSheets.get_Item(1);

                // Open Template Excel file
                Excel.Application iApp = new Excel.Application();
                Excel.Workbooks iWorkbooks = iApp.Workbooks;
                Excel.Workbook iWorkbook = iWorkbooks.Open("H:\\restool\\graph_templ.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Sheets iSheets = iWorkbook.Worksheets;
                Excel.Worksheet iSheet = (Excel.Worksheet)iSheets.get_Item(1);          

                //copy worksheet
                objSheet = iSheet;  //is that right??

                //add data
                objSheet.Cells[1, 1] = 10;
                objSheet.Cells[2, 1] = 20;
                objSheet.Cells[3, 1] = 30;
                objSheet.Cells[1, 2] = 10;
                objSheet.Cells[2, 2] = 20;
                objSheet.Cells[3, 2] = 30;

                //draw chart
                Excel.ChartObjects oChartObjs = (Excel.ChartObjects)objSheet.ChartObjects(Type.Missing);
                Excel.ChartObject oChartObj = oChartObjs.Add(0, 0, 400, 255);
                Excel.Chart oChart = oChartObj.Chart;
                oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");

                Excel.SeriesCollection xlSeriesCollection = (Excel.SeriesCollection)oChart.SeriesCollection(Type.Missing);
                Excel.Range rng;

                rng = objSheet.get_Range("A1", "A3");
                oChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
                Excel.Series xlSeries = xlSeriesCollection.Item(xlSeriesCollection.Count);
                xlSeries.Name = "Series 1";
                xlSeries.XValues = rng;
                rng = objSheet.get_Range("B1", "B3");
                xlSeries.Values = rng;
                oChart.ChartTitle.Text = "Chart Title";


                Excel.Axis oAxisX = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
                oAxisX.HasTitle = true;
                oAxisX.AxisTitle.Text = "x axis title";

                Excel.Axis oAxisY = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
                oAxisY.HasTitle = true;
                oAxisY.AxisTitle.Text = "y axis title";

                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
                iApp.Workbooks.Close();
            }
        }
    }

    Friday, March 23, 2007 7:44 PM
  • Figured out a solution.  I rewrote the code to use the clipboard to transfer the chart to the new excel document.  See below for my solution.

    Thanks

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Text;
    using System.Xml;
    using System.Collections;
    using Excel;
    using System.Reflection;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Ouput Excel and start a new workbook.
                Excel.Application objApp = new Excel.Application();
                Excel.Workbooks objBooks = objApp.Workbooks;
                Excel.Workbook objBook = objBooks.Add(Missing.Value);
                Excel.Sheets objSheets = objBook.Worksheets;
                Excel.Worksheet objSheet = (Excel.Worksheet)objSheets.get_Item(1);

                // Open Template Excel file
                Excel.Application iApp = new Excel.Application();
                Excel.Workbooks iBooks = iApp.Workbooks;
                Excel.Workbook iBook = iBooks.Open("H:\\restool\\graph_templ.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Sheets iSheets = iBook.Worksheets;
                Excel.Worksheet iSheet = (Excel.Worksheet)iSheets.get_Item(1);

                //add data
                objSheet.Cells[1, 1] = 10;
                objSheet.Cells[2, 1] = 20;
                objSheet.Cells[3, 1] = 30;
                objSheet.Cells[1, 2] = 10;
                objSheet.Cells[2, 2] = 20;
                objSheet.Cells[3, 2] = 30;


                Excel.ChartObjects iChartObjs = (Excel.ChartObjects)iSheet.ChartObjects(Type.Missing);
                Excel.ChartObject iChartObj = (Excel.ChartObject)iChartObjs.Item(1);
                iChartObj.Copy();
                objSheet.Paste(objSheet.get_Range("D1", Missing.Value), false);
                iBook.Close(false, null, null);

                //draw chart
                Excel.ChartObjects oChartObjs = (Excel.ChartObjects)objSheet.ChartObjects(Type.Missing);
                Excel.ChartObject oChartObj = (Excel.ChartObject)oChartObjs.Item(1);
                Excel.Chart oChart = oChartObj.Chart;

                //oChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");

                Excel.SeriesCollection xlSeriesCollection = (Excel.SeriesCollection)oChart.SeriesCollection(Type.Missing);
                Excel.Range rng;

                rng = objSheet.get_Range("A1", "A3");
                oChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
                Excel.Series xlSeries = xlSeriesCollection.Item(xlSeriesCollection.Count);
                xlSeries.Name = "Series 1";
                xlSeries.XValues = rng;
                rng = objSheet.get_Range("B1", "B3");
                xlSeries.Values = rng;
                oChart.ChartTitle.Text = "Chart Title";


                Excel.Axis oAxisX = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
                oAxisX.HasTitle = true;
                oAxisX.AxisTitle.Text = "x axis title";

                Excel.Axis oAxisY = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
                oAxisY.HasTitle = true;
                oAxisY.AxisTitle.Text = "y axis title";

                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
            }
        }
    }

    Friday, March 23, 2007 9:16 PM