FarPoint Forums

Hi,

I am able to readily save the entire spread to an XML file (with fpSpread1.Save(FilePath, False)) and load it again; but I need to save to, and load from, a SQL Server 2005 database field instead of a file.  I can't quite figure out how to do this, but I figure that thousands of developers before me must have done exactly this; so can someone help out with an example?

Thanks,

C17

C17,

Are you wanting to save the entire Spread control or just the SheetView object? If it is just the SheetView object, you can serialize the SheetView object to a MemoryStream and write the byte array to a field in a database table record.

        Dim stream As IO.MemoryStream = Nothing
        Try
            stream = New IO.MemoryStream
            Dim formatter As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter

            formatter.Serialize(stream, FpSpread1.Sheets(0))
           'stream should hold the array of bytes to store to the database record.

Scott,

I need to save the state of the entire Spread, as is done with the .Save() method, and then restore it later.

Regards,

C17

Hello,

What from the Spread do you need to save that you are not getting from the SheetView object? The FpSpread object is not Serializable, so the code I gave you would not work for the Spread control. You can save the Spread to a stream using the Save method, but I am not sure how to write the stream into the a database field.

I found the following and perhaps it will work for you...

Dim LayoutArray() As Byte
LayoutArray = MS.ToArray()....where MS is your stream.

Table.Rows.Add(New Object() {Nothing, "MyGridLayout", LayoutArray})

Scott,

>>What from the Spread do you need to save that you are not getting from the SheetView object?<<

The workbooks my users are creating (and I need to save in the database) will likely have multiple sheets with linked formulas.  I need to save and restore the entire workbook, not just a single sheet.

I suppose I could have my program write to a temporary file with the .Save() method, then read it in as a text stream and store it to my database field.  I'm not really conversant with streams in .NET, so I thought there was a more direct way of doing this -- and I also thought that many FarPoint developers before me must have needed to do this.  But apparently not.

You don't have to write my program for me, I can probably figure it out from here.  Thanks as always for your assistance.

Regards,

C17

C17,

One thing you could do is to seriailize each SheetView seperately to diferent fields. Then when loading them back in you can deseriailize into each SheetView object to add to the SheetViewCOllection. After the last SheetView is loaded you would need to call the LoadFormulas method to load cross sheet formulas.

Scott,

I figured out how to do it, thanks to another thread on the forums here that covered some of the same territory, and gave me the solution ... which I have slightly updated (and written in VB) here.  Here's how you save to a memory stream, and thence to a string, without writing to disk at all:

'--
'-- fpSpread1.Save(FilePath, False) saves the spread state to an XML file.
'-- Save it to an in-memory string instead.
'--
' First, save state to a memory stream instead of a file stream.
Dim ms As New MemoryStream
fpSpread1.Save(ms, False)
' Next, convert that memory stream to a string.
ms.Position = 0
Dim tr As TextReader = New StreamReader(ms, System.Text.Encoding.UTF8, False, 1024)
Dim fpState As New System.Text.StringBuilder
Dim ln As String = tr.ReadLine
While ln IsNot Nothing
     fpState.Append(ln)
     ln = tr.ReadLine
End While
' fpState now contains the same contents as the XML file that would be produced by fpSpread1.Save.
' If you write it out to a database field, make sure that the field is defined as nvarchar(max).
 

Then, after reading from the database field to a string fpState, you can restore the spread from it as follows:

'--
'-- fpSpread1.Open(FilePath) loads the spread state from an XML file.
'-- Load it from a string fpState instead.
'--
Dim xmlDoc As New Xml.XmlDocument
xmlDoc.LoadXml(fpState)
Dim ms As New MemoryStream
xmlDoc.Save(ms)
ms.Position = 0
fpSpread1.Open(ms)

You might want to have this posted on the "How to" section of the web site.

Regards,

C17

Hello,

I will pass this along. Have a great weekend.