Bill Blogs in C#

Simple Excel Formatting with OWC

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.