public void ToExcel(List <Pesertaujian> PesertaUjian, Pesertaujian JadwalPeserta)
{
// ------------ to excel ---------------------
// build excel apps
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
// create apps object
var excelApp = new Excel.Application();
excelApp.DefaultSaveFormat = Excel.XlFileFormat.xlOpenXMLWorkbook;
try
{
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.Workbooks.Add();
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// text keterangan
workSheet.Cells[1, "A"] = "DAFTAR PESERTA UJIAN " + JadwalPeserta.JenisUjian.ToUpper();
workSheet.Cells[3, "A"] = "Mata Kuliah/Kelas";
workSheet.Cells[4, "A"] = "Tahun/Semester";
workSheet.Cells[5, "A"] = "Jadwal";
workSheet.Cells[6, "A"] = "Dosen Pengampu";
workSheet.Cells[3, "C"] = JadwalPeserta.Makul + "/" + JadwalPeserta.Kelas;
workSheet.Cells[4, "C"] = JadwalPeserta.Tahun + "/" + JadwalPeserta.Semester;;
workSheet.Cells[5, "C"] = JadwalPeserta.Jadwal;
workSheet.Cells[6, "C"] = JadwalPeserta.Dosen;
workSheet.Cells[8, "A"] = "NO";
workSheet.Cells[8, "B"] = "NPM";
workSheet.Cells[8, "C"] = "NAMA";
workSheet.Cells[8, "D"] = "NILAI ANGKA";
workSheet.Cells[8, "E"] = "NILAI HURUF";
workSheet.Cells[8, "F"] = "TANDA TANGAN";
// loop
var row = 8;
foreach (var mhs in PesertaUjian)
{
row++;
workSheet.Cells[row, "A"] = "";
workSheet.Cells[row, "B"] = mhs.npm;
workSheet.Cells[row, "C"] = mhs.nama;
}
//tanda tangan
workSheet.Cells[row + 3, "B"] = "Ketua Jurusan";
workSheet.Cells[row + 7, "B"] = "...";
workSheet.Cells[row + 2, "E"] = "Magelang, ...";
workSheet.Cells[row + 3, "E"] = "Dosen Pengampu,";
workSheet.Cells[row + 7, "E"] = JadwalPeserta.Dosen.Trim();
// autofit
workSheet.Columns[1].ColumnWidth = 3;
workSheet.Columns[2].Autofit();
workSheet.Columns[3].Autofit();
workSheet.Columns[4].Autofit();
//workSheet.Columns[5].Autofit();
workSheet.Columns[6].Autofit();
// text align left
workSheet.get_Range("A1", "D" + row.ToString()).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
// border nilai
var range = workSheet.get_Range("A8", "F" + row.ToString());
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
// save excel file
//string excelFile = "C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
string excelFile = Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
workSheet.SaveAs(excelFile, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
// quit excel apps
excelApp.Workbooks.Close();
excelApp.Quit();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
// quit excel apps
excelApp.Workbooks.Close();
excelApp.Quit();
throw ex;
}
finally
{
// release excel object
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
if (book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
if (books != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
try
{
// FileInfo fileInfo = new FileInfo(Server.MapPath("~/Nilai/coba.xlsx"));
// C:\inetpub\wwwroot\portal
// FileInfo fileInfo = new FileInfo("C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
FileInfo fileInfo = new FileInfo(Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
Response.Clear();
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileInfo.Name));//"attachment;filename=" + fileInfo.Name);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.Flush();
Response.WriteFile(fileInfo.FullName);
Response.End();
// kill excel proccess
Process[] pProcess;
pProcess = Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
throw ex;
}
}
protected void LnkDownExcel_Click(object sender, EventArgs e)
{
try
{
// get row index
GridViewRow gvRow = (GridViewRow)(sender as Control).Parent.Parent;
int index = gvRow.RowIndex;
string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
con.Open();
if (this.DLUjian.SelectedItem.Text.ToUpper() == "UJIAN TENGAH SEMESTER")
{
List <Pesertaujian> PesertaUTS = new List <Pesertaujian>();
Pesertaujian JadwalPeserta = new Pesertaujian();
JadwalPeserta.Id_Prodi = this.Session["level"].ToString().Trim();
JadwalPeserta.Prodi = this.Session["Prodi"].ToString().Trim();
JadwalPeserta.KdMakul = this.GVJadwal.Rows[index].Cells[1].Text.Trim();
JadwalPeserta.Makul = this.GVJadwal.Rows[index].Cells[2].Text.Trim();
JadwalPeserta.NIDN = this.GVJadwal.Rows[index].Cells[3].Text.Trim();
JadwalPeserta.Dosen = this.GVJadwal.Rows[index].Cells[4].Text.Trim();
JadwalPeserta.Kelas = this.GVJadwal.Rows[index].Cells[5].Text.Trim();
JadwalPeserta.JenisKelas = this.GVJadwal.Rows[index].Cells[11].Text.Trim();
JadwalPeserta.Jadwal = this.GVJadwal.Rows[index].Cells[6].Text.Trim() + ", " + this.GVJadwal.Rows[index].Cells[7].Text.Trim() + "," + this.GVJadwal.Rows[index].Cells[8].Text.Trim() + "-" +
this.GVJadwal.Rows[index].Cells[9].Text.Trim() + ", Ruang " + this.GVJadwal.Rows[index].Cells[10].Text.Trim();
JadwalPeserta.JenisUjian = this.DLUjian.SelectedItem.Text.ToUpper().Trim();
JadwalPeserta.Tahun = this.DLTahun.SelectedValue.Trim();
JadwalPeserta.Semester = this.DLSemester.SelectedValue.Trim();
SqlCommand CmdUTS = new SqlCommand("SpPesertaUTS", con);
CmdUTS.CommandType = System.Data.CommandType.StoredProcedure;
CmdUTS.Parameters.AddWithValue("@id_prodi", this.Session["level"].ToString().Trim());
CmdUTS.Parameters.AddWithValue("@nidn", JadwalPeserta.NIDN);
CmdUTS.Parameters.AddWithValue("@kode_makul", JadwalPeserta.KdMakul);
CmdUTS.Parameters.AddWithValue("@kelas", JadwalPeserta.Kelas);
CmdUTS.Parameters.AddWithValue("@semester", JadwalPeserta.Tahun + JadwalPeserta.Semester);
using (SqlDataReader rdr = CmdUTS.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
Pesertaujian NewPeserta = new Pesertaujian();
NewPeserta.npm = rdr["npm"].ToString();
NewPeserta.nama = rdr["nama"].ToString();
PesertaUTS.Add(NewPeserta);
}
}
else
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Tidak Ada Peserta Ujian');", true);
return;
}
}
ToExcel(PesertaUTS, JadwalPeserta);
}
else if (this.DLUjian.SelectedItem.Text.ToUpper() == "UJIAN AKHIR SEMESTER")
{
List <Pesertaujian> PesertaUAS = new List <Pesertaujian>();
Pesertaujian JadwalPeserta = new Pesertaujian();
JadwalPeserta.Id_Prodi = this.Session["level"].ToString().Trim();
JadwalPeserta.Prodi = this.Session["Prodi"].ToString().Trim();
JadwalPeserta.KdMakul = this.GVJadwal.Rows[index].Cells[1].Text.Trim();
JadwalPeserta.Makul = this.GVJadwal.Rows[index].Cells[2].Text.Trim();
JadwalPeserta.NIDN = this.GVJadwal.Rows[index].Cells[3].Text.Trim();
JadwalPeserta.Dosen = this.GVJadwal.Rows[index].Cells[4].Text.Trim();
JadwalPeserta.Kelas = this.GVJadwal.Rows[index].Cells[5].Text.Trim();
JadwalPeserta.JenisKelas = this.GVJadwal.Rows[index].Cells[11].Text.Trim();
JadwalPeserta.Jadwal = this.GVJadwal.Rows[index].Cells[6].Text.Trim() + ", " + this.GVJadwal.Rows[index].Cells[7].Text.Trim() + "," + this.GVJadwal.Rows[index].Cells[8].Text.Trim() + "-" +
this.GVJadwal.Rows[index].Cells[9].Text.Trim() + ", Ruang " + this.GVJadwal.Rows[index].Cells[10].Text.Trim();
JadwalPeserta.JenisUjian = this.DLUjian.SelectedItem.Text.ToUpper().Trim();
JadwalPeserta.Tahun = this.DLTahun.SelectedValue.Trim();
JadwalPeserta.Semester = this.DLSemester.SelectedValue.Trim();
SqlCommand CmdUAS = new SqlCommand("SpPesertaUAS", con);
CmdUAS.CommandType = System.Data.CommandType.StoredProcedure;
CmdUAS.Parameters.AddWithValue("@id_prodi", this.Session["level"].ToString().Trim());
CmdUAS.Parameters.AddWithValue("@nidn", JadwalPeserta.NIDN);
CmdUAS.Parameters.AddWithValue("@kode_makul", JadwalPeserta.KdMakul);
CmdUAS.Parameters.AddWithValue("@kelas", JadwalPeserta.Kelas);
CmdUAS.Parameters.AddWithValue("@semester", JadwalPeserta.Tahun + JadwalPeserta.Semester);
using (SqlDataReader rdr = CmdUAS.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
Pesertaujian NewPeserta = new Pesertaujian();
NewPeserta.npm = rdr["npm"].ToString();
NewPeserta.nama = rdr["nama"].ToString();
PesertaUAS.Add(NewPeserta);
}
}
else
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Tidak Ada Peserta Ujian');", true);
return;
}
}
ToExcel(PesertaUAS, JadwalPeserta);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
return;
}
}
