Exporting to Excel in Crystal Reports .NET

Export crystal report to excel in asp.net 

ReportDocument crystalReport;

protected void Page_Load(object sender, EventArgs e)

    {

       

        crystalReport = new ReportDocument();

        crystalReport.Load(Server.MapPath("GenReportMaster.rpt"));

       

        //Set DataBase Login Info

        crystalReport.SetDatabaseLogon("sa", "Nopass123", @"BD-DAC-014", "lamolinsDB");

        string year = (Request.QueryString["year"] != "") ? Request.QueryString["year"] : "";

        string month = (Request.QueryString["month"] != "") ? Request.QueryString["month"] : "";

        string zoneString = Request.QueryString["zone"];

        string zone = (zoneString.Equals("All")) ? "" : zoneString;

        string branchString = Request.QueryString["branch"];

        string branch = (branchString.Equals("All")) ? "" : branchString;

        string brand = (Request.QueryString["brand"]!= "") ? Request.QueryString["brand"] : "";

        crystalReport.SetParameterValue("@month", month);

        crystalReport.SetParameterValue("@year", year);

        crystalReport.SetParameterValue("@zone", zone);

        crystalReport.SetParameterValue("@branch", branch);

        crystalReport.SetParameterValue("@brand", brand);

        CwrVatCrystalReportViewer.Visible = true;

        CwrVatCrystalReportViewer.DisplayGroupTree = false;

        CwrVatCrystalReportViewer.HasPageNavigationButtons = true;

        CwrVatCrystalReportViewer.HasCrystalLogo = false;

        CwrVatCrystalReportViewer.HasDrillUpButton = false;

        CwrVatCrystalReportViewer.HasSearchButton = false;

        CwrVatCrystalReportViewer.HasViewList = false;

        CwrVatCrystalReportViewer.HasToggleGroupTreeButton = false;

        CwrVatCrystalReportViewer.HasZoomFactorList = false;

        CwrVatCrystalReportViewer.ToolbarStyle.Width = new Unit("750px");

        CwrVatCrystalReportViewer.ReportSource = crystalReport;

       

    }

Convert to excel:

protected void Button1_Click(object sender, EventArgs e)

    {

        try

        {

            ExportOptions CrExportOptions;

            DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();

            ExcelFormatOptions CrFormatTypeOptions = new ExcelFormatOptions();

            CrDiskFileDestinationOptions.DiskFileName = "c:\\report.xls";

            CrExportOptions = crystalReport.ExportOptions;

            CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;

            CrExportOptions.ExportFormatType = ExportFormatType.Excel;

            CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;

            CrExportOptions.FormatOptions = CrFormatTypeOptions;

            crystalReport.Export();

            messageLabel.Text = "Download excel successful...";

        }

        catch (Exception ex)

        {

            messageLabel.Text = "Download excel failed..."+ex.ToString();

        }

    }

Prevent websites from injection in asp.net

string queryStr = "select email, fullname, from t_employee where username = user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";

To prevent from query injection we can change it by using stored procedures or 

parameterised TSQL

string queryStr = "select email, fullname from t_employee where user_name = @USER and user_date between @startdate and @enddate'";

cmd.Parameters.Add("@USER", SqlDbType.NVarChar, 100);  -- Assuming type and size
cmd.Parameters["@USER"].Value = username
cmd.Parameters.Add("@startdate ", SqlDbType.DateTime);
cmd.Parameters["@startdate "].Value = startdate ;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime);
cmd.Parameters["@enddate"].Value = enddate;

Store Excel Data To DataTable And Copy To SQLServer Database using SqlBulkCopy

This method stores the data to datatable and shows to GridView.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)

{

        string conStr="";

        switch (Extension)

        {

            case ".xls": //Excel 97-03

                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

                break;

            case ".xlsx": //Excel 07

                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

                break;

        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);

        OleDbCommand cmdExcel = new OleDbCommand();

        OleDbDataAdapter oda = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet

        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

        connExcel.Close();

        //Read Data from First Sheet

        connExcel.Open();

        //Bind Data to GridView

        GridView1.Caption = Path.GetFileName(FilePath);

        GridView1.DataSource = dt;

        GridView1.DataBind();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        oda.SelectCommand = cmdExcel;

        oda.Fill(dt);

        connExcel.Close();

        BulkInsertDataTable(“databaseTable”, dt);

 }

This method insert the data from datatable to sqlserver.

public bool BulkInsertDataTable(string tableName, DataTable dataTable)

    {

        bool isSuccuss;

        string connString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;

        SqlConnection SqlConnectionObj = new SqlConnection(connString);

       

        try

        {  

            SqlConnectionObj.Open();

SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);

            bulkCopy.DestinationTableName = tableName;

            bulkCopy.WriteToServer(dataTable);

            isSuccuss = true;

        }

        catch (Exception ex)

        {

            isSuccuss = false;

        }

        finally

        {

            SqlConnectionObj.Close();

        }

        return isSuccuss;

    }