In this article we will know how to create xml through asp.net and write the data into that excel sheet and display in a grid simultaneously.

 

We rarely see the direct creation of excel and write the content into it by asp.net. In this we added simply 3 methods to create, write and display the data into grid.

 

Create Excel Method:

 

public void createexcel()

{

        //MicroSoft.Office.Interop.Excel (import this as references)

       string filename = "";

           try

            {

//---You can use data table according to ur requirement---

//DataTable dtExcelImport = new DataTable(); use structure to wirte the columns names

//dtExcelImport = clsApp.getDataTable("select * from Yourtable");

 

                Microsoft.Office.Interop.Excel.Application oXL;

                Microsoft.Office.Interop.Excel._Workbook oWB;

                Microsoft.Office.Interop.Excel._Worksheet oSheet;

                oXL = new Microsoft.Office.Interop.Excel.Application();

                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));

                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

                string rg = "A1:";

                Microsoft.Office.Interop.Excel.Range range1;

// here you have to assingn your data table column names

                    oSheet.Cells[1, 1] = "Slno";

                    oSheet.Cells[1, 2] = "FirstName";

                    oSheet.Cells[1, 3] = "LastName";

                    oSheet.Cells[1, 4] = "Location";

                //========= accessPath to specific folder =======

// I directly taken file name.

filename = "TestExcel.xls";//DateTime.Now.ToString("dd/MMM/yyyy") + ".xls";

// iam saving the excel in same place where this project exists.

                filename = Server.MapPath("~/" + filename);

                oWB.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,

                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,

                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,

                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oWB.Close(Missing.Value, Missing.Value, Missing.Value);

                oWB = null;

                oXL.Quit();

            }

            catch (Exception ex)

            {

            }       

}

 

Write into excel sheet method :

 

protected void Insert()

{

// I directly taken values by the following variable you can use your own way

        int Slno = 1;

        string FirstName = "sivag";

        string LastName = "tiruveedi";

        string Location = "Hyderabad";

        string file = Server.MapPath("TestExcel.xls");

        string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

        using (OleDbConnection Connection = new OleDbConnection(constr))

        {

            Connection.Open();

            string query = "INSERT INTO [Sheet1$](Slno,FirstName,LastName,Location) VALUES(" + Slno + ",\"" + FirstName.Trim() + "\",\"" + LastName.Trim() + "\",\"" + Location.Trim() + "\")";

            using (OleDbCommand objCmd = new OleDbCommand(query, Connection))

            {

                objCmd.ExecuteNonQuery();

                objCmd.Dispose();

                Connection.Close();

            }

        }       

}

 

Displaying in the Grid method:

 

private void GenerateExcelData(string SlnoAbbreviation)

{

        // need to pass relative path after deploying on server

        string path = System.IO.Path.GetFullPath(@"D:\InformationNew.xls");

 

 

        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("InformationNew.xls") + "; Extended Properties=Excel 8.0;";

 

        oledbConn = new OleDbConnection(strCon);

        oledbConn.Open();

        OleDbCommand cmd = new OleDbCommand(); ;

        OleDbDataAdapter oleda = new OleDbDataAdapter();

        DataSet ds = new DataSet();

 

        // selecting distict list of Slno

        cmd.Connection = oledbConn;

        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "SELECT [Slno],[FirstName],[LastName],[Location] FROM [Sheet1$]";

        oleda = new OleDbDataAdapter(cmd);

        oleda.Fill(ds, "dsSlno");

        // binding form data with grid view

        grvData.DataSource = ds.Tables[0];

        grvData.DataBind();       

}

In Your Page_Load:

 

protected void Page_Load(object sender, EventArgs e)

{

       if (!IsPostBack)

        {

                  createexcel();

                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("InformationNew.xls") + "; Extended Properties=Excel 8.0;";

            bool Isthere = FileThere(@"D:\Project\Excel\InformationNew.xls");

            if (Isthere)

            {

                Insert();

                GenerateExcelData("Select-Command");

            }

            else

            {

 

             ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", "alert('File Does Not Exists');", true);

            }

        }

         Response.Write("Excels book is successfully created and wrote data into that file");

}

 

.aspx Page:

 

Just take grid view with the id grvData or copy paste the following code in your aspx page.

 

<div>

<asp:GridView ID="grvData" runat="server">

</asp:GridView>

</div>

 

Conclusion:

 Afterexecuting the code you will get an excel sheet with “TestExcel” name.