GrapeCity has recently released Excel Viewer, a Visual Studio Code extension that uses the FlexGrid (our JavaScript DataGrid) and FlexSheet controls from Wijmo 5 to preview CSV files and Excel spreadsheets. FlexGrid is also available as an Angular DataGrid, React DataGrid and Vue DataGrid. Developed by Microsoft, VS Code is a free, open source code editor that runs on Windows, Mac OS X, and Linux. It includes built-in support for JavaScript, TypeScript, and Node.js, with features such as IntelliSense, parameter hints, and smart code navigation. For version control, VS Code integrates seamlessly with any Git repository. In addition to all of its out-of-the-box features, VS Code provides a rich extensibility model that lets you add custom functionality, as well as a vibrant marketplace where you can easily browse and install extensions that have been contributed by other developers. This blog post describes how to use the Excel Viewer extension and explains in detail how it was implemented. You can view the source code here on GitHub.
Excel Viewer in Action
If you haven't already done so, visit code.visualstudio.com to install VS Code for your platform. To install the Excel Viewer extension, launch the VS Code Quick Open command (Ctrl+P on Windows, Command+P on OS X) and enter the following command:
ext install gc-excelviewer
Open any CSV file, with or without a .csv extension, then launch the VS Code Command Palette (F1 key). Run the following command:
CSV: Preview File
The contents of the file will be displayed in a FlexGrid control, which supports sorting and filtering via its column headers. Since VS Code does not support opening binary files, the steps are slightly different for Excel spreadsheets. First, open a folder containing one or more Excel files, then open the command palette and run the following command:
Excel: Preview File
You will be presented with a list of Excel files within the current folder (including files in subfolders, if any). Select the desired file to view it in a FlexSheet control. If multiple sheets are present, use the controls at the bottom of the view for navigation.
Writing an Extension
Now let's turn our attention to the code that implements the preview commands. If you have never written a VS Code extension before, please see the topic Extending Visual Studio Code in the online documentation. In particular, I recommend following the steps in the Hello World tutorial to ensure that you have installed the prerequisites (Node.js) and can use the Yeoman extension generator to create the files for a new extension in TypeScript. You should also become familiar with the process of running and debugging extensions using a secondary instance of VS Code known as the Extension Development Host. For clarity, the code samples that follow have been pared down to illustrate specific concepts. You can view the actual source code in the gc-excelviewer repository on GitHub.
Specifying the Package Manifest
All extensions must have a package manifest file named package.json that describes the following characteristics of the extension:
- Internal name, display name, and description strings
- Version number string in SemVer format
- Icon to use in the marketplace
- Categories and keywords to use in the marketplace
- Publisher name
- Code entry point (main)
- Conditions that will cause the extension to be activated (activationEvents)
- Functionality contained in the extension (contributes)
See this topic for a complete description of the package manifest file schema. Here is a condensed view of the Excel Viewer package manifest that shows most of the key sections:
{
"name": "gc-excelviewer",
"displayName": "Excel Viewer",
"description": "View Excel spreadsheets and CSV files within Visual Studio Code workspaces.",
"version": "1.0.7",
"icon": "img/gc-excelviewer.png",
"publisher": "GrapeCity",
"activationEvents": [
"onCommand:csv.preview",
"onCommand:excel.preview"
],
"main": "./out/src/extension",
"contributes": {
"languages": [
{
"id": "csv",
"extensions": [
".csv"
],
"aliases": [
"CSV"
]
}
],
"commands": [
{
"command": "csv.preview",
"title": "CSV: Preview File"
},
{
"command": "excel.preview",
"title": "Excel: Preview File"
}
]
}
}
The most important contribution points here are the two commands. Note that the extension will not be activated unless the user executes one of them. It doesn't matter which command is executed first, as the extension will only be activated once. The package manifest also defines a languages section for CSV files so that the extension code can readily identify file types that it can handle (although it will still work with any plain text file, regardless of its file extension). Although not shown above, the section named contributes also contains a set of configuration properties that are exposed to the user as settings in VS Code:
"configuration": {
"type": "object",
"title": "Excel Viewer configuration",
"properties": {
"csv-preview.separator": {
"type": "string",
"default": ",",
"description": "Specifies the separator used in a CSV file (normally , but also ; is common)."
},
"csv-preview.quoteMark": {
"type": "string",
"default": "\\"",
"description": "Specifies the optional character used to surround individual values in a CSV file."
},
"csv-preview.hasHeaders": {
"type": "boolean",
"default": true,
"description": "Specifies whether the first row in a CSV file represents column headers."
},
"csv-preview.theme": {
"type": "string",
"default": "cleandark",
"description": "Specifies the Wijmo theme used to style the preview grid."
}
}
}
This allows the user to specify that a semi-colon should be used as a field separator instead of a comma, for example. User settings of configuration properties are visible to extension code by means of the workspace.getConfiguration API.
Implementing a Command
All VS Code extensions have a function named activate as their entry point. Defined in the main file, src/extension.ts, this function is called once whenever any of the activationEvents described in the package manifest occur. As discussed in the preceding section, none of our extension code will execute until a user actually runs one of our commands from the Command Palette. The main file has three import statements. The first references namespaces from the vscode API. The others reference the CsvDocumentContentProvider and ExcelDocumentContentProvider classes, which our extension implements.
import {window, workspace, commands, ExtensionContext, Uri, ViewColumn} from 'vscode';
import * as csv from './csvProvider';
import * as excel from './excelProvider';
To keep things simple, we'll focus on the CSV preview command only for now. The relevant portion of the activate function is as follows:
export function activate(context: ExtensionContext) {
let version = "5.20161.151";
let previewUri: Uri;
let csvProvider = new csv.CsvDocumentContentProvider(version);
let csvSubscription = workspace.registerTextDocumentContentProvider('csv-preview', csvProvider);
let csvCommand = commands.registerCommand('csv.preview', () => {
if (!window.activeTextEditor) {
window.showInformationMessage("Open a CSV file first to show a preview.");
return;
}
let file = window.activeTextEditor.document.fileName;
if (file.startsWith("/")) {
file = file.substring(1);
}
previewUri = Uri.parse(\`csv-preview://preview/${file}\`);
return commands.executeCommand('vscode.previewHtml', previewUri, ViewColumn.One).then((success) => {
}, (reason) => {
window.showErrorMessage(reason);
});
});
context.subscriptions.push(csvProvider, csvCommand, csvSubscription);
}
Note that an instance of CsvDocumentContentProvider is created and registered with the uri scheme csv-preview (with a hyphen). Our command, named csv.preview (with a period), is registered with a callback function that performs the following tasks:
- Displays a warning message and terminates if no editor is active.
- Constructs a uri from our scheme and the active document's file system path.
- Invokes the complex command vscode.previewHtml on the uri.
Lastly, the activate function ends by associating all disposables (the document content provider, the command itself, and the scheme/provider registration) with the extension context.
Implementing a Virtual Document for CSV Files
Now that we've seen how an extension command is wired up with a uri scheme and a document content provider, let's examine how our extension converts raw text into a preview view. In VS Code, the term virtual document refers to a textual document that doesn't have a representation on disk, but is generated at runtime. The TextDocumentContentProvider interface is the vehicle for creating virtual documents in VS Code extensions. Since our extension implements two document content providers, one for CSV and one for Excel, we created an abstract class from which both inherit. The implementation is in src/baseProvider.ts:
export abstract class BaseDocumentContentProvider implements TextDocumentContentProvider {
private _version: string;
private _onDidChange = new EventEmitter<Uri>();
constructor(version: string) {
this._version = version;
}
dispose() {
this._onDidChange.dispose();
}
get version(): string {
return this._version;
}
get theme(): string {
return <string>workspace.getConfiguration('csv-preview').get("theme");
}
public provideTextDocumentContent(uri: Uri): string | Thenable<string> {
return this.createSnippet();
}
get onDidChange(): Event<Uri> {
return this._onDidChange.event;
}
abstract createSnippet(): string;
abstract snippet(text: string, theme: string, ver: string): string;
}
The key method here is provideTextDocumentContent, which takes an argument of type Uri and returns an HTML fragment that is used to render the virtual document. The onDidChange event is also part of the virtual document protocol. The version string passed to the constructor denotes a Wijmo version number, which is used by subclasses when generating the virtual document. The theme property derives its value from the extension's configuration section, which allows users to override the default value. Note that this is a Wijmo theme, not to be confused with VS Code themes. The abstract methods createSnippet and snippet are responsible for generating the strings representing virtual documents. Here is a condensed implementation of CsvDocumentContentProvider, taken from src/csvProvider.ts:
import {workspace, window} from 'vscode';
import * as base from './baseProvider';
var Base64 = require('js-base64').Base64;
var escapeStringRegexp = require('escape-string-regexp');
export class CsvDocumentContentProvider extends base.BaseDocumentContentProvider {
createSnippet(): string {
let editor = window.activeTextEditor;
let lang = editor.document.languageId;
if (lang !== 'csv' && lang !== 'plaintext') {
return this.errorSnippet("Active editor doesn't show a CSV or plain text document.");
}
let t = editor.document.getText();
let b = Base64.encode(t);
let snip = this.snippet(b, this.theme, this.version);
return snip;
}
snippet(text: string, theme: string, ver: string): string {
let sep = escapeStringRegexp(this.separator);
let quote = escapeStringRegexp(this.quoteMark);
return \`Version ${ver}\`;
}
}
Following the import statements, note the require calls that instantiate references to two NPM packages used for string manipulation:
- js-base64 performs Base64 encoding/decoding.
- escape-string-regexp escapes characters that are special within regular expressions.
The createSnippet method returns a virtual document or an error message if the active editor does not contain a valid text file. If the file is valid, the contents of the active editor are converted to a Base64 string and passed to the snippet method along with the Wijmo theme and version strings. Using Base64 encoding allows us to represent multiple lines of text with a single-quoted string literal that can be declared within the virtual document. This is necessary because when the virtual document is actually rendered, it will not have access to the vscode API. The escapeStringRegexp function is needed for the separator and quoteMark properties, which are derived from the extension's configuration section, just like the theme property in the base class. They need to be escaped because they will be used within the context of a regular expression within the virtual document. The return statement in the snippet method listed above is just an example to illustrate the use of template literals with embedded expressions, such as the version string. Omitting the surrounding quotes, let's break down the contents of the actual document template. First, we include references to style sheets and scripts from the Wijmo CDN, substituting the specified version number and theme:
<link href="http://cdn.wijmo.com/${ver}/styles/wijmo.min.css" rel="stylesheet" type="text/css" />
<link href="http://cdn.wijmo.com/${ver}/styles/themes/wijmo.theme.${theme}.min.css" rel="stylesheet" type="text/css" />
<script src="http://cdn.wijmo.com/${ver}/controls/wijmo.min.js" type="text/javascript"></script>
<script src="http://cdn.wijmo.com/${ver}/controls/wijmo.input.min.js" type="text/javascript"></script>
<script src="http://cdn.wijmo.com/${ver}/controls/wijmo.grid.min.js" type="text/javascript"></script>
<script src="http://cdn.wijmo.com/${ver}/controls/wijmo.grid.filter.min.js" type="text/javascript"></script>
<script src="http://cdn.wijmo.com/external/js-base64.js" type="text/javascript"></script>
The last script reference is not part of Wijmo per se, but was taken from the NPM package js-base64. Although we use the package for string encoding, the decoding step has to be performed outside of the package context, hence the external file reference. Next, we declare a DIV element as a placeholder for the extension's content, which will be a FlexGrid control:
<body>
<div id="flex"></div>
</body>
The remainder of the document template consists of JavaScript code, which begins by decoding the Base64 string, then converting it to an array of strings, one for each line in the active document:
var data = [], header = [];
var content = Base64.decode('${text}');
var lines = content.split(String.fromCharCode(10));
Then, the script iterates through each line in the array, using a regular expression to split it according to the separator setting, while also taking care to ignore separators that occur within a matching pair of quoteMark delimiters:
for (var i = 0; i < lines.length; i++) {
var line = lines[i];
if (line.length > 0) {
// http://markmintoff.com/2013/03/regex-split-by-comma-not-surrounded-by-quotes/
var items = line.split(/${sep}(?=(?:[^${quote}]*${quote}[^${quote}]*${quote})*[^${quote}]*$)/);
if (i === 0 && ${this.hasHeaders}) {
for (var j = 0; j < items.length; j++) {
header.push(unquote(items[j]));
}
} else {
var obj = {};
for (var j = 0; j < items.length; j++) {
obj[getHeader(j)] = unquote(items[j]);
}
data.push(obj);
}
}
}
If hasHeaders is true, the split strings are saved to an array when processing the first line. The internal function getHeader then uses this array to return the column name corresponding to a numeric index. Otherwise, if hasHeaders is false, the getHeader function generates automatic column names as in Excel (A, B, ..., Z, AA, AB, ..., AZ, BA, BB, ...):
function getHeader(n) {
if (header.length > n) {
return header[n];
}
var h1 = Math.floor(n / 26);
var h2 = n % 26;
if (h1 > 0) {
return String.fromCharCode(64 + h1) + String.fromCharCode(65 + h2);
} else {
return String.fromCharCode(65 + h2);
}
}
The internal function unquote uses a much simpler regular expression to strip the surrounding quoteMark delimiters, if present:
function unquote(text) {
if (text.length > 0) {
var regex = new RegExp(/^${quote}(.*)${quote}$/);
var match = regex.exec(text);
return match ? match[1] : text;
}
return text;
}
For each non-empty line of text, we create a JavaScript object where the properties are the column names (derived from the first line or automatically generated) and the values are the matching strings parsed by the complex regular expression. Each object is then pushed onto the end of the data array, which the newly instantiated FlexGrid control can readily consume:
var flex = new wijmo.grid.FlexGrid("#flex");
flex.isReadOnly = true;
flex.itemsSource = data;
Finally, we create a FlexGridFilter object that adds an Excel-style filtering interface to the grid's column headers:
var filter = new wijmo.grid.filter.FlexGridFilter(flex);
Implementing a Virtual Document for Excel Files
The virtual document provider for Excel follows a similar implementation pattern, but is more straightforward, as it does not involve Base64 encoding or string parsing with regular expressions, nor does it require any NPM packages. Here is a condensed implementation of ExcelDocumentContentProvider, taken from src/excelProvider.ts:
import {workspace, Uri} from 'vscode';
import * as base from './baseProvider';
export class ExcelDocumentContentProvider extends base.BaseDocumentContentProvider {
private _path = "dev/null";
public setPath(path: string) {
if (path.startsWith("/")) {
this._path = path.slice(1);
} else {
this._path = path;
}
}
createSnippet(): string {
let file = Uri.file(workspace.rootPath + "/" + this._path);
let snip = this.snippet(file.toString(), this.theme, this.version);
return snip;
}
snippet(file: string, theme: string, ver: string): string {
return \`File ${file}\`;
}
}
Unlike the CSV provider, which uses the contents of the active text editor, the Excel provider constructs a local uri with the file: scheme, which is used within the document template returned by the snippet method. As shown in the previous section, the template string begins with a set of Wijmo CDN references, followed by a DIV element that serves as a placeholder for a Wijmo control. The most interesting part of the template is the script that creates an XMLHttpRequest object to asynchronously read the contents of a local Excel file, then populates the FlexSheet control with the resulting blob. Here is a simplified version of that code:
var file = '${file}';
var sheet = new wijmo.grid.sheet.FlexSheet("#sheet");
var xhr = new XMLHttpRequest();
xhr.onload = function(e) {
sheet.load(xhr.response);
sheet.isReadOnly = true;
};
xhr.open("GET", file);
xhr.responseType = "blob";
xhr.send();
Conclusion
Whether you run Windows, OS X, or Linux, Visual Studio Code is an indispensable tool for writing and debugging cross-platform web applications. With its rich ecosystem of extensions, VS Code lets developers customize their editing environment by adding support for modern languages, debuggers, file viewers, and time-saving commands. As the Excel Viewer extension demonstrates, you can even use third-party data visualization libraries such as Wijmo 5 to build your own extensions, then publish them so that other developers can benefit from your expertise.
MESCIUS inc.