Esempio n. 1
0
 private void FrmSystemMainList_FormClosing(object sender, FormClosingEventArgs e)
 {
     try
     {
         var path = ClassPublicclass.GetTemporaryDirectory("DE_Reports");
         ClearFolder(@path + "\\DE_Reports");
         path = ClassPublicclass.GetTemporaryDirectory("INTG_graphs");
         ClearFolder(@path + "\\INTG_graphs");
     }
     catch (Exception)
     {
         // classPublicclass.showMessage(n.Message.ToString()," Close all open files");
     }
 }
Esempio n. 2
0
        public static void Generatereport(string title, string extension, string sql, string widthn)
        {
            var path = ClassPublicclass.GetTemporaryDirectory("DE_Reports");


            var rnd    = new Random();
            var surfix = rnd.Next(52);     // randomly generate a number and append to title to void error if title (filename) already exixts


            var docname = title + surfix.ToString();

            var Reports = "DE_Reports";

            GrantAccess(@path + "\\DE_Reports\\" + docname + "." + extension);
            File.Delete(@path + "\\DE_Reports\\" + docname + "." + extension);
            TextWriter tw = new StreamWriter(string.Format("{0}\\{1}\\{2}.{3}", path, Reports, docname, extension));

            tw.WriteLine(
                "<style type=\"text/css\" media=\"print\">  @page land { size: landscape; }</style><htm><header></header><body class=\"land\">");
            //tw.WriteLine("<p><img src=@"+ path +"\\INTG_Images\\clogo.bmp"+"/></p>");

            // close the stream
            //tw.Close();
            var companyNameLbl  = new Label();
            var comptellbl      = new Label();
            var mobilelbl       = new Label();
            var compaddlbl      = new Label();
            var companylogo1lbl = new Label();
            var websitelbl      = new Label();
            var emaillbl        = new Label();
            var faxlbl          = new Label();

            companyNameLbl.Text = "MY COMPANY";



            // Cursor.Current = Cursors.WaitCursor;
            var sbConnCOMBOx = new StringBuilder();

            sbConnCOMBOx.Append(ClassDatabaseConnection.cnn1);
            sbConnCOMBOx.Append(";Extended Properties=");
            sbConnCOMBOx.Append(Convert.ToChar(34));
            sbConnCOMBOx.Append(Convert.ToChar(34));
            var cnExcelCOMBOx = new OleDbConnection(sbConnCOMBOx.ToString());

            cnExcelCOMBOx.Open();
            var sbSQLCOMBOx = new StringBuilder();

            sbSQLCOMBOx.Append(sql);
            var cmdExcelCOMBOx = new OleDbCommand(sbSQLCOMBOx.ToString(), cnExcelCOMBOx);
            var drExcelCOMBOx  = cmdExcelCOMBOx.ExecuteReader();

            var contentTable = new PdfPTable(drExcelCOMBOx.FieldCount);
            var mycolspan    = 3;

            if (drExcelCOMBOx.FieldCount > 4)
            {
                mycolspan = drExcelCOMBOx.FieldCount - 2;
            }

            // header
            tw.WriteLine("<table><tr><td colspan=\"" + mycolspan + "\">" + companyNameLbl.Text +
                         " </td><td colspan=\"2\">" + DateTime.Now.Date.ToString(ClassMainclass.Dateformat) +
                         "  " + DateTime.Now.ToShortTimeString() + " </td></tr>");
            tw.WriteLine("<p>&nbsp;</p>");
            tw.WriteLine("<tr><td> Address " + compaddlbl.Text + " </td>");
            tw.WriteLine("<td> Telephone " + comptellbl.Text + " </td>");
            tw.WriteLine("<td> Website " + websitelbl.Text + " </td></tr>");
            tw.WriteLine("<tr><td> Mobile " + mobilelbl.Text + " </td>");
            tw.WriteLine("<td> Email " + emaillbl.Text + " </td>");
            tw.WriteLine("<td> Fax " + faxlbl.Text + " </td></tr></table>");
            tw.WriteLine("<p>&nbsp;</p>");
            tw.WriteLine("<p><b>" + title + "</b></p>");
            tw.WriteLine("<table border=\"1\" bordercolor=\"#003366\"  cellspacing=\"0\" width=\"100%\">");
            tw.WriteLine("<tr>");
            for (var i = 0; i < drExcelCOMBOx.FieldCount; i++)
            {
                if (drExcelCOMBOx.GetName(i).ToUpper() == "ID")
                {
                    tw.WriteLine("<td bgcolor=\"#003366\"><font color=\"#FFFFFF\"><b>No</b></font></td>");
                }
                else
                {
                    tw.WriteLine("<td bgcolor=\"#003366\"><font color=\"#FFFFFF\"><b>" +
                                 drExcelCOMBOx.GetName(i).ToString().Replace('_', ' ') + " </b></font></td>");
                }
            }

            tw.Write("</tr>");
            cnExcelCOMBOx.Close();
            var sbConnCOMBO = new StringBuilder();

            sbConnCOMBO.Append(ClassDatabaseConnection.cnn1);
            sbConnCOMBO.Append(";Extended Properties=");
            sbConnCOMBO.Append(Convert.ToChar(34));
            sbConnCOMBO.Append(Convert.ToChar(34));

            var cnExcelCOMBO = new OleDbConnection(sbConnCOMBO.ToString());

            cnExcelCOMBO.Open();
            var sbSQLCOMBO = new StringBuilder();

            sbSQLCOMBO.Append(sql);
            var cmdExcelCOMBO = new OleDbCommand(sbSQLCOMBO.ToString(), cnExcelCOMBO);
            var drExcelCOMBO  = cmdExcelCOMBO.ExecuteReader();

            while (drExcelCOMBO.Read())
            {
                tw.Write("<tr>");

                for (var K = 0; K < drExcelCOMBO.FieldCount; K++)
                {
                    tw.Write("<td>" + drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString() + "</d>");
                }

                tw.Write("</tr>");
            }

            cnExcelCOMBO.Close();


            tw.WriteLine("</table>");
            tw.WriteLine("</body></htm>");

            tw.Close();

            try
            {
                Process.Start(@path + "\\DE_Reports\\" + docname + "." + extension);
            }
            catch (Exception ex)
            {
                MessageBox.Show("The document may be open, please close it and try again" + ex.ToString());
            }



            // Microsoft.Office.Interop.Word.Document newDoc = new Microsoft.Office.Interop.Word.Document();
        }
Esempio n. 3
0
        // write on top of document
        public override void OnOpenDocument(PdfWriter writer, Document document)
        {
            var font       = new iTextSharp.text.Font();
            var boldfont   = new iTextSharp.text.Font();
            var fontclear  = new iTextSharp.text.Font();
            var fontfooter = new iTextSharp.text.Font();

            font       = FontFactory.GetFont(FontFactory.HELVETICA, 7, iTextSharp.text.Font.NORMAL);
            boldfont   = FontFactory.GetFont(FontFactory.HELVETICA, 9, iTextSharp.text.Font.BOLD);
            fontclear  = FontFactory.GetFont(FontFactory.HELVETICA, 9, iTextSharp.text.Font.ITALIC);
            fontfooter = FontFactory.GetFont(FontFactory.COURIER, 5, iTextSharp.text.Font.NORMAL);

            companyNameLbl.Text = "Company Name";

            path = ClassPublicclass.GetTemporaryDirectory("INTG_Images");

            iTextSharp.text.Image companylogo;

            try
            {
                companylogo = iTextSharp.text.Image.GetInstance(new Uri(path + "\\INTG_Images\\clogo.bmp"));
            }
            catch (Exception)
            {
                // companylogo = iTextSharp.text.Image.GetInstance(new Uri(Application.StartupPath + "\\Images\\clogo.bmp"));
                companylogo = null;
            }


            //classPublicclass.showMessage(companylogo.ToString());
            var cname = new Phrase(companyNameLbl.Text, font);

            cname.Font.Color = BaseColor.BLACK;

            var cadd = new Phrase(compaddlbl.Text, font);

            cadd.Font.Color = BaseColor.BLACK;
            var cstreet = new Phrase(street.Text, font);

            cstreet.Font.Color = BaseColor.BLACK;

            var aTable = new PdfPTable(4)
            {
                WidthPercentage = 100
            }; //4 columns

            var cell = new PdfPCell(new Phrase("Header",
                                               new iTextSharp.text.Font(iTextSharp.text.Font.FontFamily.HELVETICA, 24F)));

            if (companylogo != null)
            {
                cell = new PdfPCell();
                companylogo.ScaleAbsolute(80f, 80f);
                cell.AddElement(companylogo);
                cell.HorizontalAlignment = Element.ALIGN_LEFT;
                cell.Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
                aTable.AddCell(cell);
            }
            else
            {
                cell = new PdfPCell();
                var nph = new Phrase("", font);
                cell.AddElement(nph);
                cell.HorizontalAlignment = Element.ALIGN_LEFT;
                cell.Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
                aTable.AddCell(cell);
            }

            cell = new PdfPCell
            {
                HorizontalAlignment = Element.ALIGN_LEFT
            };
            cell.AddElement(cname);
            cell.AddElement(cstreet);
            cell.AddElement(cadd);
            cell.Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
            aTable.AddCell(cell);


            var ctel = new Phrase("Tel: " + comptellbl.Text, font);
            var cmob = new Phrase("Cell: " + mobilelbl.Text, font);
            var cfax = new Phrase("Fax: " + faxlbl.Text, font);

            cell = new PdfPCell
            {
                HorizontalAlignment = Element.ALIGN_LEFT
            };

            if (comptellbl.Text != "")
            {
                cell.AddElement(ctel);
            }

            if (mobilelbl.Text != "")
            {
                cell.AddElement(cmob);
            }

            if (faxlbl.Text != "")
            {
                cell.AddElement(cfax);
            }

            cell.Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
            aTable.AddCell(cell);

            var cemail = new Phrase("Email: " + emaillbl.Text, font);
            var cweb   = new Phrase("Web: " + websitelbl.Text, font);

            ctel.Font.Color = BaseColor.BLACK;
            cell            = new PdfPCell();

            if (emaillbl.Text != "")
            {
                cell.AddElement(cemail);
            }

            if (websitelbl.Text != "")
            {
                cell.AddElement(cweb);
            }

            // cell.HorizontalAlignment = Element.ALIGN_JUSTIFIED_ALL;
            cell.Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
            cell.HorizontalAlignment = Element.ALIGN_RIGHT;
            aTable.AddCell(cell);
            aTable.WidthPercentage = 100;

            //base.OnOpenDocument(writer, document);
            base.OnStartPage(writer, document);
            var header = new PdfPTable(new float[] { 1F })
            {
                SpacingAfter = 10F,
                ///PdfPCell cell;
                TotalWidth = fwidth
            };

            cell = new PdfPCell
            {
                Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER
            };
            cell.AddElement(aTable);
            header.AddCell(cell);
            header.WriteSelectedRows(0, -1, 50, document.Top, writer.DirectContent);
        }
Esempio n. 4
0
        //    public static String path = System.Windows.Forms.Application.StartupPath.ToString();



        public static void Generatereportpdf1(string Title, string extension, string sql, string orientation)
        {
            var path = ClassPublicclass.GetTemporaryDirectory("DE_Reports");

            Random rnd      = new Random();
            int    surfix   = rnd.Next(52);
            string sFilePDF = Title + surfix.ToString();
            string Reports  = "DE_Reports";

            try
            {
                iTextSharp.text.Font font       = new iTextSharp.text.Font();
                iTextSharp.text.Font boldfont   = new iTextSharp.text.Font();
                iTextSharp.text.Font fontclear  = new iTextSharp.text.Font();
                iTextSharp.text.Font fontfooter = new iTextSharp.text.Font();
                fontfooter = FontFactory.GetFont(FontFactory.COURIER, 7, iTextSharp.text.Font.NORMAL);
                iTextSharp.text.Font fontfooters = new iTextSharp.text.Font();
                fontfooters = FontFactory.GetFont(FontFactory.COURIER, 7, iTextSharp.text.Font.NORMAL);
                font        = FontFactory.GetFont(FontFactory.HELVETICA, 7, iTextSharp.text.Font.NORMAL);
                boldfont    = FontFactory.GetFont(FontFactory.HELVETICA, 7, iTextSharp.text.Font.BOLD);
                fontclear   = FontFactory.GetFont(FontFactory.HELVETICA, 7, iTextSharp.text.Font.ITALIC);
                iTextSharp.text.Document document = new iTextSharp.text.Document(PageSize.A4, 50, 50, 50, 50);
                PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(String.Format("{0}\\{1}\\{2}.{3}", path, Reports, sFilePDF, extension), FileMode.Create));
                writer.PageEvent = new ClassPDFFooter();

                if (orientation == "Landscape" || orientation == "L")
                {
                    document.SetPageSize(new iTextSharp.text.Rectangle(792, 612));     //landscape
                    ClassPDFFooter.fwidth = 692F;
                }
                else
                {
                    ClassPDFFooter.fwidth = 495F;
                }
                // step 3: we open the document
                document.Open();
                PdfPTable titleTable = new PdfPTable(10)
                {
                    WidthPercentage = 100
                };//10 column
                PdfPCell cell    = new PdfPCell(new Phrase("Title ", new iTextSharp.text.Font(iTextSharp.text.Font.FontFamily.HELVETICA, 24F)));
                Phrase   mytitle = new Phrase(Title, boldfont);
                boldfont.Color = mytitleFontColor;
                cell           = new PdfPCell();
                cell.AddElement(mytitle);
                cell.Colspan           = 8;
                cell.Border            = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
                cell.BackgroundColor   = mytitleColor;
                cell.VerticalAlignment = Element.ALIGN_MIDDLE;
                titleTable.AddCell(cell);
                Phrase printdate = new Phrase("Date " + System.DateTime.Now.Date.ToString(ClassMainclass.Dateformat) + " " + System.DateTime.Now.ToShortTimeString(), fontfooters);
                fontfooters.Color = mytitleFontColor;
                cell = new PdfPCell();
                cell.AddElement(printdate);
                cell.Colspan         = 2;
                cell.Border          = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER;
                cell.BackgroundColor = mytitleColor;
                //  printdate.Font.Color = mytitleFontColor;
                cell.VerticalAlignment = Element.ALIGN_MIDDLE;
                titleTable.AddCell(cell);
                PdfPTable spacetable   = new PdfPTable(1);  //1,1
                Phrase    spacecontent = new Phrase(" ", boldfont);
                spacecontent.Font.Color = iTextSharp.text.BaseColor.BLACK;
                cell = new PdfPCell(new Phrase(spacecontent))
                {
                    HorizontalAlignment = Element.ALIGN_LEFT,
                    Border = iTextSharp.text.Rectangle.NO_BORDER | iTextSharp.text.Rectangle.NO_BORDER
                };
                spacetable.AddCell(cell);
                Cursor.Current = Cursors.WaitCursor;
                StringBuilder sbConnCOMBOx = new StringBuilder();
                sbConnCOMBOx.Append(ClassDatabaseConnection.cnn1);
                sbConnCOMBOx.Append(";Extended Properties=");
                sbConnCOMBOx.Append(Convert.ToChar(34));
                sbConnCOMBOx.Append(Convert.ToChar(34));
                OleDbConnection cnExcelCOMBOx = new OleDbConnection(sbConnCOMBOx.ToString());
                cnExcelCOMBOx.Open();
                StringBuilder sbSQLCOMBOx = new StringBuilder();
                sbSQLCOMBOx.Append(sql);
                OleDbCommand    cmdExcelCOMBOx = new OleDbCommand(sbSQLCOMBOx.ToString(), cnExcelCOMBOx);
                OleDbDataReader drExcelCOMBOx  = cmdExcelCOMBOx.ExecuteReader();

                int nocols    = 10;
                int mycolspan = 1;

                if (drExcelCOMBOx.FieldCount + 1 == 2)
                {
                    nocols = 10; mycolspan = 9;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 3)
                {
                    nocols = 11; mycolspan = 5;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 4)
                {
                    nocols = 10; mycolspan = 3;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 5)
                {
                    nocols = 13; mycolspan = 3;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 6)
                {
                    nocols = 11; mycolspan = 2;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 7)
                {
                    nocols = 13; mycolspan = 2;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 8)
                {
                    nocols = 15; mycolspan = 2;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 9)
                {
                    nocols = 17; mycolspan = 2;
                }
                else if (drExcelCOMBOx.FieldCount + 1 == 10)
                {
                    nocols = 10; mycolspan = 1;
                }
                else
                {
                    nocols = drExcelCOMBOx.FieldCount + 1;
                }
                PdfPTable contentTable = new PdfPTable(nocols);

                //PdfPTable TotalsTable = new PdfPTable(nocols);
                Phrase indextitle = new Phrase("No", boldfont);
                indextitle.Font.Color = myheaderFontColor;
                cell = new PdfPCell(new Phrase(indextitle))
                {
                    HorizontalAlignment = Element.ALIGN_LEFT,
                    BackgroundColor     = myheaderColor,
                    Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                    BorderColor = myborderColor
                };
                contentTable.AddCell(cell);

                Phrase totaltitle = new Phrase("Totals", boldfont);
                totaltitle.Font.Color = myheaderFontColor;
                cell = new PdfPCell(new Phrase(totaltitle))
                {
                    HorizontalAlignment = Element.ALIGN_LEFT,
                    BackgroundColor     = myheaderColor,
                    Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                    BorderColor = myborderColor
                };
                // TotalsTable.AddCell(cell);


                // double tots1 = 0.00;
                for (int i = 0; i < drExcelCOMBOx.FieldCount; i++)
                {
                    if (drExcelCOMBOx.GetName(i).ToUpper() == "ID")
                    {
                        Phrase content = new Phrase("No", boldfont);
                        content.Font.Color = myheaderFontColor;
                        cell = new PdfPCell(new Phrase(content))
                        {
                            HorizontalAlignment = Element.ALIGN_LEFT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                        //TotalsTable.AddCell(cell);
                    }

                    else if (drExcelCOMBOx.GetName(i).ToUpper() == "DESC")
                    {
                        Phrase content = new Phrase("Description", boldfont);
                        content.Font.Color = myheaderFontColor;
                        cell = new PdfPCell(new Phrase(content))
                        {
                            HorizontalAlignment = Element.ALIGN_LEFT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                    }
                    else if (drExcelCOMBOx.GetName(i).ToUpper() == "% OF" || drExcelCOMBOx.GetName(i).ToUpper() == "% AMOUNT" || drExcelCOMBOx.GetName(i).ToUpper() == "% RATE" || drExcelCOMBOx.GetName(i).ToUpper() == "DAY" || drExcelCOMBOx.GetName(i).ToUpper() == "MONTH" || drExcelCOMBOx.GetName(i).ToUpper() == "YEAR" || drExcelCOMBOx.GetName(i).ToUpper() == "MONTH" || drExcelCOMBOx.GetName(i).ToUpper() == "SENDER" || drExcelCOMBOx.GetName(i).ToUpper() == "TOTAL DEDUCTIONS" || drExcelCOMBOx.GetName(i).ToUpper() == "PENSION" || drExcelCOMBOx.GetName(i).ToUpper() == "NHIF" || drExcelCOMBOx.GetName(i).ToUpper() == "NSSF" || drExcelCOMBOx.GetName(i).ToUpper() == "MPR" || drExcelCOMBOx.GetName(i).ToUpper() == "TAX CHARGED" || drExcelCOMBOx.GetName(i).ToUpper() == "NET PAY" || drExcelCOMBOx.GetName(i).ToUpper() == "GROSS PAY" || drExcelCOMBOx.GetName(i).ToUpper() == "BASIC PAY" || drExcelCOMBOx.GetName(i).ToUpper() == "Taxable Pay" || drExcelCOMBOx.GetName(i).ToUpper() == "PAYE" || drExcelCOMBOx.GetName(i).ToUpper() == "AMOUNT" || drExcelCOMBOx.GetName(i).ToUpper() == "QTY" || drExcelCOMBOx.GetName(i).ToUpper() == "ORDER NO" || drExcelCOMBOx.GetName(i).ToUpper() == "PRICE" || drExcelCOMBOx.GetName(i).ToUpper() == "VAT")
                    {
                        Phrase    cnt  = new Phrase(drExcelCOMBOx.GetName(i).ToString(), boldfont);
                        Paragraph amnt = new Paragraph(cnt)
                        {
                            Alignment = Element.ALIGN_RIGHT
                        };
                        amnt.Font.Color = myheaderFontColor;
                        cnt.Font.Color  = myheaderFontColor;
                        cell            = new PdfPCell(new Phrase(amnt))
                        {
                            HorizontalAlignment = Element.ALIGN_RIGHT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                    }

                    else if (drExcelCOMBOx.GetName(i).ToUpper() == "Usergroup")
                    {
                        Phrase content = new Phrase("User Group", boldfont);
                        content.Font.Color = myheaderFontColor;
                        cell = new PdfPCell(new Phrase(content))
                        {
                            HorizontalAlignment = Element.ALIGN_LEFT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                        //    TotalsTable.AddCell(cell);
                    }

                    else if (drExcelCOMBOx.GetName(i).ToUpper() == "DEFINITION")
                    {
                        Phrase content = new Phrase(Title, boldfont);
                        content.Font.Color = myheaderFontColor;
                        cell = new PdfPCell(new Phrase(content))
                        {
                            HorizontalAlignment = Element.ALIGN_LEFT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                        //   TotalsTable.AddCell(cell);
                    }

                    else
                    {
                        Phrase content = new Phrase(drExcelCOMBOx.GetName(i).Replace("_", " "), boldfont);
                        content.Font.Color = myheaderFontColor;
                        cell = new PdfPCell(new Phrase(content))
                        {
                            HorizontalAlignment = Element.ALIGN_LEFT,
                            BackgroundColor     = myheaderColor,
                            Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE,
                            BorderColor = myborderColor,
                            Colspan     = mycolspan
                        };
                        contentTable.AddCell(cell);
                        //   TotalsTable.AddCell(cell);
                    }
                }
                cnExcelCOMBOx.Close();
                StringBuilder sbConnCOMBO = new StringBuilder();
                sbConnCOMBO.Append(ClassDatabaseConnection.cnn1);
                sbConnCOMBO.Append(";Extended Properties=");
                sbConnCOMBO.Append(Convert.ToChar(34));
                sbConnCOMBO.Append(Convert.ToChar(34));
                OleDbConnection cnExcelCOMBO = new OleDbConnection(sbConnCOMBO.ToString());
                cnExcelCOMBO.Open();
                StringBuilder sbSQLCOMBO = new StringBuilder();
                sbSQLCOMBO.Append(sql);
                OleDbCommand    cmdExcelCOMBO = new OleDbCommand(sbSQLCOMBO.ToString(), cnExcelCOMBO);
                OleDbDataReader drExcelCOMBO  = cmdExcelCOMBO.ExecuteReader();
                int             j             = 1;
                while (drExcelCOMBO.Read())
                {
                    Phrase index = new Phrase(j.ToString() + ".", font);

                    cell = new PdfPCell(new Phrase(index))
                    {
                        HorizontalAlignment = Element.ALIGN_LEFT,
                        BackgroundColor     = iTextSharp.text.BaseColor.WHITE
                    };

                    if (j % 2 != 0)
                    {
                        cell.BackgroundColor = mybaseColora;
                        index.Font.Color     = mybaseFontColora;
                    }
                    else
                    {
                        cell.BackgroundColor = mybaseColor;
                        index.Font.Color     = mybaseFontColor;
                    }

                    cell.Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE;
                    cell.BorderColor = myborderColor;
                    contentTable.AddCell(cell);

                    for (int K = 0; K < drExcelCOMBO.FieldCount; K++)
                    {
                        Phrase content = new Phrase(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString().Replace('_', ' '), font);
                        try
                        {
                            if ((double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()) <= 0 || double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()) >= 0) && drExcelCOMBO.GetName(K).ToString() != "Year" && drExcelCOMBO.GetName(K).ToString() != "Month Period" && drExcelCOMBO.GetName(K).ToString() != "Year Period" && drExcelCOMBO.GetName(K).ToString() != "NHIF No" && drExcelCOMBO.GetName(K).ToString() != "NSSF NO" && drExcelCOMBO.GetName(K).ToString() != "Month" && drExcelCOMBO.GetName(K).ToString() != "Day" && drExcelCOMBO.GetName(K).ToString() != "Payroll No" && drExcelCOMBO.GetName(K).ToString() != "ID" && drExcelCOMBO.GetName(K).ToString() != "Account No" && drExcelCOMBO.GetName(K).ToString() != "Total From" && drExcelCOMBO.GetName(K).ToString() != "Total To")
                            {
                                // if (drExcelCOMBO.GetName(K).ToString() != "Year" || drExcelCOMBO.GetName(K).ToString() != "Day" || drExcelCOMBO.GetName(K).ToString() != "Month" || drExcelCOMBO.GetName(K).ToString() != "ID")
                                //  {
                                content = new Phrase(string.Format("{0:0.00}", double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString())), font);
                            }

                            else
                            {
                                content = new Phrase((drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()), font);
                            }
                            //   }
                        }
                        catch (Exception) { }
                        Paragraph nn         = new Paragraph(content);
                        Label     getcolname = new Label();

                        try
                        {
                            if ((double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()) <= 0 || double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()) >= 0) && drExcelCOMBO.GetName(K) != "Month" || drExcelCOMBO.GetName(K) != "Year" && drExcelCOMBO.GetName(K) != "Month Period" || drExcelCOMBO.GetName(K) != "Year Period" || drExcelCOMBO.GetName(K) != "NHIF No" || drExcelCOMBO.GetName(K) != "NSSF NO" || drExcelCOMBO.GetName(K) != "Day" || drExcelCOMBO.GetName(K) != "Payroll No")
                            {
                                nn.Alignment = Element.ALIGN_RIGHT;
                            }
                            else
                            {
                                nn.Alignment = Element.ALIGN_LEFT;
                            }
                        }
                        catch (Exception) { }

                        cell = new PdfPCell(new Phrase(nn));

                        try
                        {
                            if (double.Parse(drExcelCOMBO[drExcelCOMBO.GetName(K)].ToString()) >= 0)
                            {
                                cell.HorizontalAlignment = Element.ALIGN_RIGHT;
                            }
                            else
                            {
                                cell.HorizontalAlignment = Element.ALIGN_LEFT;
                            }
                        }
                        catch (Exception) { }

                        if (j % 2 != 0)
                        {
                            cell.BackgroundColor = mybaseColora;
                            index.Font.Color     = mybaseFontColora;
                        }
                        else
                        {
                            cell.BackgroundColor = mybaseColor;
                            content.Font.Color   = mybaseFontColor;
                        }
                        cell.Border      = iTextSharp.text.Rectangle.TOP_BORDER | iTextSharp.text.Rectangle.RECTANGLE;
                        cell.BorderColor = myborderColor;
                        cell.Colspan     = mycolspan;
                        contentTable.AddCell(cell);
                    }
                    j++;
                }
                cnExcelCOMBO.Close();
                contentTable.WidthPercentage = 100;
                // TotalsTable.WidthPercentage = 100;
                document.Add(spacetable);
                document.Add(spacetable);
                document.Add(spacetable);
                document.Add(spacetable);
                document.Add(spacetable);
                document.Add(spacetable);
                document.Add(titleTable);
                document.Add(spacetable);
                document.Add(contentTable);
                document.Add(spacetable);
                document.Add(spacetable);
                // document.Add(TotalsTable);
                document.Close();
                try
                {
                    System.Diagnostics.Process.Start(String.Format("{0}\\{1}\\{2}.{3}", path, Reports, sFilePDF, extension));
                }
                catch (Exception)
                {
                    // classPublicclass.showMessage("The Pdf report may be open, please close it and try again");
                }
            }
            catch (Exception n)
            {
                MessageBox.Show("Document may be open please, close it and try again " + n.ToString());
            }
        }