Is it able to both Import and Export data's through Excel?

Technology specific object identification, supported applications, web technologies, and 3rd party controls.
Vinoth
Posts: 4
Joined: Fri Jun 29, 2012 3:23 pm

Is it able to both Import and Export data's through Excel?

Post by Vinoth » Fri Jun 29, 2012 3:46 pm

Is it able to both Import and Export data's through Excel. If Soo then how to import data to excel?

Is their any inbuilt method for import excel in Ranorex 3.3 as like export?

Regards
Vinoth

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Is it able to both Import and Export data's through Excel?

Post by Ciege » Fri Jun 29, 2012 4:13 pm

On method is to have a look at the Excel Framework I shared that uses Excel Interop to interact with Excel from C#.
http://www.ranorex.com/forum/my-excel-f ... t3265.html
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

Vinoth
Posts: 4
Joined: Fri Jun 29, 2012 3:23 pm

Re: Is it able to both Import and Export data's through Excel?

Post by Vinoth » Sun Jul 01, 2012 6:05 pm

Thank you for your reply.
But my requirement is to read and update the excel. I tried it with oledb connection and it is working fine in Visual Studio but unable to read the cell value from excel in Ranorex 3.3. Please find my code below
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication6
{
    class Program
    {
        static void Main(string[] args)
        {

            
            arun a = new arun();
        a.readexcel("password");
        shankar s = new shankar();
        s.insertexcel("password", "news");          
        }

        class arun
        {
            public  void readexcel(string strcolumn)
            {
                 
                string strcolumnname = null;
                try
                {

                    OleDbConnection oconn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\\new.xls';Extended Properties=Excel 8.0;");
                    OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                    oconn.Open();
                    OleDbDataReader odr = ocmd.ExecuteReader();
                    
                    while (odr.Read())
                    {
                        strcolumnname = valid(odr, strcolumn);
                        if (strcolumnname != "")
                        {
                            break;
                        }


                    }

                    oconn.Close();
                }

                catch (Exception ex)
                {

                }

            }


            protected string valid(OleDbDataReader myreader, string colnmae)
            {
                object val = myreader[colnmae];
                if (val != DBNull.Value)
                    return val.ToString();
                else
                    // return Convert.ToString(0);
                    return val.ToString();
            }

        }


        class shankar
        {
            public void insertexcel(string strcol,string strvalue)
            {
                string strcolumnname = null;
                try
                {
                   
                    OleDbConnection oconn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\\new.xls';Extended Properties=Excel 8.0;");
                   // OleDbCommand ocmd = new OleDbCommand("INSERT INTO [Sheet1$] ("+strcol+"[0]) VALUES('"+strvalue+"');", oconn);
                  //OleDbCommand ocmd = new OleDbCommand("UPDATE [Sheet1$] set fname ='boos'", oconn);
                  OleDbCommand ocmd = new OleDbCommand("UPDATE [Sheet1$] set "+strcol+" ='"+strvalue+"'", oconn);
                    oconn.Open();

                    OleDbDataReader odr = ocmd.ExecuteReader();
           
                    //DataSet ds = new DataSet();
                    //OleDbDataAdapter da = new OleDbDataAdapter();
                    //DataTable dt = new DataTable();
                    //dt.TableName = "Sheet1";
                    //da.SelectCommand = ocmd;
                    //da.Fill(dt);
                    //ds.Tables.Add(dt);

                    oconn.Close();
                    
                }

                catch (Exception ex)
                {

                }         
            }
        }
    }
}

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Is it able to both Import and Export data's through Excel?

Post by Ciege » Sun Jul 01, 2012 8:29 pm

The excel framework I shared will do that...
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Is it able to both Import and Export data's through Excel?

Post by IanF » Mon Jul 23, 2012 1:22 am

Ciege wrote:The excel framework I shared will do that...
Can you post example of use of this framework?
Ian Fraser

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Is it able to both Import and Export data's through Excel?

Post by Ciege » Mon Jul 23, 2012 4:05 pm

The code is pretty well documented. Open it up, give it a look. It describes what you need to pass to each method and what is returned...

For example:
To Open Excel:

Code: Select all

Microsoft.Office.Interop.Excel.Application objXL = RFWExcel.OpenExcel();
To Open a Workbook:

Code: Select all

Microsoft.Office.Interop.Excel.Workbook objXLWorkBook = RFWExcel.OpenExcelWorkbook(objXL, strExcelFileName);
To read a named range:

Code: Select all

string[] MyData = null;
string strNamedRange = "MyNamedRange";
string strExcelSpreadsheet = "MySpreadsheet.xls";
MyData = RFWExcel.ExcelReadNamedRange(objXL, objXLWorkBook, strExcelFileName, strNamedRange, strSheet);
To close Excel:

Code: Select all

objXL.Quit();
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Is it able to both Import and Export data's through Excel?

Post by IanF » Mon Jul 23, 2012 9:57 pm

I created a basic excel based framework demo for the QTP community some years ago. That demo was a running example based on the QTP tutorial flight app. What may seem obvious to the writer of code may not be obvious to others.

A file reading and writing to Excel is not a framework.
Ian Fraser

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Is it able to both Import and Export data's through Excel?

Post by Ciege » Mon Jul 23, 2012 10:28 pm

IanF wrote:A file reading and writing to Excel is not a framework.
Thank you for your professional opinion on this... I will hold it most high regards with the other drivel I read on the internet...

If you would have a look at the framework that I shared, you (being of such high intelligence) should notice that there are several methods developed to access the Microsoft Excel Interop with Ranorex. This is designed to allow the Ranorex community to easily access Excel spreadsheets right from their code with full error checking and Ranorex reporting... In other words, it is a framework of methods for Ranorex users to interact with Excel.

So before passing judgment I think maybe you should understand what it is you are judging... By your definition .NET would not be a framework either I suppose...</rant off>
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Is it able to both Import and Export data's through Excel?

Post by IanF » Tue Jul 24, 2012 12:46 am

No need to be unpleasant.

Just saying if you are putting a demo out there and you are calling it a solution then make it a solution not a single file.

I am new to Ranorex and single file with no context to it is of little help.
Ian Fraser

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Is it able to both Import and Export data's through Excel?

Post by Support Team » Tue Jul 24, 2012 10:59 am

Hey guys, no need to argue on naming :D

It might be just one file, but it is quite a large one with a lot of documentation in it. And Ciege provided some sample code, too.

Call it framework, module, library, utility methods, or whatever -- the main point is that the functionality helps other Ranorex users.

Regards,
Alex
Ranorex Team
.
Image

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Is it able to both Import and Export data's through Excel?

Post by IanF » Tue Jul 24, 2012 9:55 pm

The attached file was originally written around 2002 for the Rational Robot automation tool. I converted it to VBScript for QTP in 2005. It became the data handler for a basic QTP Keyword Data driven framework.

By its self it is not a framework. I did use this file to create a framework concept demo that was shared with the QTP community:

http://www.sqaforums.com/showflat.php?C ... PHPSESSID=

This demo was an unpack and run demo with all the script assets needed. A good number of other frameworks were developed of that methodology.

So you can understand my comfusion when a single file is presented as an (Automation) Framework.
Attachments
Excel.zip
Old QTP Data handler
(4.65 KiB) Downloaded 259 times
Ian Fraser