Create a Shp file based on an Excel file (detailed development and source code)

Some people often ask the following question: how to convert the data in an Excel table into GIS data? Using the Desktop software (ArcGIS Desktop) cannot solve this problem, but it can be implemented through development!

Creating a Shp file based on an Excel table mainly creates a shp data file by using the numerical information in the Excel table, such as the x value, y value, and attribute information used to represent the coordinate of a vertex, the geometric position of the midpoint in this file is the value specified in the Excel table, and relevant attribute information is added as required.
Specifically, the entire process involves importing an Excel data table, creating a shp data file, and adding data (including geometric data and attribute data ). The following describes how to generate a Shp data file based on an Excel table:

1. Import an Excel Data Table
There are two main ways to import an Excel data table. One is to open an Excel table in the form of an OleDb data source, and the other is to open the table by calling an Excel object in the form of an Application. Because the use of OleDb data sources is more universal, this method can not only open an Excel table, but also open an Access database, SQL database, etc. (the specific call method is slightly different ), the following describes the first method:
First, define the data source connection object in the global variable, as shown below:
OleDbConnection oledbcConnection; // Excel connection
Then, add a menu item or button to open the Excel data table and add the following code for its Click () event:
Try

 

{

 

OpenFileDialog openDG = new OpenFileDialog ();

 

OpenDG. Title = "open an Excel table ";

 

OpenDG. filter = "Excel table (*. xls) | *. xls | CSV format (*. csv) | *. csv | all files (*. *) | *. *";

 

OpenDG. ShowDialog ();

 

String filename;

 

Filename = openDG. FileName;

 

String strConn

 

= @ "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filename + "; Extended Properties = Excel 8.0 ";

 

OledbcConnection = new OleDbConnection (strConn );

 

OledbcConnection. Open ();

 

DataTable table = new DataTable ();

 

Table = oledbcConnection. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, "TABLE "});

 

ExcelBookComboBoxEx. Items. Clear ();

 

Foreach (DataRow dr in table. Rows)

 

{

 

ExcelBookComboBoxEx. Items. Add (String) dr ["TABLE_NAME"]);

 

}

 

ExcelBookComboBoxEx. Text = excelBookComboBoxEx. Items [0]. ToString ();

 

}

 

Catch (Exception exception)

 

{

 

MessageBox. Show (exception. Message );

 

}
Among them, excelBookComboBoxEx is a ComboBox component used to display the table name in the Excel file (an Excel file may contain many tables, and each table name is different, and the name of the first data table is not necessarily "Sheet1 "). To avoid an error because the table name does not exist in the Excel file, use a ComboBox component to name all the tables in the Excel file. The variable table stores information about all tables in the Excel table. Therefore, "dr [" TABLE_NAME "]" is used to obtain the corresponding table name in the row.
In the excelBookComboBoxEx control, select the Excel worksheet to be opened in the Excel table and use a database connection command to connect to the data source defined earlier to obtain the data in the worksheet. Add a button to open a worksheet in Excel and add the following code for its Click () event:

Try

 

{

 

String sheetName = excelBookComboBoxEx. Text;

 

String strCom = @ "SELECT * FROM [" + sheetName + "]";

 

OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, oledbcConnection );

 

DataSet myDataSet = new DataSet ();

 

MyCommand. Fill (myDataSet, "[" + sheetName + "]");

 

ExcelDataGridViewX. DataMember = "[" + sheetName + "]";

 

ExcelDataGridViewX. DataSource = myDataSet;

 

}

 

Catch (Exception exception)

 

{

 

MessageBox. Show (exception. Message );

 

}
In the above Code, excelDataGridViewX represents a data table control used to display data in an Excel table.

Ii. Create a shp Data File

Creating a Shp data file through an Excel table may involve creating a Shp data file. Here we will first introduce the process of creating a Shp data file. Create a new Form to create the Shp file interface, set the Form Name attribute to "CreateShpFile", and add the as shown in the figure on the interface (the red font indicates the Name of the Control ):

This section describes the main controls in the form. The filePathTextBox control is used to represent the file path, the saveFileButton control is used to represent the path browsing, And the fileNameTextBox control is used to represent the new file name, shpTypeComboBox indicates the new data type (such as Point, Polyline, and Polygon). The create button is used to create a new Shp File Based on the settings. The cancel button is used to cancel the creation of a new Shp file.

First, define three variables in the global variables, as shown in the following code:

Private AxMapControl axMapControl;

Private string filePath;

Private string fileName;

AxMapControl indicates the MapControl control (mainly used to add the newly created data file to the map), filePath indicates the path of the newly created data file, and fileName indicates the name of the newly created data file.

The constructor for changing the CreateShpFile form is as follows:

Public CreateShpFile (AxMapControl _ axMapControl)

{

AxMapControl = _ axMapControl;

InitializeComponent ();

}

In this way, the MapControl object must be specified when the CreateShpFile class is defined.

Add the following code for the Click () event of the Save file path button (saveFileButton:

Private void saveFileButton_Click (object sender, EventArgs e)

{

Try

{

SaveFileDialog saveDG = new SaveFileDialog ();

SaveDG. Title = "Creating a Shp file ";

SaveDG. Filter = "Shp file (*. shp) | *. shp ";

SaveDG. ShowDialog ();

String saveFilePath = saveDG. FileName;

Int I = saveFilePath. LastIndexOf (@"\");

Int length = saveFilePath. Length;

FilePath = Microsoft. VisualBasic. Strings. Left (saveFilePath, I + 1 );

FilePathTextBox. Text = filePath;

FileName = Microsoft. VisualBasic. Strings. Right (saveFilePath, length-I-1 );

FileNameTextBox. Text = fileName;

}

Catch (Exception e)

{

MessageBox. Show (e. Message );

}

}

In the above Code, the saved full path name saveFilePath identifies the file path and file name through the symbol. When you change the text content in the filePathTextBox control and fileNameTextBox control, you must change the global variables filePath and fileName accordingly. Therefore, add the following code for the TextChanged () event of the filePathTextBox control and fileNameTextBox control:

Private void filePathTextBox_TextChanged (object sender, EventArgs e)

{

FilePath = filePathTextBox. Text;

}

Private void fileNameTextBox_TextChanged (object sender, EventArgs e)

{

FileName = fileNameTextBox. Text;

}

You can create a new Shp data file through the IWorkspaceFactory interface and IFeatureWorkspace interface. The following code is added for the Click () event of the "Create" button:

Private void create _ Click (object sender, EventArgs e)

{

IWorkspaceFactory pShpWksFact = new ShapefileWorkspaceFactory ();

IFeatureWorkspace pFeatWks;

PFeatWks = (IFeatureWorkspace) pShpWksFact. OpenFromFile (filePath, 0 );

Const string strShapeFieldName = "Shape ";

// Define attribute Fields

IFields pFields;

IFieldsEdit pFieldsEdit;

PFields = new FieldsClass ();

PFieldsEdit = (IFieldsEdit) pFields;

IField pField = new FieldClass ();

IFieldEdit pFieldEdit = new FieldClass ();

PFieldEdit. Name_2 = strShapeFieldName;

PFieldEdit. Type_2 = esriFieldType. esriFieldTypeGeometry;

PField = (IField) pFieldEdit;

// Define Geometric Attributes

IGeometryDef pgomdef = new GeometryDefClass ();

IGeometryDefEdit pgomdefedit = new GeometryDefClass ();

Pgomdefedit = (IGeometryDefEdit) pgomdef;

Switch (shpTypeComboBox. Text)

{

Case "Point ":

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryPoint;

Break;

Case "Polyline ":

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryPolyline;

Break;

Case "Polygon ":

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryPolygon;

Break;

Case "MultiPoint ":

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryMultipoint;

Break;

Case "MultiPatch ":

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryMultiPatch;

Break;

}

Pgomdefedit. SpatialReference_2 = (ISpatialReference) new UnknownCoordinateSystem ();

PFieldEdit. GeometryDef_2 = pgomdef;

PFieldsEdit. AddField (pField );

PFields = (IFields) pFieldsEdit;

IFeatureClass pFeatureClass;

PFeatureClass = pFeatWks. CreateFeatureClass (fileName, pFields, null, null, esriFeatureType. esriFTSimple, strShapeFieldName ,"");

// Add new data to Map

AxMapControl. AddShapeFile (filePath, fileName );

This. Hide ();

}

Specifically, the process of creating a Shp file is divided into four steps: Step 1: Create an IWorkspaceFactory and IFeatureWorkspace workspace (based on the file path); Step 2: Define the attribute fields of the data, by default, an attribute field named "Shape" is created for it to represent its geometric Shape. The field format is esriFieldType. esriFieldTypeGeometry; Step 3: Define the geometric attributes. The Shp data type (point, line, and plane) has been specified before. In this case, define the GeometryType and specify the space reference system, the default value is UnknownCoordinateSystem. Step 4: Create a Shp data file in the IFeatureWorkspace workspace.

3. Add data

When adding geometric data, define the IFeature object as follows:

IFeature pFeature = pFeatureClass. CreateFeature ();

If you create a vertex data, define an IPoint object to represent the vertex and set the position of the vertex, as shown below:

IPoint pPoint = new PointClass ();

PPoint. PutCoords (pointX, pointY );

If it is line or surface data, their boundary consists of a series of points, which can be implemented using the IPointCollection interface.

Before adding attribute data, you must ensure that there are corresponding attribute fields. Because no other attribute fields are added to the new data, add the corresponding fields according to the specified data column in the Excel table. The IField interface is used to add attribute fields, which are defined as follows:

IField pfield = new FieldClass ();

Define an IFieldEdit object to modify or set the field information (Note: The IField object cannot be directly modified, and the attribute field must be modified through IFieldEdit), as defined below:

IFieldEdit pfieldEdit = new FieldClass ();

Then, modify the attributes of the IFieldEdit object as needed, as follows:

PfieldEdit. Name_2 = "new field ";

PfieldEdit. Type_2 = esriFieldType. esriFieldTypeString;

The Name_2 attribute of IFieldEdit indicates that the field Name is set (Note: it is not the Name attribute. The Name_2 attribute should be used to modify the attribute field Name ), the Type_2 attribute indicates the data type (such as String, Float, and Double) of the attribute field ).

You can modify or set an attribute value of a data (IFeature) directly through IFeature. the set_Value () method can be modified by defining an IField object. code modified by set_Value:

PFeature. set_Value (pFeature. Fields. FindField (fieldName), dataRow. Cells [fieldName]. Value. ToString ());

Create a new form, set its Name to CreateShpFileBaseOnExcel, and add the following control for it:

Most of the controls are similar to the newly created Shp file. In this section, the xComboBoxEx control is used to specify the X coordinate value of the generated Shp Midpoint data as the number of cells in the column of the Excel worksheet, similarly, the yComboBoxEx control is used to specify the Y coordinate value of the point data as the cell value of which column in the Excel worksheet. The fieldListBox control lists all columns in the Excel worksheet, to add the values of these columns to the attribute values of the corresponding vertex data, click the button to add the column to the addFieldListBox list control on the right. If you do not need to add this column value, you can also click the button to move the column from the addFieldListBox list control on the right to the fieldListBox list control on the left. Add the following code for the Click () events of the addFieldButtonX and delFieldButtonX buttons:

Private void delFieldButtonX_Click (object sender, EventArgs e)

{

If (addFieldListBox. SelectedItem! = Null)

{

FieldListBox. Items. Add (addFieldListBox. SelectedItem );

AddFieldListBox. Items. Remove (addFieldListBox. SelectedItem );

}

}

Private void addFieldButtonX_Click (object sender, EventArgs e)

{

If (fieldListBox. SelectedItem! = Null)

{

AddFieldListBox. Items. Add (fieldListBox. SelectedItem );

FieldListBox. Items. Remove (fieldListBox. SelectedItem );

}

}

Similarly, the following code defines global variables and modifies constructors:

Private DevComponents. DotNetBar. Controls. DataGridViewX excelDataGridViewX;

Private AxMapControl axMapControl;

Private string filePath;

Private string fileName;

Public CreateShpFileBaseOnExcel (AxMapControl _ axMapControl, DevComponents. DotNetBar. Controls. DataGridViewX _ excelDataGridViewX)

{

AxMapControl = _ axMapControl;

ExcelDataGridViewX = _ excelDataGridViewX;

InitializeComponent ();

}

Among them, the excelDataGridViewX variable is used to represent the data table obtained in the Excel worksheet. Next, you need to obtain the data (such as the X coordinate and Y coordinate of the point) directly from this variable.

In the Load () event of the form, add items for controls such as xComboBoxEx, yComboBoxEx, and fieldListBox. The following code:

Private void CreateShpFile_Load (object sender, EventArgs e)

{

For (int I = 0; I <excelDataGridViewX. Columns. Count; I ++)

{

String headerString = excelDataGridViewX. Columns [I]. HeaderText;

XComboBoxEx. Items. Add (headerString );

YComboBoxEx. Items. Add (headerString );

FieldListBox. Items. Add (headerString );

}

}

Create a Shp File Based on the Excel table. Add attribute fields, add geometric data, and add attribute data based on the new Shp file, as shown in the following code:

Private void create _ Click (object sender, EventArgs e)

{

Try

{

IWorkspaceFactory pShpWksFact = new ShapefileWorkspaceFactory ();

IFeatureWorkspace pFeatWks;

PFeatWks = (IFeatureWorkspace) pShpWksFact. OpenFromFile (filePath, 0 );

Const string strShapeFieldName = "Shape ";

// Define attribute Fields

IFields pFields;

IFieldsEdit pFieldsEdit;

PFields = new FieldsClass ();

PFieldsEdit = (IFieldsEdit) pFields;

IField pField = new FieldClass ();

IFieldEdit pFieldEdit = new FieldClass ();

PFieldEdit. Name_2 = strShapeFieldName;

PFieldEdit. Type_2 = esriFieldType. esriFieldTypeGeometry;

PField = (IField) pFieldEdit;

// Define Geometric Attributes

IGeometryDef pgomdef = new GeometryDefClass ();

IGeometryDefEdit pgomdefedit = new GeometryDefClass ();

Pgomdefedit = (IGeometryDefEdit) pgomdef;

Pgomdefedit. GeometryType_2 = esriGeometryType. esriGeometryPoint;

Pgomdefedit. SpatialReference_2 = (ISpatialReference) new UnknownCoordinateSystem ();

PFieldEdit. GeometryDef_2 = pgomdef;

PFieldsEdit. AddField (pField );

PFields = (IFields) pFieldsEdit;

IFeatureClass pFeatureClass;

PFeatureClass = pFeatWks. CreateFeatureClass (fileName, pFields, null, null, esriFeatureType. esriFTSimple, strShapeFieldName ,"");

// Add an attribute field group

For (int I = 0; I <addFieldListBox. Items. Count; I ++)

{

IField pfield = new FieldClass ();

IFieldEdit pfieldEdit = new FieldClass ();

PfieldEdit. Name_2 = addFieldListBox. Items [I]. ToString ();

PfieldEdit. Type_2 = esriFieldType. esriFieldTypeString;

Pfield = (IField) pfieldEdit;

PFeatureClass. AddField (pfield );

}

// Draw points

For (int I = 0; I <excelDataGridViewX. Rows. Count-1; I ++)

{

DataGridViewRow dataRow = excelDataGridViewX. Rows [I];

Double pointX, pointY;

PointX = double. Parse (dataRow. Cells [xComboBoxEx. Text]. Value. ToString ());

PointY = double. Parse (dataRow. Cells [yComboBoxEx. Text]. Value. ToString ());

IPoint pPoint = new PointClass ();

PPoint. PutCoords (pointX, pointY );

IFeature pFeature = pFeatureClass. CreateFeature ();

PFeature. Shape = pPoint;

// Add all attribute values for this Vertex

For (int j = 0; j <addFieldListBox. Items. Count; j ++)

{

String fieldName = addFieldListBox. Items [j]. ToString ();

PFeature. set_Value (pFeature. Fields. FindField (fieldName), dataRow. Cells [fieldName]. Value. ToString ());

}

PFeature. Store ();

}

// Add new data to Map

AxMapControl. AddShapeFile (filePath, fileName );

This. Hide ();

}

Catch (Exception exception)

{

MessageBox. Show ("data file creation error or attribute field error! \ N error when drawing point! There may be blank data rows or columns specified for X and Y as non-numbers! \ N"

+ Exception. Message );

This. Hide ();

}

}

Call this function in the main form and define a CreateShpFileBaseOnExcel object as follows:
CreateShpFileBaseOnExcel createShpFileBaseOnExcel = new CreateShpFileBaseOnExcel (axMapControl1, excelDataGridViewX );

CreateShpFileBaseOnExcel. ShowDialog ();

AxMapControl1 is the MapControl control in the main form, and excelDataGridViewX is the control used to display Excel worksheet in the main form.
Reprinted from http://bbs.esrichina-bj.cn/ESRI/thread-54689-1-1.html