How to export one table or sheet to multiple tables or sheets

If you have one excel sheet or table and you want to export it to multiple sheets or tables use the following steps:

1. Specify the column -tablesNamesColumn- you want its data to be the names of the tables/sheets 
2. import the main sheet to MSSQL 
3. Select distinct values from the tablesNamesColumn
4. Create a table and name it "TablesNames":                                                                                       CREATE TABLE [dbo].[TablesNames](
          [id] [int] IDENTITY(1,1) PRIMARY KEY,
          [Table_Name] [nvarchar](255) NOT NULL,
   )   
5. insert the tables names in the table which contains the tables names                                               insert into TablesNames
   SELECT distinct tablesNamesColumn  
   FROM [AllSheets]
6. Now you need to loop inside the tables names and create tables in the same format         
   DECLARE @LoopCounter INT , @MaxTablesNamesId INT,
           @TableName NVARCHAR(100)
   SELECT @LoopCounter = 0 , @MaxTablesNamesId = count(*)
   FROM TablesNames
   
   
   WHILE ( @LoopCounter IS NOT NULL
           AND  @LoopCounter <= @MaxTablesNamesId)
   BEGIN
      SELECT @TableName = Table_Name FROM TablesNames
      WHERE id = @LoopCounter
     -- PRINT @TableName 
      SELECT @LoopCounter  = min(id) FROM TablesNames
      WHERE id > @LoopCounter
     
   declare @q nvarchar(max) = '
   drop table ['+@TableName+'];
   SELECT * INTO  ['+@TableName+']
   FROM [delme]
   WHERE Institution = N'''+ @TableName +''';' 
   exec (@q)
   END
7. Use the following steps to export the data to Excel 97-2003 file, because I tried Excel 2010 and 2016, the only one works fine is 97-2003  
   

Export html to PDF and Excel

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

using GisSolution.App_Code;

using System.Web;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.tool.xml;

using iTextSharp.text.html.simpleparser;

namespace Application

{

    public partial class ParcelPoints : Page

    {

        string BQP_CODE;

        const string query = "select رقم_الطلب, NO_POINT,COORDINATE_X,COORDINATE_Y from IDENTIFY_SUR_POINTS where BQP_CODE='{0}'";

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                BindGrid();

                ScriptManager scriptManager = ScriptManager.GetCurrent(Page);

                scriptManager.RegisterPostBackControl(btnExport);

                scriptManager.RegisterPostBackControl(btnExportToPDF);

            }

        }

        private void BindGrid()

        {

            //if (string.IsNullOrEmpty(hf_BQP_CODE.Value))

            BQP_CODE = Request.QueryString["BQP_CODE"];

            hf_BQP_CODE.Value = BQP_CODE;

            if (!string.IsNullOrEmpty(BQP_CODE))

            {

                DBConnection dataAccess = new DBConnection();

                using (DataTable dt = dataAccess.GetData(string.Format(query, BQP_CODE)))

                {

                    GV_ParcelsPoints.DataSource = dt;

                    GV_ParcelsPoints.DataBind();

                }

            }

        }

        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)

        {

            GV_ParcelsPoints.PageIndex = e.NewPageIndex;

            BindGrid();

        }

        protected void ExportToExcel(object sender, EventArgs e)

        {

            if (!string.IsNullOrEmpty(hf_BQP_CODE.Value))

            {

                DBConnection dataAccess = new DBConnection();

                ExportToExcel(dataAccess.GetData(string.Format(query, hf_BQP_CODE.Value)), string.Format(@"Parcel_{0}_Points_{1}", hf_BQP_CODE.Value,DateTime.Now));

            }

        }

        protected void btnExportToPDF_Click(object sender, EventArgs e)

        {

            if (!string.IsNullOrEmpty(hf_BQP_CODE.Value))

            {

                DBConnection dataAccess = new DBConnection();

                ExportGridToPDF(dataAccess.GetData(string.Format(query, hf_BQP_CODE.Value)), string.Format(@"Parcel_{0}_Points_{1}", hf_BQP_CODE.Value, DateTime.Now));

            }

        }

        protected void ExportToExcel(DataTable tblData, string fileName)

        {

            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");

            HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

            HttpContext.Current.Response.Clear();

            HttpContext.Current.Response.Buffer = true;

            HttpContext.Current.Response.Charset = "UTF-8";

            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            StringWriter stringWrite = new StringWriter();

            HtmlTextWriter htmlWrite =

            new HtmlTextWriter(stringWrite);

            DataGrid grdExcel = new DataGrid();

            grdExcel.AllowPaging = false;

            grdExcel.DataSource = tblData;

            grdExcel.DataBind();

            foreach (DataGridItem i in grdExcel.Items)

            {

                foreach (TableCell tc in i.Cells)

                    tc.Attributes.Add("class", "text");

            }

            grdExcel.RenderControl(htmlWrite);

            HttpContext.Current.Response.Write("");

            string style = @" ";

            HttpContext.Current.Response.Write(style);

            HttpContext.Current.Response.Write(stringWrite.ToString());

            HttpContext.Current.Response.Flush();

            HttpContext.Current.Response.End();

        }

        private void ExportGridToPDF(DataTable tblData, string fileName)

        {

            Response.ContentType = "application/pdf; charset=utf-8\"";

            Response.AddHeader("content-disposition", "attachment;filename=UserDetails.pdf");

            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            StringWriter sw = new StringWriter();

            HtmlTextWriter hw = new HtmlTextWriter(sw);

            GV_ParcelsPoints.AllowPaging = false;

            BindGrid();

            int cc=GV_ParcelsPoints.Rows.Count;

            GV_ParcelsPoints.RenderControl(hw);

            StringReader sr = new StringReader(sw.ToString());

            Document pdfDoc = new Document(PageSize.A4, 7f, 7f, 7f, 0f);

            HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

            PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

            pdfDoc.Open();

            htmlparser.Parse(sr);

            pdfDoc.Close();

            Response.Write(pdfDoc);

            Response.End();

        }

        //Confirms that an HtmlForm control is rendered for the

        //specified ASP.NET server control at run time.

        public override void VerifyRenderingInServerForm(Control control)

        { }

    }

}

Export / Import Data from/to oracle via SSH Secure shell

After you login to the shell
type su - oracle and hist enter
type the Exp/Imp query

to open sql plus
type su - oracle