C# Adventures in Excel

rahvyn · May 19th, 2007, 11:33 AM

Hi All;

I'm working on a simple desktop application that does the following:

1 - Reads in an Excel spreadsheet into a DataGridView as follows:

try
            {
                string strConnectionString = "";
                if (header)
                {
                    strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                 "Data Source=" + strFilePath + ";Jet OLEDB:Engine Type=5;" +
                                 "Extended Properties=\"Excel 8.0;HDR=Yes\"";
                }
                else
                {
                    strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                 "Data Source=" + strFilePath + ";Jet OLEDB:Engine Type=5;" +
                                 "Extended Properties=\"Excel 8.0;HDR=No\"";
                }
                OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
                cnCSV.Open();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
                OleDbDataAdapter daCSV = new OleDbDataAdapter();
                daCSV.SelectCommand = cmdSelect;
                dtCSV = new DataTable("Batch");
                daCSV.Fill(dtCSV);
                cnCSV.Close();
                daCSV = null;
                return dtCSV;

            }

2 - allows the user to add columns, delete columns, and re-order columns.
3 - I then parse through the grid verifying the values in the cells match a specified length, type, etc.
4 - output the grid values to a csv file.

Everything is pretty much working except for parsing the values, and outputing the csv. After the columns are re-ordered, it seems like they retain their original index. So when I parse through like this:

for (int r = 0; r <= dgvMain.Rows.Count - 2; r++)
{
    for (int c = 0; c <= dgvMain.Columns.Count; c++)
    {
        value = dgvMain.Rows[r].Cells[c].Value.ToString();
    }
}
The variable value contains the value of the original cell. In otherwords, say I have 2 columns, 0 and 1. I then switch their positions, so now have 1, 0. If I parse through as above, I will still be looking at them as 0, 1, not 1, 0. Or, if I move column 10 to position 1, and read that, I wont read the value until c = 10, but I need to read it when c = 1. What I need to do is to reset the indexes of the columns to be in the order that they are displayed. Has anyone done anything like this before?

rahvyn · May 19th, 2007, 02:22 PM

Got a solution if anyone is interested:

In order to do this, you will need to call the GetFirstColumn method on
the Columns collection, as this method will take display order into account
(while the iterator will not). You need to follow that up with a call to
GetNextColumn using the previous column. This makes it perfect for an
iterator:

Code:

private static IEnumerable<DataGridViewColumn>GetDisplayOrderEnumeration(DataGridViewColumnCollection columns)
{
    // Get the first column.
    DataGridViewColumn column = columns.GetFirstColumn(DataGridViewElementStates.None);

    // Continue while there is a column.
    while (column != null)
    {
        // Yield the column.
        yield return column;

        // Get the next column.
        column = columns.GetNextColumn(column,DataGridViewElementStates.None, DataGridViewElementStates.None);
    }
}

The yield keyword will help create an IEnumerable implementation which
you can use a foreach statement to cycle though. You can use it to get the
name of the column to get the value of from the row:

Code:

for (int r = 0; r <= dgvMain.Rows.Count - 2; r++)
{
    foreach (DataGridViewColumn c in GetDisplayOrderEnumeration(dgvMain.Columns))
    {
        value = dgvMain.Rows[r].Cells[c.Name].Value.ToString();
    }
}

The method I gave you will give you an enumeration you can cycle through
to get the rows in the display order, which you can then use to access the
values in the same order in the underlying data source.

Thanks to Nicholas over at the msdn forums.

robbyking · May 22nd, 2007, 12:21 PM

Thanks for posting your solution!