//
you're reading...
DotSpatial

Create an Extension to Import an Excel Worksheet

Introduction

DotSpatial is an open-source project that contains controls which can be used to manipulate and display geographic information. This article explains how to create a DotSpatial extension by using the online template. The extension we are creating will allow the user to import points from an Excel file. It will also demonstrate how to programmatically create a FeatureSet.

Getting Started

If you are not familiar with creating a simple DotSpatial-based extension, please consider the introductory article. For practical purposes, we assume you are coming to this article after having completed the previous one.

Creating a New Project

Create a new project using the DotSpatial Plugin Template. You may delete the Readme.txt and modify the name of the MyPlugin1 class (to reflect the functionality provided by the extension). I named mine ImportFromExcelPlugin.

Add a new class file to the project (Project, Add New Item…) named ExcelHelper. We will place our logic in this class and call it from the button in our plugin class. This separation of concerns makes maintaining the code easier.

Using Windows Forms

We are going to prompt the user to select a file using the OpenFileDialog. This requires adding a reference to our project. Using the Add Reference dialog (Project, Add Reference…) find, and add a reference to System.Windows.Forms.

We also want to add a using statement to our project so that we can access the OpenFileDialog class without using its fully qualified namespace (System.Windows.Forms.OpenFileDialog) each time. This will also mean that we can use other classes in the same namespace, like the MessageBox class, without specifying the full namespace.

Add the statement beneath the other using statements, which are near the top of the file.

using System.Windows.Forms;

Getting the Selected File

Using the OpenFileDialog is fairly simple. We set the type of files we want the user to be able to select as the Filter. We then call ShowDialog() to freeze our extension while the user selects a file. If they don’t click cancel, the FileName property will have the file they wish to open. Add this public method to your class, which will do this task.

public static FeatureSet OpenExcelFile()
{
    OpenFileDialog openDialog = new OpenFileDialog();
    openDialog.Filter = "Excel Files|*.xlsx";
    if (openDialog.ShowDialog() == DialogResult.OK)
    {
        DataTable excelTable = ConvertExcelFileToDataTable(openDialog.FileName);
        return ConvertDataTableToFeatureSet(excelTable);
    }
    return null;
}

We call two methods here that we haven’t yet created. We’ll create them next.

Converting the Excel File to a DataTable

Working with files can pose a number of issues. For example, perhaps a process deletes the file after the user selects it and right before we open it. We’ll ignore most of these cases to keep things simple.

We also assume that the Excel file has a sheet named Sheet1 with a lat and a long column. We’re only working with xlsx files, but you could use another connection string to connect to other Excel file formats. You may want to go ahead an create a sample Excel file that you can import.

image

To convert the Excel file to a DataTable, we establish a connection to the file, execute a select all command and use a OleDbDataAdapter to fill a DataTable with the results.

You’ll want to add a few more using statements to your class file.

    using System.Data.OleDb;
    using System.Data;
    using DotSpatial.Projections;
    using DotSpatial.Data;
    using DotSpatial.Topology;

And the following static method.

private static DataTable ConvertExcelFileToDataTable(string excelFileName)
{
    string connectionString =
        String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", excelFileName);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        string query = "SELECT * FROM [Sheet1$]";
        connection.Open();
        OleDbCommand command = new OleDbCommand(query, connection);
        OleDbDataAdapter adapter = new OleDbDataAdapter(command);

        DataTable excelTable = new DataTable();
        adapter.Fill(excelTable);
        return excelTable;
    }
}

You’ll notice that we’ve used static methods, because our code follows a functional style and there aren’t any objects to which our class needs to hold a reference.

The using () block above ensures that the connection object is properly disposed when our method returns, even if there is an exception.

Converting the DataTable to a FeatureSet

Creating a FeatureSet from a DataTable is fairly straightforward. I’ll let you examine the commented code below.

private static FeatureSet ConvertDataTableToFeatureSet(DataTable excelTable)
{
    // See if table has the lat, long columns
    if (excelTable.Columns.Contains("lat") & excelTable.Columns.Contains("long"))
    {
        FeatureSet fs = new FeatureSet(FeatureType.Point);
        fs.Projection = KnownCoordinateSystems.Geographic.World.WGS1984;

        // Set columns of attribute table
        fs.DataTable = excelTable.Clone();

        foreach (DataRow excelRow in excelTable.Rows)
        {
            double lat = Double.Parse(excelRow["lat"].ToString());
            double lon = Double.Parse(excelRow["long"].ToString());

            // Add the point to the FeatureSet
            Coordinate coord = new Coordinate(lon, lat);
            Point point = new Point(coord);
            IFeature feature = fs.AddFeature(point);

            // Bring over all of the data as attribute data.
            for (int i = 0; i <= excelTable.Columns.Count - 1; i++)
            {
                feature.DataRow[i] = excelRow[i];
            }
        }
        return fs;
    }
    else
    {
        MessageBox.Show("The excel table must have lat and long columns.");
        return null;
    }
}

Wire up the Plugin to access our helper class

Our helper class is complete and we can reference it from our plugin class. Open ImportFromExcelPlugin. Change the name of the SimpleActionItem from “My Button Caption” to “Add Layer from Excel…”

Replace the ButtonClick event handler. Here, we add the FeatureSet created by our ExcelHelper to the map and assign it LegendText.

public void ButtonClick(object sender, EventArgs e)
{
    var featureSet = ExcelHelper.OpenExcelFile();

    if (featureSet != null)
    {
        //add feature set to map
        var layer = App.Map.Layers.Add(featureSet);
        layer.LegendText = "Points From Excel";
    }
}

We are done! Build and run your project.

image

About these ads

About mudnug

Graduated from the University of Missouri in 2007 with a degree in Computer Science. Helped create the enterprise resource planning software used by Dome Technology which provides an estimate of the cost of building a dome, tracks project progress, and provides historical and forecasted information about construction projects. Presently works on an open source mapping framework called DotSpatial.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.