Epoch dates in Excel and Java

Dates and times are very complicated as we know and almost every sentence should be followed by a caveat, probably to do with time zones and/or leap seconds. But, let's just press on anyway!

Excel stores dates as a number of days since a reference ("Epoch") date - the fractional part represents the time, for example 34567.5 means midday on day 34567.

Java has a whole load of stuff in the java.time package (and others) but it is easy enough to get dates as a Unix time-stamp, which is a number of seconds since the epoch.

The Excel epoch is 1-Jan-1900 and the Unix epoch is 1-Jan-1970.

I had some code outputting from Java which I wanted to make into a text file that could be imported by Excel. Formatting dates as text so that Excel could re-convert seemed troublesome because it's not clear exactly what formats it supports and it (probably!) depends on the locale. Why not output the Excel date serial number, then all that's needed is to format it as a date in the sheet?

The Excel serial number should be:

(Unix time stamp) / (number of seconds in a day) + offset
  

where offset is to account for the difference between the two epochs, i.e. number of days between 1-Jan-1900 and 1-Jan-1970.

Java can work this out, here is some jshell

   1 : import java.time.*;
   2 : import java.time.temporal.*;
   3 : var unix_epoch  = LocalDateTime.of(1970,1,1,0,0,0,0);
   4 : var excel_epoch = LocalDateTime.of(1900,1,1,0,0,0,0);
   5 : ChronoUnit.DAYS.between(excel_epoch,unix_epoch)
   

which gives the answer 25567

Excel can work this out with

=date(1970,1,1)

which formatted as a number gives 25569.

Two days out. The Excel one gives the right answer if used as the offset in the formula but other systems also suggest the proper answer is 25567.

The discrepancy comes because

• Excel and Java disagree as to whether 1900 was a leap year
• Excel epoch date is "Day 1" rather than starting from zero (in other words, =date(1900,1,1) gives 1)

So - I just hard-coded 25569 as a magic constant to use as the offset.

Postscript - seems it's impossible to type in an Excel date before 1900, it doesn't autoformat it. If you try and do arithmetic to subtract 2 days from 1-jan-1900 you just get ##########. If you use the DATE function it wraps, eg. =DATE(1800,1,1) gives a date in the year 3700. This is weird because I can imagine uses for dates in the 19th century, like genealogy, but not for the 38th century (extreme forecasting??)

Entering symbols on the iOS keyboard

Today I was trying to enter a degree sign ° in an iMessage. You can switch layout to numbers and then symbols that gives you a few symbols but not degree. I googled and found a lot of links about enabling the Emoji keyboard and copy/pasting from a sync'd document. I googled a bit more and found that it's simpler than that, just press and hold the zero key and it gives you an option of inserting zero or degree. Quite a few of the other keys have multiple meanings too, I am too lazy to list them all here.

String::Format in C++Builder

If you are writing C++ using the VCL there is a potential problem. Something like:

TVarRec array[] = { .... };
String s = String::Format("....", EXISTINGARRAY(array));

The problem is that a TVarRec initialised with a string doesn't hold the string, only a void* pointer to its data. So if you create any temp strings in the array they may be destroyed before String::Format runs. For example:

TVarRec array[] = { Edit1->Text };
Caption = String::Format("Hi %s", EXISTINGARRAY(array));

Here, Edit1->Text is not a 'real' string, it's a property which calls GetText, which creates a string on the fly. This is destroyed almost straight away, leaving array with a dangling pointer. The solution is to make sure the lifetime of the string extends past the Format, i.e.

String txt = Edit1->Text;
TVarRec array[] = { txt };
Caption = String::Format("Hi %s", EXISTINGARRAY(array));

So in that case you might as well use ARRAYOFCONST, i.e.

String txt = Edit1->Text;
Caption = String::Format("Hi %s", ARRAYOFCONST((txt)));

Resources in Managed C++

I was trying to write an MSBuild file for a managed C++ project, using Windows SDK 7.1 (and not Visual Studio). Mostly the supplied .targets/.props files do the hard work for you (the documentation seems rather sketchy AFAICS.) One stumbling block was the resources, I kept getting an error:

System.Resources.MissingManifestResourceException: Could not find any resources appropriate for the specified culture or the neutral culture.  Make sure "controller.Form1.resources" was correctly embedded or linked into assembly "controller" at compile time, or that all the satellite assemblies required are loadable and fully signed.

It turned out that the code was looking for resources named "controller.XXX.resources" whereas ResGen was creating them "XXX.resources". The solution was to use the RootNamespace element in the MSBuild file, i.e.

<PropertyGroup>
<RootNamespace>controller</RootNamespace>
</PropertyGroup>

Creating an IStream from a resource

In Win32, suppose you have an application resource (added with the resource compiler) and you want to open an OLE IStream interface on it. One reason might be to load an image with GDI+.

I initially made my own IStream implementation to provide read-only access to the resource data but I eventually decided to do the following, which is less code but does make a copy of the resource. The API is 'inspired by' the Shell's CreateStreamOnHGlobal function.

HRESULT CreateStreamOnResource(LPCTSTR name, LPSTREAM* stream) {
 HINSTANCE hInst = GetModuleHandle(0);
 return CreateStreamOnResource(hInst, name, stream);
}

HRESULT CreateStreamOnResource(HINSTANCE hInst, LPCTSTR name, LPSTREAM* stream)
{
 assert(hInst != 0);
 assert(name != 0);
 assert(stream != 0);
 *stream = NULL;
 HRSRC hC = FindResource(hInst, name, RT_RCDATA);
 if (hC) {
  // This is not really a HGLOBAL http://msdn.microsoft.com/en-us/library/windows/desktop/ms648046(v=vs.85).aspx
  HGLOBAL hG = LoadResource(hInst, hC);
  if (hG) {
   void* bytes = LockResource(hG);
   ULONG size = SizeofResource(hInst, hC);
   // Create a new empty stream.
   HRESULT hr = CreateStreamOnHGlobal(NULL, TRUE, stream);
   if (SUCCEEDED(hr)) {
    ULONG written;
    // Copy the resource into it.
    hr = (*stream)->Write(bytes, size, &written);
   }
   return hr;
  }
 }
 return E_INVALIDARG;
}

It should have been even easier - just get an HGLOBAL with LoadResource and pass it to CreateStreamOnHGlobal but LoadResource does not really return a handle to global memory so I needed to make the additional steps of creating a new stream, locking the resource and copying its data. I think I remember in the Win16 days you really did have to worry about loading and unloading resources from the segmented .exe but these days I think it all just gets mapped anyway.

ListView and custom Cells

If you want to display a 'complex object' in a ListView, specify it as a parameter, for example

ListView<Thing> listView = new ListView<>();

However this only displays the result of calling Thing.toString() on the objects. To show more complex information you need to customise the ListCell. Start by providing a factory which creates new, customised cells.

listView.setCellFactory(new Callback<ListView<Thing>, ListCell<Thing>>() {
 @Override
 public ListCell<Thing> call(ListView<Thing> p) {
  return new ThingCell();
 }
});

JavaFX maintains some kind of relationship between ListCells and items in the list; it seems to be roughly one cell per visible row, not one cell per item in the list. You need to override updateItem() in Cell to attach a list item to a cell.

public class ThingCell extends ListCell<Thing> {
 @Override
 protected void updateItem(Thing t, boolean isblank) {
  super.updateItem(t, isblank);
  ...
 }
}

In other words, JavaFX uses the factory to create some cells, then attaches the list's contents to them. As the list is scrolled, different objects will be attached in turn. Bear in mind, t may be null - either because you've added a null object to the list, or because JavaFX wants a cell to suitable for displaying an empty row. In the latter case, isblank will be true. The appearance of the cell may be the same for either, in which case you can ignore isblank.
When an object is attached, remember to bind its properties to the cell, instead of just setting them. If the item then changes 'internally', the list will update automatically. For example, suppose Thing has a title property. If you do this:

protected void updateItem(Thing t, boolean isblank) {
 super.updateItem(t, isblank);
 if (t != null) {
  setText(t.titleProperty().get());
 }
 else {
  setText("");
 }
}

the item will display correctly when it is added to the list (and also if JavaFX re-attaches the objects), but if someone calls thing.titleProperty().set("New text") the list will not change. Instead, try

protected void updateItem(Thing t, boolean isblank) {
 super.updateItem(t, isblank);
 if (t != null) {
  textProperty().bind(t.titleProperty());
 }
 else {
  textProperty().unbind();
  textProperty().set("");
 }
}

Of course, if the list items never change after they've been added to the list, it isn't necessary to create the bindings and the first method can be used.

Cellular Automaton

This little cellular automaton was discovered by me in about 1995 but I'm sure someone else invented it before. If anyone knows its proper name I'd love to hear it. It can't be used to do anything clever but it does produce a surprising variety of patterns from a simple rule.
Each cell holds a value from 0-255 and has four neighbours. On each iteration, if the sum of all neighbours equals a given number ('the rule') the cell's value becomes 255. Otherwise it is decreased by 1 if it is greater than zero. If it's zero, then it stays as zero.
I've implemented it using HTML5 canvas, doing pixel-level access with ImageData.

See it here. It works on the 'modern' browsers that I've tested; Firefox, Safari and Chrome. It ought to work on IE9 and above and maybe Opera.
A quick note on the implementation:
I created a 2d context for the canvas element

var canvas = document.getElementById("ccc");
var ctx = canvas.getContext("2d");

Then an ImageData to cover the whole canvas.

var imd = ctx.createImageData(canvas.width,canvas.height);

I drew the image with

ctx.putImageData(imd, 0, 0);

I didn't work directly with the ImageData's data, which is always in 32-bit RGBA format. Instead I created a Uint8Array with 8 bits per pixel (I only needed to store 0-255), then converted it to image format as follows

function convi(imd, data) {
 var i;
 var out = imd.data;
 for (i=0; i<data.length; ++i) {
  out[i*4] = out[i*4+1] = out[i*4+2] = data[i];
  out[i*4+3] = 255;
 }
}

I just converted the 0-255 to a greyscale but here would be the place to implement a colour look-up table and get a nice palette.