Why can't we use the 0-th CellXf ?

[stla]

Hello,

I wanted to create a xlsx file with a date column. And no other column, just to try.
In my first attempt (see below), I set the number format id in the 0-th component of the field _styleSheetCellXfs of the StyleSheet. That does not work: the xlsx file is created, but the numbers are not formatted as dates.

My second attempt (see below) was successful. I let the 0-th component of _styleSheetCellXfs as an empty CellXf, and I put the CellXf with the number format id in the 1-th component.

Is it something expected ? What is the role of the 0-th CellXf if we can't use it to define a style ?

First attempt:

{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE TemplateHaskell #-}
module Testdate where
import Codec.Xlsx
import qualified Data.Map.Lazy as DML
import qualified Data.ByteString.Lazy as L
import Data.Time.Clock.POSIX (getPOSIXTime)
import Control.Lens
import Data.Dates 
import Data.Dates.Formats (parseDateFormat)
import Data.Either.Extra (fromRight)

 -- convert a date given as "YYYY/MM/DD" to its corresponding Excel number value
excelDate :: String -> Double
excelDate date = fromIntegral $ datesDifference origin datetime 
    where origin = DateTime{year = 1899, month=12, day=30, hour=0, minute=0, second=0}
          datetime = fromRight $ parseDateFormat "YYYY/MM/DD" date

-- input some dates
dates = ["2017/10/01", "2017/10/02"]

-- input locations of the cells
locations = [(1,1), (2,1)]

-- create cells for these dates
emptyCell = Cell { _cellStyle = Nothing, 
                   _cellValue = Nothing, 
                   _cellComment = Nothing, 
                   _cellFormula = Nothing }
cells = map (\x -> set cellStyle (Just 0) $ 
                     set cellValue (Just $ CellDouble $ excelDate x) $ 
                       emptyCell) dates 

-- create worksheet
emptyWorksheet = Worksheet { _wsColumns = [], 
                             _wsRowPropertiesMap = DML.empty, 
                             _wsCells = DML.empty, 
                             _wsDrawing = Nothing, 
                             _wsMerges = [], 
                             _wsSheetViews = Nothing, 
                             _wsPageSetup = Nothing, 
                             _wsConditionalFormattings = DML.empty, 
                             _wsDataValidations = DML.empty, 
                             _wsPivotTables = [] }
worksheet = set wsCells (DML.fromList $ zip locations cells) emptyWorksheet

-- create styleSheet
emptyStyleSheet = minimalStyleSheet
emptyCellXf =  (_styleSheetCellXfs emptyStyleSheet) !! 0
cellXf = set cellXfApplyNumberFormat (Just True) $ 
           set cellXfNumFmtId (Just (stdNumberFormatId NfDMmmYy)) $ 
             emptyCellXf
stylesheet = set styleSheetCellXfs [cellXf] emptyStyleSheet

-- create xlsx 
emptyXlsx = Xlsx { _xlSheets = [], 
                   _xlStyles = emptyStyles, 
                   _xlDefinedNames = DefinedNames [], 
                   _xlCustomProperties = DML.fromList [] }
xlsx = set xlStyles (renderStyleSheet stylesheet) $ 
         set xlSheets [("Sheet1", worksheet)] emptyXlsx

--
main :: IO ()
main = do
  ct <- getPOSIXTime
  L.writeFile "mydatexlsx1.xlsx" $ fromXlsx ct xlsx

Correction:

cells = map (\x -> set cellStyle (Just 1) $ 
                     set cellValue (Just $ CellDouble $ excelDate x) $ 
                       emptyCell) dates 
stylesheet = set styleSheetCellXfs [emptyCellXf, cellXf] emptyStyleSheet

[qrilka]

MS has wonders in its pockets :)
Actually I don't know what could be so special about xf 0 and spec says:

Cells in the Sheet Part reference the xf records by zero-based index.

And at the moment I don't find anything else about it :(

[blargg]

Couldn't find it in a spec anywhere, but this stack overflow post suggests that the 0th and 1st indices are reserved. https://stackoverflow.com/a/11118442. Strange design, but it seems to be the expected behavior.

Based on my understanding, it could be safer to auto populate the reserved styles, but haven't looked into this much.

[qrilka]

thanks @blargg for the link
PR with with some way to populate the reserved styles or at least add haddock describing the issue will be highly appreciated