wxGrid with MS Excel (copy/paste)

If you have a cool piece of software to share, but you are not hosting it officially yet, please dump it in here. If you have code snippets that are useful, please donate!
Post Reply
el-512
In need of some credit
In need of some credit
Posts: 9
Joined: Wed May 04, 2005 7:00 am

wxGrid with MS Excel (copy/paste)

Post by el-512 »

Hi,

Jorg just suggested to place this peace of code here - and I obey :-)

I inserted the attached code into the grid-sample and it's supposed to copy / paste data from or to MS-Excel.

The basic thing is, that in Excel the clipboard data seperates the fields within one line with tabs, the lines (no wonder) with a linefeed.

I did all things manually - OK, there may be a better solution (more automatic or without my "GetSelectedRows()" problem), but this solution works. Use it where appropriate.

el-512

Code: Select all


#include <wx/clipbrd.h>

void GridFrame::CopyData( wxCommandEvent& WXUNUSED(ev) )
{
int i,k;
wxString copy_data;
bool something_in_this_line;

    copy_data.Clear();

    for (i=0; i<grid->GetRows(); i++) {     // step through all lines
        something_in_this_line = false;     // nothing found yet
        for (k=0; k<grid->GetCols(); k++) { // step through all colums
            if (grid->IsInSelection(i,k)) { // this field is selected!!!
                if (something_in_this_line == false) {  // first field in this line => may need a linefeed
                    if (copy_data.IsEmpty() == false) {     // ... if it is not the very first field
                        copy_data = copy_data + wxT("\n");  // next LINE
                    }
                    something_in_this_line = true;
                } else {                                // if not the first field in this line we need a field seperator (TAB)
                    copy_data = copy_data + wxT("\t");  // next COLUMN
                }
                copy_data = copy_data + grid->GetCellValue(i,k);    // finally we need the field value :-)
            }
        }
    }

/*  I tried this, but it DID NOT WORK!!!!
wxArrayInt row_index, col_index;

    row_index = grid->GetSelectedRows();    // the ARRAYS were EMPTY!!! Don't know why!
    col_index = grid->GetSelectedCols();

    for (i=0; i<row_index.GetCount(); i++) {
        if (i > 0) {
            copy_data = copy_data + wxT("\n");  // next ROW
        }
        for (k=0; k<col_index.GetCount(); k++) {
            if (k > 0) {
                copy_data = copy_data + wxT("\t");  // next COLUMN
            }
            copy_data = copy_data + grid->GetCellValue(row_index[i],col_index[k]);
        }
    }
*/

    wxOpenClipboard();          // now copy all these things into the clipbord
    wxEmptyClipboard(); 
    wxSetClipboardData(wxDF_TEXT,copy_data.c_str(),0,0); 
    wxCloseClipboard(); 
}

void GridFrame::PasteData( wxCommandEvent& WXUNUSED(ev) )
{
wxString copy_data;
wxString cur_field;
wxString cur_line;
int i,k,k2;

    wxOpenClipboard();          // now copy all these things into the clipbord
    copy_data = (char *)wxGetClipboardData(wxDF_TEXT);
    wxCloseClipboard();

    // I admit, the string contains all fields from min-selection to max selection,
    // even if rows or columns inbetween are NOT selected
    // Don't know if this is an EXCEL or WX problem!
    // But as the same thing happenes in Word, I think, it's Bills fault!

//    i = 0; k = 0;   // Where to insert - 0,0 or left/top current cursor position
    i = grid->GetGridCursorRow();
    k = grid->GetGridCursorCol();
    k2= k;

    do {
        cur_line = copy_data.BeforeFirst('\n');
        copy_data = copy_data.AfterFirst('\n');
        do {
            cur_field = cur_line.BeforeFirst('\t');
            cur_line  = cur_line.AfterFirst ('\t');
            grid->SetCellValue(i,k,cur_field);
            k++;
        } while(cur_line.IsEmpty() == false);
        i++;
        k = k2;
    } while (copy_data.IsEmpty() == false);
}

Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

Thanks, much appreciated! :-)

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
goeba
Earned a small fee
Earned a small fee
Posts: 21
Joined: Tue May 17, 2005 8:13 pm

Post by goeba »

Hi,

the GetSelectedRows and so on does not work, because (I think) this only returns the Rows which are completely selected by clicking on the label.

I have written a similar Copy&Paste for my grid - yours is slightly better, because I do not care about multiple selections.

The whole thing does not work for Cells containing text with - you guess it - linefeeds in it.

There must be a more sophisticated Clipboard format excel supports, but I don
timg
Earned some good credits
Earned some good credits
Posts: 148
Joined: Mon Jan 23, 2006 6:52 pm

Post by timg »

Hi,

I was trying to use this code in my project and found that it is extremely slow when copying large grids (100 x 2000). If you replace all the lines like these:

copy_data = copy_data + wxT("\t"); // next COLUMN

with
copy_data.Append(wxT("\t"));

it is much faster.

Seems .Append on a string is faster than = itself + other string.
tasdev
Knows some wx things
Knows some wx things
Posts: 29
Joined: Tue Apr 26, 2005 12:18 am
Location: Australia

Post by tasdev »

[quote="goeba"]Hi,

the GetSelectedRows and so on does not work, because (I think) this only returns the Rows which are completely selected by clicking on the label.

I have written a similar Copy&Paste for my grid - yours is slightly better, because I do not care about multiple selections.

The whole thing does not work for Cells containing text with - you guess it - linefeeds in it.

There must be a more sophisticated Clipboard format excel supports, but I don
sergio
Knows some wx things
Knows some wx things
Posts: 39
Joined: Fri Sep 01, 2006 8:19 pm
Location: Costa Rica

Post by sergio »

Hi el-512 and thanks for the tip, I modified the code a little bit:

Code: Select all

//mygrid.h
/******************************************************************************/
#ifndef MYGRID_H
#define MYGRID_H 
/******************************************************************************/
#include <wx/grid.h>
/******************************************************************************/
class  myGrid : public wxGrid
{
public:
       myGrid(wxWindow *parent, wxWindowID id = wxID_ANY,
          const wxPoint& pos = wxDefaultPosition,
          const wxSize& size = wxDefaultSize)
      : wxGrid(parent,id,pos,size) {};
       
       void Copy(wxCommandEvent &WXUNUSED(event));
       void Paste(wxCommandEvent &WXUNUSED(event));
       void OnRightMouseDown(wxGridEvent &event); 

private:
       DECLARE_EVENT_TABLE()
};
/******************************************************************************/
#endif

Code: Select all

//mygrid.cpp
#include "mygrid.h"
#include <wx/clipbrd.h> 
#include <wx/menu.h>
/******************************************************************************/
BEGIN_EVENT_TABLE(myGrid,wxGrid)
	EVT_MENU(wxID_COPY,myGrid::Copy)
	EVT_MENU(wxID_PASTE,myGrid::Paste)
        EVT_GRID_CELL_RIGHT_CLICK(myGrid::OnRightMouseDown)
END_EVENT_TABLE()
/******************************************************************************/ 
void myGrid::Copy(wxCommandEvent &event)
{
    wxString copy_data;
    bool something_in_this_line;

    copy_data.Clear();

    for (int i=0; i<GetRows();i++)
    {     
        something_in_this_line = false;    
        for (int j=0; j<GetCols(); j++)
        {  
            if (IsInSelection(i,j))
            { 
                if (something_in_this_line == false)
                {  
                    if (copy_data.IsEmpty() == false) 
                    {   
                        copy_data.Append(wxT("\n"));  
                    }
                    something_in_this_line = true;
                } 
                else
                {                                
                    copy_data.Append(wxT("\t"));  
                }
                copy_data = copy_data + GetCellValue(i,j);    
            }
        }
    }

    wxOpenClipboard();          
    wxEmptyClipboard();
    wxSetClipboardData(wxDF_TEXT,copy_data.c_str(),0,0);
    wxCloseClipboard();
}
/******************************************************************************/
void myGrid::Paste(wxCommandEvent &event)
{
    wxString copy_data;
    wxString cur_field;
    wxString cur_line;
   
    wxOpenClipboard();          
    copy_data = (char *)wxGetClipboardData(wxDF_TEXT);
    wxCloseClipboard();

    int i = GetGridCursorRow();
    int j = GetGridCursorCol();
    int k = j;
    
    while(!copy_data.IsEmpty()) 
    {
        cur_line = copy_data.BeforeFirst('\n');
        while(!cur_line.IsEmpty())
        {
            cur_field = cur_line.BeforeFirst('\t');
            SetCellValue(i,j,cur_field);
            j++; 
            cur_line  = cur_line.AfterFirst ('\t');
        } 
        i++;
        j=k;
        copy_data = copy_data.AfterFirst('\n');
    } 
}
/******************************************************************************/
void myGrid::OnRightMouseDown(wxGridEvent &event)
{
    wxMenu *pmenuPopUp = new wxMenu;
    wxMenuItem* pItem;
    pItem = new wxMenuItem(pmenuPopUp,wxID_COPY, wxT("Copy"));
    pmenuPopUp->Append(pItem);
    
   
    pItem = new wxMenuItem(pmenuPopUp,wxID_PASTE, wxT("Paste"));
    pmenuPopUp->Append(pItem);
    if(!CanEnableCellControl()) 
        pItem->Enable(false);   
           
    PopupMenu(pmenuPopUp,event.GetPosition());
    delete pmenuPopUp;
}
/******************************************************************************/


Specially the paste function is easier to read (at least to me). I hope this helps someone.

Regards,

Sergio
manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

Post by manianis »

goeba wrote:Hi, ...

I have written a keyboard-handler for my grid which accepts Ctrl C and Ctrl V, so if anyone is interested, I can post it. ... Regards,


Andreas
Please I'm interested...
grf
Knows some wx things
Knows some wx things
Posts: 38
Joined: Thu Jun 01, 2006 1:53 pm
Location: Germany

Post by grf »

A late reply from me, but today I had the same problem.
A keyboard handler could be implemented like the following snipplets:

While creating the grid you need to connect the copy/paste function like this:

Code: Select all

my_grid = new wxGrid(my_panel, -1, wxPoint(0, 0), wxSize(300, 300));

//connect the grid to the key event handler to provide Ctrl+C and Ctrl+V
my_grid->Connect(wxID_ANY,
                                   wxEVT_KEY_DOWN,
                                   wxKeyEventHandler(my_frame::CopyPasteData),
                                  (wxObject*) NULL,
                                   this);
Now the copy/paste fuction:

Code: Select all

void my_frame::CopyPasteData(wxKeyEvent& event) {

  
  if ((event.GetUnicodeKey() == 'C') && (event.ControlDown() == true)) {

   //put all the copy functionality here

 } else if ((event.GetUnicodeKey() == 'V') && (event.ControlDown() == true)) {

  //put all the paste functionality here

 }

 event.Skip();

}
That's it.

Regards
Michael
Sowas kommt von sowas.
MarcoC++
In need of some credit
In need of some credit
Posts: 1
Joined: Fri Nov 18, 2011 9:49 am

Re: wxGrid with MS Excel (copy/paste)

Post by MarcoC++ »

I've got a little problem. At first i 'd say that i'm really at the first stemps with C++ and some thinghs are quite confused expecially where write some parts of the declaration code.
In this case i use the functions written above and they runs.
The problem is with the "GetUnicodeKey"
The compiler says that : "class wxKeyEvent has no member called 'GetUnicodeKey'"
In the wxUserGuide I found:
wxKeyEvent::GetUnicodeKey
wxChar GetUnicodeKey() const
Returns the Unicode character corresponding to this key event.
This function is only available in Unicode build, i.e. when wxUSE_UNICODE is 1.

Ok... I don't understand where is supposed to be declared wxUSE_UNICODE == 1
It should not be use by default settings???

do I have to define it in someway????

another questions just we are uin theme:
when you write for example: for (int j=0; j<GetCols(); j++)
the compiler sand me an error... i solved this writing for (int j=0; j<grid->GetCols(); j++) //grid is the pointer to mywxGrid
in fact how could it understand which is the object it has to GetCols if I don't specifies a pointer????


Thanks in advance and sorry if my English is not perfect.
DerKleineNik
Knows some wx things
Knows some wx things
Posts: 29
Joined: Fri Sep 09, 2011 9:59 am

Re: wxGrid with MS Excel (copy/paste)

Post by DerKleineNik »

MarcoC++ wrote:This function is only available in Unicode build, i.e. when wxUSE_UNICODE is 1.

Ok... I don't understand where is supposed to be declared wxUSE_UNICODE == 1
It should not be use by default settings???

do I have to define it in someway????
You have to define if you want to use Unicode or not before compiling wyWidgets. How exactly you do that depends on the Software you use to compile wxWidgets.
guyanqiu
Knows some wx things
Knows some wx things
Posts: 25
Joined: Sun Jun 13, 2010 2:44 pm

Re: wxGrid with MS Excel (copy/paste)

Post by guyanqiu »

Thanks all.
I do a little change

Code: Select all

/******************************************************************************/
void myGrid::Copy(wxCommandEvent &event)
{
    wxString copy_data;
    bool something_in_this_line;

    copy_data.Clear();

    for (int i=0; i<GetRows();i++)
    {     
        something_in_this_line = false;   
        for (int j=0; j<GetCols(); j++)
        { 
            if (IsInSelection(i,j))
            {
                if (something_in_this_line == false)
                { 
                    if (copy_data.IsEmpty() == false)
                    {   
                        copy_data.Append(wxT("\r\n"));  // in windows if copy to notepad need \r\n to newline
                    }
                    something_in_this_line = true;
                }
                else
                {                               
                    copy_data.Append(wxT("\t")); 
                }
                copy_data = copy_data + GetCellValue(i,j);   
            }
        }
    }

    wxOpenClipboard();         
    wxEmptyClipboard();
    wxSetClipboardData(wxDF_TEXT,copy_data.mb_str(),0,0); // in Windows , the need this for UNICODE
    wxCloseClipboard();
}
/******************************************************************************/
paddle
Knows some wx things
Knows some wx things
Posts: 43
Joined: Sat Oct 12, 2019 4:57 pm

Re: wxGrid with MS Excel (copy/paste)

Post by paddle »

sergio wrote: Thu Sep 21, 2006 11:55 pm This post above
Code above does not work anymore.

Code: Select all

    wxOpenClipboard();          
    copy_data = (char *)wxGetClipboardData(wxDF_TEXT);
    wxCloseClipboard();
needs to be replaced by

Code: Select all

                if (wxTheClipboard->Open())
                {
                    if (wxTheClipboard->IsSupported( wxDF_TEXT ))
                    {
                        wxTextDataObject data;
                        wxTheClipboard->GetData( data );
                        copy_data = data.GetText();
                    }
                    wxTheClipboard->Close();
                }
Other things are broken in Sergio code since lot has changed. Below is edited code with syntax changes. Note that I have not used as is so there might be other things to correct :

Code: Select all

//mygrid.cpp
#include "mygrid.h"
#include <wx/clipbrd.h> 
#include <wx/menu.h>
/******************************************************************************/
BEGIN_EVENT_TABLE(myGrid,wxGrid)
	EVT_MENU(wxID_COPY,myGrid::Copy)
	EVT_MENU(wxID_PASTE,myGrid::Paste)
        EVT_GRID_CELL_RIGHT_CLICK(myGrid::OnRightMouseDown)
END_EVENT_TABLE()
/******************************************************************************/ 
void myGrid::Copy(wxCommandEvent &event)
{
    wxString copy_data;
    bool something_in_this_line;

    copy_data.Clear();

    for (int i=0; i<GetNumberRows();i++)
    {     
        something_in_this_line = false;    
        for (int j=0; j<GetNumberCols(); j++)
        {  
            if (IsInSelection(i,j))
            { 
                if (something_in_this_line == false)
                {  
                    if (copy_data.IsEmpty() == false) 
                    {   
                        copy_data.Append(wxT("\n"));  
                    }
                    something_in_this_line = true;
                } 
                else
                {                                
                    copy_data.Append(wxT("\t"));  
                }
                copy_data = copy_data + GetCellValue(i,j);    
            }
        }
    }

	if (wxTheClipboard->Open())
                {
                    wxTheClipboard->Clear();
                    wxTheClipboard->SetData( new wxTextDataObject(copy_data) );
                    wxTheClipboard->Close();
                }
}
/******************************************************************************/
void myGrid::Paste(wxCommandEvent &event)
{
    wxString copy_data;
    wxString cur_field;
    wxString cur_line;
   
 	if (wxTheClipboard->Open())
                {
                    if (wxTheClipboard->IsSupported( wxDF_TEXT ))
                    {
                        wxTextDataObject data;
                        wxTheClipboard->GetData( data );
                        copy_data = data.GetText();
                    }
                    wxTheClipboard->Close();
         }

    int i = GetGridCursorRow();
    int j = GetGridCursorCol();
    int k = j;
    
    while(!copy_data.IsEmpty()) 
    {
        cur_line = copy_data.BeforeFirst('\n');
        while(!cur_line.IsEmpty())
        {
            cur_field = cur_line.BeforeFirst('\t');
            SetCellValue(i,j,cur_field);
            j++; 
            cur_line  = cur_line.AfterFirst ('\t');
        } 
        i++;
        j=k;
        copy_data = copy_data.AfterFirst('\n');
    } 
}
/******************************************************************************/
void myGrid::OnRightMouseDown(wxGridEvent &event)
{
    wxMenu *pmenuPopUp = new wxMenu;
    wxMenuItem* pItem;
    pItem = new wxMenuItem(pmenuPopUp,wxID_COPY, wxT("Copy"));
    pmenuPopUp->Append(pItem);
    
   
    pItem = new wxMenuItem(pmenuPopUp,wxID_PASTE, wxT("Paste"));
    pmenuPopUp->Append(pItem);
    if(!CanEnableCellControl()) 
        pItem->Enable(false);   
           
    PopupMenu(pmenuPopUp,event.GetPosition());
    delete pmenuPopUp;
}
/******************************************************************************/
Post Reply