Kerning (Character Spacing) with Microsoft Office Embedded Charts

Note: This probably applies to copy/pasted charts between Excel/Word, I was using VBA, so I had to find the VBA way to fix it.

I like Office VBA automation. Sometimes Microsoft makes it hard for me to remember that fact though.

The usual problem is that the easy stuff is easily automated with VBA. Which is good. But if you want to do something slightly irregular, dare I say even advanced, then you're in for a world of hurt.

When copying and pasting charts between Excel and Word, the traditional wisdom is to copy and paste as a picture because it's the most reliable reproduction of the original chart once pasted.

Fine if all you want is a static chart pasted into a Word document, but if you want to give the Word user the ability to modify the chart or data, then you need to copy and paste a chart object.

Although it's fully supported by the VBA interop runtime, the results are less than fantastic.
Here's some code that pastes an existing Excel Chart Object

Sub PasteChartAsInteractive(chart As Excel.chart1)
    Dim PageWidth As Long
    Dim WidthRatio As Double
    chart.ChartArea.Copy
    Dim myShape As Shape
    
    Selection.Style = ActiveDocument.Styles("Normal")
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

    'Commented out line is pasting as image.
    'Selection.PasteSpecial Placement:=wdInLine, DataType:=wdPasteMetafilePicture

    Selection.PasteAndFormat (wdChart)
    
    Selection.EndOf Unit:=wdParagraph, Extend:=wdMove
    Selection.TypeParagraph
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    
    Selection.range.ListFormat.ApplyBulletDefault
    Selection.TypeText "..." + vbCr

End Sub

But the problem with the inserted chart is that the kerning is all messed up. Observe the results.

The kerning fault is especially noticeable in the title above. There is virtually no spacing between characters in any letters. Some even appear to overlap.

Google searches mostly turn up the workaround of 'paste as image', which was not an option for me, the chart had to be editable and live.

I had given up and was going to move away from Excel Charts and into using Word Graph Objects This was going to be an ordeal because I had a bunch of existing charts in this macro, pasted as images, and the formatting all needed to be consistent. My impression is that the graph and chart API's are not really that similar.

Then I stumbled on a solution with Clouseau-esque serendipity. Changing the object layout (Format -> Object -> Layout) to use 'Square' rather than 'In-line' made the kerning problem disappear. What's more, I could change the layout back to 'In-line' and kerning was still proper.

There was a minor issue with finding the appropriate commands to do this with VBA. Normally I record a macro of the action I want to reproduce if I'm not totally sure how. With macros, the Layout tab is disabled, so I had to dig around online to find the VBA.

Sub PasteChartAsInteractive(chart As Excel.chart)
    Dim PageWidth As Long
    Dim WidthRatio As Double
    chart.ChartArea.Copy
    Dim myShape As Shape
    
    Selection.Style = ActiveDocument.Styles("Normal")
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    
    Selection.PasteAndFormat (wdChart)
    Set myShape = Selection.Paragraphs(1).range.InlineShapes(1).ConvertToShape
    myShape.ConvertToInlineShape
    
    Selection.EndOf Unit:=wdParagraph, Extend:=wdMove
    Selection.TypeParagraph
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    
    Selection.range.ListFormat.ApplyBulletDefault
    Selection.TypeText "..." + vbCr

End Sub

And the result

It's too bad that we have to resort to these cheap tricks when trying to get members of the Office suite to behave together. You would think by now, the integration would be much tighter.

By the way, did you notice that the Y-axis title is truncated in both images? The fix for that is to add superfluous characters to the Y-axis title until all the important characters show.

Paginated Printing of WPF Visuals - II

Last time I discussed paginated printing of wpf visuals, I mentioned that the pagination didn't support a whole lot of options.

There was no support for margins or headers for example. The print was edge-to-edge which is OK when you're a developer and you're going to throw the paper out right after printing, but not so good if you are the end user and need to put the paper in a binder, or if you've dropped the whole mess on the floor and need to put it back in order.

The easy way to put more visuals with the visuals you have rendered is to use a ContainerVisual object and stack some visuals together. With my previous entry there was a problem because it transformed and rendered the original Visual directly to print. This meant that the Visual could not be part of ContainerVisual as it already had a visual parent.

To solve that problem, I rendered the Visual to bitmap before print. That way I could use ContainerVisual with the bitmap and stack up any number of visuals in the container. Here's the class.

class FrameworkVisualPrint : DocumentPaginator
{
    private readonly FrameworkElement Element;
    private const double XMargin = 1 * 96;
    private const double YMargin = 1 * 96;
    public String Header { set; get; }

    public FrameworkVisualPrint(FrameworkElement element)
    {
        Element = element;
    }

    private const double ScaleFactor = 0.5;

    private DrawingVisual GetHeader(int pageNumber)
    {
        var header = new DrawingVisual();

        using (var dc = header.RenderOpen())
        {
            var text = new FormattedText("Page " + (pageNumber + 1) + Environment.NewLine +
                Header, CultureInfo.CurrentCulture, FlowDirection.LeftToRight, 
                new Typeface("Arial"), 8, Brushes.Black);
            dc.DrawText(text, new Point(XMargin, 0.5 * 96));
        }

        return header;
    }

    public override DocumentPage GetPage(int pageNumber)
    {
        var tgroup = new TransformGroup();
        tgroup.Children.Add(new ScaleTransform(ScaleFactor, ScaleFactor));
        Element.RenderTransform = tgroup;
        tgroup.Children.Add(new TranslateTransform(-PageSize.Width * (pageNumber % Columns) 
            + XMargin, -PageSize.Height * (pageNumber / Columns) + YMargin));
        Element.RenderTransform = tgroup;

        try
        {
            var area = new Rect(
                new Point((PageSize.Width * (pageNumber % Columns)) / ScaleFactor,
                          (PageSize.Height * (pageNumber / Columns)) / ScaleFactor),
                new Size(PageSize.Width / ScaleFactor,
                         pageSize.Height / ScaleFactor));

            Element.Clip = new RectangleGeometry(area);
            var elementSize = new Size(
                Element.ActualWidth,
                Element.ActualHeight);
            Element.Measure(elementSize);
            Element.Arrange(new Rect(new Point(0, 0), elementSize));

            // Make a bitmap from the transformed Element
            var bitmap = PngBitmap(Element, (int)area.Width, (int)area.Height);
            var vis = new DrawingVisual();

            // Draw the bitmap into a DrawingVisual object
            DrawingContext dc = vis.RenderOpen();
            dc.DrawImage(bitmap, new Rect(0, 0, area.Width, area.Height));
            dc.Close();

            // Grab a header to apply (with page numbers)
            var header = GetHeader(pageNumber);
            // Container for all the new stuff.
            var cVisual = new ContainerVisual();

            cVisual.Children.Add(header);
            cVisual.Children.Add(vis);
            return new DocumentPage((cVisual));

        }
        finally
        {
            Element.RenderTransform = null;
            Element.Clip = null;
        }

    }

    private RenderTargetBitmap PngBitmap(Visual visual, int width, int height)
    {
        // Render
        var rtb =
            new RenderTargetBitmap(
                (int)(width / ScaleFactor),
                (int)(height / ScaleFactor),
                96.0 / ScaleFactor,
                96.0 / ScaleFactor,
                PixelFormats.Default);
        rtb.Render(visual);


        return rtb;
    }

    private Size pageSize;
    public override Size PageSize
    {
        set
        {
            pageSize = value;
            pageSize.Width -= XMargin;
            pageSize.Height -= YMargin;
        }
        get { return pageSize; }
    }
}

The class isn't actually complete. See the previous article for the initial version. This is just new and changed methods.

The important bits are commented.

• Make a bitmap from the Visual
• Draw the bitmap to a new DrawingVisual Object
• Stick the DrawingVisual and any other visuals needed in a ContainerVisual
• Bob's your uncle!