Adventures in Excel

[=?Utf-8?B?UmFodnlu?=]

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. (I
dont update the underlying datatable as if they add a column, it is unbound.
But not sure if this is correct)
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?

[Nicholas Paldino [.NET/C# MVP]]

Rahvyn,

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:

private static IEnumerable<DataGridViewColumn>
GetDisplayOrderEnumeration(DataGridViewColumnColle ction columns)
{
// Get the first column.
DataGridViewColumn column =
columns.GetFirstColumn(DataGridViewElementStates.N one);

// 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);
}
}
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahvyn" <Ra****@discussions.microsoft.comwrote in message
news:D1**********************************@microsof t.com...

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.
(I
dont update the underlying datatable as if they add a column, it is
unbound.
But not sure if this is correct)
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?

[=?Utf-8?B?UmFodnlu?=]

Thanks Nicholas. I am unfamiliar with the yield keyword. What does this
return?

[=?Utf-8?B?UmFodnlu?=]

Also, how would I use something like this as I loop through the rows /
columns to get the values?

"Rahvyn" wrote:

Thanks Nicholas. I am unfamiliar with the yield keyword. What does this
return?

[Nicholas Paldino [.NET/C# MVP]]

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:

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.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahvyn" <Ra****@discussions.microsoft.comwrote in message
news:86**********************************@microsof t.com...

Also, how would I use something like this as I loop through the rows /
columns to get the values?

"Rahvyn" wrote:

>Thanks Nicholas. I am unfamiliar with the yield keyword. What does this
return?

[=?Utf-8?B?UmFodnlu?=]

Thank you Nicholas, that works perfectly, very elegant solution.

"Nicholas Paldino [.NET/C# MVP]" wrote:

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:

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.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahvyn" <Ra****@discussions.microsoft.comwrote in message
news:86**********************************@microsof t.com...

Also, how would I use something like this as I loop through the rows /
columns to get the values?

"Rahvyn" wrote:

Thanks Nicholas. I am unfamiliar with the yield keyword. What does this
return?