Sunday, September 15, 2013

How to show a Crystal Report in an Excel file


In this Tutorial, there is a table which includes 3 labels, and 3 text boxes, and 3 buttons, and a Crystal Report, and a data table, and a database table 

The table shape : 


The table code : 

 <table bgcolor="#66FFFF" border="2" bordercolor="#cccccc">
            <tr>
                <td>
                    <asp:Label ID="lblname" Text="Employee Name" runat="server"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtEmpNam" runat="server" Width="218px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblphone" Text="Employee Phone" runat="server"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtphone" runat="server" Width="218px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblAge" Text="Employee Age" runat="server"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtAge" runat="server" Width="218px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Button ID="btnadd" runat="server" Text="Add" Style="margin-top: 25px" Width="140px" />
                </td>
                <td>
                    <asp:Button ID="btnclear" runat="server" Text="clear" Style="margin-top: 23px" Width="140px" />
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Button ID="btnShowReport" runat="server" Text="Show Report" Width="140px" />
                </td>
            </tr>
        </table>

The Data table : 





The data base Table : 





//note : EmploeeNo is an Identity 

The Crystal Report : 






The Add Button Code : 

 protected void btnadd_Click(object sender, EventArgs e)
        {
            if (txtEmpNam.Text != string.Empty && txtphone.Text != string.Empty && txtAge.Text != string.Empty)
            {
                txtAge.ForeColor = Color.Black;
                txtphone.ForeColor = Color.Black;
                txtAge.ForeColor = Color.Black;
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Saleem\Documents\Project.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
                Con.Open();
                String query = "Insert into Employee(EmployeeName , EmployeePhone, EmployeeAge)Values('" + txtEmpNam.Text + "','" + txtphone.Text + "','" + txtAge.Text + "')";
                SqlCommand com = new SqlCommand(query, Con);
                com.ExecuteNonQuery();
                Con.Close();
            }
            else if (txtEmpNam.Text == string.Empty && txtphone.Text == string.Empty && txtAge.Text == string.Empty)
            {
                txtEmpNam.ForeColor = Color.Red;
                txtEmpNam.Text = "please Enter the Employee name";
                txtphone.ForeColor = Color.Red;
                txtphone.Text = "please Enter the Employee phone";
                txtAge.ForeColor = Color.Red;
                txtAge.Text = "please Enter the Employee age";
            }
            else if (txtEmpNam.Text == string.Empty && txtphone.Text == string.Empty)
            {
                txtEmpNam.ForeColor = Color.Red;
                txtEmpNam.Text = "please Enter the Employee name";
                txtphone.ForeColor = Color.Red;
                txtphone.Text = "please Enter the Employee phone";
            }
            else if (txtEmpNam.Text == string.Empty && txtAge.Text == string.Empty)
            {
                txtEmpNam.ForeColor = Color.Red;
                txtEmpNam.Text = "please Enter the Employee name";
                txtAge.ForeColor = Color.Red;
                txtAge.Text = "please Enter the Employee age";
            }
            else if (txtphone.Text == string.Empty && txtAge.Text == string.Empty)
            {
                txtphone.ForeColor = Color.Red;
                txtphone.Text = "please Enter the Employee phone";
                txtAge.ForeColor = Color.Red;
                txtAge.Text = "please Enter the Employee age";
            }
            else if (txtEmpNam.Text == string.Empty)
            {
                txtEmpNam.ForeColor = Color.Red;
                txtEmpNam.Text = "please Enter the Employee name";
            }
            else if (txtphone.Text == string.Empty)
            {
                txtphone.ForeColor = Color.Red;
                txtphone.Text = "please Enter the Employee phone";
            }
            else if (txtAge.Text == string.Empty)
            {
                txtAge.ForeColor = Color.Red;
                txtAge.Text = "please Enter the Employee age";
            }
            txtAge.Text = string.Empty;
            txtEmpNam.Text = string.Empty;
            txtphone.Text = string.Empty; 

        }

The Clear Button Code : 

 void btnclear_Click(object sender, EventArgs e)
        {
            txtAge.Text = string.Empty;
            txtEmpNam.Text = string.Empty;
            txtphone.Text = string.Empty; 

        }

The Show Report Button Code : 

 protected void btnShowReport_Click(object sender, EventArgs e)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Saleem\Documents\Project.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            Conn.Open();
            String queryy = "SELECT EmployeeNo, EmployeeName, EmployeePhone, EmployeeAge From Employee";
            SqlCommand comm = new SqlCommand(queryy, Conn);
            SqlDataAdapter Adapter = new SqlDataAdapter(comm);
            DataSet1 ds = new DataSet1();
            Adapter.Fill(ds, "Employee");
            Conn.Close();
            CrystalReport1 crs = new CrystalReport1();
            crs.SetDataSource(ds.Tables[0]);
            crs.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "CrystalReport1");
        }
// if you don't want to download the report before viewing make the true false, then you will directly see the report after the click

The Report in the Excel file: 



// The data in the report is only for testing

No comments :

Post a Comment