Creating local report using MS Reporting Services

Microsoft reporting services provide the way to represent data in the form of reports in easy manner. It’s a powerful tool, we can generate complex reports using it.   We can create reports by creating Server Reports Project in visual studio  and publish them to server. We can also create local report for ASP.NET using the same mechanism. Here i want to show you how can we achieve this.

Before going further check if your system got Reporting Services components installed. Create a Server Reports project (here i am using VS2005) by following the steps in this link. In this example i am assuming that your report consumes data returned from Stored Procedure and your SP takes some parameters. The report files created by this project will have .rdl extension and these files are XML files so you can easily change them using any notepad.

Now copy your final report file(.rdl) to ASP.NET application and change the file extension to .rdlc. Now open the report file using any text editor like notepad then remove the parameters tags if your reports requires any parameters.

How to remove parameter tags:

<ReportParameters></ReportParameters> and <QueryParameters></QueryParameters> section of the XML represents the parameters required by your report. So delete these tags and content inside these tags and save the file. Now your report is ready to use in your ASP.NET application.

Add report viewer control to your webpage. Below code shows, code required to use SSRS reports as local report.

Default.aspx:

<rsweb:ReportViewer ID="rptViewer" runat="server" Width="100%" ShowBackButton="false" ShowExportControls="true"
ShowFindControls="true" ShowPageNavigationControls="true" ShowRefreshButton="false" ShowPrintButton="false"
ShowParameterPrompts="false" ShowZoomControl="false" Visible="false">
<LocalReport>

</LocalReport>
</rsweb:ReportViewer>

Default.aspx.cs:

DataTable dt = GetReportData(param1);
String path = "~/Reports/YourReport.rdlc";
BindDataToReport(dt, path);
private void BindDataToReport(DataTable reportDataTable,String reportPath)
{

    if (reportDataTable != null && reportDataTable.Rows.Count > 0)
    {
        rptViewer.Visible = true;
        rptViewer.LocalReport.ReportPath = string.Empty;
        ReportDataSource rptDataSource = new ReportDataSource("ReportDataSource", reportDataTable);
        rptViewer.LocalReport.DataSources.Clear();
        rptViewer.Reset();
        rptViewer.LocalReport.ReportPath = Server.MapPath(reportPath);
        rptViewer.LocalReport.DataSources.Add(rptDataSource);
        rptViewer.LocalReport.Refresh();
    }
    else
    {

       //else part here
    }
}

Note:

In the above example i created report using VS2005 and used it as local report in the project created by .NET3.5(VS2008) because the local reports in VS2010 uses SSRS 2008 schema while VS2008 uses SSRS 2005 schema. SSRS 2008 introduces tablix data region concept which is the combination of matrix,table and list. This is not available in SSRS 2005. So take care while using reports as local report.

I hope this post helpful to you. Happy coding

Reading Excel sheet in ASP.NET


             One day I got a requirement of reading excel sheet (excel sheet may be either 2003 or 2007 version). To implement this I started googling and found some interesting solutions from Microsoft website and finally I developed code to read the excel sheet in the following way.
             Here I created a web application and added an upload user control to a page (assume Default.aspx). Once the user uploads the excel file I will store it into the server machine and read the excel sheet from the server machine. Here is the code.
I created a class to read the excel file depending on the excel version.

ExcelReading.cs:

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.OleDb;
using System.Collections.Generic;

public class ExcelReading
{
public ExcelReading()
{
//
// TODO: Add constructor logic here
//
}
    //open the excel file using OLEDB
    OleDbConnection con;
//List of Worksheet names
    public string[] workSheetNames = new string[] { };
    private string fileName;  //Path and file name of the Excel Workbook
    private string connectionString;

    public string[] ExcelRead(string fileNamePath,string extn)
    {
        this.fileName = fileNamePath;

        switch (extn)
        {
            //Excel 97-2003 file
            case ".xls":
                OpenExcelFile(false);
                break;
            //Excel 2007 file
            case ".xlsx":
                OpenExcelFile(true);
                break;
            default:
                break;

        }

        return workSheetNames;

    }

    private void OpenExcelFile(bool isOpenXMLFormat)
    {
//while reading excel sheet Oledbdata Reader scans first 8 rows
        //and based on the majority data types it treats them as either integer or text.
        //The non matching data types are treated as NULL or empty. To solve this problem
        //use IMEX=1 property extender        

        if (isOpenXMLFormat)
            //read a 2007 file
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +@fileName + ";Extended Properties="Excel 8.0;HDR=YES;"";
        else
            //read a 97-2003 file
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@fileName + ";Extended Properties='Excel 8.0;IMEX=1'";
        try
        {
            con = new OleDbConnection(connectionString);
            con.Open();

            //get all the available sheets
            System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            //get the number of sheets in the file
            workSheetNames = new String[dataSet.Rows.Count];
            int i = 0;
            foreach (DataRow row in dataSet.Rows)
            {
                //insert the sheet's name in the current element of the array
                //and remove the $ sign at the end
                workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ''','$' });
                i++;
            }
            if (dataSet != null)
                dataSet.Dispose();
        }
        catch(Exception e)
        {
            throw e;
        }

        finally
        {
            con.Close();
            con.Dispose();
        }

    }

    public System.Data.DataTable GetWorksheet(string worksheetName,string range)
    {
        OleDbConnection con = new OleDbConnection();
        System.Data.DataTable excelDataTable = new System.Data.DataTable();
        try
        {
            con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                "select * from [" + worksheetName + "$" + range + "]", con);

            con.Open();
            cmd.Fill(excelDataTable);
        }
        catch { }
        finally
        {
            con.Close();
            con.Dispose();
        }

        return excelDataTable;
    }

}

Here is the code of the page and its code behind file from which I uploaded the excel sheet.
Default.aspx:

<asp:FileUpload ID="ctrlFileUpload"  runat="server" EnableViewState="true" Width="100%" onkeypress="return false;" onkeydown="return false;" /></br>
<asp:Button ID="btnUpload" runat="server" Enabled="true" Text="Upload" OnClick=" btnUpload _Click"/>

Default.aspx.cs:

protected void btnUpload_Click(object sender, EventArgs e)

{

      string FileName = ctrlFileUpload.PostedFile.FileName;

      string _strUploadedFileExtn = System.IO.Path.GetExtension(ctrlFileUpload.PostedFile.FileName);

//Saving the file into server machine

      ctrlFileUpload.PostedFile.SaveAs(Server.MapPath(".") + "\UploadedExcel\" + FileName);

//Reading the file from the previously saved location.

string path = @"../UploadedExcel/" + FileName;

      string fileNamePath = Server.MapPath(path);

System.Data.DataTable dataTable = new System.Data.DataTable();

string[] workSheetNames = excelReading.ExcelRead(fileNamePath, _strUploadedFileExtn);

//Read the required worksheet from the array. Here I am reading first sheet

string sheetName= workSheetNames[1];

//Specify the range of the data which you want to read from excel sheet

string range = "B8:B100";

dataTable = excelReading.GetWorksheet(sheetName, range);

//Read the data from datatable object

if (dataTable.Rows.Count > 0)

      {

          foreach (DataRow row in dataTable.Rows)

    {

      //Here write the code to read the data from datatable row.

}

      }

}

Another way of reading excel sheet:

We can also read the excel sheet by referring ‘Microsoft.Interop.Excel.dll’ but before going in that way I suggest you to go through below link  because microsoft is not recommending this way.

http://support.microsoft.com/kb/257757
References for reading excel sheet:

http://support.microsoft.com/kb/306572

http://support.microsoft.com/kb/316934

http://support.microsoft.com/kb/316934

http://code.msdn.microsoft.com/NitoExcel