Click here to Skip to main content
15,892,927 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I can create a new Excel sheet by using the Stored Procedure in the following code, but can't understand how to insert one value from an ASP.Net control into the last column of the Excel Sheet. I searched Google, but many articles use their own libraries files which cannot be downloaded. So how can I implement in this code only?

private void ExportFromSQL()
        {
            try
            {
                System.Data.DataTable dtexcel = new System.Data.DataTable();
                Hashtable ht = new Hashtable();

                dtexcel = BindComboWithParm(ht, "Get_Cols_Forexcelsheet");
                //dtexcel.Columns.Add("Company Id", typeof(string));

                //foreach (DataRow dr in dtexcel.Rows)
                //{
                //    dr["Company_Id"] = lblUploadRegistrationId.Text;
                //}
                using (XLWorkbook wb = new XLWorkbook())
                {

                    wb.Worksheets.Add(dtexcel, "Customer");
                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;filename=Customer_Creation.xlsx");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                    }
                }
                lblMessage.Visible = false;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
                lblMessage.Visible = true;
            }
        }

        public DataTable BindComboWithParm(Hashtable ht, string sp)
        {
            try
            {
                command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = sp;
                command.Connection = connection;
                SqlDataAdapter Adp = new SqlDataAdapter(command);
                Dt = new DataTable();
                Adp.Fill(Dt);
                lblMessage.Visible = false;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
                lblMessage.Visible = true;                
            }

            return Dt;
        }
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900