public void procExcel(string fileName, string schoolPicDir)
{
Sekolah sekolah = new Sekolah();
DataRekap dataRekap = new DataRekap();
Program pr = new Program();
try
{
IWorkbook workbook = null;
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
//First sheet
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
sekolah = pr.SetSekolah(sheet);
//pr.gemboxWriting(sekolah);
}
sheet = workbook.GetSheetAt(1);
if (sheet != null)
{
dataRekap = pr.setDataTable(sheet);
}
pr.gemboxWriting(sekolah, dataRekap);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private DataRekap setDataTable(ISheet sheet)
{
DataRekap dataRekap = new DataRekap();
dataRekap.uraian[0] = sheet.GetRow(6).GetCell(1).ToString().Trim();
dataRekap.uraian[1] = sheet.GetRow(7).GetCell(1).ToString().Trim();
dataRekap.guru[0] = sheet.GetRow(6).GetCell(2).ToString().Trim();
dataRekap.guru[1] = sheet.GetRow(7).GetCell(2).ToString().Trim();
dataRekap.tendik[0] = sheet.GetRow(6).GetCell(3).ToString().Trim();
dataRekap.tendik[1] = sheet.GetRow(7).GetCell(3).ToString().Trim();
dataRekap.ptk[0] = (int.Parse(dataRekap.guru[0]) + int.Parse(dataRekap.tendik[0])).ToString();
dataRekap.ptk[1] = (int.Parse(dataRekap.guru[1]) + int.Parse(dataRekap.tendik[1])).ToString();
dataRekap.pd[0] = sheet.GetRow(6).GetCell(5).ToString().Trim();
dataRekap.pd[1] = sheet.GetRow(7).GetCell(5).ToString().Trim();
dataRekap.uraianSarpras[0] = sheet.GetRow(17).GetCell(1).ToString().Trim();
dataRekap.uraianSarpras[1] = sheet.GetRow(18).GetCell(1).ToString().Trim();
dataRekap.uraianSarpras[2] = sheet.GetRow(19).GetCell(1).ToString().Trim();
dataRekap.jumlahSarpras[0] = sheet.GetRow(17).GetCell(2).ToString().Trim();
dataRekap.jumlahSarpras[1] = sheet.GetRow(18).GetCell(2).ToString().Trim();
dataRekap.jumlahSarpras[2] = sheet.GetRow(19).GetCell(2).ToString().Trim();
return(dataRekap);
}
public void gemboxWriting(Sekolah sekolah, DataRekap dataRekap)
{
string FILE_NAME = "SURAT LAPORAN " + sekolah.namaSekolah.ToUpper() + ".docx";
string HEADER_SURAT = "SURAT LAPORAN REKAPITULASI " + sekolah.namaSekolah.ToUpper();
string NAMA_SEKOLAH_SURAT = "NAMA SEKOLAH \t\t : \t" + sekolah.namaSekolah.ToUpper();
string NAMA_KEPALASKOLAH_SURAT = "NAMA KEPALA SEKOLAH \t : \t" + sekolah.namaKepalaSekolah.ToUpper();
string ALAMATSEKOLAH_SURAT = "ALAMAT SEKOLAH \t : \t" + sekolah.alamatSekolah.ToUpper();
string KELURAHAN_SURAT = "KELURAHAN \t\t : \t" + sekolah.kelurahan.ToUpper();
string KECAMATAN_SURAT = "KECAMATAN \t\t : \t" + sekolah.kecamatan.ToUpper();
string PROVINSI_SURAT = "PROVINSI \t\t : \t" + sekolah.provinsi.ToUpper();
// If using Professional version, put your serial key below.
//ComponentInfo.SetLicense("FREE-LIMITED-KEY");
ComponentInfo.SetLicense("DH5L-ED6Q-R7O0-DY0H");
// Create new empty document.
var document = new DocumentModel();
var largeFont = new CharacterStyle("Large Font")
{
CharacterFormat = { Size = 24 }
};
document.Styles.Add(largeFont);
// Add new section with two paragraphs, containing some text and symbols.
/*document.Sections.Add(
* new Section(document,
* new Paragraph(document,
* new Run(document, "SURAT REKAPITULASI SD NEGERI KEMBANG.") { CharacterFormat = { FontName = "Windings", Size = 24} },
* new SpecialCharacter(document, SpecialCharacterType.LineBreak),
* new Run(document, "\xFC" + "\xF0" + "\x32") { CharacterFormat = { FontName = "Wingdings", Size = 48 } }),
* new Paragraph(document, "DENGAN INI KAMI MENYATAKAN.")));*/
document.Sections.Add(
new Section(document,
new Paragraph(document,
new Run(document, HEADER_SURAT)
{
CharacterFormat = { Style = largeFont, Bold = true, Size = 26 }
})
{
ParagraphFormat =
{
Alignment = GemBox.Document.HorizontalAlignment.Center
}
},
new Paragraph(document, "Dengan surat ini ingin melaporkan data rekapitulasi yang bertanda tangan di bawah ini :"),
new Paragraph(document, NAMA_SEKOLAH_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document, NAMA_KEPALASKOLAH_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document, ALAMATSEKOLAH_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document, KELURAHAN_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document, KECAMATAN_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document, PROVINSI_SURAT)
{
ParagraphFormat =
{
LeftIndentation = 30,
RightIndentation = 20,
SpecialIndentation = 30
}
},
new Paragraph(document,
new Run(document, "A. Data PTK dan PD")
{
CharacterFormat = { Style = largeFont, Bold = true, Size = 14 }
})
{
ParagraphFormat =
{
Alignment = GemBox.Document.HorizontalAlignment.Left
}
}));
var section = new Section(document);
document.Sections.Add(section);
// Create a table with 100% width.
GemBox.Document.Tables.Table table = new GemBox.Document.Tables.Table(document);
table.TableFormat.PreferredWidth = new TableWidth(100, TableWidthUnit.Percentage);
section.Blocks.Add(table);
for (int r = 0; r < 3; r++)
{
// Create a row and add it to table.
var row = new TableRow(document);
table.Rows.Add(row);
Console.WriteLine("Nambah Row");
for (int c = 0; c < 6; c++)
{
// Create a cell and add it to row.
var cell = new TableCell(document);
row.Cells.Add(cell);
// Create a paragraph and add it to cell.
if (r == 0)
{
if (c == 0)
{
var paragraph = new Paragraph(document, "No");
cell.Blocks.Add(paragraph);
}
if (c == 1)
{
var paragraph = new Paragraph(document, "Uraian");
cell.Blocks.Add(paragraph);
}
if (c == 2)
{
var paragraph = new Paragraph(document, "Guru");
cell.Blocks.Add(paragraph);
}
if (c == 3)
{
var paragraph = new Paragraph(document, "Tendik");
cell.Blocks.Add(paragraph);
}
if (c == 4)
{
var paragraph = new Paragraph(document, "PTK");
cell.Blocks.Add(paragraph);
}
if (c == 5)
{
var paragraph = new Paragraph(document, "PD");
cell.Blocks.Add(paragraph);
}
}
else
{
if (c == 0)
{
var paragraph = new Paragraph(document, r.ToString());
cell.Blocks.Add(paragraph);
}
if (c == 1)
{
var paragraph = new Paragraph(document, dataRekap.uraian[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
if (c == 2)
{
var paragraph = new Paragraph(document, dataRekap.guru[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
if (c == 3)
{
var paragraph = new Paragraph(document, dataRekap.tendik[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
if (c == 4)
{
var paragraph = new Paragraph(document, dataRekap.ptk[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
if (c == 5)
{
var paragraph = new Paragraph(document, dataRekap.pd[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
}
}
}
document.Sections.Add(
new Section(document,
new Paragraph(document,
new Run(document, "B. Data Sarpras")
{
CharacterFormat = { Style = largeFont, Bold = true, Size = 14 }
})
{
ParagraphFormat =
{
Alignment = GemBox.Document.HorizontalAlignment.Left
}
}));
var sectionSapras = new Section(document);
document.Sections.Add(sectionSapras);
GemBox.Document.Tables.Table tableSapras = new GemBox.Document.Tables.Table(document);
tableSapras.TableFormat.PreferredWidth = new TableWidth(100, TableWidthUnit.Percentage);
sectionSapras.Blocks.Add(tableSapras);
for (int r = 0; r < 4; r++)
{
// Create a row and add it to table.
var row = new TableRow(document);
tableSapras.Rows.Add(row);
for (int c = 0; c < 3; c++)
{
// Create a cell and add it to row.
var cell = new TableCell(document);
row.Cells.Add(cell);
// Create a paragraph and add it to cell.
if (r == 0)
{
if (c == 0)
{
var paragraph = new Paragraph(document, "No");
cell.Blocks.Add(paragraph);
}
if (c == 1)
{
var paragraph = new Paragraph(document, "Uraian");
cell.Blocks.Add(paragraph);
}
if (c == 2)
{
var paragraph = new Paragraph(document, "Jumlah");
cell.Blocks.Add(paragraph);
}
}
else
{
if (c == 0)
{
var paragraph = new Paragraph(document, r.ToString());
cell.Blocks.Add(paragraph);
}
if (c == 1)
{
var paragraph = new Paragraph(document, dataRekap.uraianSarpras[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
if (c == 2)
{
var paragraph = new Paragraph(document, dataRekap.jumlahSarpras[r - 1].ToString());
cell.Blocks.Add(paragraph);
}
}
}
}
document.Sections.Add(
new Section(document,
new Paragraph(document,
new Run(document, "Demikian laporan ini dibuat agar dapat ditindaklanjuti")
{
CharacterFormat = { Style = largeFont, Bold = true, Size = 14 }
})
{
ParagraphFormat =
{
Alignment = GemBox.Document.HorizontalAlignment.Left
}
}));
// Save Word document to file's path.
Console.WriteLine("Write file");
document.Save(FILE_NAME);
Console.WriteLine("Sukses");
}
