Excel Viewer Control and Grid Control

Add-Ons
1

I’m writing a tool for comparing Excel files, similarly to how my Arbed tool works for comparing Xojo projects.

There’s currently no editing, so it’s mainly about showing Excel sheets as closely as possible to how Excel shows them.

It uses the MBS LibXL plugin (requires a separate purchase) but no other MBS plugins. It’s written in plain REALbasic / Xojo code (runs on 2012r2.1 and later).

It’s based on a GridControl, again written in plain Xojo code (no plugins used). The plugin control is similar to a Listbox Control, with the extra feature that each row’s height can be individually specified (ListBoxes only let you choose the column sizes individually). And it doesn’t store the data for you - instead, you have to implement the CellPaint event where you’d draw your text. This makes it fast and possible to handle any number of rows and columns (ListBox is limited to 255 columns, BTW).

Here’s a current comparison of mine vs. Excel’s rendering: http://files.tempel.org/RB/ExcelView/TTsVsExcel1.png

A video showing selection handling and scrolling: http://files.tempel.org/RB/ExcelView/TTsExcelView1.mov

The source Excel file is here: https://www.destatis.de/DE/Themen/Querschnitt/Statistische-Wochenberichte/wochenberichte-bevoelkerung-xlsx.html

A video showing the GridView, with its selection features, goto and reveal (which only scrolls as little as necessary to make the cell visible), and scroll performance with 100s of visible cells: http://files.tempel.org/RB/ExcelView/TTsGridView1.mov

I haven’t decided what to do with them yet. I’ve spent a few long days on this, and wonder if Xojo users have a need for these classes as well. If so, I’d consider offering them with source code licenses (at least $100 for the GridView and twice as much for the XL view as it stands now).

2

Hello Thomas,

Offered in good faith (not intended as criticism).

I’m not sure I understand your value proposition. Developers can already freely embed a Google Sheets or Microsoft Excel spreadsheet in a HTMLViewer, WebHTMLViewer or indeed a standalone webpage, automatically display updates to those spreadsheets (or ranges within those spreadsheets) and in some cases enable edits (based on the sharing and publishing settings of the original document which are different for each platform)?

You may be hard pressed to develop a better solution than Microsoft Spreadsheet Compare freely available with Microsoft Office. Spreadsheet Compare has been available for many years, although likely not widely known (unless you have worked in accounting, banking, finance, insurance or audit etc).

More generally on pricing. Xojo Licences start around $99 but more generally $299. Is it prudent to charge developers as much again for a piece of additional but limited or highly specific functionality? Of course, my view doesn’t apply to Add-Ons such MBS or GraffitiSuite that include whole eco-systems of functionality to support their value propositions.

Just some things to consider.

Kind regards, Andrew

3

Hi Andrew,
thanks for pointing those things out.

The “Microsoft Spreadsheet Compare” is similar to what I have in mind, indeed. When I was looking for a XL diff tool a while ago I found nothing alike - and here’s why: [quote]Important: Spreadsheet Compare is only available with Office Professional Plus 2013 or Microsoft 365 Apps for enterprise.[/quote]

So, my business idea is to provide such a tool to those who don’t have the Pro / Enterprise version.

Although, you say: “freely available” - only for Pro / Enterprise, right? My Excel 2016 for macOS doesn’t have this compare feature.

And there’s one other reason: I want to be able to get a textual report of the diff, so that I can use that with version control tools such as git, e.g. for automatically generating change logs.

But is it possible to control what’s being show there? E.g, can you programmatically choose which sheet and which cells to focus on?

4

Hello Thomas,

In respect of your first observation:

You may be referring to an old reference. See Overview of Spreadsheet Compare.
According to that page: [quote]Excel 2013 Office for business Spreadsheet Compare 2013 Spreadsheet Compare 2016 Spreadsheet Compare 2019[/quote]

That said, I do recall it can be installed separately on Windows. Maybe dig a bit more on how strict the install restrictions are.

There are versions that support Office 2019 on Windows (current version). Not sure there was ever a Mac version, however Mac users can always use Spreadsheet Compare with Office 365 (Web).

In respect of your second observation: [quote]But is it possible to control what’s being show there? E.g, can you programmatically choose which sheet and which cells to focus on?[/quote]

Not programatically (unless scripted in the workbook), but coding is usually unnecessary. YES, it is possible for both Google Sheets and Microsoft Excel, to just show a range or named range (show a table snippet or graph) or full sheet at the time of publishing, or have the embed subscribe to live updates etc. I’ve tried it once, for publishing customer specific pricing etc.

I hope that helps.

Kind regards, Andrew

5

Hello Thomas, All that said, I don’t wish to discourage you - there is IMO definitely a market for an app or more pointedly a web service to compare or audit spreadsheet models (with rollbacks for rejected edits).

Kind regards, Andrew

6

Never ask your customers for advice in regards to price.

7

Looks good. Go on!

8

Great work. Please go ahead with your decision and give a discount as initial offer.

9

In Delphi I use this code to trigger the Windows file previews. That works fine. It gives you access to the OS preview handler which you can render in a window or pane or anything that has a correct handle. It has two parts, first you obtain the correct CLSID for the filetype and you pass this to the PreviewHandler which you invoke as a Com Object. I have tested it with all Office types, PDF, txt, xml etc.

I’ve tried to port this code to Xojo, but that took too much time. This is not the complete module, only the 2 essential functions.

 function GetPreviewHandlerCLSID(const AFileName: string): string;
var
  LRegistry: TRegistry;
  LKey: String;
begin
  LRegistry := TRegistry.Create();
  try
    LRegistry.RootKey := HKEY_CLASSES_ROOT;
    LKey := ExtractFileExt(AFileName) + '\\shellex\\{8895b1c6-b41f-4c1c-a562-0d564250836f}';
    if LRegistry.KeyExists(LKey) then
    begin
      LRegistry.OpenKeyReadOnly(LKey);
      Result:=LRegistry.ReadString('');
      LRegistry.CloseKey;
    end
    else
      Result := '';
  finally
    LRegistry.Free;
  end;
end;
 
procedure THostPreviewHandler.LoadPreviewHandler;
const
  GUID_ISHELLITEM = '{43826d1e-e718-42ee-bc55-a1e261c37bfe}';
var
  prc                   : TRect;
  LPreviewGUID          : TGUID;
  LInitializeWithFile   : IInitializeWithFile;
  LInitializeWithStream : IInitializeWithStream;
  LInitializeWithItem   : IInitializeWithItem;
  LIStream              : IStream;
  LShellItem            : IShellItem;
begin
 
  FLoaded:=False;
  FPreviewGUIDStr:=GetPreviewHandlerCLSID(FFileName);
  if FPreviewGUIDStr='' then exit;
 
  if FFileStream<>nil then
    FFileStream.Free;
 
  LPreviewGUID:= StringToGUID(FPreviewGUIDStr);
 
  FPreviewHandler := CreateComObject(LPreviewGUID) As IPreviewHandler;
  if (FPreviewHandler = nil) then
    exit;
 
  if FPreviewHandler.QueryInterface(IInitializeWithFile, LInitializeWithFile) = S_OK then
    LInitializeWithFile.Initialize(StringToOleStr(FFileName), STGM_READ)
  else
  if FPreviewHandler.QueryInterface(IInitializeWithStream, LInitializeWithStream) = S_OK then
  begin
      FFileStream := TFileStream.Create(FFileName, fmOpenRead or fmShareDenyNone);
      LIStream := TStreamAdapter.Create(FFileStream, soOwned) as IStream;
      LInitializeWithStream.Initialize(LIStream, STGM_READ);
  end
  else
  if FPreviewHandler.QueryInterface(IInitializeWithItem, LInitializeWithItem) = S_OK then
  begin
    SHCreateItemFromParsingName(PChar(FileName), nil, StringToGUID(GUID_ISHELLITEM), LShellItem);
    LInitializeWithItem.Initialize(LShellItem, 0);
  end
  else
  begin
    FPreviewHandler.Unload;
    FPreviewHandler:=nil;
    exit;
  end;
 
  prc := ClientRect;
  FPreviewHandler.SetWindow(Self.Handle, prc);
end;
10

Some more goals for my software:

  1. I plan to use this primarily on MacOS, so existing Windows-only solutions are no help to me.

  2. I need to be able to compare Sheets where the data is arranged similarly to Database Tables, with specific columns and keyed rows, and where I can detect if a column or row has been inserted, removed or moved.

  3. I want to be able to “undo” differences interactively, or apply one into the other.

  4. I want to detect differences only of content, not formatting.

BTW there are other 3rd party tools (the need for comparing spreadsheets has been around forever, after all):

(All found via How to compare two Excel files for differences)

But they’re all Windows-only, it seems.

Then there are also some online tools:

These are very simply and cannot handle column-specific changes. They just compare entire rows. Also, I wouldn’t want to upload any private data there.

11

Hi Thomas,

I’ve been building Excel models since the late 80’s (which is admittedly nothing special).

Much of the functionality to which you refer can be achieved in various ways within an Excel file.

Normally, I would use Spreadsheet Compare to track structural changes to a model, more than I would to track changes to data input, preferring to analyse changes to data by logging them within the model itself.

Once I establish a model, using a hidden sheet and VBA I create a change log by listening for changes to the editable cells of the spreadsheet, which is relatively easy (even if you don’t know VBA). There a great many tutorials on Youtube. I haven’t watched this one to the end, but it seemed on point. Unlike the video though, I keep the entire history, to roll forward or back.

Establishing a change log, allows a user or manager to view and selectively accept (do nothing) or deny (rollback) changes to the dataset change by change as required.

The next level of sophistication is to allow the creation of named “scenarios”, whereby at a point in time, all new changes are considered part of a set or scenario within the log, which can be rolled back and forward in a single action.

This is perfect for financial modelling, budgeting etc, as users test assumptions, think, reflect/argue and rethink.

For example: “Hey Thomas, remember yesterday, when we ran the expansion numbers for 10 new branches, instead of 4, let’s go back to that”…

It becomes a 1 sec process and maintains a single spreadsheet file (preventing a proliferation of file versions [or file creep, as we call it]).

Building data logs and optionally scenarios into an Excel Model, is way-way-way faster than saving, comparing and correcting versions of a file for data issues.

Kind regards, Andrew

12

Good points, Andrew. And if I’d have better control over the workflow, I’d not even use Excel for entering data but use a custom frontend that drives a networked database, where my software has full control and can even make sure there are no logical errors in the data. But, alas, I have to work with what is presented to me, and that’s plain non-VBA-capable spreadsheets and I have to figure out what’s changed afterwards.

Can I even do this kind of modification tracking in Excel for macOS? And if I can, can I have it write a changelog to a text file so that my git process can pick that up (for adding to the commit log)?

13

I recently stumbled into the VBA editor of Excel on macOS. It looks like Windows from the 90s. The editor is there, but quite slow.

With VBA you can easily track changes and do a changelog to a text file. Just don’t ever expect nice code or anything OOP. It’s normal to use variants, “on error resume next” is a classic. Don’t forget to declare your variables correctly:

dim s, t as string

is not

dim s as string, t as string

Tell your users: here is your new file. And lock the heck out of it.

The crimes against mankind I did in 20 years Excel development…

14

Hello Thomas and Beatrix,

I forgot to mention as of Office 2016, Excel has an Add-On that provides the more modern Excel Javascript API.

Please refer to Excel Javascript API.

If I was starting over with Excel, I’d likely run a mile from VBA and use the Javascript implementation. I have history with VBA so I haven’t yet made extensive use of the new Javascript API.

Kind regards, Andrew