By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,369 Members | 1,138 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,369 IT Pros & Developers. It's quick & easy.

C# Problem reading numbers from excel file

P: n/a
I'm trying to read rows from an Excel file and display them in an
ASP.NET DataGridview. I am using C# for the code file. I am using
OleDb to read from the Excel file. The columns that contain text load
into the grid fine, but the columns that contain just numbers don't
show up at all. I tried converting the text of the cells to an
integer first, but I get an error for converting from a type DBNull.
Anybody who has any help at all, I would greatly appreciate it. Here
is the code I am using to read from the Excel and load the
Datagridview:

private void popGrid(String filename)
{
String sConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new
OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM
[Sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet dset = new DataSet();

objAdapter1.Fill(dset);

objConn.Close();

DataSet inSet = new DataSet();
DataTable inTable = new DataTable();
inTable.Columns.Add("BLnum");
inTable.Columns.Add("Pieces");
inTable.Columns.Add("Weight");
inTable.Columns.Add("Size");
inTable.Columns.Add("custRel");
inTable.Columns.Add("ssRel");
inTable.Columns.Add("palletEx");
inTable.Columns.Add("pckDate");
inTable.Columns.Add("ldFee");

for (int i = 11; i < dset.Tables[0].Rows.Count; i++)
{

DataRow inRow = inTable.NewRow();
DataRow row = dset.Tables[0].Rows[i];
if (row[0].ToString() != "")
{
inRow["BLnum"] = row[0].ToString();//A
inRow["Pieces"] = row[2].ToString();//C
inRow["Weight"] = row[3].ToString();//D
inRow["Size"] = row[4].ToString();//E
inRow["custRel"] = row[5].ToString();//F
inRow["ssRel"] = row[6].ToString();//G
inRow["palletEx"] = row[6].ToString();//H
inRow["pckDate"] = row[8].ToString();//I
inRow["ldFee"] = row[10].ToString();//K
inTable.Rows.Add(inRow);
}
}
inSet.Tables.Add(inTable);

loadGrid.DataSource = inSet;
loadGrid.DataBind();

}

May 1 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Are you sure you are looking at the right column? You are getting a
DBNull, which when coming from Excel, more than likely means that the cell
was blank/empty.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

<rw*****@iastate.eduwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
I'm trying to read rows from an Excel file and display them in an
ASP.NET DataGridview. I am using C# for the code file. I am using
OleDb to read from the Excel file. The columns that contain text load
into the grid fine, but the columns that contain just numbers don't
show up at all. I tried converting the text of the cells to an
integer first, but I get an error for converting from a type DBNull.
Anybody who has any help at all, I would greatly appreciate it. Here
is the code I am using to read from the Excel and load the
Datagridview:

private void popGrid(String filename)
{
String sConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new
OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM
[Sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet dset = new DataSet();

objAdapter1.Fill(dset);

objConn.Close();

DataSet inSet = new DataSet();
DataTable inTable = new DataTable();
inTable.Columns.Add("BLnum");
inTable.Columns.Add("Pieces");
inTable.Columns.Add("Weight");
inTable.Columns.Add("Size");
inTable.Columns.Add("custRel");
inTable.Columns.Add("ssRel");
inTable.Columns.Add("palletEx");
inTable.Columns.Add("pckDate");
inTable.Columns.Add("ldFee");

for (int i = 11; i < dset.Tables[0].Rows.Count; i++)
{

DataRow inRow = inTable.NewRow();
DataRow row = dset.Tables[0].Rows[i];
if (row[0].ToString() != "")
{
inRow["BLnum"] = row[0].ToString();//A
inRow["Pieces"] = row[2].ToString();//C
inRow["Weight"] = row[3].ToString();//D
inRow["Size"] = row[4].ToString();//E
inRow["custRel"] = row[5].ToString();//F
inRow["ssRel"] = row[6].ToString();//G
inRow["palletEx"] = row[6].ToString();//H
inRow["pckDate"] = row[8].ToString();//I
inRow["ldFee"] = row[10].ToString();//K
inTable.Rows.Add(inRow);
}
}
inSet.Tables.Add(inTable);

loadGrid.DataSource = inSet;
loadGrid.DataBind();

}

May 1 '07 #2

P: n/a
Thanks for the response. I'm sure I'm looking in the correct column,
because when I change the value in excel column to have any non-
numerical characters it displays the column properly. Any other
ideas??

May 1 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.