How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 3 (Elizabeth Maher)

In the previous articles, we have covered how to add a web controller to the Server project that can route requests and allow use to generate a Word document with record details using OpenXml and return it to the client. If you missed them:

How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 1

How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 2

In this article we will cover how to create report that summarizes all the records in an entity.

Routing the Request

In a previous article we added an entry to the Routes table, shown below, in the Global.asax of the Server project, to handle requests for our ExportToWord controller.

C#:

protected void Application_Start(object sender, EventArgs e)
{
            RouteTable.Routes.MapHttpRoute("ExportToWord", 
                "reports/{controller}/{entity}/{id}", 
                new { id = RouteParameter.Optional });
}

VB:

 Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        RouteTable.Routes.MapHttpRoute("ExportToWord",
            "reports/{controller}/{entity}/{id}",
            New With {.id = RouteParameter.Optional})
End Sub

The above code adds the ability for the application to handle requests like http://server/MyLSApplication/reports/ExportToWord/Events for summary reports and http://server/MyLSApplication/reports/ExportToWord/Events/1 for detailed reports. We have already written the code to handle the request for detailed reports in previous article. Now we will focus on the summary report.

Handling the Request

Below is the code to handle a request like http://server/MyLSApplication/reports/ExportToWord/Events.  We will add another method to the ExportToWordController class created in the previous article.

C#:

        public HttpResponseMessage Get(string entity)
        {
            IDataServiceQueryable queryable;

            using (ServerApplicationContext ctx = ServerApplicationContext.CreateContext())
            {
                switch (entity)
                {
                    case "Events":
                        queryable = ctx.DataWorkspace.ApplicationData.EventSessions;
                        break;
                    default:
                        throw new ArgumentException("Unsupported query request");
                }

                DataTable table = GetTableData(queryable);

                MemoryStream s = CreateSummaryWordDoc(entity, table);

                return GetWordDocResponseMessage(s, entity + ".docx");
            }

VB:

Public Function GetValues(ByVal entity As String) As HttpResponseMessage
        Dim queryable As IDataServiceQueryable
        Using ctx As ServerApplicationContext = ServerApplicationContext.CreateContext()
            Select Case entity
                Case "Events"
                    queryable = ctx.DataWorkspace.ApplicationData.EventSessions
                Case Else
                    Throw New ArgumentException("Unsupported query request")
            End Select

            Dim entityData As DataTable = GetTableData(queryable)

            Dim s As MemoryStream = CreateSummaryWordDoc(entity, entityData)

            Return GetWordDocResponseMessage(s, entity & ".docx")
        End Using
End Function

The code to handle the summary report is very similar for that of the detailed report. The differences are the data is now a table of information, not a dictionary, and our function for creating the Word document needs to create a table.

Get the Data

Below is the function for getting all the table data from the query object. As noted previously, this code was added to make the function to create the Word document easier to read.

C#:

private DataTable GetTableData(IDataServiceQueryable queryable)
{

            DataTable table = new DataTable();
            System.Collections.IEnumerator enumerator =
                queryable.GetEnumerator();


            //Get column headers
            PropertyInfo[] columnHeaders = null;
            if (enumerator.MoveNext())
            {
                columnHeaders = enumerator.Current.GetType().GetProperties(
                    BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static);
                columnHeaders = RemoveUnreadableProperties(columnHeaders);


                foreach (var heading in columnHeaders)
                {
                    table.Columns.Add(heading.Name, heading.PropertyType);
                }

                //Write out values
                foreach (var element in queryable)
                {
                    var row = table.NewRow();
                    foreach (var heading in columnHeaders)
                    {
                        row[heading.Name] = heading.GetValue(element);
                    }
                    table.Rows.Add(row);
                }
            }
            return table;
}
private PropertyInfo[] RemoveUnreadableProperties(PropertyInfo[] columnProperties)
{
            return columnProperties.Where(p =>
                !p.PropertyType.IsArray
                && !p.PropertyType.IsGenericType
                && !"Microsoft.LightSwitch.Framework.Server.PersonInfo".Equals(
                    p.PropertyType.FullName)
                && !p.PropertyType.FullName.StartsWith("LightSwitchApplication")
                ).ToArray();
}

VB:

Private Function GetTableData(queryable As IDataServiceQueryable) As DataTable
        Dim table As New DataTable()
        Dim enumerator As System.Collections.IEnumerator = queryable.GetEnumerator()

        'Get column headers
        Dim columnHeaders() As PropertyInfo = Nothing
        If enumerator.MoveNext() Then
            columnHeaders = enumerator.Current.GetType().GetProperties(
                BindingFlags.Public Or BindingFlags.Instance _
                Or BindingFlags.Static)
            columnHeaders = RemoveUnreadableProperties(columnHeaders)

            For Each heading In columnHeaders
                table.Columns.Add(heading.Name, heading.PropertyType)
            Next

            'Write out values
            For Each element In queryable
                Dim row As DataRow = table.NewRow()
                For Each heading In columnHeaders
                    row(heading.Name) = heading.GetValue(element)
                Next
                table.Rows.Add(row)
            Next
        End If

        Return table
End Function
Private Function RemoveUnreadableProperties(columnHeaders() As PropertyInfo) As PropertyInfo()
        Return (From p In columnHeaders
               Where Not p.PropertyType.IsArray AndAlso
               Not p.PropertyType.IsGenericType AndAlso
               Not "Microsoft.LightSwitch.Framework.Server.PersonInfo".Equals(
                   p.PropertyType.FullName) AndAlso
               Not p.PropertyType.FullName.StartsWith("LightSwitchApplication")).ToArray()
End Function

Create the Document

The CreateSummaryWordDoc function creates a word document with the entity name as the title and adds a table listing all the printable properties of the records for that entity.

C#:

private MemoryStream CreateSummaryWordDoc(string entityName, DataTable dataTable)
{
            MemoryStream s = new MemoryStream();

            //Create word document
            using (WordprocessingDocument wordDocument = 
                WordprocessingDocument.Create(s, WordprocessingDocumentType.Document, true))
            {

                wordDocument.AddMainDocumentPart();
                wordDocument.MainDocumentPart.Document = new Document(new Body());
                Body body = wordDocument.MainDocumentPart.Document.Body;

                // Get the Styles part for this document.
                AddStyleDefinitionsPart(wordDocument.MainDocumentPart);
                wordDocument.MainDocumentPart.StyleDefinitionsPart.Styles.Append(GetTitleStyle());
                wordDocument.MainDocumentPart.StyleDefinitionsPart.Styles.Append(GetTableStyle());

                //Add Title to Document
                body.Append(
                    new Paragraph(
                      new ParagraphProperties(new ParagraphStyleId() { Val = "Title" }),
                      new Run(new Text(entityName))
                     )
                );

                //Add table of entries in the entity
                Table table = new Table();
                table.Append(
                    new TableProperties(
                    new TableStyle() { Val = "GridTable1Light-Accent1" },           
                    new TableLook() { 
                        FirstRow = true }
                    )
                );
               
                //Add heading row for table
                TableRow headingRow = new TableRow();
                foreach (var column in dataTable.Columns)
                {
                    headingRow.Append(new TableCell(new Paragraph(
                        new Run(new Text(column.ToString())))));
                }
                table.Append(headingRow);

                //Add text for each event.
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    TableRow row = new TableRow();
                    foreach (var cell in dataRow.ItemArray)
                    {
                        row.Append(new TableCell(new Paragraph(
                            new Run(new Text(cell.ToString())))));
                    }
                    table.Append(row);
                }
                wordDocument.MainDocumentPart.Document.Append(table);

                wordDocument.MainDocumentPart.Document.Save();
                wordDocument.Close();
            }

            return s;
}

VB:

Private Function CreateSummaryWordDoc(entityName As String, data As DataTable) As MemoryStream
        Dim s As New MemoryStream

        'Create Word document
        Using wordDocument As WordprocessingDocument =
            WordprocessingDocument.Create(s, WordprocessingDocumentType.Document, True)

            'Create the main document part,
            ' which is the part that holds the text.
            wordDocument.AddMainDocumentPart()
            wordDocument.MainDocumentPart.Document = New Document(New Body())
            Dim docBody As Body = wordDocument.MainDocumentPart.Document.Body

            'Get the Styles part for this document
            ' and add our custom styles
            If wordDocument.MainDocumentPart.StyleDefinitionsPart Is Nothing Then
                Call New Styles().Save(
                    wordDocument.MainDocumentPart.AddNewPart(Of StyleDefinitionsPart))
            End If
            wordDocument.MainDocumentPart.StyleDefinitionsPart.Styles.Append(
                GetTitleStyle())
            wordDocument.MainDocumentPart.StyleDefinitionsPart.Styles.Append(
                GetTableStyle())

            'Add Title to Document
            docBody.Append(New Paragraph( _
                           New ParagraphProperties(
                               New ParagraphStyleId() With {.Val = "Title"}
                               ),
                           New Run(New Text(entityName)))
                       )

            'Add table of entries in the entity
            Dim t As New Table()
            t.Append( _
                New TableProperties( _
                    New TableStyle() With {.Val = "GridTable1Light-Accent1"},
                    New TableLook() With {.FirstRow = True}
                    )
                )

            'Add heading row for table
            Dim headingRow As New TableRow
            For Each col In data.Columns
                headingRow.Append( _
                    New TableCell(New Paragraph(
                        New Run(New Text(col.ToString()))
                        ))
                    )
            Next
            t.Append(headingRow)

            'Add text for each event
            For Each dataRow As DataRow In data.Rows
                Dim row As New TableRow
                For Each cell In dataRow.ItemArray
                    row.Append(New TableCell(New Paragraph(
                        New Run(New Text(cell.ToString())))))
                Next
                t.Append(row)

            Next

            wordDocument.MainDocumentPart.Document.Append(t)

            wordDocument.MainDocumentPart.Document.Save()
            wordDocument.Close()

        End Using

        Return s

  End Function

Styles

Styles must be added to documents created using OpenXml. Below is the code necessary to create the Heading 1 style used by CreateSummaryWordDoc. 

C#:

Style GetTitleStyle()
{
            Style titleStyle = new Style()
            {
                Type = StyleValues.Paragraph,
                StyleId = "Title",
                CustomStyle = true
            };
            titleStyle.Append(
                 new StyleName() { Val = "Title" },
                 new StyleRunProperties(
                      new Color() { Val = "2E74B5" },
                      new FontSize() { Val = "56" }
                       )
                );
            return titleStyle;
}

VB:

 Private Function GetHeading1Style() As Style
        Dim heading1Style As New Style With {.Type = StyleValues.Paragraph,
                                             .StyleId = "Heading1"}
        heading1Style.Append( _
            New StyleName() With {.Val = "Heading 1"},
            New BasedOn() With {.Val = "Normal"},
            New NextParagraphStyle With {.Val = "Normal"},
            New StyleParagraphProperties( _
                New KeepNext(),
                New KeepLines(),
                New SpacingBetweenLines() With {.Before = "240", .After = "0"}
                ),
            New StyleRunProperties( _
                New Color() With {.Val = "2E74B5"},
                New FontSize() With {.Val = "32"}
               )
        )
        Return heading1Style
End Function

The code for the table styling is a little more complicated as the style for the cells and borders have to be specified separately.  Conditional formatting also has to be described if the table style is to support First Row, Last Row, First Column, etc. conditional formatting in Word tables.  The following code is a simplified version of the Grid Table 1 Light Accent 1 table style found a normal Word document.  This style only supports First Row conditional formatting, but other conditional formatting could easily be added to the end of the style definition.  The Open XML SDK 2.5 Productivity Tool (part of the Open Xml SDK download) is helpful when trying to craft styles for an document.

C#:

Style GetTableStyle()
{
                Style tableStyle = new Style() { Type = StyleValues.Table, 
                    StyleId = "GridTable1Light-Accent1" };
                tableStyle.Append(
                    new StyleName() { Val = "Grid Table 1 Light Accent 1" },
                    new StyleParagraphProperties(
                        new SpacingBetweenLines() { After = "0", Line = "240", 
                            LineRule = LineSpacingRuleValues.Auto }
                        ),
                    new StyleTableProperties(
                        new TableStyleRowBandSize() { Val = 1 },
                        new TableStyleColumnBandSize() { Val = 1 },
                        new TableIndentation() { Width = 0, Type = TableWidthUnitValues.Dxa },
                        new TableBorders(
                            new TopBorder() { Val = BorderValues.Single, Color = "BDD6EE" },
                            new LeftBorder() { Val = BorderValues.Single, Color = "BDD6EE" },
                            new BottomBorder() { Val = BorderValues.Single, Color = "BDD6EE" },
                            new RightBorder() { Val = BorderValues.Single, Color = "BDD6EE" },
                            new InsideHorizontalBorder() { Val = BorderValues.Single,
                                Color = "BDD6EE" },
                            new InsideVerticalBorder() { Val = BorderValues.Single, 
                                Color = "BDD6EE" }
                            ),
                        new TableCellMarginDefault(
                            new TableCellLeftMargin() { Width = 108, Type = TableWidthValues.Dxa},
                            new TableCellRightMargin() { Width = 108, Type = TableWidthValues.Dxa}
                            )
                        ),
                    new TableStyleProperties(
                        new RunPropertiesBaseStyle(
                            new Bold()
                            ),
                        new TableStyleConditionalFormattingTableProperties(
                            new TableStyleConditionalFormattingTableCellProperties(
                                new BottomBorder() { Val = BorderValues.Double, Color = "9CC2E5" }
                                )
                            )
                        ) { Type = TableStyleOverrideValues.FirstRow }
                 );      
            
            return tableStyle;
}

VB:

 Private Function GetTableStyle() As Style
        Dim tableStyle As New Style() With {.Type = StyleValues.Table,
                                            .StyleId = "GridTable1Light-Accent1"}
        tableStyle.Append(
            New StyleName() With {.Val = "Grid Table 1 Light Accent 1"},
            New StyleParagraphProperties(
                New SpacingBetweenLines() With {.After = "0", .Line = "240",
                                                .LineRule = LineSpacingRuleValues.Auto}
                ),
            New StyleTableProperties(
                New TableStyleRowBandSize() With {.Val = 1},
                New TableStyleColumnBandSize With {.Val = 1},
                New TableIndentation() With {.Width = 0, .Type = TableWidthUnitValues.Dxa},
                New TableBorders(
                    New TopBorder() With {.Val = BorderValues.Single, .Color = "BDD6EE"},
                    New LeftBorder() With {.Val = BorderValues.Single, .Color = "BDD6EE"},
                    New RightBorder() With {.Val = BorderValues.Single, .Color = "BDD6EE"},
                    New BottomBorder() With {.Val = BorderValues.Single, .Color = "BDD6EE"},
                    New InsideHorizontalBorder() With {.Val = BorderValues.Single,
                                                       .Color = "BDD6EE"},
                    New InsideVerticalBorder() With {.Val = BorderValues.Single,
                                                     .Color = "BDD6EE"}
                    ),
                New TableCellMarginDefault(
                    New TableCellLeftMargin() With {.Width = 108, .Type = TableWidthValues.Dxa},
                    New TableCellRightMargin() With {.Width = 108, .Type = TableWidthValues.Dxa}
                    )
                ),
            New TableStyleProperties(
                New RunPropertiesBaseStyle(New Bold()),
                New TableStyleConditionalFormattingTableProperties(
                    New TableStyleConditionalFormattingTableCellProperties(
                        New BottomBorder() With {.Val = BorderValues.Double, .Color = "9CC2E5"}
                        )
                    )
                ) With {.Type = TableStyleOverrideValues.FirstRow}
            )
        Return tableStyle
 End Function

Send A Response

We will use the same function to craft an HttpResponseMessage with the document as part of the response as mentioned in the first article in this series. I’ve included the code below for easier reference.

C#:

 private HttpResponseMessage GetWordDocResponseMessage(MemoryStream s, String suggestDocName)
        {
            HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
            //write the document
            s.Position = 0;
            result.Content = new StreamContent(s);
            //Set the content type to .docx
            result.Content.Headers.ContentType = 
                new System.Net.Http.Headers.MediaTypeHeaderValue(
                    "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
            //Set the name of the file
            result.Content.Headers.ContentDisposition = 
                new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
            result.Content.Headers.ContentDisposition.FileName = suggestDocName;
            return result;
        }

VB:

 Private Function GetWordDocResponseMessage(s As MemoryStream, suggestedDocName As String) _
        As HttpResponseMessage

        Dim result As New HttpResponseMessage(HttpStatusCode.OK)

        'Write the document
         s.Position = 0
         result.Content = New StreamContent(s)
        'Set the content type to .docx
        result.Content.Headers.ContentDisposition = New Headers.ContentDispositionHeaderValue("attachment")
        result.Content.Headers.ContentDisposition.FileName = suggestedDocName

        Return result
 End Function

Try it Out

We have now created all the code necessary create a summary document. Try launching the application and typing http://localhost:[debuggingPort]/reports/ExportToWord/Events in the Internet Explorer address bar. You should see something like the following.

image

Open From the Client

To create the report from the client, we need a button to open a window to the correct url on the server.  In the case of the Event Manager, I have added a button called GetReport to the command bar of the browse screen for the EventSession entity.

image

The execute code for the button is as follows.

myapp.BrowseEventSessions.GetReport_execute = function (screen) {
    window.open("../reports/ExportToWord/Events");
};

Conclusion

In this article series we learned how to use the LightSwitchServerContext along with the Open XML SDK to generate reports in Word documents for our LightSwitch HTML application. Generating Word documents gives users an easy way to share information from a LightSwitch application. With the power of OpenXml, these reports can be incredibly beautiful. I hope you’ve found this article series useful.

-Elizabeth Maher, Senior SDET, Cloud Business Apps Team