C# Excel connection component tray builder
Introduction
This article presents a component tray class that assist in creating OleDb connection string to connect to Excel files. Since connection to Excel using OleDb is a good deal more complex than connecting to most databases.
Anatomy of an Excel connection string
Provider: for Excel 97 (.xls) format the provider is Microsoft.Jet.OLEDB.4.0 while Excel 2007 and higher (.xlsx) format the provider is Microsoft.ACE.OLEDB.12.0.
Data Source: This is the full path and Excel file name to open.
Extended Properties:
(optional)
HDR: The default is ‘true’ which treats the first row in each WorkSheet as column names. If your sheets don’t have column names then HDR will be ‘false’.
IMEX: This refers to IMport EXport mode. There are three possible values, 0 and 2 will result in ImportMixedTypes (where values are obtained from the system registry) which work in tangent with MaxScanRows (Excel does not provide the detailed schema definition of the tables it finds. It need to scan the rows before deciding the data types of the fields.)
Caveat: In regards to ImportMixedTypes settings in the system registry, best to avoid changing this setting unless this is for personal usage as on a customer computer they may have policies preventing reading entries in the registry which means you can’t give a registry file to modify the registry nor can you write code as it will fail with access denied.
Other extended properties, ReadOnly, and FirstRowHasNames (same as HDR) are not covered here.
Component tray
Using this component solves issues of creating a proper connection string that when done by manually writing the connection string something is out of place or missing which in turn throws a runtime exception.
Setup
- Build the class project found under the header “Source Code”. To test the component, create a new Windows Forms project, add a reference to the component class project which was just built.
- Open the default form, from the IDE toolbox find the component at the top of the toolbox, double-click and the component is placed in the area just below the form.
- Single click the component, select properties followed by selecting the button for FileName property.
- Pressing this button invokes an OpenDialog. Traverse to where you have an Excel file, select the file and press OK.
- Now select Headers property, the default value is YES, this is for HDR indicating the first row contains column names. If the first row is data select NO.
- The Provider property before selecting a file is XLS. If you selected an Excel 2007 file the value automatically changes to XLSX.
- Mode value defaults to AsText which is the most common value. If you have issues opening your file try one of the other values.
- Both FileExists and OleDbConnectionString are read-only and are empty if FileName property is not set. If you set the FileName property then remove it FileExists is false and OledbConnection string is an empty string.
Using
A simple example, place a ListBox and a Button onto the form. In form Shown event (or load event) add the following code.
private void Form1_Shown(object sender, EventArgs e)
{
listBox1.DataSource = excelHelper1.SheetNames();
}
Build and run the project. You should see sheet names in the ListBox. Stop the application.
Now add the following code for the button.
private void button1_Click(object sender, EventArgs e)
{
if (excelHelper1.FileExists && excelHelper1.SheetExists(listBox1.Text))
{
var dt = new DataTable();
using (var cn = new OleDbConnection() { ConnectionString = excelHelper1.ConnectionString() })
{
using (var cmd = new OleDbCommand() { Connection = cn })
{
cmd.CommandText = $"SELECT * FROM [{listBox1.Text}]";
cn.Open();
dt.Load(cmd.ExecuteReader());
}
}
}
The selected sheet name in the ListBox will be loaded into a DataTable. Note there is no try/catch, we want to ensure the connection string generated by the component works, feel free to add a try/catch. Once you have validated this works move forward by changing SELECT * to SELECT F1, F2 or SELECT F1 As SomeFieldName, F2 As AnotherFieldName etc. where HDR is NO.
If there are issues, the first thing to change is IMEX value and try again. In the majority of cases, this resolves any issues.
What the component does not assist with is the actual loading of data, only building the connection string. Excel can be troublesome in regards to reading sheet data especially when columns have mixed data types.
Summary
This article has provided a non-visual component for building connection strings to Excel where once the connection string has been validated you can copy the connection string from the property OleDbConnection string and use it in a form or data class to work with Excel.