The steps i have explained here are specific to one of the project requirements that i have worked on.
Summary
Following is a rough summary of what i have done below
- Create a crystal report
- Determine the data retrieval mechanism
- Create a schema that has two tables, one to carry report headers other to carry report data.
- Create the report layout based on the two tables added above.
- Databind the report from the data returned by the data retrieval mechanism.
- Set the report headers.
- Generate a report object out of the data bound report.
- Export the report document to a memory stream with the export format type excel (pdf, doc or anything).
- Write the stream to the response.
Requirements :
- It should allow the data binding of the report data from the form code-behind.
- The report headers should be able to be localized to the user locale.
- Users should be able to specify the report title.
To be able to edit report column headers and the title dynamically, we are using a dataset which contain two tables. The first table contains the actual data that is to be databinded with the report. The second table contains the report headers and title. Obviously there will be only one row in the second table.
Preconditions :
You need to have a report file in place and have its layout defined using two tables that we've discussed above.
I've used LINQ data contract called GetCatalogItemsForReportResult to get the results from the persistent storage. Basically this is a contract for a stored procedure that i have written to retrieve the dataset.
protected void ExportToExcel_OnClick(object sender, EventArgs e)
{
//populate the report document.
ReportDocument rptDocCatalogPriceList = getPopulatedReportDocument();
MemoryStream oStream; // using System.IO
oStream = (MemoryStream)rptDocCatalogPriceList.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
Response.Clear();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", "attachment; filename=" + ddlSupplier.SelectedValue + "_" + ddlCatalogs.SelectedValue + ".xls");
Response.ContentType = "application/xls";
Response.BinaryWrite(oStream.ToArray());
Response.End();
}
///
/// 1. get a report document
/// 2. load it with the report
/// 3. populate the report with data
/// 4. retur the populated report doc
///
/// A populated
private ReportDocument getPopulatedReportDocument(string reportSourcePath)
{
//the report document path
string reportPath = Server.MapPath(reportSourcePath);
ReportDocument rptDocCatalogPriceList = new ReportDocument();
//load the report from the report path
rptDocCatalogPriceList.Load(reportPath);
//assign the report with the data source
rptDocCatalogPriceList.SetDataSource(getPopulatedDataSet());
return rptDocCatalogPriceList;
}
private DataSet getPopulatedDataSet()
{
DataSet ds = new DataSet();
populateDTCatalogItemReportCaptions(ds);
populateDTCatalogItems(ds);
return ds;
}
///
/// assign the dataset with a datatable that is carrying the table headers and report header
/// we can assign any new field to the table that needs to be displayed in the report
/// but make sure not to change the order of the table fields which could cause incorrect display of
/// columns.
///
///
private void populateDTCatalogItemReportCaptions(DataSet dsCarrier)
{
TranslationService ts = getTranslationSvc();
//the table that carries report data
ReportCaptions.DTCatalogItemReportCaptionsDataTable dtCatItemCaptions = new ReportCaptions.DTCatalogItemReportCaptionsDataTable();
DataRow dr = dtCatItemCaptions.NewRow();
int noOfColumns = dr.Table.Columns.Count;
//the very first row is to carry the report header so we eliminate that and start from the second column
for (int i = 1; i in the translation table
//get the appropriate value and set it for the column header
//what i have done here is retrieving the column headers from a seperate service that is focused //on getting translations for ui fields
dr[i] = ts.GetTranslation(string.Concat("ColumnHeader", "." , dr.Table.Columns[i].ColumnName ));
}
//set the report title
dr["ReportHeader"] = txtHeading.Text;
//add the row to the table
dtCatItemCaptions.Rows.Add(dr);
//add the table to the containing DataSet
dsCarrier.Tables.Add(dtCatItemCaptions);
}
///
/// populates the passed in data set with the table DTCatalogItems.
/// this table is carrying data that is to be displayed in the report
/// ...ps: there is another table to carry the table headers in the report
///
/// the dataset that carries report info
private void populateDTCatalogItems(DataSet dsCarrier)
{
//the table that carries report data
ReportCaptions.DTCatalogItemsDataTable dtCatItem = new ReportCaptions.DTCatalogItemsDataTable();
//the list of catalog items that need to be shown in the report
//this is the place where it refers to the LINQ contract to get the data from the database. You can do this step the way you want it to be. It is as simple as filling a generic list from some table from the database.
List itemList = getCatalogService().GetItemsByCatalogIdForReporting(ddlCatalogs.SelectedValue.ToInt64());
//iterate through the loop and populate the table with from the list
foreach (GetCatalogItemsForReportResult item in itemList)
{
DataRow dr = dtCatItem.NewRow();
dr["ArticleNo"] = item.ArticleNo;
dr["ItemName"] = item.ItemName;
//the price could be adjusted appropriately
dr["Price"] = getAdjustedPrice(item.Price);
dr["Currency"] = item.Currency;
dr["UnitOfSale"] = item.UnitOfSale;
.
.
.
dtCatItem.Rows.Add(dr);
}
//assign the table to the data set
dsCarrier.Tables.Add(dtCatItem);
}
