public static void ExcelExport(ObjectListView olv, string defaultName)
{
SLDocument sl = new SLDocument();
SLStyle style = sl.CreateStyle();
for (int i = 1; i <= olv.Columns.Count; ++i)
{
sl.SetCellValue(1, i, olv.Columns[i - 1].Text);
}
for (int i = 1; i <= olv.Columns.Count; ++i)
{
for (int j = 1; j <= olv.Items.Count; ++j)
{
string cellVal = olv.Items[j - 1].SubItems[i - 1].Text;
int cellValNumeric = -1;
if (int.TryParse(cellVal, out cellValNumeric))
{
sl.SetCellValue(j + 1, i, cellValNumeric);
}
else
{
sl.SetCellValue(j + 1, i, cellVal);
}
System.Drawing.Color backColor = olv.Items[j - 1].BackColor;
System.Drawing.Color foreColor = olv.Items[j - 1].ForeColor;
style.Fill.SetPattern(PatternValues.Solid, backColor, foreColor);
sl.SetCellStyle(j + 1, i, style);
SLTable tbl = sl.CreateTable(1, 1, olv.Items.Count + 1, olv.Columns.Count);
// Синий
//tbl.SetTableStyle(SLTableStyleTypeValues.Medium2);
// Зеленый
//tbl.SetTableStyle(SLTableStyleTypeValues.Medium4);
// Красный
//tbl.SetTableStyle(SLTableStyleTypeValues.Medium3);
tbl.Sort(1, false);
sl.InsertTable(tbl);
}
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog()
{
Filter = "XLS Format|*.xlsx",
FileName = defaultName + " " + GetCurrentDate() + ".xlsx",
Title = "Экспорт ... "
};
DialogResult dResult = saveFileDialog1.ShowDialog();
if (dResult == DialogResult.OK)
{
sl.SaveAs(saveFileDialog1.FileName);
Process.Start(saveFileDialog1.FileName);
}
}
private void OpenImageToXLSReporteGrafico(List <EstadoCitaCortoDTO> EstadosCitas, byte[] Contenido, string Excel)
{
using (var sl = new SLDocument())
{
var pic = new SLPicture(Contenido, ImagePartType.Png);
pic.SetPosition(3, 2);
sl.InsertPicture(pic);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Grafica UADyCS");
sl.AddWorksheet("Tabla Reporte");
sl.SelectWorksheet("Tabla Reporte");
int filaInicio = 6;
int numeroColumnaInicio = 4;
int numeroColumnaCuerpo = numeroColumnaInicio;
int numeroFila = filaInicio;
sl.SetCellValue(numeroFila - 2, numeroColumnaInicio, "Reporte UADyCS");
sl.SetCellValue(numeroFila, numeroColumnaInicio, "UADyCS");
EstadosCitas.ForEach(fila =>
{
numeroColumnaCuerpo = numeroColumnaInicio + 1;
if (numeroFila == filaInicio)
{
fila.Valores.ForEach(estado => {//Descripcion del estado=> solicitado,atendido,..
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item1); numeroColumnaCuerpo++;
});
}
numeroFila++;
//Nombre de la UADyCS
sl.SetCellValue(numeroFila, numeroColumnaInicio, fila.Descripcion);
numeroColumnaCuerpo = numeroColumnaInicio;
fila.Valores.ForEach(estado => {
numeroColumnaCuerpo++;
//Cantidad del estado
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item2);
});
});
SLTable tbl = sl.CreateTable(filaInicio, numeroColumnaInicio, numeroFila, numeroColumnaCuerpo);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium9);
sl.InsertTable(tbl);
sl.AutoFitColumn(numeroColumnaInicio, numeroColumnaCuerpo);
sl.SaveAs(Excel);
sl.Dispose();
}
}
public static void ExcelExport(ObjectListView olv, string defaultName)
{
SLDocument sl = new SLDocument();
SLStyle style = sl.CreateStyle();
for (int i = 1; i <= olv.Columns.Count; ++i)
{
sl.SetCellValue(1, i, olv.Columns[i - 1].Text);
}
for (int i = 1; i <= olv.Columns.Count; ++i)
{
for (int j = 1; j <= olv.Items.Count; ++j)
{
string cellVal = olv.Items[j - 1].SubItems[i - 1].Text;
int cellValNumeric = -1;
if (int.TryParse(cellVal, out cellValNumeric))
{
sl.SetCellValue(j + 1, i, cellValNumeric);
}
else
{
sl.SetCellValue(j + 1, i, cellVal);
}
/*if (StgGetInt("ExStyle") == 0)
* {
* System.Drawing.Color backColor = olv.Items[j - 1].BackColor;
* System.Drawing.Color foreColor = olv.Items[j - 1].ForeColor;
* style.Fill.SetPattern(PatternValues.Solid, backColor, foreColor);
*
* sl.SetCellStyle(j + 1, i, style);
* }*/
}
}
SLTable tbl = sl.CreateTable(1, 1, olv.Items.Count + 1, olv.Columns.Count);
// Синий
tbl.SetTableStyle(SLTableStyleTypeValues.Medium2);
// Зеленый
// tbl.SetTableStyle(SLTableStyleTypeValues.Medium4);
// Красный
// tbl.SetTableStyle(SLTableStyleTypeValues.Medium3);
// Никакой
tbl.Sort(1, false);
sl.InsertTable(tbl);
string filePath = Path.GetDirectoryName(Application.ExecutablePath) + "\\" + defaultName + ".xlsx";
sl.SaveAs(filePath);
Process.Start(filePath);
}
public void Save(string filePath)
{
DataTable tempTable = new DataTable();
for (int i = 0; i < this.Columns.Count; i++)
{
tempTable.Columns.Add(this.Columns[i].ColumnName);
}
for (int i = 0; i < this.Rows.Count; i++)
{
DataRow row = tempTable.NewRow();
for (int j = 0; j < this.Columns.Count; j++)
{
row[j] = this.Rows[i][j].ToString();
}
tempTable.Rows.Add(row);
}
SLDocument sL = new SLDocument();
int iStartRowIndex = 1;
int iStartColumnIndex = 1;
sL.ImportDataTable(iStartRowIndex, iStartColumnIndex, tempTable, true);
SLStyle style = sL.CreateStyle();
int iEndRowIndex = iStartRowIndex + tempTable.Rows.Count;
int iEndColumnIndex = iStartColumnIndex + tempTable.Columns.Count - 1;
sL.AutoFitColumn(iStartColumnIndex, iEndColumnIndex, 500);
SLTable table = sL.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Medium17);
sL.InsertTable(table);
try
{
sL.SaveAs(filePath);
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
}
internal void FormatColumns <T>(List <PropertyInfo> properties, IEnumerable <T> records, bool makeTable, IEnumerable <int> hideColumns, DisplayNoWrap[] wrapAttributes, DisplayWidth[] widthAttributes)
{
int skippedColumns = 0;
int columnIndex = 0;
for (int k = 0; k < properties.Count; k++)
{
if (!hideColumns.Contains(k))
{
SLStyle style = new SLStyle();
if (wrapAttributes[k] != null)
{
style.SetWrapText(false);
}
else
{
style.SetWrapText(true);
}
var widthAttribute = widthAttributes[k];
if (widthAttribute != null)
{
doc.SetColumnWidth(columnIndex + 1, widthAttribute.Width);
}
doc.SetColumnStyle(columnIndex + 1, style);
columnIndex++;
}
else
{
skippedColumns++;
}
}
if (makeTable)
{
SLTable tbl = doc.CreateTable(1, 1, records.Count() + 1, properties.Count() - skippedColumns);
tbl.SetTableStyle(SLTableStyleTypeValues.Light1);
doc.InsertTable(tbl);
}
}
public void NilaiToExcel(string status, string kodeKelas, string kodeSemester)
{
killExcelProcess();
Stopwatch sw = Stopwatch.StartNew();
string path = "Temp\\Data Nilai";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SLDocument sl = new SLDocument();
dbTabel = "detailmapelkelas INNER JOIN mapel USING (kode_mapel) INNER JOIN guru USING (id_guru)";
field = "kode_mapel as 'Kode Mapel', nama_guru as 'Nama Guru', mata_pelajaran as 'Mata Pelajaran', " +
"kategori_mapel as 'Kategori', nama_guru as 'Pengajar'";
cond = "kode_kelas = '" + kodeKelas + "'";
dt = db.GetDataTable(field, dbTabel, cond);
foreach (DataRow row in dt.Rows)
{
DataTable result = new DataTable();
result.Columns.Add("No.", typeof(int));
result.Columns.Add("No. Induk", typeof(string)); result.Columns.Add("Nama Siswa", typeof(string));
result.Columns.Add("Skala (P)", typeof(string)); result.Columns.Add("Angka (P)", typeof(string));
result.Columns.Add("Predikat (P)", typeof(string)); result.Columns.Add("Deskripsi (P)", typeof(string));
result.Columns.Add("Skala (K)", typeof(string)); result.Columns.Add("Angka (K)", typeof(string));
result.Columns.Add("Predikat (K)", typeof(string)); result.Columns.Add("Deskripsi (K)", typeof(string));
result.Columns.Add("Skala (S)", typeof(string)); result.Columns.Add("SB/B/C/K", typeof(string));
result.Columns.Add("Deskripsi (S)", typeof(string));
dbTabel = "nilai INNER JOIN siswa USING (nis_siswa) INNER JOIN mapel USING (kode_mapel) INNER JOIN kelas USING (kode_kelas)";
field = "nis_siswa as 'NIS Siswa', nama_siswa as 'Nama Siswa', p_skala as 'Skala (P)', p_ang as 'Angka (P)', " +
"p_pred as 'Predikat (P)', p_desk as 'Deskripsi (P)', k_skala as 'Skala (K)', k_ang as 'Angka (K)', " +
"k_pred as 'Predikat (K)', k_desk as 'Deskripsi (K)', s_skala as 'Skala (S)', s_sikap as 'SB/B/C/K', " +
"s_desk as 'Deskripsi (S)'";
cond = "status_siswa != '" + status + "' AND tahun_ajaran = '" + getTahun + "' AND kode_semester = '" + kodeSemester +
"' AND kode_kelas = '" + kodeKelas + "' AND kode_mapel ='" + row["Kode Mapel"].ToString() + "'";
query = "SELECT " + field + " FROM " + dbTabel + " WHERE " + cond;
myComm = new MySqlCommand(query, myConn);
myConn.Open();
myReader = myComm.ExecuteReader();
i = 1;
while (myReader.Read())
{
result.Rows.Add(new object[]
{
i.ToString(), myReader.GetString("NIS Siswa"), myReader.GetString("Nama Siswa"),
myReader.GetString("Skala (P)"), myReader.GetString("Angka (P)"),
myReader.GetString("Predikat (P)"), myReader.GetString("Deskripsi (P)"),
myReader.GetString("Skala (K)"), myReader.GetString("Angka (K)"),
myReader.GetString("Predikat (K)"), myReader.GetString("Deskripsi (K)"),
myReader.GetString("Skala (S)"), myReader.GetString("SB/B/C/K"),
myReader.GetString("Deskripsi (P)")
});
i++;
}
myConn.Close();
sl.AddWorksheet(row["Kode Mapel"].ToString());
int iStartRowIndex = 9;
int iStartColumnIndex = 1;
sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, result, true);
style = sl.CreateStyle();
int iEndRowIndex = iStartRowIndex + result.Rows.Count + 1 - 1;
int iEndColumnIndex = iStartColumnIndex + result.Columns.Count - 1;
table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Light10);
sl.InsertTable(table);
int iStartColumnCount = 1;
int iStartRowCount = 1;
int iEndColumnCount = result.Columns.Count;
int iEndRowCount = result.Rows.Count;
style = sl.CreateStyle();
style.Font.FontName = "Times New Roman";
style.Font.FontSize = 12;
sl.AutoFitColumn(iStartColumnCount, iEndColumnCount);
sl.SetColumnStyle(iStartColumnCount, iEndColumnCount, style);
query = "Select nama_kelas from kelas where kode_kelas = '" + kodeKelas + "'";
myConn.Open();
myComm = new MySqlCommand(query, myConn);
myReader = myComm.ExecuteReader();
while (myReader.Read())
{
nama_kelas = myReader.GetString("nama_kelas");
}
myConn.Close();
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Center;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 18;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(1, 1, 1, 13);
sl.SetCellValue(1, 1, "DATA NILAI SISWA KELAS " + nama_kelas);
sl.SetCellStyle(1, 1, style);
sl.MergeWorksheetCells(2, 1, 2, 13);
sl.SetCellValue(2, 1, "TAHUN AJARAN " + getTahun);
sl.SetCellStyle(2, 1, style);
sl.MergeWorksheetCells(3, 1, 3, 7);
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Left;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 12;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(4, 1, 4, 2);
sl.SetCellValue(4, 1, "Pengajar");
sl.SetCellStyle(4, 1, style);
sl.MergeWorksheetCells(4, 3, 4, 7);
sl.SetCellValue(4, 3, row["Pengajar"].ToString());
sl.SetCellStyle(4, 3, style);
sl.MergeWorksheetCells(5, 1, 5, 2);
sl.SetCellValue(5, 1, "Mata Pelajaran");
sl.SetCellStyle(5, 1, style);
sl.MergeWorksheetCells(5, 3, 5, 7);
sl.SetCellValue(5, 3, row["Mata Pelajaran"].ToString());
sl.SetCellStyle(5, 3, style);
sl.MergeWorksheetCells(6, 1, 6, 2);
sl.SetCellValue(6, 1, "Kategori");
sl.SetCellStyle(6, 1, style);
sl.MergeWorksheetCells(6, 3, 6, 4);
sl.SetCellValue(6, 3, row["Kategori"].ToString());
sl.SetCellStyle(6, 3, style);
sl.MergeWorksheetCells(7, 1, 7, 2);
sl.SetCellValue(7, 1, "Tahun Ajaran");
sl.SetCellStyle(7, 1, style);
sl.MergeWorksheetCells(7, 3, 7, 4);
sl.SetCellValue(7, 3, getTahun);
sl.SetCellStyle(7, 3, style);
}
sl.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
filename = "Data Nilai Kelas " + nama_kelas + " [" + kodeSemester + "-" + getTahun.ToString().Replace("/", "-") + "].xlsx";
sl.SaveAs(appRootdir() + "\\" + path + "\\" + filename);
sw.Stop();
MessageBox.Show("Selesai dalam: " + Convert.ToInt16(sw.Elapsed.TotalSeconds) + " detik");
}
public void KelasToExcel(string status)
{
killExcelProcess();
Stopwatch sw = Stopwatch.StartNew();
string path = "Temp\\Data Kelas";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SLDocument sl = new SLDocument();
dbTabel = "kelas INNER JOIN guru USING (id_guru)";
field = "kode_kelas as 'Kode Kelas', nama_kelas as 'Nama Kelas', nama_guru as 'Wali Kelas'";
cond = "status_kelas= '" + status + "' AND tahun_ajaran = '" + getTahun + "'";
dt = db.GetDataTable(field, dbTabel, cond);
foreach (DataRow row in dt.Rows)
{
DataTable result = new DataTable();
result.Columns.Add("No", typeof(string));
result.Columns.Add("No. Induk", typeof(string));
result.Columns.Add("NISN", typeof(string));
result.Columns.Add("Nama Siswa", typeof(string));
result.Columns.Add("Tanggal Lahir", typeof(string));
result.Columns.Add("No. Telp. Siswa", typeof(string));
result.Columns.Add("Nama Ayah", typeof(string));
result.Columns.Add("Alamat Siswa", typeof(string));
dbTabel = "detailkelassiswa INNER JOIN siswa USING (nis_siswa) INNER JOIN orangtua USING (nis_siswa)";
field = "detailkelassiswa.nis_siswa as 'NIS', nisn_siswa as 'NISN', nama_siswa as 'Nama Siswa', " +
"tanggal_lahir as 'Tanggal Lahir', siswa.no_telp as 'No. Telp. Siswa', nama_ayah as 'Nama Ayah', alamat as 'Alamat Siswa'";
cond = "detailkelassiswa.kode_kelas= '" + row["Kode Kelas"].ToString() +
"' AND (keterangan = 'Data Siswa' OR keterangan = 'Data Kelas')";
query = "SELECT " + field + " FROM " + dbTabel + " WHERE " + cond;
myComm = new MySqlCommand(query, myConn);
myConn.Open();
myReader = myComm.ExecuteReader();
i = 1;
while (myReader.Read())
{
result.Rows.Add(new object[] {
i.ToString(), myReader.GetString("NIS"), myReader.GetString("NISN"),
myReader.GetString("Nama Siswa"), myReader.GetString("Tanggal Lahir"),
myReader.GetString("No. Telp. Siswa"), myReader.GetString("Nama Ayah"),
myReader.GetString("Alamat Siswa")
});
i++;
}
myConn.Close();
sl.AddWorksheet(row["Nama Kelas"].ToString());
int iStartRowIndex = 7;
int iStartColumnIndex = 1;
sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, result, true);
style = sl.CreateStyle();
int iEndRowIndex = iStartRowIndex + result.Rows.Count + 1 - 1;
int iEndColumnIndex = iStartColumnIndex + result.Columns.Count - 1;
table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Light10);
sl.InsertTable(table);
int iStartColumnCount = 1;
int iStartRowCount = 1;
int iEndColumnCount = result.Columns.Count;
int iEndRowCount = result.Rows.Count;
style = sl.CreateStyle();
style.Font.FontName = "Times New Roman";
style.Font.FontSize = 12;
sl.AutoFitColumn(iStartColumnCount, iEndColumnCount);
sl.SetColumnStyle(iStartColumnCount, iEndColumnCount, style);
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Center;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 18;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(1, 1, 1, 7);
sl.SetCellValue(1, 1, "DATA KELAS SMA NEGERI 1 JAMPANGKULON");
sl.SetCellStyle(1, 1, style);
sl.MergeWorksheetCells(2, 1, 2, 7);
sl.SetCellValue(2, 1, "TAHUN AJARAN " + getTahun);
sl.SetCellStyle(2, 1, style);
sl.MergeWorksheetCells(3, 1, 3, 7);
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Left;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 12;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(4, 1, 4, 2);
sl.SetCellValue(4, 1, "Nama Kelas");
sl.SetCellStyle(4, 1, style);
sl.MergeWorksheetCells(4, 3, 4, 4);
sl.SetCellValue(4, 3, row["Nama Kelas"].ToString());
sl.SetCellStyle(4, 3, style);
sl.MergeWorksheetCells(5, 1, 5, 2);
sl.SetCellValue(5, 1, "Wali Kelas");
sl.SetCellStyle(5, 1, style);
sl.MergeWorksheetCells(5, 3, 5, 4);
sl.SetCellValue(5, 3, row["Wali Kelas"].ToString());
sl.SetCellStyle(5, 3, style);
sl.MergeWorksheetCells(6, 1, 6, 2);
sl.SetCellValue(6, 1, "Tahun Ajaran");
sl.SetCellStyle(6, 1, style);
sl.MergeWorksheetCells(6, 3, 6, 4);
sl.SetCellValue(6, 3, getTahun);
sl.SetCellStyle(6, 3, style);
}
sl.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
filename = "Data Kelas [" + getTahun.ToString().Replace("/", "-") + "].xlsx";
sl.SaveAs(appRootdir() + "\\" + path + "\\" + filename);
sw.Stop();
MessageBox.Show("Selesai dalam: " + Convert.ToInt16(sw.Elapsed.TotalSeconds) + " detik");
}
public void GuruToExcel(string status)
{
killExcelProcess();
Stopwatch sw = Stopwatch.StartNew();
string path = "Temp\\Data Guru";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SLDocument sl = new SLDocument();
sl.AddWorksheet(getTahun.ToString().Replace("/", "-"));
sl.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
dt = new DataTable();
dt.Columns.Add("No", typeof(string));
dt.Columns.Add("Nama Guru", typeof(string));
dt.Columns.Add("NIP", typeof(string));
dt.Columns.Add("NUPTK", typeof(string));
dt.Columns.Add("Keterangan", typeof(string));
field = "nama_guru as 'Nama Guru', nip as 'NIP', nuptk as 'NUPTK', keterangan as 'Keterangan'";
dbTabel = "guru";
cond = "status_guru = '" + status + "' ORDER BY nip, nama_guru ASC";
query = "SELECT " + field + " FROM " + dbTabel + " WHERE " + cond;
myComm = new MySqlCommand(query, myConn);
myConn.Open();
myReader = myComm.ExecuteReader();
i = 1;
while (myReader.Read())
{
dt.Rows.Add(new object[] {
i.ToString(), myReader.GetString("Nama Guru"), myReader.GetString("NIP"),
myReader.GetString("NUPTK"), myReader.GetString("Keterangan")
});
i++;
}
myConn.Close();
int iStartRowIndex = 4;
int iStartColumnIndex = 1;
sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dt, true);
style = sl.CreateStyle();
int iEndRowIndex = iStartRowIndex + dt.Rows.Count + 1 - 1;
int iEndColumnIndex = iStartColumnIndex + dt.Columns.Count - 1;
table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Light10);
sl.InsertTable(table);
int iStartColumnCount = 0;
int iEndColumnCount = dt.Columns.Count;
int iEndRowCount = dt.Rows.Count;
style = sl.CreateStyle();
style.Font.FontName = "Times New Roman";
style.Font.FontSize = 12;
sl.AutoFitColumn(iStartColumnCount, iEndColumnCount);
sl.SetColumnStyle(iStartColumnCount, iEndColumnCount, style);
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Center;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 18;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(1, 1, 1, 5);
sl.SetCellValue(1, 1, "DATA GURU SMA NEGERI 1 JAMPANGKULON");
sl.SetCellStyle(1, 1, style);
sl.MergeWorksheetCells(2, 1, 2, 5);
sl.SetCellValue(2, 1, "TAHUN AJARAN " + getTahun);
sl.SetCellStyle(2, 1, style);
filename = "Data Guru [" + getTahun.ToString().Replace("/", "-") + "].xlsx";
sl.SaveAs(appRootdir() + "\\" + path + "\\" + filename);
sw.Stop();
MessageBox.Show("Selesai dalam: " + Convert.ToInt16(sw.Elapsed.TotalSeconds) + " detik");
}
public void SiswaToExcel(string status)
{
killExcelProcess();
Stopwatch sw = Stopwatch.StartNew();
string path = "Temp\\Data Siswa";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SLDocument sl = new SLDocument();
sl.AddWorksheet(getTahun.ToString().Replace("/", "-"));
sl.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
dt = new DataTable();
dt.Columns.Add("No", typeof(int)); dt.Columns.Add("Nama Siswa", typeof(string));
dt.Columns.Add("NIS", typeof(string)); dt.Columns.Add("NISN", typeof(string));
dt.Columns.Add("Tempat Lahir", typeof(string)); dt.Columns.Add("Tanggal Lahir", typeof(string));
dt.Columns.Add("Jenis Kelamin", typeof(string)); dt.Columns.Add("Agama", typeof(string));
dt.Columns.Add("Status", typeof(string)); dt.Columns.Add("Anak ke-", typeof(int));
dt.Columns.Add("Alamat Siswa", typeof(string)); dt.Columns.Add("No. Telp. Rumah", typeof(string));
dt.Columns.Add("Sekolah Asal", typeof(string)); dt.Columns.Add("Diterima di Kelas", typeof(string));
dt.Columns.Add("Diterima Tanggal", typeof(string)); dt.Columns.Add("Nama Ayah", typeof(string));
dt.Columns.Add("Nama Ibu", typeof(string)); dt.Columns.Add("Alamat Orangtua", typeof(string));
dt.Columns.Add("No. Telp.", typeof(string)); dt.Columns.Add("Pekerjaan Ayah", typeof(string));
dt.Columns.Add("Pekerjaan Ibu", typeof(string)); dt.Columns.Add("Nama Wali", typeof(string));
dt.Columns.Add("Alamat Wali", typeof(string)); dt.Columns.Add("Pekerjaan Wali", typeof(string));
myConn.Open();
query = "SELECT siswa.nis_siswa as 'NIS', nisn_siswa as 'NISN', nama_siswa as 'Nama Siswa', tempat_lahir as " +
"'Tempat Lahir', tanggal_lahir as 'Tanggal Lahir', jenis_kelamin as 'Jenis Kelamin', kelas.nama_kelas " +
"as 'Diterima di Kelas', status_keluarga as 'Status Anak', anak_ke as 'Anak Ke-', agama as 'Agama', " +
"siswa.no_telp as 'No. Telp. Siswa', alamat as 'Alamat Siswa', asal_sekolah as 'Asal Sekolah', " +
"tanggal_masuk as 'Diterima Tanggal', nama_ayah as 'Nama Ayah', nama_ibu as 'Nama Ibu', " +
"pekerjaan_ayah as 'Pekerjaan Ayah', pekerjaan_ibu as 'Pekerjaan Ibu', orangtua.no_telp as " +
"'No. Telp. Ortu', alamat_ortu as 'Alamat Ortu', nama_wali as 'Nama Wali', pekerjaan_wali as " +
"'Pekerjaan Wali', alamat_wali as 'Alamat Wali' FROM detailkelassiswa INNER JOIN siswa ON " +
"siswa.nis_siswa = detailkelassiswa.nis_siswa INNER JOIN orangtua ON siswa.nis_siswa = orangtua.nis_siswa " +
"INNER JOIN kelas ON detailkelassiswa.kode_kelas = kelas.kode_kelas WHERE tahun_ajaran = '" + getTahun +
"' AND (detailkelassiswa.keterangan = 'Data Siswa' OR detailkelassiswa.keterangan = " +
"'Data Kelas') AND status_siswa = '" + status + "' ORDER BY siswa.nis_siswa";
myComm = new MySqlCommand(query, myConn);
myReader = myComm.ExecuteReader();
i = 1;
while (myReader.Read())
{
string diterima = myReader.GetString("Diterima Tanggal"); string tanggal_diterima = diterima.Substring(0, 2);
string bulan_diterima = diterima.Substring(3, 2); string tahun_diterima = diterima.Substring(6, 4);
string diterima_bulan = getBulan(bulan_diterima);
string lahir = myReader.GetString("Tanggal Lahir"); string tanggal_lahir = lahir.Substring(0, 2);
string bulan_lahir = lahir.Substring(3, 2); string tahun_lahir = lahir.Substring(6, 4);
string lahir_bulan = getBulan(bulan_lahir);
getLahir = tanggal_lahir + " " + lahir_bulan + " " + tahun_lahir;
getDiterima = tanggal_diterima + " " + diterima_bulan + " " + tahun_diterima;
dt.Rows.Add(new object[]
{ i,
myReader.GetString("Nama Siswa"), myReader.GetString("NIS"), myReader.GetString("NISN"),
myReader.GetString("Tempat Lahir"), getLahir, myReader.GetString("Jenis Kelamin"),
myReader.GetString("Agama"), myReader.GetString("Status Anak"), myReader.GetString("Anak ke-"),
myReader.GetString("Alamat Siswa"), myReader.GetString("No. Telp. Siswa"), myReader.GetString("Asal Sekolah"),
myReader.GetString("Diterima di Kelas"), getDiterima, myReader.GetString("Nama Ayah"),
myReader.GetString("Nama Ibu"), myReader.GetString("Alamat Ortu"), myReader.GetString("No. Telp. Ortu"),
myReader.GetString("Pekerjaan Ayah"), myReader.GetString("Pekerjaan Ibu"), myReader.GetString("Nama Wali"),
myReader.GetString("Alamat Wali"), myReader.GetString("Pekerjaan Wali") });
i++;
}
myConn.Close();
int iStartRowIndex = 4;
int iStartColumnIndex = 1;
sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dt, true);
style = sl.CreateStyle();
int iEndRowIndex = iStartRowIndex + dt.Rows.Count + 1 - 1;
int iEndColumnIndex = iStartColumnIndex + dt.Columns.Count - 1;
table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Light10);
sl.InsertTable(table);
int iStartCount = 1;
int iEndCount = dt.Columns.Count;
style = sl.CreateStyle();
style.Font.FontName = "Times New Roman";
style.Font.FontSize = 12;
sl.AutoFitColumn(iStartCount, iEndCount);
sl.SetColumnStyle(iStartCount, iEndCount, style);
style = sl.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Center;
style.Alignment.JustifyLastLine = true;
style.Alignment.ShrinkToFit = true;
style.Font.FontSize = 18;
style.Font.Bold = true;
style.Font.FontName = "Times New Roman";
sl.MergeWorksheetCells(1, 1, 1, 10);
sl.SetCellValue(1, 1, "DATA SISWA SMA NEGERI 1 JAMPANGKULON");
sl.SetCellStyle(1, 1, style);
sl.MergeWorksheetCells(2, 1, 2, 10);
sl.SetCellValue(2, 1, "TAHUN AJARAN " + getTahun);
sl.SetCellStyle(2, 1, style);
filename = "Data Siswa [" + getTahun.ToString().Replace("/", "-") + "].xlsx";
sl.SaveAs(appRootdir() + "\\" + path + "\\" + filename);
sw.Stop();
MessageBox.Show("Selesai dalam: " + Convert.ToInt16(sw.Elapsed.TotalSeconds) + " detik");
}
static void Main(string[] args)
{
Random rand = new Random();
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("IP Address", typeof(string));
dt.Columns.Add("Date (UTC)", typeof(DateTime));
dt.Columns.Add("Size (MB)", typeof(double));
dt.Columns.Add("Cost", typeof(decimal));
for (int i = 0; i < 10; ++i)
{
dt.Rows.Add(string.Format("Prod{0}", rand.Next(5)),
string.Format("{0}.{1}.{2}.{3}", rand.Next(256), rand.Next(256), rand.Next(256), rand.Next(256)),
DateTime.UtcNow.AddDays(rand.NextDouble() * 20),
decimal.Round((decimal)(rand.NextDouble() * 500 + 200), 4),
decimal.Round((decimal)(rand.NextDouble() * 20 + 5), 2));
}
SLDocument sl = new SLDocument();
int iStartRowIndex = 3;
int iStartColumnIndex = 2;
sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dt, true);
// This part sets the style, but you might be using a template file,
// so the styles are probably already set.
SLStyle style = sl.CreateStyle();
style.FormatCode = "yyyy/mm/dd hh:mm:ss";
sl.SetColumnStyle(4, style);
style.FormatCode = "#,##0.0000";
sl.SetColumnStyle(5, style);
style.FormatCode = "$#,##0.00";
sl.SetColumnStyle(6, style);
// The next part is optional, but it shows how you can set a table on your
// data based on your DataTable's dimensions.
// + 1 because the header row is included
// - 1 because it's a counting thing, because the start row is counted.
int iEndRowIndex = iStartRowIndex + dt.Rows.Count + 1 - 1;
// - 1 because it's a counting thing, because the start column is counted.
int iEndColumnIndex = iStartColumnIndex + dt.Columns.Count - 1;
SLTable table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
table.SetTableStyle(SLTableStyleTypeValues.Medium17);
table.HasTotalRow = true;
table.SetTotalRowFunction(5, SLTotalsRowFunctionValues.Sum);
sl.InsertTable(table);
sl.SaveAs("ImportDataTable.xlsx");
Console.WriteLine("End of program");
Console.ReadLine();
}
public void ShouldInsertTable()
{
SLDocument sl = new SLDocument();
int i, j;
for (i = 2; i <= 12; ++i)
{
for (j = 2; j <= 6; ++j)
{
if (i == 2)
{
sl.SetCellValue(i, j, string.Format("Col{0}", j));
}
else
{
sl.SetCellValue(i, j, i * j);
}
}
}
// tabular data ranges from B2:F12, inclusive of a header row
SLTable tbl = sl.CreateTable("B2", "F12");
tbl.SetTableStyle(SLTableStyleTypeValues.Medium9);
sl.InsertTable(tbl);
for (i = 2; i <= 12; ++i)
{
for (j = 9; j <= 15; ++j)
{
if (i == 2)
{
sl.SetCellValue(i, j, string.Format("Col{0}", j));
}
else
{
sl.SetCellValue(i, j, i * j);
}
}
}
tbl = sl.CreateTable("I2", "O12");
tbl.HasTotalRow = true;
// 1st table column, column I
tbl.SetTotalRowLabel(1, "Totals");
// 7th table column, column O
tbl.SetTotalRowFunction(7, SLTotalsRowFunctionValues.Sum);
tbl.SetTableStyle(SLTableStyleTypeValues.Dark4);
tbl.HasBandedColumns = true;
tbl.HasBandedRows = true;
tbl.HasFirstColumnStyled = true;
tbl.HasLastColumnStyled = true;
// sort by the 3rd table column (column K) in descending order
tbl.Sort(3, false);
sl.InsertTable(tbl);
sl.SaveAs("Tables.xlsx");
}
/// <summary>
/// Se exporta la informacion a un excel.
/// </summary>
/// <returns>Devuelve un booleano, true = se exporto correctamente, false = fallo al exportar el documento.</returns>
public bool ExportarExcel()
{
SLDocument slDocumento = new SLDocument();
SLStyle slEstilo = new SLStyle();
int indxFila = 2;
slEstilo.Font.FontSize = 12;
slEstilo.Font.Bold = true;
using (SistemaPlanillaEntities PE = new SistemaPlanillaEntities())
{
var col = (from C in PE.Colaborador
select new expColaborador
{
ID_Colaborador = C.ID_Colaborador,
Nombre = C.Nombre,
Genero = C.Genero,
Edad = C.Edad,
Fecha_Nacimiento = C.Fecha_Nacimiento,
Fecha_Ingreso = C.Fecha_Ingreso,
ID_Area = C.ID_Area,
ID_Puesto = C.ID_Puesto
}).ToList();
var usu = (from U in PE.Usuario
select new expUsuario
{
ID_Usuario = U.ID_Usuario,
ID_Colaborador = U.ID_Colaborador,
Privilegios = U.Privilegios
}).ToList();
var are = (from A in PE.Area
select new expArea
{
ID_Area = A.ID_Area,
Descripcion = A.Descripcion
}).ToList();
var pue = (from P in PE.Puesto
select new expPuesto
{
ID_Puesto = P.ID_Puesto,
Descripcion = P.Descripcion
}).ToList();
slDocumento.SetCellValue(1, 1, "ID_Colaborador");
slDocumento.SetCellValue(1, 2, "Nombre");
slDocumento.SetCellValue(1, 3, "Genero");
slDocumento.SetCellValue(1, 4, "Edad");
slDocumento.SetCellValue(1, 5, "Fecha_Nacimiento");
slDocumento.SetCellValue(1, 6, "Fecha_Ingreso");
slDocumento.SetCellValue(1, 7, "Area");
slDocumento.SetCellValue(1, 8, "Puesto");
foreach (expColaborador colaborador in col)
{
slDocumento.SetCellValue(indxFila, 1, colaborador.ID_Colaborador);
slDocumento.SetCellValue(indxFila, 2, colaborador.Nombre);
slDocumento.SetCellValue(indxFila, 3, colaborador.Genero);
slDocumento.SetCellValue(indxFila, 4, colaborador.Edad);
slDocumento.SetCellValue(indxFila, 5, colaborador.Fecha_Nacimiento);
slDocumento.SetCellValue(indxFila, 6, colaborador.Fecha_Ingreso);
slDocumento.SetCellValue(indxFila, 7, colaborador.ID_Area);
slDocumento.SetCellValue(indxFila, 8, colaborador.ID_Puesto);
indxFila++;
}
indxFila--;
SLTable tbl = slDocumento.CreateTable("A1", "H" + indxFila);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium1);
slDocumento.InsertTable(tbl);
slDocumento.SetCellValue(1, 10, "ID_Usuario");
slDocumento.SetCellValue(1, 11, "ID_Colaborador");
slDocumento.SetCellValue(1, 12, "Contrasenna");
slDocumento.SetCellValue(1, 13, "Privilegios");
indxFila = 2;
foreach (expUsuario usuario in usu)
{
slDocumento.SetCellValue(indxFila, 10, usuario.ID_Usuario);
slDocumento.SetCellValue(indxFila, 11, usuario.ID_Colaborador);
slDocumento.SetCellValue(indxFila, 12, "*****");
slDocumento.SetCellValue(indxFila, 13, usuario.Privilegios);
indxFila++;
}
indxFila--;
tbl = slDocumento.CreateTable("J1", "M" + indxFila);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium1);
slDocumento.InsertTable(tbl);
slDocumento.SetCellValue(1, 15, "ID_Area");
slDocumento.SetCellValue(1, 16, "Descripcion");
indxFila = 2;
foreach (expArea area in are)
{
slDocumento.SetCellValue(indxFila, 15, area.ID_Area);
slDocumento.SetCellValue(indxFila, 16, area.Descripcion);
indxFila++;
}
indxFila--;
tbl = slDocumento.CreateTable("O1", "P" + indxFila);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium1);
slDocumento.InsertTable(tbl);
slDocumento.SetCellValue(1, 18, "ID_Puesto");
slDocumento.SetCellValue(1, 19, "Descripcion");
indxFila = 2;
foreach (expPuesto puesto in pue)
{
slDocumento.SetCellValue(indxFila, 18, puesto.ID_Puesto);
slDocumento.SetCellValue(indxFila, 19, puesto.Descripcion);
indxFila++;
}
indxFila--;
tbl = slDocumento.CreateTable("R1", "S" + indxFila);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium1);
slDocumento.InsertTable(tbl);
slDocumento.AutoFitColumn("A", "S");
string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\Administracion Municipalidad";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
slDocumento.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\Administracion Municipalidad\ListadoColaboradores.xlsx");
MessageBox.Show("Datos exportados exitosamente en '" + filePath + "ListadoColaboradores.xlsx'");
}
return(true);
}
