content: {-# LANGUAGE OverloadedStrings #-}
  {-# LANGUAGE OverloadedLists #-}
  {-# LANGUAGE RecordWildCards   #-}
  {-# LANGUAGE QuasiQuotes       #-}
  {-# LANGUAGE CPP #-}
  
  module TestXlsx where
  
  #ifdef USE_MICROLENS
  import Lens.Micro.Platform
  #else
  import Control.Lens
  #endif
  import Control.Monad.State.Lazy
  import Data.ByteString.Lazy (ByteString)
  import Data.Map (Map)
  import qualified Data.Map as M
  import Data.Time.Clock.POSIX (POSIXTime)
  import qualified Data.Vector as V
  import Text.RawString.QQ
  import Text.XML
  
  import Codec.Xlsx
  import Codec.Xlsx.Formatted
  import Codec.Xlsx.Types.Internal
  import Codec.Xlsx.Types.Internal.CommentTable
  import Codec.Xlsx.Types.Internal.CustomProperties
         as CustomProperties
  import Codec.Xlsx.Types.Internal.SharedStringTable
  
  import PivotTableTests
  import DrawingTests
  
  testXlsx :: Xlsx
  testXlsx = Xlsx sheets minimalStyles definedNames customProperties DateBase1904
    where
      sheets =
        [ ("List1", sheet1)
        , ("Another sheet", sheet2)
        , ("with pivot table", pvSheet)
        , ("cellrange DV source", foreignDvSourceSheet) -- "foreign" sheet holding validation data
        , ("cellrange DV test", foreignDvTestSheet) -- applies validation using foreign cell ranges
        , ("hidden sheet", def & wsState .~ Hidden & cellValueAt (1,1) ?~ CellText "I'm hidden!")
        , ("VERY hidden sheet", def & wsState .~ VeryHidden & cellValueAt (1,1) ?~ CellText "I'm VERY hidden!!")
        ]
      sheet1 = Worksheet cols rowProps testCellMap1 drawing ranges
        sheetViews pageSetup cFormatting validations [] (Just autoFilter)
        tables (Just protection) sharedFormulas def
      sharedFormulas =
        M.fromList
          [ (SharedFormulaIndex 0, SharedFormulaOptions (CellRef "A5:C5") (Formula "A4"))
          , (SharedFormulaIndex 1, SharedFormulaOptions (CellRef "B6:C6") (Formula "B3+12"))
          ]
      autoFilter = def & afRef ?~ CellRef "A1:E10"
                       & afFilterColumns .~ fCols
      fCols = M.fromList [ (1, Filters DontFilterByBlank
                               [FilterValue "a", FilterValue "b",FilterValue "ZZZ"])
                         , (2, CustomFiltersAnd (CustomFilter FltrGreaterThanOrEqual "0")
                             (CustomFilter FltrLessThan "42"))]
      tables =
        [ Table
          { tblName = Just "Table1"
          , tblDisplayName = "Table1"
          , tblRef = CellRef "A3"
          , tblColumns = [TableColumn "another text"]
          , tblAutoFilter = Just (def & afRef ?~ CellRef "A3")
          }
        ]
      protection =
        fullSheetProtection
        { _sprScenarios = False
        , _sprLegacyPassword = Just $ legacyPassword "hard password"
        }
      sheet2 = def & wsCells .~ testCellMap2
      foreignDvSourceSheet = def & wsCells .~ cellRangeDvSourceMap
      foreignDvTestSheet = def &  wsDataValidations .~ foreignValidations &
                                  wsCells . at (1, 1) ?~
                                      (def & cellValue ?~ CellText ("Hi! try " <> unCellRef testForeignDvRange))
      pvSheet = sheetWithPvCells & wsPivotTables .~ [testPivotTable]
      sheetWithPvCells = def & wsCells .~ testPivotSrcCells
      rowProps = M.fromList [(1, RowProps { rowHeight       = Just (CustomHeight 50)
                                          , rowStyle        = Just 3
                                          , rowHidden       = False
                                          })]
      cols = [ColumnsProperties 1 10 (Just 15) (Just 1) False False False]
      drawing = Just $ testDrawing { _xdrAnchors = map resolve $ _xdrAnchors testDrawing }
      resolve :: Anchor RefId RefId -> Anchor FileInfo ChartSpace
      resolve Anchor {..} =
        let obj =
              case _anchObject of
                Picture {..} ->
                  let blipFill = (_picBlipFill & bfpImageInfo ?~ fileInfo)
                  in Picture
                     { _picMacro = _picMacro
                     , _picPublished = _picPublished
                     , _picNonVisual = _picNonVisual
                     , _picBlipFill = blipFill
                     , _picShapeProperties = _picShapeProperties
                     }
                Graphic nv _ tr ->
                  Graphic nv testLineChartSpace tr
        in Anchor
           { _anchAnchoring = _anchAnchoring
           , _anchObject = obj
           , _anchClientData = _anchClientData
           }
      fileInfo = FileInfo "dummy.png" "image/png" "fake contents"
      ranges = [mkRange (1,1) (1,2), mkRange (2,2) (10, 5)]
      minimalStyles = renderStyleSheet minimalStyleSheet
      definedNames = DefinedNames [("SampleName", Nothing, "A10:A20")]
      sheetViews = Just [sheetView1, sheetView2]
      sheetView1 = def & sheetViewRightToLeft ?~ True
                       & sheetViewTopLeftCell ?~ CellRef "B5"
      sheetView2 = def & sheetViewType ?~ SheetViewTypePageBreakPreview
                       & sheetViewWorkbookViewId .~ 5
                       & sheetViewSelection .~ [ def & selectionActiveCell ?~ CellRef "C2"
                                                     & selectionPane ?~ PaneTypeBottomRight
                                               , def & selectionActiveCellId ?~ 1
                                                     & selectionSqref ?~ SqRef [ CellRef "A3:A10"
                                                                               , CellRef "B1:G3"]
                                               ]
      pageSetup = Just $ def & pageSetupBlackAndWhite ?~  True
                             & pageSetupCopies ?~ 2
                             & pageSetupErrors ?~ PrintErrorsDash
                             & pageSetupPaperSize ?~ PaperA4
      customProperties = M.fromList [("some_prop", VtInt 42)]
      cFormatting = M.fromList [(SqRef [CellRef "A1:B3"], rules1), (SqRef [CellRef "C1:C10"], rules2)]
      cfRule c d = CfRule { _cfrCondition  = c
                          , _cfrDxfId      = Just d
                          , _cfrPriority   = topCfPriority
                          , _cfrStopIfTrue = Nothing
                          }
      rules1 = [ cfRule ContainsBlanks 1
               , cfRule (ContainsText "foo") 2
               , cfRule (CellIs (OpBetween (Formula "A1") (Formula "B10"))) 3
               ]
      rules2 = [ cfRule ContainsErrors 3 ]
  
  testCellMap1 :: CellMap
  testCellMap1 = M.fromList [ ((1, 2), cd1_2), ((1, 5), cd1_5), ((1, 10), cd1_10)
                            , ((3, 1), cd3_1), ((3, 2), cd3_2), ((3, 3), cd3_3), ((3, 7), cd3_7)
                            , ((4, 1), cd4_1), ((4, 2), cd4_2), ((4, 3), cd4_3)
                            , ((5, 1), cd5_1), ((5, 2), cd5_2), ((5, 3), cd5_3)
                            , ((6, 2), cd6_2), ((6, 3), cd6_3)
                            ]
    where
      cd v = def {_cellValue=Just v}
      cd1_2 = cd (CellText "just a text, fließen, русский <> и & \"in quotes\"")
      cd1_5 = cd (CellDouble 42.4567)
      cd1_10 = cd (CellText "")
      cd3_1 = cd (CellText "another text")
      cd3_2 = def -- shouldn't it be skipped?
      cd3_3 = def & cellValue ?~ CellError ErrorDiv0
                  & cellFormula ?~ simpleCellFormula "1/0"
      cd3_7 = cd (CellBool True)
      cd4_1 = cd (CellDouble 1)
      cd4_2 = cd (CellDouble 123456789012345)
      cd4_3 = (cd (CellDouble (1+2))) { _cellFormula =
                                              Just $ simpleCellFormula "A4+B4<>11"
                                      }
      cd5_1 = def & cellFormula ?~ sharedFormulaByIndex (SharedFormulaIndex 0)
      cd5_2 = def & cellFormula ?~ sharedFormulaByIndex (SharedFormulaIndex 0)
      cd5_3 = def & cellFormula ?~ sharedFormulaByIndex (SharedFormulaIndex 0)
      cd6_2 = def & cellFormula ?~ sharedFormulaByIndex (SharedFormulaIndex 1)
      cd6_3 = def & cellFormula ?~ sharedFormulaByIndex (SharedFormulaIndex 1)
  
  cellRangeDvSourceMap :: CellMap
  cellRangeDvSourceMap = M.fromList [ ((1, 1), def & cellValue ?~ CellText "A-A-A")
                                      , ((2, 1), def & cellValue ?~ CellText "B-B-B")
                                      , ((1, 2), def & cellValue ?~ CellText "C-C-C")
                                      , ((2, 2), def & cellValue ?~ CellText "D-D-D")
                                      , ((1, 3), def & cellValue ?~ CellDouble 6)
                                      , ((2, 3), def & cellValue ?~ CellDouble 7)
                                      , ((3, 1), def & cellValue ?~ CellDouble 5)
                                      , ((3, 2), def & cellValue ?~ CellText "numbers!")
                                      , ((3, 3), def & cellValue ?~ CellDouble 5)
                                    ]
  
  testCellMap2 :: CellMap
  testCellMap2 = M.fromList [ ((1, 2), def & cellValue ?~ CellText "something here")
                            , ((3, 5), def & cellValue ?~ CellDouble 123.456)
                            , ((2, 4),
                               def & cellValue ?~ CellText "value"
                                   & cellComment ?~ comment1
                              )
                            , ((10, 7),
                               def & cellValue ?~ CellText "value"
                                   & cellComment ?~ comment2
                              )
                            , ((11, 4), def & cellComment ?~ comment3)
                            ]
    where
      comment1 = Comment (XlsxText "simple comment") "bob" True
      comment2 = Comment (XlsxRichText [rich1, rich2]) "alice" False
      comment3 = Comment (XlsxText "comment for an empty cell") "bob" True
      rich1 = def & richTextRunText.~ "Look ma!"
                  & richTextRunProperties ?~ (
                     def & runPropertiesBold ?~ True
                         & runPropertiesFont ?~ "Tahoma")
      rich2 = def & richTextRunText .~ "It's blue!"
                  & richTextRunProperties ?~ (
                     def & runPropertiesItalic ?~ True
                         & runPropertiesColor ?~ (def & colorARGB ?~ "FF000080"))
  
  testTime :: POSIXTime
  testTime = 123
  
  fromRight :: Show a => Either a b -> b
  fromRight (Right b) = b
  fromRight (Left x) = error $ "Right _ was expected but Left " ++ show x ++ " found"
  
  testStyleSheet :: StyleSheet
  testStyleSheet = minimalStyleSheet & styleSheetDxfs .~ [dxf1, dxf2, dxf3]
                                     & styleSheetNumFmts .~ M.fromList [(164, "0.000")]
                                     & styleSheetCellXfs %~ (++ [cellXf1, cellXf2])
    where
      dxf1 = def & dxfFont ?~ (def & fontBold ?~ True
                                   & fontSize ?~ 12)
      dxf2 = def & dxfFill ?~ (def & fillPattern ?~ (def & fillPatternBgColor ?~ red))
      dxf3 = def & dxfNumFmt ?~ NumFmt 164 "0.000"
      red = def & colorARGB ?~ "FFFF0000"
      cellXf1 = def
          { _cellXfApplyNumberFormat = Just True
          , _cellXfNumFmtId          = Just 2 }
      cellXf2 = def
          { _cellXfApplyNumberFormat = Just True
          , _cellXfNumFmtId          = Just 164 }
  
  
  withSingleUnderline :: SharedStringTable -> SharedStringTable
  withSingleUnderline = withUnderline FontUnderlineSingle
  
  withDoubleUnderline :: SharedStringTable -> SharedStringTable
  withDoubleUnderline = withUnderline FontUnderlineDouble
  
  withUnderline :: FontUnderline -> SharedStringTable -> SharedStringTable
  withUnderline u (SharedStringTable [text, XlsxRichText [rich1, RichTextRun (Just props) val]]) =
      let newprops = props & runPropertiesUnderline .~ Just u  
      in SharedStringTable [text, XlsxRichText [rich1, RichTextRun (Just newprops) val]] 
  
  testSharedStringTable :: SharedStringTable
  testSharedStringTable = SharedStringTable $ V.fromList items
    where
      items = [text, rich]
      text = XlsxText "plain text"
      rich = XlsxRichText [ RichTextRun Nothing "Just "
                          , RichTextRun (Just props) "example" ]
      props = def & runPropertiesBold .~ Just True
                  & runPropertiesItalic .~ Just True
                  & runPropertiesSize .~ Just 10
                  & runPropertiesFont .~ Just "Arial"
                  & runPropertiesFontFamily .~ Just FontFamilySwiss
  
  testSharedStringTableWithEmpty :: SharedStringTable
  testSharedStringTableWithEmpty =
    SharedStringTable $ V.fromList [XlsxText ""]
  
  testCommentTable :: CommentTable
  testCommentTable = CommentTable $ M.fromList
      [ (CellRef "D4", Comment (XlsxRichText rich) "Bob" False)
      , (CellRef "A2", Comment (XlsxText "Some comment here") "CBR" True) ]
    where
      rich = [ RichTextRun
               { _richTextRunProperties =
                 Just $ def & runPropertiesBold ?~ True
                            & runPropertiesCharset ?~ 1
                            & runPropertiesColor ?~ def -- TODO: why not Nothing here?
                            & runPropertiesFont ?~ "Calibri"
                            & runPropertiesScheme ?~ FontSchemeMinor
                            & runPropertiesSize ?~ 8.0
               , _richTextRunText = "Bob:"}
             , RichTextRun
               { _richTextRunProperties =
                 Just $ def & runPropertiesCharset ?~ 1
                            & runPropertiesColor ?~ def
                            & runPropertiesFont ?~ "Calibri"
                            & runPropertiesScheme ?~ FontSchemeMinor
                            & runPropertiesSize ?~ 8.0
               , _richTextRunText = "Why such high expense?"}]
  
  testStrings :: ByteString
  testStrings = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si><t>plain text</t></si>
    <si><r><t>Just </t></r><r><rPr><b /><i />
    <sz val="10"/><rFont val="Arial"/><family val="2"/></rPr><t>example</t></r></si>
    </sst>
  |]
  
  testStringsWithSingleUnderline :: ByteString
  testStringsWithSingleUnderline = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si><t>plain text</t></si>
    <si><r><t>Just </t></r><r><rPr><b /><i /><u />
    <sz val="10"/><rFont val="Arial"/><family val="2"/></rPr><t>example</t></r></si>
    </sst>
  |]
  
  testStringsWithDoubleUnderline :: ByteString
  testStringsWithDoubleUnderline = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si><t>plain text</t></si>
    <si><r><t>Just </t></r><r><rPr><b /><i /><u val="double"/>
    <sz val="10"/><rFont val="Arial"/><family val="2"/></rPr><t>example</t></r></si>
    </sst>
  |]
  
  testStringsWithEmpty :: ByteString
  testStringsWithEmpty = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2">
    <si><t/></si>
    </sst>
  |]
  
  testComments :: ByteString
  testComments = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <authors>
      <author>Bob</author>
      <author>CBR</author>
    </authors>
    <commentList>
      <comment ref="D4" authorId="0" visible="False">
        <text>
          <r>
            <rPr>
              <b/><sz val="8"/><color indexed="81"/><rFont val="Calibri"/>
              <charset val="1"/><scheme val="minor"/>
            </rPr>
            <t>Bob:</t>
          </r>
          <r>
            <rPr>
              <sz val="8"/><color indexed="81"/><rFont val="Calibri"/>
              <charset val="1"/> <scheme val="minor"/>
            </rPr>
            <t xml:space="preserve">Why such high expense?</t>
          </r>
        </text>
      </comment>
      <comment ref="A2" authorId="1" visible="True">
        <text><t>Some comment here</t></text>
      </comment>
    </commentList>
  </comments>
  |]
  
  testCustomProperties :: CustomProperties
  testCustomProperties = CustomProperties.fromList
      [ ("testTextProp", VtLpwstr "test text property value")
      , ("prop2", VtLpwstr "222")
      , ("bool", VtBool False)
      , ("prop333", VtInt 1)
      , ("decimal", VtDecimal 1.234) ]
  
  testCustomPropertiesXml :: ByteString
  testCustomPropertiesXml = [r|
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="prop2">
      <vt:lpwstr>222</vt:lpwstr>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="prop333">
      <vt:int>1</vt:int>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="4" name="testTextProp">
      <vt:lpwstr>test text property value</vt:lpwstr>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="5" name="decimal">
      <vt:decimal>1.234</vt:decimal>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="6" name="bool">
      <vt:bool>false</vt:bool>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="7" name="blob">
      <vt:blob>
      ZXhhbXBs
      ZSBibG9i
      IGNvbnRl
      bnRz
      </vt:blob>
    </property>
  </Properties>
  |]
  
  testFormattedResult :: Formatted
  testFormattedResult = Formatted cm styleSheet merges
    where
      cm = M.fromList [ ((1, 1), cell11)
                      , ((1, 2), cell12)
                      , ((2, 5), cell25) ]
      cell11 = Cell
          { _cellStyle   = Just 1
          , _cellValue   = Just (CellText "text at A1")
          , _cellComment = Nothing
          , _cellFormula = Nothing }
      cell12 = Cell
          { _cellStyle   = Just 2
          , _cellValue   = Just (CellDouble 1.23)
          , _cellComment = Nothing
          , _cellFormula = Nothing }
      cell25 = Cell
          { _cellStyle   = Just 3
          , _cellValue   = Just (CellDouble 1.23456)
          , _cellComment = Nothing
          , _cellFormula = Nothing }
      merges = []
      styleSheet =
          minimalStyleSheet & styleSheetCellXfs %~ (++ [cellXf1, cellXf2, cellXf3])
                            & styleSheetFonts   %~ (++ [font1, font2])
                            & styleSheetNumFmts .~ numFmts
      nextFontId = length (minimalStyleSheet ^. styleSheetFonts)
      cellXf1 = def
          { _cellXfApplyFont = Just True
          , _cellXfFontId    = Just nextFontId }
      font1 = def
          { _fontName = Just "Calibri"
          , _fontBold = Just True }
      cellXf2 = def
          { _cellXfApplyFont         = Just True
          , _cellXfFontId            = Just (nextFontId + 1)
          , _cellXfApplyNumberFormat = Just True
          , _cellXfNumFmtId          = Just 164 }
      font2 = def
          { _fontItalic = Just True }
      cellXf3 = def
          { _cellXfApplyNumberFormat = Just True
          , _cellXfNumFmtId          = Just 2 }
      numFmts = M.fromList [(164, "0.0000")]
  
  testRunFormatted :: Formatted
  testRunFormatted = formatted formattedCellMap minimalStyleSheet
    where
      formattedCellMap = flip execState def $ do
          let font1 = def & fontBold ?~ True
                          & fontName ?~ "Calibri"
          at (1, 1) ?= (def & formattedCell . cellValue ?~ CellText "text at A1"
                            & formattedFormat . formatFont  ?~ font1)
          at (1, 2) ?= (def & formattedCell . cellValue ?~ CellDouble 1.23
                            & formattedFormat . formatFont . non def . fontItalic ?~ True
                            & formattedFormat . formatNumberFormat ?~ fmtDecimalsZeroes 4)
          at (2, 5) ?= (def & formattedCell . cellValue ?~ CellDouble 1.23456
                            & formattedFormat . formatNumberFormat ?~ StdNumberFormat Nf2Decimal)
  
  testFormatWorkbookResult :: Xlsx
  testFormatWorkbookResult = def & xlSheets .~ sheets
                                 & xlStyles .~ renderStyleSheet style
    where
      cellMap1 = M.fromList [((1, 1), Cell { _cellStyle   = Nothing
                                                , _cellValue   = Just (CellText "text at A1 Sheet1")
                                                , _cellComment = Nothing
                                                , _cellFormula = Nothing })]
      cellMap2 = M.fromList [((2, 3), Cell { _cellStyle   = Just 1
                                                , _cellValue   = Just (CellDouble 1.23456)
                                                , _cellComment = Nothing
                                                , _cellFormula = Nothing })]
      sheets = [ ("Sheet1", def & wsCells .~ cellMap1) , ("Sheet2", def & wsCells .~ cellMap2) ]
      style = minimalStyleSheet & styleSheetNumFmts .~ M.fromList [(164, "DD.MM.YYYY")]
                                & styleSheetCellXfs .~ [cellXf1, cellXf2]
      cellXf1 = def
        & cellXfBorderId .~ Just 0
        & cellXfFillId   .~ Just 0
        & cellXfFontId   .~ Just 0
      cellXf2 = def
          { _cellXfApplyNumberFormat = Just True
          , _cellXfNumFmtId          = Just 164 }
    
  testFormatWorkbook :: Xlsx
  testFormatWorkbook = formatWorkbook sheets minimalStyleSheet
    where
      sheetNames = ["Sheet1", "Sheet2"]
      testFormattedCellMap1 = M.fromList [((1,1), (def & formattedCell . cellValue ?~ CellText "text at A1 Sheet1"))]
        
      testFormattedCellMap2 = M.fromList [((2,3), (def & formattedCell . cellValue ?~ CellDouble 1.23456
                                                   & formattedFormat . formatNumberFormat ?~ (UserNumberFormat "DD.MM.YYYY")))]
      sheets = zip sheetNames [testFormattedCellMap1, testFormattedCellMap2]
  
  testCondFormattedResult :: CondFormatted
  testCondFormattedResult = CondFormatted styleSheet formattings
    where
      styleSheet =
          minimalStyleSheet & styleSheetDxfs .~ dxfs
      dxfs = [ def & dxfFont ?~ (def & fontUnderline ?~ FontUnderlineSingle)
             , def & dxfFont ?~ (def & fontStrikeThrough ?~ True)
             , def & dxfFont ?~ (def & fontBold ?~ True) ]
      formattings = M.fromList [ (SqRef [CellRef "A1:A2", CellRef "B2:B3"], [cfRule1, cfRule2])
                               , (SqRef [CellRef "C3:E10"], [cfRule1])
                               , (SqRef [CellRef "F1:G10"], [cfRule3]) ]
      cfRule1 = CfRule
          { _cfrCondition  = ContainsBlanks
          , _cfrDxfId      = Just 0
          , _cfrPriority   = 1
          , _cfrStopIfTrue = Nothing }
      cfRule2 = CfRule
          { _cfrCondition  = BeginsWith "foo"
          , _cfrDxfId      = Just 1
          , _cfrPriority   = 1
          , _cfrStopIfTrue = Nothing }
      cfRule3 = CfRule
          { _cfrCondition  = CellIs (OpGreaterThan (Formula "A1"))
          , _cfrDxfId      = Just 2
          , _cfrPriority   = 1
          , _cfrStopIfTrue = Nothing }
  
  testFormattedCells :: Map (RowIndex, ColumnIndex) FormattedCell
  testFormattedCells = flip execState def $ do
      at (1, 1) ?=
                    (def & formattedRowSpan .~ 5
                       & formattedColSpan .~ 5
                       & formattedFormat . formatBorder . non def . borderTop .
                                                          non def . borderStyleLine ?~ LineStyleDashed
                       & formattedFormat . formatBorder . non def . borderBottom .
                                                          non def . borderStyleLine ?~ LineStyleDashed)
      at (10, 2) ?= (def & formattedFormat . formatFont . non def . fontBold ?~ True)
  
  testRunCondFormatted :: CondFormatted
  testRunCondFormatted = conditionallyFormatted condFmts minimalStyleSheet
    where
      condFmts = flip execState def $ do
          let cfRule1 = def & condfmtCondition .~ ContainsBlanks
                            & condfmtDxf . dxfFont . non def . fontUnderline ?~ FontUnderlineSingle
              cfRule2 = def & condfmtCondition .~ BeginsWith "foo"
                            & condfmtDxf . dxfFont . non def . fontStrikeThrough ?~ True
              cfRule3 = def & condfmtCondition .~ CellIs (OpGreaterThan (Formula "A1"))
                            & condfmtDxf . dxfFont . non def . fontBold ?~ True
          at (CellRef "A1:A2")  ?= [cfRule1, cfRule2]
          at (CellRef "B2:B3")  ?= [cfRule1, cfRule2]
          at (CellRef "C3:E10") ?= [cfRule1]
          at (CellRef "F1:G10") ?= [cfRule3]
  
  validations :: Map SqRef DataValidation
  validations = M.fromList
      [ ( SqRef [CellRef "A1"], def
        )
        , ( SqRef [CellRef "A1", CellRef "B2:C3"], def
          { _dvAllowBlank       = True
          , _dvError            = Just "incorrect data"
          , _dvErrorStyle       = ErrorStyleInformation
          , _dvErrorTitle       = Just "error title"
          , _dvPrompt           = Just "enter data"
          , _dvPromptTitle      = Just "prompt title"
          , _dvShowDropDown     = True
          , _dvShowErrorMessage = True
          , _dvShowInputMessage = True
          , _dvValidationType   = ValidationTypeList $ ListExpression ["aaaa","bbbb","cccc"]
          }
        )
      , ( SqRef [CellRef "A6", CellRef "I2"], def
          { _dvAllowBlank       = False
          , _dvError            = Just "aaa"
          , _dvErrorStyle       = ErrorStyleWarning
          , _dvErrorTitle       = Just "bbb"
          , _dvPrompt           = Just "ccc"
          , _dvPromptTitle      = Just "ddd"
          , _dvShowDropDown     = False
          , _dvShowErrorMessage = False
          , _dvShowInputMessage = False
          , _dvValidationType   = ValidationTypeDecimal $ ValGreaterThan $ Formula "10"
          }
        )
      , ( SqRef [CellRef "A7"], def
          { _dvAllowBlank       = False
          , _dvError            = Just "aaa"
          , _dvErrorStyle       = ErrorStyleStop
          , _dvErrorTitle       = Just "bbb"
          , _dvPrompt           = Just "ccc"
          , _dvPromptTitle      = Just "ddd"
          , _dvShowDropDown     = False
          , _dvShowErrorMessage = False
          , _dvShowInputMessage = False
          , _dvValidationType   = ValidationTypeWhole $ ValNotBetween (Formula "10") (Formula "12")
          }
        )
      ]
  
  testForeignDvRange :: Range
  testForeignDvRange = CellRef "B2:B7"
  
  foreignValidations :: Map SqRef DataValidation
  foreignValidations = M.fromList
    [ ( SqRef [testForeignDvRange], def
          { _dvAllowBlank       = True
          , _dvError            = Just "incorrect data"
          , _dvErrorStyle       = ErrorStyleInformation
          , _dvErrorTitle       = Just "error title"
          , _dvPrompt           = Just "Input kebab string"
          , _dvPromptTitle      = Just "I love kebab-case"
          , _dvShowDropDown     = True
          , _dvShowErrorMessage = True
          , _dvShowInputMessage = True
          , _dvValidationType   = ValidationTypeList $ RangeExpression $ CellRef "'cellrange DV source'!$A$1:$B$2"
          }
        )
    ]
  