VSTA and LINQ to XML with Excel

VSTA 2 can be used to extend a host application to use LINQ in a variety of ways.  One example of this is to use LINQ to XML to use an XML file as input.  LINQ to XML is doubly convenient because Office products, such as Excel 2007, work well with XML files.

Using LINQ to XML to input data that was created or manipulated in an Excel spreadsheet is much easier than the traditional “VBA” like methods previously used.  Our previous ExcelDataEntry sample (code included below) used approximately 90 lines of code to accomplish the same data input as this LINQ to XML sample does in approximately 30 lines.  Also, this LINQ to XML sample does not require the Excel program or Excel references while the ExcelDataEntry sample required a specific version of Excel be installed on the end user’s machine. 

By extending a host application with LINQ to XML in VSTA, you can offer the end user the ability to use Office to create, modify, and view data without tying the end user to Office.  With LINQ to XML any XML editor, including the VSTA design time, can be used.   

Below is a simple example of how to use LINQ in VSTA v 2 to input data from an XML file (with the ShapeAppCSharp sample).  Instructions on how to use Excel to modify the XML file used for input are also included.

Link to attachments

Attached is an add-in for ShapeAppCSharp and a simple xml file.  To use this,

Here is the xml file and code from the add-in:
XML File:

<?xml version="1.0" encoding="utf-8"?>

<shapes>

      <shape>

            <Name>Circle</Name>

            <SizeX>50</SizeX>

            <SizeY>50</SizeY>

            <LocationX>50</LocationX>

            <LocationY>50</LocationY>          

      </shape>

      <shape>

            <Name>Star</Name>

            <SizeX>60</SizeX>

            <SizeY>60</SizeY>

            <LocationX>100</LocationX>

            <LocationY>100</LocationY>

      </shape>

      <shape>

            <Name>Square</Name>

            <SizeX>75</SizeX>

            <SizeY>75</SizeY>

            <LocationX>200</LocationX>

            <LocationY>200</LocationY>

      </shape>

</shapes

AddIn:

private void AppAddIn_Startup(object sender, EventArgs e)

{

   string shapeNamePrefix = "Microsoft.VisualStudio.Tools.Applications.Samples.ShapeApp.";

   //load the simple xml file

   string file = System.IO.Path.Combine(System.Environment.GetFolderPath(

      System.Environment.SpecialFolder.MyDocuments),

      @"ShapeAppCSharp\\ShapeApp_DataInput XML.xml");

   System.Xml.Linq.XElement XmlFile = System.Xml.Linq.XElement.Load(file);

   //Query the data in the xml file for shapes

   var shapes = from shape in XmlFile.Descendants("shape")

      select new

      {

         Name = (string)shape.Element("Name"),

         SizeX = (int) shape.Element("SizeX"),

         SizeY = (int) shape.Element("SizeY"),

         LocX = (int) shape.Element("LocationX"),

         LocY = (int) shape.Element("LocationY")

      };

   //add the shapes from the xml file to active drawing

   IShape shapeToAdd;

   foreach (var shape in shapes)

   {

      shapeToAdd = this.AvailableShapes[shapeNamePrefix + shape.Name].Clone();

      shapeToAdd.Location = Application.CreatePoint(shape.LocX, shape.LocY);

      shapeToAdd.Size = Application.CreateSize(shape.SizeX, shape.SizeY);

      this.ActiveDrawing.Shapes.Add(shapeToAdd);

   }

}

To use Excel to update the attached XML file used to import data into ShapeAppCSharp,

1)    Ensure that the Developer tab is visible (there are other ways to work with XML in Excel that do not involve the Developer tab). 

2)    Select import from the Developer tab.

3)    Navigate to and select the file in the Import XML dialog.

4)    Select the “Xml table in existing worksheet” option from the Import Data dialog.

5)    The XML data will now appear in the spreadsheet.

6)    Enter data for a new shape.

7)    To show the XML Source pane, select Source from the Developer tab.

8)    In the XML Source pane select Verify Map for Export to ensure that the data can be exported into XML.

9)    Select Export from the Developer tab.

10)  Overwrite the original XML file or save the updated data to a new one.

11)  The data added through Excel can now be seen in the XML file.

Code from the ExcelDataEntry sample:

#region local variables

private Microsoft.Office.Interop.Excel.ApplicationClass myExcel;

private Microsoft.Office.Interop.Excel.Workbooks myBooks;

private string shapeNamePrefix = "Microsoft.VisualStudio.Tools.Applications.Samples.ShapeApp.";

#endregion

public void AppAddIn_Startup(object sender, EventArgs e)

{

   //open excel file

   string file = System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments), @"ShapeAppCSharp\\ShapeApp_DataInput.xls");

   OpenExcelFile(file);

   //open the sheet

   Microsoft.Office.Interop.Excel.Worksheet mySheet = new Microsoft.Office.Interop.Excel.Worksheet();

   mySheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[1];

   //get the number of shapes to add

   Microsoft.Office.Interop.Excel.Range myRange;

   myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[1, 8];

   double inputValue = (double)myRange.Value2;

   int numShapes = Convert.ToInt16(inputValue);

   //FOR each entry

   for (int i = 1; i <= numShapes; i++)

   {

      //   get the shape name

      myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i + 1, 1];

      string shapeName = shapeNamePrefix + (string)myRange.Value2;

      //   get the shape size

      myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i + 1, 2];

      inputValue = (double)myRange.Value2;

      int shapeSizeX = Convert.ToInt16(inputValue);

      myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i + 1, 3];

      inputValue = (double)myRange.Value2;

      int shapeSizeY = Convert.ToInt16(inputValue);

      //   get the shape location

      myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i + 1, 4];

      inputValue = (double)myRange.Value2;

      int shapeLocX = Convert.ToInt16(inputValue);

      myRange = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i + 1, 5];

      inputValue = (double)myRange.Value2;

      int shapeLocY = Convert.ToInt16(inputValue);

      //   create the shape

      try

      {

         IShape newShape = this.AvailableShapes[shapeName].Clone();

         newShape.Size = Application.CreateSize(shapeSizeX, shapeSizeY);

         newShape.Location = Application.CreatePoint(shapeLocX, shapeLocY);

         //   add the shape to the drawing

         this.ActiveDrawing.Shapes.Add(newShape);

      }

      catch (Exception ex)

      {

         System.Windows.Forms.MessageBox.Show("Error entering data.  " + ex.Message);

      }

   }//NEXT shape

   myBooks.Close();

}

private void OpenExcelFile(string fullFileName)

{

   //create a missing value object for use in the optional parameters

   object missingValue = System.Reflection.Missing.Value;

   //create a new instance of excel and make it visible

   myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

   myExcel.Visible = true;

   //create an excel workbooks instance

   myBooks = myExcel.Workbooks;

   //open the excel file using the missingValue object for the optional paramaters

   myBooks.Open(fullFileName, missingValue, missingValue,

      missingValue, missingValue, missingValue, missingValue, missingValue,

      missingValue, missingValue, missingValue, missingValue,

      missingValue, missingValue, missingValue);

}