SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software

Development Forums for Programmer, Administrators and Users

Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Sqlserver to excel is this de right way

Last post 05-04-2010, 1:24 PM by deva. 0 replies.
Sort Posts:
  •  05-04-2010, 1:24 PM 20991

    • deva is not online. Last active: 05-04-2010, 2:03 PM deva
    • Joined on 05-03-2010

    Sqlserver to excel is this de right way

    Hello
    I work on an intranet site management fees.
     management part of review tired me, I chose to work with Excel for simplicity
    That's what I want to do:
    I have a listbox containing the modules , for a list of students registered in a module, the responsable for this must complete an authentication form.
    I want that after authenticating the excel file containing the student opens  (I do not know if this is the right method, you comments are welcome)
    to export data from sqlserver to excel I used this code:
    <pre> cn.Open();
           SqlDataAdapter da = new SqlDataAdapter(@"DECLARE @MATIERE nvarchar(max)
    SELECT @MATIERE =
      STUFF(
      (
      select distinct ',[' + LIBELLE_MATIERE + ']'
      from MATIERE where CODE_MODULE =11
      for xml path('')
     ),
     1,1,'')

    DECLARE @SQL nvarchar(max)
    SELECT @SQL = N'
    select
     *
    from (

      SELECT     ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE,
     EXAMEN.NOTE,
                         MATIERE.LIBELLE_MATIERE
    FROM         ETUDIANT INNER JOIN
                          EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN
                          CAPITALISE_MODULE ON
                          ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN
                          MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN
                          MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE
                          AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE
    WHERE      (CAPITALISE_MODULE.CODE_MODULE = 11) AND
                          (CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1)
    ) Data
    PIVOT (
     SUM(NOTE)
     FOR LIBELLE_MATIERE
     IN (
      ' + @MATIERE + '
     )
    ) PivotTable
    '

    exec sp_executesql @SQL

    ", cn);
           DataTable dt = new DataTable();
           da.Fill(dt);
           cn.Close();
     
           Response.Clear();
           Response.ContentType = "application/vnd.ms-excel";
           string sep = "";
           foreach (DataColumn dc in dt.Columns)
           {
                  Response.Write(sep + dc.ColumnName);
                  sep = "\t";
           }
           Response.Write("\n");
     
           int i;
           foreach (DataRow dr in dt.Rows)
           {
                  sep = "";
                  for (i = 0; i &lt; dt.Columns.Count; i++)
                  {
                         Response.Write(sep + dr[i].ToString());
                         sep = "\t";
                  }
                  Response.Write("\n");
           }
     
    }


      
    }
    </pre>

    this code works with sql queries  simple but with my query i had an empty excel file (I tried this query  and she is correct).
    Should I display data in gridview and the user clicks on a button to export to excel?
    If a person already working on this project thank you to advise me.
    Thank you very much.
View as RSS news feed in XML
Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems