Formatting Cell in a generated Word document

Question

I am generating invoices from within my LightSwitch billing application, and after an invoice is generated, the SubTotal, SalesTax and Total Due cells in the Word document are not of the correct format.

I have gone through every single menu option, and explored other possible ways to have it formatted before it gets put in the document's Content control, but I just can't get the right format.

The numbers are being displayed as 4100.0000, but they should be like 4,100.00 or at least like 4100.00.

How can this be done?

I realise that this is borderline off-topic, but I figured I'd post here anyway as I believe the solution involves writing code, as opposed to using Word to accomplish this, because I have gone through all the options, and there just doesn't seem to be an option in there to change formatting for a single cell.

enter image description here

As requested, code for document generation:

using System;
using System.Linq;
using System.IO;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Client;
using Microsoft.LightSwitch.Presentation;
using Microsoft.LightSwitch.Presentation.Extensions;
using OfficeIntegration;
namespace LightSwitchApplication
{
    public partial class Billing
    {
        partial void GenerateInvoice_Execute()
        {
            var mappings = new List<ColumnMapping>();

            mappings.Add(new ColumnMapping("InvoiceId", "InvoiceId"));
            mappings.Add(new ColumnMapping("DateGenerated", "DateGenerated"));

            mappings.Add(new ColumnMapping("InvoiceDataGridSubTotal", "SubTotal"));
            mappings.Add(new ColumnMapping("InvoiceDataGridSalesTax", "Tax"));
            mappings.Add(new ColumnMapping("InvoiceDataGridDateDue", "DateDue"));
            mappings.Add(new ColumnMapping("InvoiceDataGridTotalDue", "Total"));

            object doc;
            string path = @"C:\Users\Jason\Documents\SV Invoice.docx";

            doc = OfficeIntegration.Word.GenerateDocument(path, this.BillingTables.SelectedItem, mappings);
        }
    }
}

The applpication auto-populates the cells in the word document. But when I enter the numbers into my lightswitch application, I **always** enter them as `4,100.00`. And when I don't, the Lightswitch app auto-formats them anyway - so they should be coming up in the Word document with the correct formatting, I would assume. — Arrow, Nov 22 '12 at 10:02
What do you mean with "auto-populate"? Please show the code. — Daniel Hilgarth, Nov 22 '12 at 10:12

Arrow · Answer 1 · 2012-11-23T06:28:15.747

Code and Word formatting are unnecessary in this case. In order to have your numbers correctly formatted in the dynamically-generated Word document, you must set the Format to use from within the Data Designer in Visual Studio LightSwitch:

Open your LightSwitch Project
Expand the Project Node > Open a Table containing a number you'd like to format
Select the Field that requires formatting
In the Properties pane, scroll down until you see Formatting (Format Pattern)
Now here's where you format your numbers. See this link for your available options. I chose to format using C2.

Following the above steps, I was able to generate a Word document with the following format for money:

4500.00 instead of 4500.0000.

Daniel Hilgarth · Accepted Answer · 2012-11-22T11:04:54.290

1

You should be able to supply a third parameter to the ColumnMapping constructor: A delegate that formats the values:

Func<decimal, string> formatDelegate = x => x.ToString("c2");
mappings.Add(new ColumnMapping("InvoiceDataGridSubTotal", "SubTotal",
                               FormatDelegate: formatDelegate));

edited Nov 22 '12 at 11:04

answered Nov 22 '12 at 10:21

Daniel Hilgarth

171,043
40
335
443

`Cannot convert lambda expression to type 'object' because it is not a delegate type.`, says Intellisense. – Arrow Nov 22 '12 at 10:27
`The name 'Format' does not exist in the current context.` re: the update. – Arrow Nov 22 '12 at 10:36
@Iateyourtoothpaste: What about the new version? – Daniel Hilgarth Nov 22 '12 at 10:50
sorry, it does not produce errors *but it doesn't display the number anymore. It simply outputs the following text to the word document:* System.Func`2[System.Decimal,System.String] – Arrow Nov 22 '12 at 10:53
Could you please show me which parameters the ColumnMapping constructor has? And please try the new version... BTW: I am currently getting my info from here: http://blogs.msdn.com/b/bethmassi/archive/2012/07/18/new-and-improved-office-integration-pack-extension-for-lightswitch.aspx - Point 3. – Daniel Hilgarth Nov 22 '12 at 11:04
Thank you very much for your help. It led me down the right path to figuring out how to format them - and I also learned how to use delegates lol - I've managed to solve the issue; I just wrote an answer. RE: Params for the ColumnMapping constructor: `ColumnMapping.ColumnMapping(string OfficeColumnName, List TableFields)` and also `ColumnMapping.ColumnMapping(string OfficeColumnName, string EntityFieldName, [objectStaticValue = null], [Delegate FormatDelegate = null])` – Arrow Nov 23 '12 at 06:23
1

@Iateyourtoothpaste: Thanks for the parameters of the constructor. My latest version should work than. But your approach is the way to go for simple formattings. – Daniel Hilgarth Nov 23 '12 at 08:37

Formatting Cell in a generated Word document

2 Answers2