Wednesday, September 16, 2009

Reading Excel files in C#

When importing any kind of data into an application simply using a text file is sometimes not sufficient, especially if the users already have information in an Excel file.

Using an Excel file in C# is like connecting to a database. You provide a connection string (which, among other things, include the file path) and use SQL statements to read the file. The following snippet of code connects an Excel file to a DataGridView form component.

OleDbConnection connection = new OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=foo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";);


try
{
  connection.Open();


  OleDbCommand cmd = connection.CreateCommand();
  cmd.CommandText = "SELECT * FROM [Sheet1$]";


  DataSet dataSet = new DataSet();
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);


  dataAdapter.Fill(dataSet, "ImportData");
  dataGridView_Sheet1.DataSource = dataSet.Tables["ImportData"].DefaultView;
}

catch { }
finally
{
  if (connection != null)
  {
    connection.Close();
    connection.Dispose();
  }
}

The SQL-statement includes the name of the desired sheet in the Excel file. Do note that the sheet name is fully editable by the user, and the default name is language dependant. Reading all sheets in an Excel file is fairly simple:

public string[] GetExcelSheetNames()
{
  OleDbConnection objConn = null;
  System.Data.DataTable dt = null;

  try
  {
    objConn = new OleDbConnection(ExcelConnectionString);
    objConn.Open();
  
    // Get the data table containg the schema guid.
    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  
    if (dt == null)
    {
      return null;
    }
  
    string[] excelSheets = new String[dt.Rows.Count];
    int i = 0;
  
    foreach (System.Data.DataRow row in dt.Rows)
    {
      excelSheets[i] = row["TABLE_NAME"].ToString();
      i++;
    }
  
    return excelSheets;
  }
  catch
  {
    return null;
  }
  finally
  {
    // Clean up.
    if (objConn != null)
    {
      objConn.Close();
      objConn.Dispose();
    }
    if (dt != null)
    {
      dt.Dispose();
    }
  }
}



The OleDbConnection object takes a connection string as parameter in the constructor. The format of the parameter can found at http://www.connectionstrings.com/. The connection string in the example above is suitable for Excel files with the xls file extension. The correct connection string for Excel 2007 files (with e.g. the xlsx file extension can be found at the Connection String web site.

Two important things are worth mentioning about the connection string. The HDR parameter indicates that the first row in the Excel file shall be treated as column headers. If the first row is not used to describe the data, simply set HDR=No. The IMEX parameter makes all columns be treated as strings, if the parameter is set to No then is the column’s data type based on the first row with data (which is the second row in the file if HDR is set to Yes). If a column in the file has mixed data with integers and characters then should this parameter be set to Yes. This little parameter has caused me a lot of problems…

If the first row in the data has only numeric then is the data type set to e.g. double. But no exception is thrown when a row’s column has a mixture of numeric and characters, the data is instead set to null.

Sunday, August 30, 2009

How to listen for KeyPress events in a DataGridView

When listening to KeyPress events in a DataGridView, you will only listen to events when the actual DataGridView control has focus, and not when you are typing text in a column (e.g. DataGridViewTextBoxColumn) in the grid.
If you want to listen to key events (or any event) in the column, you need to first listen to an event that will fire when the DataGridViewTextBoxColumn control is entering editing mode.

FooDataGridView += DataGridViewEditingControlShowingEventHandler(FooDataGridView_EditingControlShowing);

Then you will be able to listen for the KeyPress event:

private TextBox areaValueTextBox = null;

void FooDataGridView_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
    if (e.Control is TextBox)
    {
        if (areaValueTextBox == null)
        {
            areaValueTextBox = e.Control as TextBox;
            areaValueTextBox.KeyPress += new KeyPressEventHandler(areaValueTextBox_KeyPress);
        }
        else
            areaValueTextBox = e.Control as TextBox;
    }
}

void areaValueTextBox_KeyPress(object sender, KeyPressEventArgs e)
{
    System.Diagnostics.Debug.WriteLine("==> " + areaValueTextBox.Text + e.KeyChar.ToString());
}
If we didn't create a TextBox object, and instead created a TextBox each time we entered the method, then would we create a new event handler each time, and every event handled would be called, not just newly created. One problem though, is that the KeyPress event will not be fired when tab or return is pressed. One way to solve this problem is to listen to the cell's begin and end edit events. The CellBeginEdit event will be fired when the user start to edit the cell, e.g. by pressing F2, and the CellEndEdit is fired when user hits tab or return.

FooDataGridView.CellBeginEdit += new DataGridViewCellCancelEventHandler(FooDataGridView_CellBeginEdit);
FooDataGridView.CellEndEdit += new DataGridViewCellEventHandlerFooDataGridView_CellEndEdit);
void FooDataGridView_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
    System.Diagnostics.Debug.WriteLine("Start edit...");
}
void FooDataGridView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    System.Diagnostics.Debug.WriteLine("...end edit.");
}

Initialize NHibernate with cascade

The Hibernate lazy associations is a wonderful feature which minimize the database communication when loading (large) sets of data.
But sometimes you need to load the full dataset, and you don't want to set the lazy flag in the mapping files to false. The solution is to use the static method NHibernate.NHibernateUtil.Initialize(), but the problem is that this method doesn't cascade to associated objects, you only initialize the current object, and no underlying collection. The following is a snippet of code that initialize all collections, and their collections, and so on. 

protected void InitializeWithCascade(object rootObject)
{
  PropertyInfo[] propInfos = rootObject.GetType().GetProperties();

  foreach (PropertyInfo property in propInfos)
  {
   MethodInfo mi = property.GetGetMethod();
   System.Collections.ICollection collection = mi.Invoke(rootObject, null) as System.Collections.ICollection;

   if (collection != null)
   {
    // This is a collection
    System.Collections.IEnumerator iter = collection.GetEnumerator();
    while (iter.MoveNext())
    {
     // Initialize the collection
     NHibernate.NHibernateUtil.Initialize(iter.Current);

     // Iterate the set
     InitializeWithCascade(iter.Current);
    }
   }
  }
}

Saturday, August 29, 2009

Linker problems with Borland Builder

When building a project in Borland Builder (version 5) you can get a linker error such as:

[Linker Error] Fatal: Error detected (LME351)
[Linker Warning] Cannot reserve virtual memory at addr 461A0000 for -1703936 bytes (errcode 87)

This is a known bug in Borland Builder, and the reason is that the debug information file is too big. Every project with forms create a debug file (a *.tds file), when the size of this file exceeds roughly 32 MB the linker throws an error. The solution is to disable the debugging ability of forms with many controls:

  • Right-click a form in the "Project Manager", select "Edit local options..." and un-check the "Debug information" checkbox.

  • Delete the project's *.tds file and the vcl50*-files in the Borland installation directory (e.g. C:\Program Files\Borland\CBuilder5\Lib\vcl50.*).

  • Re-compile all files you have changed debug settings on.