示例#1
0
        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);
            }
        }
示例#2
0
        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);
        }
示例#3
0
        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");
        }