Hi, I already tried the method of the gridview but I have hundreds of students in a module managing the grid is heavy. To excel I try this code
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 < dt.Columns.Count; i++)
{
Response.Write(sep + dr\[i\].ToString());
sep = "\\t";
}
Response.Write("\\n");
}
}
}
excel file generated by this code gives me this: Sudeni_id_____marks_matter1____marks_matte2 but how to allow just the marks between 0 and 20? is a method to calculate the average module directl:ysum(marks_matter1*coefficient)/sum(coefficent) I chose XML because it's easy to send to the database(I read that in an article )but i have a problem with generate it with sql,excel i dont have any edea if the data are not the same table.
