none
Clipboard & Unicode for excel RRS feed

  • Question

  • I have a  datagrid with several rows/columns. Now i want to copy the cells of the datagrid into excel. However the whenever i paste it into excel, it always pastes ??? instead of the (asian) symbols that should be pasted. This complies to both the default datagrid copy function, and the a custom written concatted string that creates string into excel accpetable format.

    Serveral procedures i've done:
    - using default GetClipboardObject function in the datagrid
    - using a custom string builder using Clipboard.SetText( string, Textformat.Unicode)
    - using a custom string builder using Clipboard.SetText( string, Textformat.ASCII)
    - setting autoconvert to false(, so in theory it shouldn't be able to get anything else than unicode).
    - setting the clipboard data to a memory stream, where the bytes are encoded with a other encoding.

    The more i try, the more i get the impression there is a bug in the clipboard function.
    I am using Vista, SP1, .net 3.5, Visual Studio 2008.

    This is the current code i have:

     private void Copy() 
            { 
                if (this.dataGridView1.GetCellCount(DataGridViewElementStates.Selected) > 0) 
                { 
     
                    int max = this.dataGridView1.SelectedCells.Count; 
                    int start = int.MaxValue; 
                    int startCol = int.MaxValue; 
                    int endCol = int.MinValue; 
                    int endRow = int.MinValue; 
                    for (int i = 0; i < max; i++) 
                    { 
                        start = Math.Min(this.dataGridView1.SelectedCells[i].RowIndex, start); 
                        endRow = Math.Max(this.dataGridView1.SelectedCells[i].RowIndex, endRow); 
                        startCol = Math.Min(this.dataGridView1.SelectedCells[i].ColumnIndex, startCol); 
                        endCol = Math.Max(this.dataGridView1.SelectedCells[i].ColumnIndex, endCol); 
                    } 
     
     
                    StringBuilder builder = new StringBuilder(); 
     
                    const string tab = "\t"
                    int current = start; 
                    while (current <= endRow) 
                    { 
                        int currentCol = startCol; 
                        while (currentCol <= endCol) 
                        { 
                            string val = this.dataGridView1[currentCol, current].Value as string
                            builder.Append(val == null ? string.Empty : val); 
     
                            if (currentCol++ < endCol) 
                            { 
                                builder.Append(tab);  
                            } 
                            else 
                            { 
                                break
                            } 
                        } 
     
                        if (current++ < endRow) 
                            builder.Append(Environment.NewLine); 
                    } 
     
     
                    try 
                    { 
                        // Add the selection to the clipboard. 
                        string code = builder.ToString(); 
     
     
                        var dataObject = new DataObject();    
                        var bytes = System.Text.Encoding.Unicode.GetBytes(code); 
                        var stream = new System.IO.MemoryStream(bytes); 
                        dataObject.SetData("UnicodeText"false, stream); 
                        Clipboard.SetDataObject(dataObject, true); 
     
                    } 
                    catch (System.Runtime.InteropServices.ExternalException) 
                    { 
                        MessageBox.Show("Accessing the clipboard failed"); 
                    } 
     
                     
                } 
     
          
            } 
     

    Also when using notepad, i can seemlessly copy the lines containing the symbols into Excel. And in my C# application, my code generates exactly the same code which is returned using in the IDataObject class when pasting from notepad. So i really don't see why it my c# app shouldn't work and notepad does.

    Anybody got a idea what i can do to fix this?



    Saturday, January 10, 2009 11:20 AM

Answers

  • Well i figured out the reason why copy pasting failed. The reason is how idiotic as it may sound that the datagridview, apart from my own code did it's own copy function. The copy function that by default ships in the datagridview is bugged.

    To solve the issues i placed the ClipboardCopyMode to disable. After using this and using the code i mentioned above i got every working as expected. Thanks for all your help tho.
    • Marked as answer by Lawrence Kok Monday, January 12, 2009 8:59 AM
    Monday, January 12, 2009 8:59 AM

All replies

  •  What kind of strings are you dealing with? Is is some kind of a foreign language? Then you will need to set up a code page for the culture you are working with.
    AlexB
    Sunday, January 11, 2009 3:01 AM
  • Currently i'm dealing with two languages. One is the (English-)phonetic alphabet, the other is used for Japanese text. Ideally i want it to be as versatile as possible.

    Under System.Text i don't see any way to use another Codepage also.
    I haven't mentioned this but: when i do: 'paste special paste as Unicode' it copies it correctly.
    Sunday, January 11, 2009 7:53 AM
  •  No, you did mention that.

    To reproduce the Japonics correctly set the code page: 932 - Japanese Shift-JIS. You will also have to download Japanese language pack.

    AlexB
    Sunday, January 11, 2009 3:56 PM
  • Well, i've just changed the the above code with this chunk of code. I assume that is what you mean?

                       // Add the selection to the clipboard. 
                 string code = builder.ToString(); 


                 DataObject dataObject = new DataObject(); 
                 Encoding enc = Encoding.GetEncoding(932); 
                 byte[] bytes = enc.GetBytes(code);                    
                 MemoryStream stream = new System.IO.MemoryStream(bytes); 
                 dataObject.SetData("UnicodeText"false, stream); 
                 Clipboard.SetDataObject(dataObject, true); 

    And i still get get '???' in excel.
    Sunday, January 11, 2009 4:10 PM
  •  Well, first find out if Excel is eligible for the Japonics. I am not trying to challenge Excel, perhaps it is, but you should make sure and double check.

    Also do you have a language service pack installed? Without it nothing will happen. Those characters must be in your computer's dll somewhere. I am sure there could be a separate folder in Program Files directory.

    At the same time since you succeed with Clipboard that may be in your system already. Read this. This is so called IME business. Perhaps you should set up IME property for Excel?

    AlexB
    Sunday, January 11, 2009 6:23 PM
  •  And how about this: IME Conversion Mode Values.

    I had a crazy idea long time ago to learn Japanese. Did not get very far, just to understand the general set up of the language and amuse my friends with stories about it.

    AlexB
    Sunday, January 11, 2009 6:27 PM
  •  I think you will find everything you need inthis thread.
    AlexB
    Sunday, January 11, 2009 6:30 PM
  • In my story i state that i am able to type Japonics (in excel, notepad etc), this is also confirmed by the part where i say i can copy/paste it from notepad flawlessly. And in Vista, i didn't need to install a language service pack, my vista only required me to add a new Keyboard IME.

    But please keep focus on the actual problem, the problem lies in copying from c# application to excel. I never stated that typing was relavant to it. The IME is just a input editor that allows you to input the unicode characters, it does not modify the actual encoding.

    p.s. i use the IME daily, i know how to use it, thanks for your concern though.
    Sunday, January 11, 2009 6:35 PM
  •  It occured to me what the problem is. I was not focusing on what you were saying. What is the column type where you store your text? You must store it as a blob, or varbinary(MAX) in Sql Server parlance. I believe the same type exists in Excel.

    Store everything as a binary type and you will be fine. Binary type does not care aboout Nipponics or anything else. It is binary.

    AlexB
    Sunday, January 11, 2009 7:09 PM
  • I do not touch the field type of Excel at all,  the default is set to Text. Which is just fine see screenshot:
    Free Image Hosting at www.ImageShack.us

    Also here are various copy/paste function which i've tried. It's hard to show that i actually copy pasted them, but you should take my on word it. Here's a screenshot of that:
    Free Image Hosting at www.ImageShack.us

    Pasting it from notepad, it also uses field type 'text'.
    Sunday, January 11, 2009 7:35 PM
  •  Can you define VarBinary(MAX) in Excel and store the language there programmatically? It would be very easy. Also what you ar showing seems to be European text.
    AlexB
    Sunday, January 11, 2009 11:35 PM
  • Well i could, but that does not solve the actual problem. Keep in mind that i already have a paste function that works namely 'paste special paste as unicode'! Why would i need a such a silly hack that makes things even more complicated as it is? I don't think so, the solution you propossed is a big no-go.

    Yes, i am using european text (i'm dutch). Basicly above the japonic it says with a describtion (in english) how that piece of text is placed there e.g. 'this text is typed' and underneath that it states wagashi - わがし. Are there more japonics than that then? Unicode = Unicode in my opinion, doesn't matter for what language you use it.
    Monday, January 12, 2009 7:49 AM
  • Well i figured out the reason why copy pasting failed. The reason is how idiotic as it may sound that the datagridview, apart from my own code did it's own copy function. The copy function that by default ships in the datagridview is bugged.

    To solve the issues i placed the ClipboardCopyMode to disable. After using this and using the code i mentioned above i got every working as expected. Thanks for all your help tho.
    • Marked as answer by Lawrence Kok Monday, January 12, 2009 8:59 AM
    Monday, January 12, 2009 8:59 AM
  •  It sound like a detective story but what makes you think that the Copy method is bugged in DataGridView?
    AlexB
    Monday, January 12, 2009 7:09 PM
  • Because the default copy function doesn't copy unicode characters in a way that is compatible with Excel, whereas my copy custom written copy function is? Basicly we can blame either excel for not processing copy/paste data (Unicode should be favorable, before ANSI text) or the datagrid.

    Reason for blaming the datagrid, is because it can lead to simple adjustments where other developers can benefit from. We can argue about about the fact the datagrid does both ANSI and UNICODE copy function. I would rather see a option to explictly tell the datagrid to either force unicode, force ansi, or support both for copying. But that differs for each of us.

    p.s. i'm not sure the datagrid even does unicode copying at all. The reason i assume it does, is because 'paste as unicode' works, but giving it seccond thoughts i also had my own unicode copy routine. It could be that excel detects ansi-text from the default datagrid copy function, and unicode from my own routine.

    I don't understand your remark about the detective story tho.
    Tuesday, January 13, 2009 3:55 PM