17 March 2021

Export Excel charts as SVG files

Speaking of Excel and its numerous use cases, there is a certain missing feature that I have wanted for years: the ability to export charts in SVG format. A vector-based file format, SVG is particularly well suited to render charts and scale them to almost any resolution, because it redraws chart components to match the new size instead of enlarging static pixels, as regular image formats do. There were complicated workarounds involving exporting to PDF first and then extracting charts with third-party software, but I always found them too cumbersome for regular use.

But during the last quarter of 2019, the process became a lot simpler after Office 365 added an option to convert PowerPoint slides to SVG files. To convert an Excel chart instead, all you need to do now is to copy that chart into a blank PowerPoint presentation and save that slide as SVG! I have used this method several times for my blog, both for the articles related to the coronavirus pandemic, as well as for other articles with charts and visualizations.

There are a couple of other matters to solve though to effectively use these SVG files on the web. The first issue for me was that Blogger does not accept SVG file uploads, so I had to find another place to host them. While you can upload these files to Google Drive or OneDrive, you do not get a proper URL to use as image file source in a blog post, and I did not want to start paying for file hosting for a simple personal project as this blog.

After some searching, I finally settled on GitHub, where I started a new account and uploaded the SVG charts. But since the files are now served from another server than the rest of the blog, there is a small chance these charts will fail to load for people visiting the blog. To prevent that, I am using the picture element to provide a back-up image in case the SVG file is unavailable (or if the browser does not support the format, but this is a minor concern nowadays). To make things easier, when I copy the chart from Excel to PowerPoint as described above, I save two versions, one as SVG and the other in PNG format; this also ensures that the images have the same size. In the blog post I then upload the PNG image through Blogger’s regular interface, and put both URLs inside picture using this template:

<picture class="graph">
	<source type="image/svg+xml" srcset="path_to/SVG_version/on_GitHub.svg">
	<img src="path_to/fallback_version/on_Blogger.png" alt="Image description">
</picture>

The SVG file can be further optimized to improve its display and reduce its size. Since it is basically a plain text file with a different extension, these changes can be easily done in any text editor. The most important tweak for me is to make the chart fully responsive. The version originally exported from PowerPoint has a fixed size declared in the file header:

<svg width="960" height="720" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" overflow="hidden">

In this case the chart would not grow beyond these dimensions when embedded on a web page. I usually replace these sizes with a viewBox attribute, which preserves the placement of the chart elements, but removes the width and height restrictions and allows the image to scale naturally with the web page:

<svg viewBox="0 0 960 720" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" overflow="hidden">

It you do not want to manually edit the file, you can use an online SVG optimization tool such as SVGOMG. It includes an option to replace static dimensions with a viewBox.

Another thing that I like to fix in these SVG charts are the fonts declarations. The export uses inline styles for each text box, which is highly redundant and makes the file larger than it needs to be:

<text fill="#404040" font-family="Segoe UI,Segoe UI_MSFontService,sans-serif" font-weight="400" font-size="13" transform="translate(833.968 327)">

I like to remove most of these inline styles (especially font-family and font-weight) and replace them with a style declaration at the beginning of the file, immediately after the opening <svg> tag. Since SVG respects CSS cascading rules, these font styles will be applied to all text boxes in this file. This has the advantage of creating a more consistent overall look for the chart, and you can match it to the font used on the rest of the site.

<style>
	text { font-family: "Segoe UI",Candara,sans-serif; font-weight: 400; }
</style>

Takes a bit of work, but personally I think it is worth it for the better resolution of the resulting chart.

Post a Comment