On Thu, Oct 31, 2019 at 3:14 PM Mark Murphy <jmarkmurphy@xxxxxxxxx> wrote:
I just want to let you all know, that if you can do it in Excel, you can
probably do it in POI. If you can't do it in Excel, then there is nothing
you can do about it. POI just writes the spreadsheet in Excel format.
Well, I think I understand what you are trying to say, but it contains
some critical fuzziness in concept which often trips people up.
My issue is with the phrase "do it in Excel". There are things you can
do in Excel, the interactive GUI program from Microsoft, which do not
map to the Excel file format in a way that some people might think.
The classic example is AutoFit Column Width. You can "do this in
Excel" so why do most Excel file-writing libraries, even some very
full-featured ones, lack this feature?
The reason is that this is functionality which is performed entirely
by the Excel GUI. What gets saved in the file is merely the actual
column widths. There is no way to tell, by looking at the file itself,
whether the columns were sized using AutoFit or manually. And how is
the Excel GUI calculating these widths? By rendering the data
graphically and counting the pixels and adding a fudge factor, that's
how. For a library to do this, it would have to know how to apply the
various numeric formats and know the exact character widths, including
kerning, for whatever fonts are being used. On top of that, there may
be exotic cases like text which is rotated at an arbitrary angle.
Even the utterly mundane feature of writing both a formula and its
evaluated result in a cell is something that libraries almost never
do, because it would require replicating Excel's entire portfolio of
functions, on top of parsing all the references. Basically, the
library would need its own copy of Excel's calculation engine. It
would be ridiculous (though I have heard of projects that try to do
this anyway).
So, it would be more precise to say that anything that can be written
to an Excel file, you can write with POI. Beyond that, things that you
can "do in Excel" *might* be doable with POI, though you shouldn't
expect it.
I already went over in a fair amount of detail what goes into writing
prepopulated autofilters. XlsxWriter provides about as high-level API
support as you could reasonably expect. I would think POI's support is
similar (but probably more verbose, because Java).
John Y.
midrange.com
