CODEWITHSUNDEEP
c#asp.netexcelwebmatrix
Anup Singh
Anup Singh

Reputation: 323

render excel data to web page row by row (C#)

Can someone help me print data row by row using WebMatrix 2

i am trying to use excel 2007 and/or 2003 the last line DataGrid1 is not working. this is my third day trying, please help.

String Excel = "C://TEST/test_2.xls";
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";

string.System.Data.OleDb.OleDbConnection objConn = new`system.Data.OleDb.OleDbConnection(sConnectionString);`


objConn.Open();
System.Data.OleDb.OleDbCommand objCmdSelect = new System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

System.Data.OleDb.OleDbDataAdapter objAdapter1 = new System.Data.OleDb.OleDbDataAdapter(objCmdSelect);

System.Data.DataSet objDataset1 = new System.Data.DataSet();

objAdapter1.Fill(objDataset1, "XLData");
objConn.Close();
int c = objDataset1.Tables[0].Columns.Count;
int count =0;

//For Test
Response.Write("Colums: "+objDataset1.Tables[0].Columns.Count.ToString()+"<br>");
Response.Write("Rows: "+objDataset1.Tables[0].Rows.Count.ToString()+"<br>");


// Bind data to DataGrid control. Following part not working, also i like to print data with for or foreach loop)

DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();
//Other option if Data.Grid does not work!!
change Rows to Columns to print Columns.
for (int i=0;i<r;i++){
for (int j=0;j<c;j++){
Response.Write(objDataset1.Tables[0].Rows[i].ItemArray[j]+" ");
}
Response.Write("<br>");
}

Upvotes: 0

Views: 612

Answers (1)

Sid Holland
Sid Holland

Reputation: 2921

Well aside from the various syntax errors in your code, you've created a new OleDbDataAdapter but not attached it to anything. I suspect that your program is throwing an exception during the Fill statement because it doesn't have an SQL command to execute. You need to edit your instantiation of the OleDataDbAdapter object to:

System.Data.OleDb.OleDbDataAdapter objAdapter1 = new System.Data.OleDb.OleDbDataAdapter(objCmdSelect);

Also, as DJ KRAZE correctly pointed out, adding the following lines to the top of your code will help you clean things up a bit and make it a little easier to understand:

using System.Data;
using System.Data.OleDb;

I'm not sure what your comment about "printing" data with a loop means, but binding the data to the DataGrid means you don't have to.

EDIT:

Here's how you can improve your loop

foreach(DataRow row in objDataset1.Tables[0].Rows)
{
    foreach(DataColumn col in objDataset1.Tables[0].Columns)
    {
        Response.Write(row[col.ColumnName].ToString());
    }

    Response.Write(count++ + "<br>");
}

Upvotes: 1

Related Questions