Using C# to find drawn arrows in Excel

Question

I was wondering if there was a way to find a drawn arrow in an excel spreadsheet using C#. For example, if there was an arrow drawn from A2 to A6, can a C# program open that excel file, and search for arrows, and return the cells A2 and A6, or something of that nature. Apologies if this sounds like a stupid question, but it's a "management question", and I can't seem to find the answer. Thanks all.

Answer 1

When you are talking about Arrow Shapes in Excel, then there are 28 types of Arrow Shapes. See image below

The entire list can be retrieved from HERE

So when you are looping through shapes, you will have to account for all of these. Also what Richard Morgan mentioned in the comment is correct. You will have to use the TopLeftCell.Address and BottomRightCell.Address to find the range.

Your code would look like this. (BTW I am using Interop to automate Excel)

Let's say our worksheet looks like this. Now we don't want the Red Shapes as they are not Arrow Shapes.

My Assumptions

There is a workbook called Shapes.xlsx in C:\ from which we have to retrieve the info

Code (Tried And Tested in VS 2010 and Excel 2010)

using System;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;
            xlexcel = new Excel.Application();
            xlexcel.Visible = true;

            // Open a File
            xlWorkBook = xlexcel.Workbooks.Open("C:\\Shapes.xlsx", 0, true, 5, "", "", true,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            // Set Sheet 1 as the sheet you want to work with
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            foreach (Microsoft.Office.Interop.Excel.Shape Shp in xlWorkSheet.Shapes)
            {

                // Disclaimer: I am a vb.net guy so if you feel the syntax of switch should be written 
                // in some other way then feel free to edit it :) In VB.Net, I could write 
                // Case 33 To 60 instead of writing so many cases.
                switch ((int)Shp.AutoShapeType)
                {
                    case 33: case 34: case 35: case 36: case 37: case 38: case 39: case 40:
                    case 41: case 42: case 43: case 44: case 45: case 46: case 47: case 48:
                    case 49: case 50: case 51: case 52: case 53: case 54: case 55: case 56:
                    case 57: case 58: case 59: case 60:

                            MessageBox.Show("Shape Found in Cell Range from " + 
                                             Shp.TopLeftCell.Address + 
                                             " to " + 
                                             Shp.BottomRightCell.Address);

                    break;
                }
            }

            //Once done close and quit Excel
            xlWorkBook.Close(false, misValue, misValue);
            xlexcel.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}

These are the 3 messages boxes that I get