Doing DotSpatial in Visual C# 2010 Express: Using Data in Microsoft Excel 2007 Table to Create Points: 3 Errors

Feb 23, 2011 at 7:27 PM
Edited Feb 23, 2011 at 8:00 PM

Hi all,

I have values of the XY-coordinates of 8 GIS points stored in Microsoft Excel 2007 Table that is in the C;Drive of my terminal PC in Microsoft NT 4 LAN System.

        FID            Id      LocationID             X        Y
0 1      EB1 -77.01507 38.870909
1 2      EB2 -77.01507 38.870745
2    3      EB3 -77.01507 38.870615
3 4      EB4 -77.01518 38.870868
4 5      EB5 -77.01531 38.87071
5 6      EB6 -77.01535 38.870614
6 7      EB7 -77.01518 38.870744
7 8      EB8 -77.01519 38.870631

                The .xlsx Sheet Name: FtMcNair.

How can I write the C# code in the DotSpatial project to connect to this database?  How can the DotSpatial - Visual C# 2010 Express code read the values of the XY-coordinates of 8 GIS points to create a DotSpatial Map in Geographic Coordinate System GCS_WGS_1984?  Please kindly help and advise me as much as you can. 

Thanks,

Scott Chang 

 

Developer
Feb 25, 2011 at 7:55 PM

Hi Scott,

Here is a sample code that will create the point featureSet from your excel file (just modify the excelFileName to the correct location of your excel file).


private void btnExcel_Click(object sender, EventArgs e)
{
     string excelFileName = "c:\\Users\\Jiri\\Desktop\\scott.xlsx";
     string sheetName = "FtMcNair";

     //this is the connection string to the xlsx file (excel2007)
     string connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";", excelFileName);
     using (OleDbConnection conn = new OleDbConnection(connstr))
     {
          string strSQL = string.Format("SELECT * FROM [{0}$]", sheetName);

          OleDbCommand cmd = new OleDbCommand(strSQL, conn);
          DataSet ds = new DataSet();
          OleDbDataAdapter da = new OleDbDataAdapter(cmd);
          da.Fill(ds);
          DataTable excelTable = ds.Tables[0];
          //dataGridView1.DataSource = excelTable;

          //creating the point features set
          DotSpatial.Data.FeatureSet fs = new DotSpatial.Data.FeatureSet(FeatureType.Point);
          //set the projection to WGS1984
          fs.Projection = KnownCoordinateSystems.Geographic.World.WGS1984;

          //copy the attribute table column names from the excel table
          fs.DataTable = excelTable.Clone();

          //populate the point features
          foreach (DataRow row in excelTable.Rows)
          {
              //get x, y
              double x = Convert.ToDouble(row["x"]);
              double y = Convert.ToDouble(row["y"]);
                    
              //creates a new coordinate 
              Coordinate c = new Coordinate(x, y);
              //passes the coordinate to a new point
              DotSpatial.Topology.Point p = new DotSpatial.Topology.Point(c);

              DotSpatial.Data.IFeature currentFeature = fs.AddFeature(p);

              //add the cell values for each row
              for (int i = 0; i < excelTable.Columns.Count; i++)
              {
                  currentFeature.DataRow[i] = row[i];
              }
          }
          //add feature set to the map
          map1.Layers.Add(fs);
      }
  }


In this code, you import the excel sheet into a DataSet and then create the FeatureSet based on values in the DataTable.
Regards,

Jiri

Feb 28, 2011 at 2:40 PM
Edited Feb 28, 2011 at 2:48 PM

Hi Jiri,  Thanks for your nice response and valuable C# code.

In my Visual C# 2010 Express, I lanched a new project "FtMN8PtsExcel", did "Add References" for DotSpatial.Controls, DotSpatial.Data, DotSpatial.Projections, DotSpatial.Symbology,DotSpatial.Tologoly,

and completed the following C# coding:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
//using System.Data.DataSet;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DotSpatial.Data;
using DotSpatial.Projections;
using DotSpatial.Symbology;
using DotSpatial.Topology;


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

        private void btnExcel_Click(object sender, EventArgs e)
        {
            string excelFileName = "c:\\DotSpatial\\FtMcNair8Pts.xlsx";
            string sheetName = "FtMcNair";

            //this is the connection string to the xlsx file (excel2007)
            string connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";", excelFileName);
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                string strSQL = string.Format("SELECT * FROM [{0}$]", sheetName);

                OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds);
                DataTable excelTable = ds.Tables[0];
                //dataGridView1.DataSource = excelTable;

                //creating the point features set
                DotSpatial.Data.FeatureSet fs = new DotSpatial.Data.FeatureSet(FeatureType.Point);
                //set the projection to WGS1984
                fs.Projection = KnownCoordinateSystems.Geographic.World.WGS1984;

                //copy the attribute table column names from the excel table
                fs.DataTable = excelTable.Clone();

                //populate the point features
                foreach (DataRow row in excelTable.Rows)
                {
                    //get x, y
                    double x = Convert.ToDouble(row["x"]);
                    double y = Convert.ToDouble(row["y"]);

                    //creates a new coordinate 
                    Coordinate c = new Coordinate(x, y);
                    //passes the coordinate to a new point
                    DotSpatial.Topology.Point p = new DotSpatial.Topology.Point(c);

                    DotSpatial.Data.IFeature currentFeature = fs.AddFeature(p);

                    //add the cell values for each row
                    for (int i = 0; i < excelTable.Columns.Count; i++)
                    {
                        currentFeature.DataRow[i] = row[i];
                    }
                }
                //add feature set to the map
                map1.Layers.Add(fs);
            }

        }
    }
}

I got the following 3 errors:

Error 1 'DataSet' is an ambiguous reference between 'System.Data.DataSet' and 'DotSpatial.Data.DataSet' C:\Documents and Settings\e1enxshc\my documents\visual studio 2010\Projects\FtMN8PtsExcel\FtMN8PtsExcel\Form1.cs 38 17 FtMN8PtsExcel
Error 2 'DataSet' is an ambiguous reference between 'System.Data.DataSet' and 'DotSpatial.Data.DataSet' C:\Documents and Settings\e1enxshc\my documents\visual studio 2010\Projects\FtMN8PtsExcel\FtMN8PtsExcel\Form1.cs 38 34 FtMN8PtsExcel
Error 3 The name 'map1' does not exist in the current context C:\Documents and Settings\e1enxshc\my documents\visual studio 2010\Projects\FtMN8PtsExcel\FtMN8PtsExcel\Form1.cs 73 17 FtMN8PtsExcel

I know (i) Errors #1 and #2 are related to Microsoft System. But I do not know how to fix them.  (ii) Error #3 is related to DotSpatial.  I know how to add the DotSpatial "Legend"-"Map" on the Form1

(I did it for my first DotSpatial project before), but I do not know how to make the "btnExcel" and the DotSpatial "Legend"-"Map" co-existing on the Form1 simultaneously!!!???

Please kindly help and advise me how to solve these 3 errors.

Thanks again,

Scott Chang  

Developer
Feb 28, 2011 at 4:28 PM

Hi Scott,

Check if you have added the DotSpatial Map and Legend controls on your Form. Also check the name of your Map control. In

map1.Layers.Add(fs)

 

you should use the correct name of your Map control instead of "map1"

Jiri

Feb 28, 2011 at 6:22 PM

Hi Jiri, 

I will add the DotSpatial Legend and Map controls on my Form1.  But I still do not feel I get the answers from you to resolve the 3 errors:

Microsoft DataSet problems revealed in Errors #1 & 2, and my last question about the co-existing of the btnExcel and the DotSpatial Legend and Map.

Please clarify your instructions a little more and reply. 

Thanks,

Scott Chang

Developer
Feb 28, 2011 at 6:33 PM

Hi Scott,

Regarding Errors #1 & 2:

instead of: DataSet ds = new DataSet(); you should write: System.Data.DataSet ds = new System.Data.DataSet();


To make the legend, map and btn excel coexist on Form1:

  1. Add the map control from "visual studio toolbox" to Form1. Set the name of the map control to "map1".
  2. Add the legend control from "visual studio toolbox" to Form1. Set the name of the legend control to "legend1"
  3. Add a new button to Form1. Set the name of the button to "btnExcel"
  4. In the Form1 designer view, double-click on "btnExcel". This will generate the code:
private void btnExcel_Click(object sender, EventArgs e)
{

}

Replace the empty btnExcel_Click by the code from the example above.

Regards,
Jiri
Mar 1, 2011 at 2:55 PM
Edited Mar 1, 2011 at 6:29 PM

Hi Jiri, Thanks for your nice response.

(1) Errors #1 & 2 are resolved:  System.Data.DataSet ds = new System.Data.DataSet();  is working correctly and nicely.

(2) Error #3 is not resolved yet.  Issue of making the DotSpatial "Legend", "Map" and btnExcel co-exist on Form1 (to get Map1):

     If I just drag a regular Button from the All Windows Form of Toolbox first, then "Legend" and "Map" of the DotSpatial in the left Toolbox to the Form1, I have to set the 'dock' property of the "Legend" and "Map" as Fill/Top/Left/Right.   The regular Button (i.e. btnExcel) will be buried under the "Legend" box and can not be clicked on, after the project is executed.  This is a problem I can see first. 

     In the last 2 months, I tried to get my "Doing DotSpatial in Visual C# 2010 Express" started, Matthew K. and Mudnug made 3 videos -see below: 

  Comments
mudnug Feb 4 at 12:06 PM 
Here are a few videos to help you get started with the Dec 15, 2010 release.

Creating a new project and getting the visual components and references wired up in Visual C# 2010 Express (except Tool Manager).
http://screencast.com/t/htiyqvLh

How to add DotSpatial controls to Toolbox
http://screencast.com/t/tuEfbInl

How to Setup the Tool Manager
http://screencast.com/t/GFIbtZ3iB

 

mudnug Jan 19 at 1:30 PM 
There are easter eggs on the documentation page. Mouse around and see if you find something colorful to click on.
==================================================================================
They helped me greatly in creating DotSpatial.Controls, adding DotSpatial References, designing the Graphical User Interface (GUI), etc.  Thus, I was able to do DotSpatial AddManager, Legend, Map, SpatialStatusStrip, SpatialToolStrip, ToolManager, SplitContainer (for Panel1/Legend and Panel2/Map), and clicking on the "Select" Menu for changing the property of "spatialToolStrip1 DotSpatial.Controls.SpatialToolStrip": from Map to map1.  Therefore, I was able to create my first 2 DotSpatial projects "scDotSpatial-1" and "scDotSpatial_NewPoint-btnCoord" (with your valuable help).   In my  "scDotSpatial_NewPoint-btnCoord" project, the following C# code statements: 
 IFeature currentFeature = fs.AddFeature(p);
            fs.SaveAs(@"C:\Temp\test-1PtFtMcNair.shp",true);
            MessageBox.Show("Shapefile created successfully.");
 were used to save the results in my C:\Temp folder.
In my current project "FtMN8ptsExcel", you gave me the following C# code statements:
                   //add feature set to the map
                     map1.Layers.Add(fs);
to plot the 8 points on map1 directly.  I think I can do this step by doing the above-mentioned complex DotSpatial C# procedures to get map1 done.  But, I think I can not see and click on the btnExcel that is buried under the DotSpatial "Legend"!!?? This is my problem now.  
One more thing to discuss: In the DotSpatial Tutorial (1) - Working with DotSpatial controls (for Visual Basic), Step 3: Design the GUI. (GUI-Graphical User Interface): fig. 8 shows the following:
                 Baic Map Operations
         Operations |Load Map|   |Clear Map| |Zoom In| |Zoom to Extend|
These are quite different from the formats of MapWindow 6.0(Orlando)/the C# DotSpatial (I use now).   Why are they different?
Jiri, please tell me: (1) how I can get the visual btnExcel that is not buried under the DotSpatial "Legend" on the Form1 in my FtMN8PtsExcel project. Do I have to compart the regular button (btnExcel) and the DotSpatial "Legend" and "Map"? (2) can I do: fs.SaveAs(@"C:\Temp\FtMcNair8Pts.shp",true); in my FtMN8PtsExcel project?  (3) What is the best way to design the GUI in the DotSpatial projects?
Please kindly help, advise and respond.
Many Thanks again,
Scott Chang