Bill Blogs in C#

Just a quick post. My newest article is online at sqljunkies.com

View it here. (It includes a picture for those interested.)

Question: Creating Excel Templates in OWC

Thank for offering to help. I am having a tough time getting started using OWC. Maybe because MS doesn’t  provide any documentation...

I have found out how to dynamically create a spreadsheet:

Add a reference to OWC11

then program:

        Dim xlsheet As New SpreadsheetClass
        ' put some data into the spreadsheet.
        Dim i As Int32
        For i = 1 To 10
            xlsheet.ActiveSheet.

            Cells(i, i) = i.ToString
        Next
        ' use this just to get a unique filename...  
        Dim xlFileName As String = System.DateTime.Now.Ticks.ToString() + ".xls"
        ' save it off to the filesystem...
        xlsheet.Export(Server.MapPath(".") + "\\" + xlFileName, OWC11.SheetExportActionEnum.ssExportActionNone, OWC11.SheetExportFormat.ssExportHTML)
        ' set content header so browser 
        ' knows you'r sending Excel workbook...
        Response.ContentType = "application/x-msexcel"
        ' stream it on out!
        Response.WriteFile(
          Server.MapPath(".") + "\\" + xlFileName)

But...
This isn't quite what I need.

What I would like to do is to

1) "open" a base spreadsheet (has formatting, borders, etc)
2) populate the spreadsheet with data from SQL (the easy part)
3) present the data to the user
4) provide the Excel toolbar so the user can edit the data in the spreadsheet prior to saving it.

To accomplish step 1, I believe that the spreadsheet needs to be saved as XML (read this somewhere). I have done this. But now what?

I do not want to run Excel per-se on the server because of 1) licensing and 2) server load. OWC is supposedly very light weight.

Answer:

I found two different ways to do this.

The first way (preferred):

1. Save your XLS template as an XML file. (which you did.)
2. Create the new spreadsheet as you have.
3. Use the XMLData property of the spreadsheet component to load the XML stream from the saved template:
System.IO.StreamReader rs = new StreamReader("template.xml");
string xmlContentFromFile = rs.ReadToEnd();
xlsSheet.XMLData = xmlContentFromFile;
That will give you the information from your XML spreadsheet.
4. Load the SQL Data into your spreadsheet.

If you need to use OWC 9.0, or Excel 2000:

1. Save the XLS template as an HTML.
2. Create the new spreadsheet.
3. Use the HTMLURL property on your new spreadsheet to load the saved HTML.
xlsSheet.HTMLURL = http://localhost/mysavedTemplate.html;
4. Load the SQL data into your spreadsheet.

Q: Why does Dataset.GetChanges() return an empty dataset? Examine the following code: The dataset stored in ChangedDoc.xml is empty. Why?

ds.ReadXml("http://localhost:400/TMSEProto/Proposal.xml");
ds.AcceptChanges();
ds.Tables["LineDetail"].Rows[2].Delete();

DataSet ds1;
ds1 = ds.GetChanges(DataRowState.Deleted);
ds1.WriteXmlSchema("c:\\ChangedSchema.xml");
ds1.WriteXml("c:\\ChangedDoc.xml");
// ChangedDoc.xml is empty :-(

A: It’s all in the Diffgrams.

There is more than one WriteXml method.  DataSet.GetChanges() does return the deleted rows. But, they are marked as deleted, so they are hidden from normal view.  To write the deleted rows, you need to serialize the dataset asa diffgram:

WriteXml( XmlWriteMode.DiffGram );

This will serialize both the original and current values, and will therefore include the deleted rows. The deleted rows will be marked as deleted. In fact, every modified row will show two versions: the original and the modified version.