예제 #1
0
        public static PortalAA ImportToDataTable(string FilePath, string SheetName)
        {
            DataTable dt = new DataTable();
            FileInfo fi = new FileInfo(FilePath);
            PortalAA dd = null;

            // Check if the file exists
            if (!fi.Exists)
                throw new Exception("File " + FilePath + " Does Not Exists");

            using (ExcelPackage xlPackage = new ExcelPackage(fi))
            {
                // get the first worksheet in the workbook
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[SheetName];

                // Fetch the WorkSheet size
                ExcelCellAddress startCell = worksheet.Dimension.Start;
                ExcelCellAddress endCell = worksheet.Dimension.End;
                 dd = new PortalAA();
                // create all the needed DataColumn
                for (int col = startCell.Column; col <= endCell.Column; col++)
                    dt.Columns.Add(col.ToString());
                int rol = 1;
                // place all the data into DataTable
                for (int row = startCell.Row; row <= endCell.Row; row++)
                {
                    DataRow dr = dt.NewRow();
                    int x = 0;
                    for (int col = startCell.Column; col <= endCell.Column; col++)
                    {
                      //  sp =Convert.ToString( worksheet.Cells[row, col].Value); ;
                        if (row != 1)
                        {
                            dr[x++] = worksheet.Cells[row, col].Value;

                            if (col == 1)
                            {
                                dd.transID =Convert.ToString( worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 2)
                            {
                                dd.amt = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 3)
                            {
                                dd.agt = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 4)
                            {
                                dd.applicantname = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 5)
                            {
                                dd.applicantemail = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 6)
                            {
                                dd.applicantpnumber = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 7)
                            {
                                dd.applicant_addy = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 8)
                            {
                                dd.product_title = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 9)
                            {
                                dd.item_code = Convert.ToString(worksheet.Cells[row, col].Value); ;

                            }

                            if (col == 10)
                            {
                                dd.pc= Convert.ToString(worksheet.Cells[row, col].Value); ;

                                dd.xgt = "xpay";
                                XObjs.Registration ap = null;
                                Retriever pp = new Retriever();
                                ap = pp.getRegistrationBySysID2(dd.agt);
                                if (ap != null)
                                {
                                    dd.agentname = ap.Surname;
                                    dd.agentemail = ap.Email;
                                    dd.agentpnumber = ap.PhoneNumber;
                                }

                            }
                        }
                    }
                   // dt.Rows.Add(dr);
                    rol = rol + 1;
                }
            }
            return dd;
        }
예제 #2
0
        public PortalAA readExcel5(string Filename)
        {
            XObjs.Registration ap = null;
            string path = Filename;
            string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            //   string szConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            OleDbConnection conn = new OleDbConnection(szConn);
            conn.Open();

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
            OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            adpt.Fill(ds);
            string email = "";
            string user_name = "";
            string password = "";

            PortalAA dd2 = new PortalAA();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                // Result pp = new Result();
                //  string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
                dd2.transID = Convert.ToString(dr[0]);
                dd2.amt = Convert.ToString(dr[1]);
                dd2.agt = Convert.ToString(dr[2]);
                dd2.xgt = "xpay";
                dd2.applicantname = Convert.ToString(dr[3]);
                dd2.applicantemail = Convert.ToString(dr[4]);

                dd2.applicantpnumber = Convert.ToString(dr[5]);

                dd2.applicant_addy = Convert.ToString(dr[6]);

                Retriever pp = new Retriever();
                ap = pp.getRegistrationBySysID2(dd2.agt);
                if (ap != null)
                {
                    dd2.agentname = ap.Surname;
                    dd2.agentemail = ap.Email;
                    dd2.agentpnumber = ap.PhoneNumber;
                }
                dd2.product_title = Convert.ToString(dr[7]);
                dd2.item_code = Convert.ToString(dr[8]);
                dd2.pc = Convert.ToString(dr[8]);
                //  pp.subject_code = Convert.ToString(dr[1]);
                //  pp.score = Convert.ToInt32(dr[2]);
                // pp.score = null;

            }

            conn.Close();
            return dd2;
        }