Skip to content
← Go back

Support for excel XLSX format in BeBanjo

September 1st, 2015 (Revision 1), download as PDF

Up to Excel 2003 the maximum number of columns allowed in a worksheet was 256, which could cause problems if your Metadata Template in BeBanjo contains lots of attributes. Microsoft changed the limit in Excel 2007 to 16,384 and BeBanjo now supports the XLSX document type used by Excel 2007 and subsequent versions.

This note describes what has changed in full, and as usual you can contact support or your technical account manager for further clarification.

Support for Excel XLSX format in BeBanjo

A brave new world!

Up to Excel 2003 the maximum number of columns allowed in a worksheet was 256, which can cause problems if your Metadata Template in BeBanjo contains lots of attributes or you specify many multiple values for attributes.

Microsoft changed the limit in Excel 2007 with the introduction of the XLSX document type that BeBanjo now supports.

The maximum worksheet size for an XLSX document is 1,048,576 rows by 16,384 columns; this means you can produce, for example, Schedule exports that contain many hundreds of columns. Rejoice!

By default, BeBanjo will continue to use the XLS documents and you need to contact your Technical Account Manager to enable support for XLSX documents.

Please note that once support for XLSX documents is enabled for your Company in BeBanjo you will not be able to import any XLS documents you have (you’d need to save them as XLSX documents), and all Excel documents created by BeBanjo will be XLSX documents.

In addition to being able to export a ginormous number of Metadata attributes there’s another quite subtle but potentially important benefit to using XLSX documents: columns for Metadata Attributes that are not dates or durations (e.g. short and long text) are formatted as Text instead of General. Told you it was subtle.

If a cell is formatted as General and you enter a value like 0.00 or 1.10 then Excel will helpfully store a value of 0 or 1.1 (and that’s what BeBanjo would import). Boo!

But, if the the cell is formatted as Text then the value that you enter is what is stored. Therefore, using XLSX documents means that means you no longer have to change the formatting of cells to Text, or use the apostrophe trick (e.g. entering ‘0.00 to store a literal value of 0.00). Yay!

Changes to filenames

Regardless of whether you have the XLSX document type enabled in BeBanjo, the release of BeBanjo#3788 changes the filenames of the various Excel documents produced by BeBanjo (whether or not they’re XLS or XLSX).

The new filenames are:

  • YYYY-MM-DDTHH-MM Schedule Export (Company Name).xlsx/xls
  • YYYY-MM-DDTHH-MM Catalogue Export (Company Name).xlsx/xls
  • YYYY-MM-DDTHH-MM Title Import Errors (Company Name).xlsx/xls
  • YYYY-MM-DDTHH-MM Title Import Template (Company Name).xlsx/xls
  • YYYY-MM-DDTHH-MM Pending Rights Report (Company Name).xlsx/xls

This means you shouldn’t encounter the problem with files being overwritten, or being unable to easily determine which file is the most current. Nifty!

Reference

  • movida#3788 Support XLSX format for Import Titles and Exports