ASP PROGRAMMING REFERENCE

Excel Reporting with C# ASP.NET Data Reader

Online reporting is important to keeping in touch with the business finances and inventory. The code below demonstrates the syntax required to produce an Excel Report with ASP.NET/C# in 6 steps.

1. Begin by creating your database connection.
2. Create a valid SQL Statement
3. Open the data reader and have it loop through the records until end of file.
4. Create a table structure using HTML to format the report layout.
5. Close the Reader
6. Close the Database Connection

private void Page_Load(Object sender, EventArgs E) {

//Create a connection to the database

string connstr = ConfigurationSettings.AppSettings["strDSN"];
OleDbConnection con = new OleDbConnection(connstr);
con.Open();

string ssql="Select fname, middleInitial, lname FROM tableName";

//Build command
OleDbCommand cmd = new OleDbCommand(ssql, con);

//Create a data reader which will read each record from the select
//command in a forward-only, read-only mode
OleDbDataReader rdr = cmd.ExecuteReader();

// Report Formating for Excel if format not Null
if (Request.QueryString["format"]!=null){
  Response.Clear();
  Response.ContentType= "application/x-msexcel";
  Response.AddHeader("Content-Disposition", "inline; filename=ReportName.xls");
}

//Format Table Header

Response.Write("<link href=images/report.css rel=stylesheet type=text/css><table border=1 cellpadding=1 cellspacing=0><tr><td colspan=24>Report Title</td></tr><tr><td>First Name</td><td>Middle Initial</td><td>Last Name</td></tr>");

while(rdr.Read())
{
  Response.Write("<tr><td>"+rdr["fname"]+"&nbsp;</td>");
  Response.Write("<td>"+rdr["middleInitial"]+"</td>");
  Response.Write("<td>"+rdr["lname"]+"</td>");
  Response.Write("<td>"+rdr["ss"]+"</td>");
  Response.Write("<td>"+rdr["DriversLicense"]+"</td>");
  Response.Write("<td>"+rdr["StreetAddress"]+"<br>"+rdr["StreetAddress2"]+"</td>");
  Response.Write("<td>"+rdr["city"]+"</td>");
  Response.Write("<td>"+rdr["state"]+"</td>");
  Response.Write("<td>"+rdr["zipcode"]+"</td>");
  Response.Write("<td>"+rdr["email"]+"</td>");
  Response.Write("<td>"+rdr["workphone"]+"</td>");
  Response.Write("<td>"+rdr["maritalstatus"]+"</td>");
  Response.Write("<td>"+rdr["dependents"]+"</td>");
  Response.Write("<td>"+rdr["employementStatus"]+"</td>");
  Response.Write("<td>"+rdr["totalValueJewelry"]+"</td>");
  Response.Write("<td>"+rdr["totalMonthlyIncome"]+"</td>");
  Response.Write("<td>"+rdr["totalMonthlyExpense"]+"</td>");
  Response.Write("<td>"+rdr["LoanYouOwe"]+"</td>");
  Response.Write("<td>"+rdr["AmountRequested"]+"</td>");
  Response.Write("<td>"+rdr["NeedForPurpose"]+"</td>");
  Response.Write("<td>"+rdr["circumstance"]+"</td>");
  Response.Write("<td>"+rdr["ReceivedZakatBefore"]+"</td>");
  Response.Write("<td>"+rdr["DateZakatReceived"]+"</td>");
  Response.Write("<td>"+rdr["OraganizationNameThatProvidedZakat"]+"</td></tr>");
}

Response.Write("</table>");

// Close Reader
rdr.Close();

// Close Database Connection
con.Close();

}

// Happy ASP Programming

// Return to the Main ASP Programming Reference Page