G
Guest
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?
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?