none
Very Urgent !!!!!!!!!!!!! Excel 2007 Serializing and Deserializing Selected Range RRS feed

  • Question

  • Hi ,



     I am using Dotnet framework 4  , VSTO and Office Excel 2007 . When the user selects a particular range in the Excel document and click a button, I need to serialize the  selection(including formatting ) in to a text file and send this . At the receiving side I need to read this file , De-serialize the content to clipboard  and paste this in the receivers excel sheet without loosing formatting .

    The code I wrote for it is :

    Serialization Part

    Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);<br/>
     string formattedText = string.Empty;<br/>
     byte[] fileContent;<br/>
     if (activeWorksheet.Application.Selection != null)<br/>
          {<br/>
            if (activeWorksheet.Application.Selection is Excel.Range)<br/>
            {<br/>
              //Copy range to clipboard<br/>
              activeWorksheet.UsedRange.Copy(Type.Missing);<br/>
              iData = (IDataObject)Clipboard.GetDataObject();<br/>
              //formattedText is a strng variable <br/>
              formattedText = iData.GetData(DataFormats.Rtf).ToString();<br/>
              //fileContent is the byte array <br/>
              fileContent = Encoding.Default.GetBytes(formattedText);<br/>
              FileStream fs = new FileStream("D:\\test.txt", FileMode.Create);<br/>
              BinaryWriter bw = new BinaryWriter(fs);<br/>
              bw.Write(fileContent);<br/>
              bw.Close();<br/>
              Clipboard.Clear();<br/>
            }<br/>
          }
    


    De-serialization Part

          // create reader & open file<br/>
           System.IO.TextReader tr = new System.IO.StreamReader("D:\\test.txt");<br/>
                <br/>
          Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);<br/>
          string mystring = Convert.ToString(tr.ReadToEnd());<br/>
          // close the stream <br/>
          tr.Close();<br/>
          Clipboard.SetData(DataFormats.Rtf, mystring);<br/>
          activeWorksheet.PasteSpecial(TextDataFormat.Rtf, false, false, false, false, false, Type.Missing);<br/>
          Clipboard.Clear();
    

    Now a COM  error is throwing Exception from HRESULT While executing PasteSpecial() method


    The facts I need to know is

    1) Can I serialize and De-Serialize excel range ? if not how I can achieve the similar functionality ?

    2)How can I serialize/Deserialize Cipboard with excel range data ? What DataFormat to specify Instead of DataFormats.Rtf?

    3)In order to achive this functionality is there a way to convert the selection into a DataSet and then to xml and perform the reverse operation at reciever ? is there any built in classes available for that ?

    I can show row text data without any  formatting if i give Dataformats.Text while serializing and De-Serializing operation , but this is not the complete functonality I want and I really need to preserve formatting .


     

    Sunday, March 13, 2011 10:40 AM

All replies

  • Hi VSTO,

    Thanks for posting in the MSDN Forum.

    As far as you side Serialization and De-serialize, I think you are able to transform you data into text via some kind of format like ini file. It’s based on my experience that isn’t a good idea to handle your issue via PasteSpecial method.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 14, 2011 11:45 AM
    Moderator
  • IDataObject only supports a limited number of Dataformats  and i tried RTF (not working rich text metadata is rendered in the cells ) , CommaSeparatedValue ,Text , StringFormat ( Worked but showing with  no formatting) . But I need to persist formatting in my target document . Excel.Range object is not serializable though . 

    We got Rtf for word what is the similar format supported for Excel ???

     

    Monday, March 14, 2011 1:06 PM