customizing nodeXL

Jan 13, 2010 at 7:54 AM

I have looked into the APIs and could easily draw nodes and vertices which I read from the Database

Now i am trying to export these data into a XLS template  so that graph could be drawn autmatically.

Since I will be integrating this  graph into C#.net panel , is it posible to display only the graph and then hide the XLS data from the users  programatically ?

also I need to save the changes back to new XLS template.

Would that be possible or Do i need to look into APIs and draw the graphs from scratch ?

 

thanks

 

 

 

Jan 14, 2010 at 6:37 AM

I don't think it's possible to hide all the worksheets in the workbook.  Excel seems to insist on there being at least one visible worksheet at all times.

I'm not sure what you mean by saving the changes back to a new XLS template.  Are you programmatically filling in a NodeXL workbook and now you need to save it as a file?  Please provide more details.

-- Tony

 

Jan 14, 2010 at 6:43 AM
Ye i want to programatically fill the NodeXL workbook or template , It would be filled from the database.
Is that possible? If yes , can you please provide me some details on how to begin with.

I could directly draw the nodes by reading values from database as well but I have to do that programatically so I am trying to see if I could load the data from database and dump to XLS template

thanks


On Thu, Jan 14, 2010 at 1:22 PM, tcap479 <notifications@codeplex.com> wrote:

From: tcap479

I don't think it's possible to hide all the worksheets in the workbook.  Excel seems to insist on there being at least one visible worksheet at all times.

I'm not sure what you mean by saving the changes back to a new XLS template.  Are you programmatically filling in a NodeXL workbook and now you need to save it as a file?  Please provide more details.

-- Tony

 

Read the full discussion online.

To add a post to this discussion, reply to this email (NodeXL@discussions.codeplex.com)

To start a new discussion for this project, email NodeXL@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--
Nipen Mark
Jan 15, 2010 at 4:44 PM

Yes, you can do that.  My next post here will contain a complete, compilable C# program that creates a workbook from the NodeXL template and fills in some sample edges.  I cobbled the program together from other NodeXL code; I wouldn't normally structure it like this.

-- Tony

Jan 15, 2010 at 4:45 PM

using System;
using System.IO;
using System.Reflection;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace SimpleWorkbookFiller
{
class Program
{
    //*************************************************************************
    //  Method: Main()
    //
    /// <summary>
    /// Demonstrates how the NodeXL template can be filled by an external
    /// application.
    /// </summary>
    ///
    /// <remarks>
    /// Requirements:
    ///
    /// <para>
    /// Excel 2007.
    /// </para>
    ///
    /// <para>
    /// The NodeXL Excel 2007 Template must be installed.  This is available at
    /// http://nodexl.codeplex.com/Release/ProjectReleases.aspx.
    /// </para>
    ///
    /// </remarks>
    //*************************************************************************

    static void
    Main
    (
        String [] args
    )
    {
        // Create a workbook instance of the NodeXL template.

        Application oApplication = new ApplicationClass();
        oApplication.Visible = true;

        String sTemplatePath;

        if ( !TryGetTemplatePath(oApplication, out sTemplatePath) )
        {
            OnUnexpectedCondition("NodeXL is not installed.");
        }

        Workbook oWorkbook = oApplication.Workbooks.Add(sTemplatePath);

        // Fill in the edge table on the edge worksheet.

        ListObject oEdgeTable;

        if ( !TryGetTable(oWorkbook, "Edges", "Edges", out oEdgeTable) )
        {
            OnUnexpectedCondition("Can't find edge table.");
        }

        AddSampleEdges(oEdgeTable);

        // Clean up.

        Marshal.ReleaseComObject(oWorkbook);
        Marshal.ReleaseComObject(oEdgeTable);
        Marshal.ReleaseComObject(oApplication);
    }

    //*************************************************************************
    //  Method: AddSampleEdges()
    //
    /// <summary>
    /// Adds sample edges to the edge table in the NodeXL workbook.
    /// </summary>
    ///
    /// <param name="edgeTable">
    /// The edge table in the NodeXL workbook.
    /// </param>
    //*************************************************************************

    private static void
    AddSampleEdges
    (
        ListObject edgeTable
    )
    {
        Debug.Assert(edgeTable != null);

        SetTableCellValue(edgeTable, 0, Vertex1ColumnName, "Edge1-1");
        SetTableCellValue(edgeTable, 1, Vertex1ColumnName, "Edge2-1");
        SetTableCellValue(edgeTable, 2, Vertex1ColumnName, "Edge3-1");

        SetTableCellValue(edgeTable, 0, Vertex2ColumnName, "Edge1-2");
        SetTableCellValue(edgeTable, 1, Vertex2ColumnName, "Edge2-2");
        SetTableCellValue(edgeTable, 2, Vertex2ColumnName, "Edge3-2");
    }

    //*************************************************************************
    //  Method: TryGetTemplatePath()
    //
    /// <summary>
    /// Attempts to get the full path to the application's template file.
    /// </summary>
    ///
    /// <param name="application">
    /// The Excel application.
    /// </param>
    ///
    /// <param name="templatePath">
    /// Where the path to the template file gets stored regardless of the
    /// return value.
    /// </param>
    ///
    /// <remarks>
    /// true if the template file exists.
    /// </remarks>
    //*************************************************************************

    private static Boolean
    TryGetTemplatePath
    (
        Microsoft.Office.Interop.Excel.Application application,
        out String templatePath
    )
    {
        Debug.Assert(application != null);

        templatePath = Path.Combine(application.TemplatesPath, TemplateName);

        return ( File.Exists(templatePath) );
    }

    //*************************************************************************
    //  Method: SetTableCellValue()
    //
    /// <summary>
    /// Sets the value of one cell in a table.
    /// </summary>
    ///
    /// <param name="table">
    /// The table with the cell that needs to be set.
    /// </param>
    ///
    /// <param name="zeroBasedTableRow">
    /// Zero-based index of the data row.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the table column.
    /// </param>
    ///
    /// <param name="cellValue">
    /// Value to set.
    /// </param>
    //*************************************************************************

    private static void
    SetTableCellValue
    (
        ListObject table,
        Int32 zeroBasedTableRow,
        String columnName,
        Object cellValue
    )
    {
        Debug.Assert(table != null);
        Debug.Assert(zeroBasedTableRow >= 0);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        Range oTableColumnData;

        if ( !TryGetTableColumnData(table, columnName, out oTableColumnData) )
        {
            OnUnexpectedCondition("Missing table column.");
        }

        Range oCell = (Range)oTableColumnData.Cells[zeroBasedTableRow + 1, 1];

        oCell.set_Value(Missing.Value, cellValue);
    }

    //*************************************************************************
    //  Method: TryGetTable()
    //
    /// <overloads>
    /// Attempts to get a table (ListObject) by name.
    /// </overloads>
    ///
    /// <summary>
    /// Attempts to get a table (ListObject) from a workbook by worksheet name
    /// and table name.
    /// </summary>
    ///
    /// <param name="workbook">
    /// Workbook to get the table from.
    /// </param>
    ///
    /// <param name="worksheetName">
    /// Name of the worksheet containing the table.
    /// </param>
    ///
    /// <param name="tableName">
    /// Name of the table to get.
    /// </param>
    ///
    /// <param name="table">
    /// Where the requested table gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="workbook" /> contains a worksheet named <paramref
    /// name="worksheetName" /> that has a table (ListObject) named
    /// <paramref name="tableName" />, the ListObject is stored at <paramref
    /// name="table" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTable
    (
        Microsoft.Office.Interop.Excel.Workbook workbook,
        String worksheetName,
        String tableName,
        out ListObject table
    )
    {
        Debug.Assert(workbook != null);
        Debug.Assert( !String.IsNullOrEmpty(worksheetName) );
        Debug.Assert( !String.IsNullOrEmpty(tableName) );

        table = null;

        Microsoft.Office.Interop.Excel.Worksheet oWorksheet;

        return ( TryGetWorksheet(workbook, worksheetName, out oWorksheet) &&
            TryGetTable(oWorksheet, tableName, out table) );
    }

    //*************************************************************************
    //  Method: TryGetWorksheet()
    //
    /// <summary>
    /// Attempts to get a worksheet by name.
    /// </summary>
    ///
    /// <param name="workbook">
    /// Workbook to get a worksheet from.
    /// </param>
    ///
    /// <param name="worksheetName">
    /// Name of the worksheet to get.
    /// </param>
    ///
    /// <param name="worksheet">
    /// Where the requested worksheet gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="workbook" /> contains a worksheet named <paramref
    /// name="worksheetName" />, the worksheet is stored at <paramref
    /// name="worksheet" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetWorksheet
    (
        Microsoft.Office.Interop.Excel.Workbook workbook,
        String worksheetName,
        out Worksheet worksheet
    )
    {
        Debug.Assert(workbook != null);
        Debug.Assert( !String.IsNullOrEmpty(worksheetName) );

        worksheet = null;

        Object oSheet;

        try
        {
            oSheet = workbook.Sheets[worksheetName];
        }
        catch (COMException)
        {
            return (false);
        }

        if ( !(oSheet is Worksheet) )
        {
            return (false);
        }

        worksheet = (Worksheet)oSheet;

        return (true);
    }

    //*************************************************************************
    //  Method: TryGetTable()
    //
    /// <summary>
    /// Attempts to get a table (ListObject) from a worksheet by table name.
    /// </summary>
    ///
    /// <param name="worksheet">
    /// Worksheet to get the table from.
    /// </param>
    ///
    /// <param name="tableName">
    /// Name of the table to get.
    /// </param>
    ///
    /// <param name="table">
    /// Where the requested table gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="worksheet" /> contains a table (ListObject) named
    /// <paramref name="tableName" />, the ListObject is stored at <paramref
    /// name="table" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTable
    (
        Microsoft.Office.Interop.Excel.Worksheet worksheet,
        String tableName,
        out ListObject table
    )
    {
        Debug.Assert(worksheet != null);
        Debug.Assert( !String.IsNullOrEmpty(tableName) );

        table = null;

        try
        {
            table = worksheet.ListObjects[tableName];

            return (true);
        }
        catch (COMException)
        {
            return (false);
        }
    }

    //*************************************************************************
    //  Method: TryGetTableColumnData()
    //
    /// <overloads>
    /// Attempts to get the data range of one column of a table.
    /// </overloads>
    ///
    /// <summary>
    /// Attempts to get the data range of one column of a table given a table
    /// and column name.
    /// </summary>
    ///
    /// <param name="table">
    /// Table to get the column data range from.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the column to get data range for.
    /// </param>
    ///
    /// <param name="tableColumnData">
    /// Where the column data range gets stored if true is returned.  The data
    /// range includes only that part of the column within the table's data
    /// body range.  This excludes any header or totals row.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="table" /> contains a column named <paramref
    /// name="columnName" />, the column's data range is stored at <paramref
    /// name="tableColumnData" /> and true is returned.  false is returned
    /// otherwise.
    /// </remarks>
    //
    //  TODO: This hasn't been tested with a totals row.
    //*************************************************************************

    public static Boolean
    TryGetTableColumnData
    (
        ListObject table,
        String columnName,
        out Range tableColumnData
    )
    {
        Debug.Assert(table != null);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        tableColumnData = null;

        ListColumn oColumn;

        return (
            TryGetTableColumn(table, columnName, out oColumn)
            &&
            TryGetTableColumnData(oColumn, out tableColumnData)
            );
    }

    //*************************************************************************
    //  Method: TryGetTableColumn()
    //
    /// <summary>
    /// Attempts to get a table column given the column name.
    /// </summary>
    ///
    /// <param name="table">
    /// Table to get the column from.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the column to get.
    /// </param>
    ///
    /// <param name="column">
    /// Where the column named <paramref name="columnName" /> gets stored if
    /// true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="table" /> contains a column named <paramref
    /// name="columnName" />, the column is stored at <paramref
    /// name="column" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTableColumn
    (
        ListObject table,
        String columnName,
        out ListColumn column
    )
    {
        Debug.Assert(table != null);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        column = null;

        try
        {
            column = table.ListColumns[columnName];

            return (true);
        }
        catch (COMException)
        {
            return (false);
        }
    }

    //*************************************************************************
    //  Method: TryGetTableColumnData()
    //
    /// <summary>
    /// Attempts to get the data range of one column of a table given the
    /// column.
    /// </summary>
    ///
    /// <param name="column">
    /// Column to get the data range from.
    /// </param>
    ///
    /// <param name="tableColumnData">
    /// Where the column data range gets stored if true is returned.  The data
    /// range includes only that part of the column within the table's data
    /// body range.  This excludes any header or totals row.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    //
    //  TODO: This hasn't been tested with a totals row.
    //*************************************************************************

    public static Boolean
    TryGetTableColumnData
    (
        ListColumn column,
        out Range tableColumnData
    )
    {
        Debug.Assert(column != null);

        tableColumnData = null;

        Debug.Assert(column.Parent is ListObject);

        ListObject oTable = (ListObject)column.Parent;

        Range oDataBodyRange = oTable.DataBodyRange;

        if (oDataBodyRange == null)
        {
            // This happens when the user deletes the first data row of a one-
            // row table.  It looks like an empty row is there, but the
            // DataBodyRange is actually null.

            Int32 iRow;

            // Is there a header row?

            Range oRangeToUse = oTable.HeaderRowRange;

            if (oRangeToUse != null)
            {
                // Yes.  Use the row after the header row.

                iRow = oRangeToUse.Row + 1;
            }
            else
            {
                // No.  Use the first row of the table.

                oRangeToUse = oTable.Range;

                iRow = oRangeToUse.Row;
            }

            Debug.Assert(oTable.Parent is Worksheet);

            Worksheet oWorksheet = (Worksheet)oTable.Parent;

            oDataBodyRange = oWorksheet.get_Range(

                oWorksheet.Cells[iRow, oRangeToUse.Column],

                oWorksheet.Cells[iRow,
                    oRangeToUse.Column + oRangeToUse.Columns.Count - 1]
                );
        }

        return ( TryIntersectRanges(oDataBodyRange, column.Range,
            out tableColumnData) );
    }

    //*************************************************************************
    //  Method: TryIntersectRanges()
    //
    /// <summary>
    /// Attempts to get the intersection of two ranges.
    /// </summary>
    ///
    /// <param name="range1">
    /// First range.  Can be null.
    /// </param>
    ///
    /// <param name="range2">
    /// Range to intersect with <paramref name="range1" />.  Can be null.
    /// </param>
    ///
    /// <param name="intersection">
    /// Where the intersection of <paramref name="range1" /> and <paramref
    /// name="range2" /> gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if the intersection is not null.
    /// </returns>
    //*************************************************************************

    public static Boolean
    TryIntersectRanges
    (
        Range range1,
        Range range2,
        out Range intersection
    )
    {
        intersection = null;

        if (range1 != null && range2 != null)
        {
            intersection = range1.Application.Intersect(range1, range2,

                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value
                );
        }

        return (intersection != null);
    }

    //*************************************************************************
    //  Method: OnUnexpectedCondition()
    //
    /// <summary>
    /// Attempts to get the full path to the application's template file.
    /// </summary>
    ///
    /// <param name="application">
    /// The Excel application.
    /// </param>
    ///
    /// <param name="templatePath">
    /// Where the path to the template file gets stored regardless of the
    /// return value.
    /// </param>
    ///
    /// <remarks>
    /// true if the template file exists.
    /// </remarks>
    //*************************************************************************

    private static void
    OnUnexpectedCondition
    (
        String errorMessage
    )
    {
        throw new InvalidOperationException(errorMessage);
    }


    //*************************************************************************
    //  Constants
    //*************************************************************************

    private const String TemplateName = "NodeXLGraph.xltx";

    private const String Vertex1ColumnName = "Vertex 1";
    private const String Vertex2ColumnName = "Vertex 2";
}

}

Jan 18, 2010 at 10:05 AM
sounds good and thank you for the code . Is it possible to hide  the populated data and show only  taskpane ( viewer)  from the XLS template through APIs ?
I am trying to see if  we could somehow embed the XLS template itself  c#.panel ( on our existing application ) that way I dont have to draw vertices and edges again .

I dont know if this is a right solution , but i have also tried to read the XLS and could draw programatically as well.

thanks.






On Fri, Jan 15, 2010 at 11:30 PM, tcap479 <notifications@codeplex.com> wrote:

From: tcap479

using System;
using System.IO;
using System.Reflection;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace SimpleWorkbookFiller
{
class Program
{
    //*************************************************************************
    //  Method: Main()
    //
    /// <summary>
    /// Demonstrates how the NodeXL template can be filled by an external
    /// application.
    /// </summary>
    ///
    /// <remarks>
    /// Requirements:
    ///
    /// <para>
    /// Excel 2007.
    /// </para>
    ///
    /// <para>
    /// The NodeXL Excel 2007 Template must be installed.  This is available at
    /// http://nodexl.codeplex.com/Release/ProjectReleases.aspx.
    /// </para>
    ///
    /// </remarks>
    //*************************************************************************

    static void
    Main
    (
        String [] args
    )
    {
        // Create a workbook instance of the NodeXL template.

        Application oApplication = new ApplicationClass();
        oApplication.Visible = true;

        String sTemplatePath;

        if ( !TryGetTemplatePath(oApplication, out sTemplatePath) )
        {
            OnUnexpectedCondition("NodeXL is not installed.");
        }

        Workbook oWorkbook = oApplication.Workbooks.Add(sTemplatePath);

        // Fill in the edge table on the edge worksheet.

        ListObject oEdgeTable;

        if ( !TryGetTable(oWorkbook, "Edges", "Edges", out oEdgeTable) )
        {
            OnUnexpectedCondition("Can't find edge table.");
        }

        AddSampleEdges(oEdgeTable);

        // Clean up.

        Marshal.ReleaseComObject(oWorkbook);
        Marshal.ReleaseComObject(oEdgeTable);
        Marshal.ReleaseComObject(oApplication);
    }

    //*************************************************************************
    //  Method: AddSampleEdges()
    //
    /// <summary>
    /// Adds sample edges to the edge table in the NodeXL workbook.
    /// </summary>
    ///
    /// <param name="edgeTable">
    /// The edge table in the NodeXL workbook.
    /// </param>
    //*************************************************************************

    private static void
    AddSampleEdges
    (
        ListObject edgeTable
    )
    {
        Debug.Assert(edgeTable != null);

        SetTableCellValue(edgeTable, 0, Vertex1ColumnName, "Edge1-1");
        SetTableCellValue(edgeTable, 1, Vertex1ColumnName, "Edge2-1");
        SetTableCellValue(edgeTable, 2, Vertex1ColumnName, "Edge3-1");

        SetTableCellValue(edgeTable, 0, Vertex2ColumnName, "Edge1-2");
        SetTableCellValue(edgeTable, 1, Vertex2ColumnName, "Edge2-2");
        SetTableCellValue(edgeTable, 2, Vertex2ColumnName, "Edge3-2");
    }

    //*************************************************************************
    //  Method: TryGetTemplatePath()
    //
    /// <summary>
    /// Attempts to get the full path to the application's template file.
    /// </summary>
    ///
    /// <param name="application">
    /// The Excel application.
    /// </param>
    ///
    /// <param name="templatePath">
    /// Where the path to the template file gets stored regardless of the
    /// return value.
    /// </param>
    ///
    /// <remarks>
    /// true if the template file exists.
    /// </remarks>
    //*************************************************************************

    private static Boolean
    TryGetTemplatePath
    (
        Microsoft.Office.Interop.Excel.Application application,
        out String templatePath
    )
    {
        Debug.Assert(application != null);

        templatePath = Path.Combine(application.TemplatesPath, TemplateName);

        return ( File.Exists(templatePath) );
    }

    //*************************************************************************
    //  Method: SetTableCellValue()
    //
    /// <summary>
    /// Sets the value of one cell in a table.
    /// </summary>
    ///
    /// <param name="table">
    /// The table with the cell that needs to be set.
    /// </param>
    ///
    /// <param name="zeroBasedTableRow">
    /// Zero-based index of the data row.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the table column.
    /// </param>
    ///
    /// <param name="cellValue">
    /// Value to set.
    /// </param>
    //*************************************************************************

    private static void
    SetTableCellValue
    (
        ListObject table,
        Int32 zeroBasedTableRow,
        String columnName,
        Object cellValue
    )
    {
        Debug.Assert(table != null);
        Debug.Assert(zeroBasedTableRow >= 0);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        Range oTableColumnData;

        if ( !TryGetTableColumnData(table, columnName, out oTableColumnData) )
        {
            OnUnexpectedCondition("Missing table column.");
        }

        Range oCell = (Range)oTableColumnData.Cells[zeroBasedTableRow + 1, 1];

        oCell.set_Value(Missing.Value, cellValue);
    }

    //*************************************************************************
    //  Method: TryGetTable()
    //
    /// <overloads>
    /// Attempts to get a table (ListObject) by name.
    /// </overloads>
    ///
    /// <summary>
    /// Attempts to get a table (ListObject) from a workbook by worksheet name
    /// and table name.
    /// </summary>
    ///
    /// <param name="workbook">
    /// Workbook to get the table from.
    /// </param>
    ///
    /// <param name="worksheetName">
    /// Name of the worksheet containing the table.
    /// </param>
    ///
    /// <param name="tableName">
    /// Name of the table to get.
    /// </param>
    ///
    /// <param name="table">
    /// Where the requested table gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="workbook" /> contains a worksheet named <paramref
    /// name="worksheetName" /> that has a table (ListObject) named
    /// <paramref name="tableName" />, the ListObject is stored at <paramref
    /// name="table" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTable
    (
        Microsoft.Office.Interop.Excel.Workbook workbook,
        String worksheetName,
        String tableName,
        out ListObject table
    )
    {
        Debug.Assert(workbook != null);
        Debug.Assert( !String.IsNullOrEmpty(worksheetName) );
        Debug.Assert( !String.IsNullOrEmpty(tableName) );

        table = null;

        Microsoft.Office.Interop.Excel.Worksheet oWorksheet;

        return ( TryGetWorksheet(workbook, worksheetName, out oWorksheet) &&
            TryGetTable(oWorksheet, tableName, out table) );
    }

    //*************************************************************************
    //  Method: TryGetWorksheet()
    //
    /// <summary>
    /// Attempts to get a worksheet by name.
    /// </summary>
    ///
    /// <param name="workbook">
    /// Workbook to get a worksheet from.
    /// </param>
    ///
    /// <param name="worksheetName">
    /// Name of the worksheet to get.
    /// </param>
    ///
    /// <param name="worksheet">
    /// Where the requested worksheet gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="workbook" /> contains a worksheet named <paramref
    /// name="worksheetName" />, the worksheet is stored at <paramref
    /// name="worksheet" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetWorksheet
    (
        Microsoft.Office.Interop.Excel.Workbook workbook,
        String worksheetName,
        out Worksheet worksheet
    )
    {
        Debug.Assert(workbook != null);
        Debug.Assert( !String.IsNullOrEmpty(worksheetName) );

        worksheet = null;

        Object oSheet;

        try
        {
            oSheet = workbook.Sheets[worksheetName];
        }
        catch (COMException)
        {
            return (false);
        }

        if ( !(oSheet is Worksheet) )
        {
            return (false);
        }

        worksheet = (Worksheet)oSheet;

        return (true);
    }

    //*************************************************************************
    //  Method: TryGetTable()
    //
    /// <summary>
    /// Attempts to get a table (ListObject) from a worksheet by table name.
    /// </summary>
    ///
    /// <param name="worksheet">
    /// Worksheet to get the table from.
    /// </param>
    ///
    /// <param name="tableName">
    /// Name of the table to get.
    /// </param>
    ///
    /// <param name="table">
    /// Where the requested table gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="worksheet" /> contains a table (ListObject) named
    /// <paramref name="tableName" />, the ListObject is stored at <paramref
    /// name="table" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTable
    (
        Microsoft.Office.Interop.Excel.Worksheet worksheet,
        String tableName,
        out ListObject table
    )
    {
        Debug.Assert(worksheet != null);
        Debug.Assert( !String.IsNullOrEmpty(tableName) );

        table = null;

        try
        {
            table = worksheet.ListObjects[tableName];

            return (true);
        }
        catch (COMException)
        {
            return (false);
        }
    }

    //*************************************************************************
    //  Method: TryGetTableColumnData()
    //
    /// <overloads>
    /// Attempts to get the data range of one column of a table.
    /// </overloads>
    ///
    /// <summary>
    /// Attempts to get the data range of one column of a table given a table
    /// and column name.
    /// </summary>
    ///
    /// <param name="table">
    /// Table to get the column data range from.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the column to get data range for.
    /// </param>
    ///
    /// <param name="tableColumnData">
    /// Where the column data range gets stored if true is returned.  The data
    /// range includes only that part of the column within the table's data
    /// body range.  This excludes any header or totals row.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="table" /> contains a column named <paramref
    /// name="columnName" />, the column's data range is stored at <paramref
    /// name="tableColumnData" /> and true is returned.  false is returned
    /// otherwise.
    /// </remarks>
    //
    //  TODO: This hasn't been tested with a totals row.
    //*************************************************************************

    public static Boolean
    TryGetTableColumnData
    (
        ListObject table,
        String columnName,
        out Range tableColumnData
    )
    {
        Debug.Assert(table != null);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        tableColumnData = null;

        ListColumn oColumn;

        return (
            TryGetTableColumn(table, columnName, out oColumn)
            &&
            TryGetTableColumnData(oColumn, out tableColumnData)
            );
    }

    //*************************************************************************
    //  Method: TryGetTableColumn()
    //
    /// <summary>
    /// Attempts to get a table column given the column name.
    /// </summary>
    ///
    /// <param name="table">
    /// Table to get the column from.
    /// </param>
    ///
    /// <param name="columnName">
    /// Name of the column to get.
    /// </param>
    ///
    /// <param name="column">
    /// Where the column named <paramref name="columnName" /> gets stored if
    /// true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    ///
    /// <remarks>
    /// If <paramref name="table" /> contains a column named <paramref
    /// name="columnName" />, the column is stored at <paramref
    /// name="column" /> and true is returned.  false is returned otherwise.
    /// </remarks>
    //*************************************************************************

    public static Boolean
    TryGetTableColumn
    (
        ListObject table,
        String columnName,
        out ListColumn column
    )
    {
        Debug.Assert(table != null);
        Debug.Assert( !String.IsNullOrEmpty(columnName) );

        column = null;

        try
        {
            column = table.ListColumns[columnName];

            return (true);
        }
        catch (COMException)
        {
            return (false);
        }
    }

    //*************************************************************************
    //  Method: TryGetTableColumnData()
    //
    /// <summary>
    /// Attempts to get the data range of one column of a table given the
    /// column.
    /// </summary>
    ///
    /// <param name="column">
    /// Column to get the data range from.
    /// </param>
    ///
    /// <param name="tableColumnData">
    /// Where the column data range gets stored if true is returned.  The data
    /// range includes only that part of the column within the table's data
    /// body range.  This excludes any header or totals row.
    /// </param>
    ///
    /// <returns>
    /// true if successful.
    /// </returns>
    //
    //  TODO: This hasn't been tested with a totals row.
    //*************************************************************************

    public static Boolean
    TryGetTableColumnData
    (
        ListColumn column,
        out Range tableColumnData
    )
    {
        Debug.Assert(column != null);

        tableColumnData = null;

        Debug.Assert(column.Parent is ListObject);

        ListObject oTable = (ListObject)column.Parent;

        Range oDataBodyRange = oTable.DataBodyRange;

        if (oDataBodyRange == null)
        {
            // This happens when the user deletes the first data row of a one-
            // row table.  It looks like an empty row is there, but the
            // DataBodyRange is actually null.

            Int32 iRow;

            // Is there a header row?

            Range oRangeToUse = oTable.HeaderRowRange;

            if (oRangeToUse != null)
            {
                // Yes.  Use the row after the header row.

                iRow = oRangeToUse.Row + 1;
            }
            else
            {
                // No.  Use the first row of the table.

                oRangeToUse = oTable.Range;

                iRow = oRangeToUse.Row;
            }

            Debug.Assert(oTable.Parent is Worksheet);

            Worksheet oWorksheet = (Worksheet)oTable.Parent;

            oDataBodyRange = oWorksheet.get_Range(

                oWorksheet.Cells[iRow, oRangeToUse.Column],

                oWorksheet.Cells[iRow,
                    oRangeToUse.Column + oRangeToUse.Columns.Count - 1]
                );
        }

        return ( TryIntersectRanges(oDataBodyRange, column.Range,
            out tableColumnData) );
    }

    //*************************************************************************
    //  Method: TryIntersectRanges()
    //
    /// <summary>
    /// Attempts to get the intersection of two ranges.
    /// </summary>
    ///
    /// <param name="range1">
    /// First range.  Can be null.
    /// </param>
    ///
    /// <param name="range2">
    /// Range to intersect with <paramref name="range1" />.  Can be null.
    /// </param>
    ///
    /// <param name="intersection">
    /// Where the intersection of <paramref name="range1" /> and <paramref
    /// name="range2" /> gets stored if true is returned.
    /// </param>
    ///
    /// <returns>
    /// true if the intersection is not null.
    /// </returns>
    //*************************************************************************

    public static Boolean
    TryIntersectRanges
    (
        Range range1,
        Range range2,
        out Range intersection
    )
    {
        intersection = null;

        if (range1 != null && range2 != null)
        {
            intersection = range1.Application.Intersect(range1, range2,

                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value
                );
        }

        return (intersection != null);
    }

    //*************************************************************************
    //  Method: OnUnexpectedCondition()
    //
    /// <summary>
    /// Attempts to get the full path to the application's template file.
    /// </summary>
    ///
    /// <param name="application">
    /// The Excel application.
    /// </param>
    ///
    /// <param name="templatePath">
    /// Where the path to the template file gets stored regardless of the
    /// return value.
    /// </param>
    ///
    /// <remarks>
    /// true if the template file exists.
    /// </remarks>
    //*************************************************************************

    private static void
    OnUnexpectedCondition
    (
        String errorMessage
    )
    {
        throw new InvalidOperationException(errorMessage);
    }


    //*************************************************************************
    //  Constants
    //*************************************************************************

    private const String TemplateName = "NodeXLGraph.xltx";

    private const String Vertex1ColumnName = "Vertex 1";
    private const String Vertex2ColumnName = "Vertex 2";
}

}

Read the full discussion online.

To add a post to this discussion, reply to this email (NodeXL@discussions.codeplex.com)

To start a new discussion for this project, email NodeXL@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--
Nipen Mark
Jan 18, 2010 at 4:13 PM

I do not know a way to hide the worksheets while keeping the graph pane visible.  If you try to do that manually by right-click each worksheet tab and selecting "Hide," for example, Excel will stop you from hiding the final worksheet with the message "A workbook must contain at least one visible worksheet."

-- Tony

Jan 18, 2010 at 4:20 PM

Using the NodeXL template just to show a graph (and hiding the actual workbook from the user, if that is even possible) sounds awfully indirect and complicated to me.  Can you just use the NodeXLControl in your application and bypass Excel entirely?  You don't have to draw any vertices or edges; the control does that for you.

If you're not familiar with the NodeXLControl, look for the latest "NodeXL Class Libraries" download on the Downloads tab.

-- Tony