ColaReport - Demo 4
 Home
 FAQ
 Examples
 Documentation
 Download
 Feedback
 Support 		  Demo 4       demo4.aspx.cs Complete Code       Return to Examples

This example demonstrates how to use pre-group labels and output to Excel.

Sample report output:

Demo: demo4.aspx

Invoice DateInvoice NumberInvoice Total
 Customer: ABC Computers       
 1/1/2003   1   $1,000.00 
 1/1/2003   2   $700.00 
 1/2/2003   3   $500.00 
 ABC Computers Subtotal      $2,200.00 
        
 Customer: Joe's Electronics           
 1/2/2003   4   $600.00 
 1/2/2003   5   $300.00 
 Joe's Electronics Subtotal        $900.00 
        
 Total      $3,100.00 
Send To Excel


The code for Page_Load appears below. 

	private void Page_Load(object sender, System.EventArgs e)
	{
		Report oReport;
		CellColumn oCol;
		Group oGroup;
		DataSet dsRpt;
		string sQuery;

		OleDbConnection oConn;
		OleDbDataAdapter daRpt;

		// Get connection string
		oConn = new OleDbConnection (ConfigurationSettings.AppSettings["gsConn"]);

We order by CustomerName first, since we want to group the data by CustomerName. 

		
		//We later create a "group" on CustomerName.
		//It is therefore important to order by CustomerName here.
		sQuery = "SELECT c.CustomerName, i.InvoiceDate, i.InvoiceId, i.InvoiceTotal" +
			" FROM Customer c, Invoice i" +
			" WHERE c.customerid = i.customerid" +
			" ORDER BY c.CustomerName, i.InvoiceDate, i.InvoiceId";

		daRpt = new OleDbDataAdapter (sQuery, oConn);
		dsRpt = new DataSet();
		daRpt.Fill(dsRpt);

		// Define Report 
		oReport = new Report ();

We test to see if the user clicked on the link (generated later) that tells us to output to Excel. If so, we set the content type to tell the user's browser to launch Excel. We also set the number of lines to 0, telling Report not to bother with page breaks. 


		// See if we should output to Excel
		if (Request["toExcel"] == "1")
		{ 
			// Change content type, removing page breaks
			Response.ContentType = "application/vnd.ms-excel";
			Response.Charset = "";
			this.EnableViewState = false;
			
			// IMPORTANT! Turn off padding for Excel numeric data
			oReport.PadCellColumns = false;
							
			oReport.LinesPerPage = 0;
			oReport.LinesFirstPage = 0;
		} 
		else
		{
			oReport.LinesFirstPage = 33;
			oReport.LinesPerPage = 33;
		}

Do some formatting, and create columns for the report. 



		oReport.Style.Add ("font-family", "Verdana");
		oReport.HeaderRowAttributes.Attributes["bgcolor"] = "#efefdf";

		oReport.FirstPageHeader.Text = "<h2<Demo: demo4.aspx>/h2>";
		oReport.FirstPageHeader.Style.Add ("font-family", "Verdana");
		oReport.PageHeader.Text = "<h3<Demo: demo4.aspx>/h3>";
		oReport.PageHeader.Style.Add ("font-family", "Verdana");
	
		oCol = oReport.CreateCellColumn ("InvoiceDate", "Invoice Date"); 
		oCol.StringFormat = "{0:d}";  //date format

		oCol = oReport.CreateCellColumn ("InvoiceId", "Invoice Number"); 
		oCol.Attributes["align"] = "right";

		oCol = oReport.CreateCellColumn ("InvoiceTotal", "Invoice Total"); 
		oCol.SumColumn = true; //display totals for this column
		oCol.Attributes["align"] = "right";
		oCol.StringFormat = "{0:c}";  //currency format

Create a group for the grand totals. Note that the StringFormat is set to "" for InvoiceDate. That is because when Cells is created, each GroupCell inherits attribute information from its corresponding ColumnCell, to make it easier on the developer in most cases. It was set to "{0:d}", but that inhibits the Text value from being printed. 

		// Create a group for the grand totals
		oGroup = oReport.CreateTotalGroup (true);
		oGroup.Cells["InvoiceDate"].Text = "Total";	
		oGroup.Cells["InvoiceDate"].StringFormat = ""; //reset; it inherited "{0:d}"
		oGroup.Cells["InvoiceDate"].Style.Add("font-weight", "bold");
		oGroup.Cells["InvoiceTotal"].Style.Add("font-weight", "bold");
		oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,200);
		oGroup.BlankLineAfterGroup = false;

We want to group the data by CustomerName. Note that we did not create a CellColumn for CustomerName earlier. We can still group data by it, though. 

		// Group data by CustomerName
		oGroup = oReport.CreateGroup ("CustomerName", true);

		// Note, there is no CellColumn defined for CustomerName.
		// We put the Customer in the InvoiceDate column for the group.
		oGroup.Cells["InvoiceDate"].StringFormat = "{0:g} Subtotal";
		oGroup.Cells["InvoiceDate"].Style.Add("font-weight", "bold");

Specify that we want to print out a line whenever we begin a new group (when we encounter a new CustomerName value). We indicate that the CustomerName value should be displayed in the InvoiceDate column. 


		// Indicate that we want a line before the group begins
		oGroup.PreGroupLabel = true;
		oGroup.PreGroupRow.Attributes["bgcolor"] = "#ccffff";
		oGroup.PreGroupRow.Cells["InvoiceDate"].StringFormat = "<b>Customer: {0:g}</b>";

		oGroup.Attributes["bgcolor"] = "#efefef";
		//oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,249);

Bind and output the report. 


		//Bind dataset
		oReport.Bind (dsRpt);

		//Set label text to report output
		lblReport.Text = oReport.Execute();

We add a link at the bottom of the report that links back to the same page, but with "toExcel" passed in. If the user clicks on it, the same data will be obtained, but the content type will set to indicate to the browser to launch Excel. 

		// If we did not output to Excel, output a link allowing that option.
		if (!(Request["toExcel"] == "1"))
		{
			lblReport.Text += "<a href='" + Request.RawUrl + "?toExcel=1'>Send To Excel</a>";
		}
	}
Copyright © 2003