require "stringio"
require "spreadsheet/excel/writer/biff8"
require "spreadsheet/excel/internals"
require "spreadsheet/excel/internals/biff8"
require "bigdecimal"

module Spreadsheet
  module Excel
    module Writer
      ##
      # Writer class for Excel Worksheets. Most write_* method correspond to an
      # Excel-Record/Opcode. You should not need to call any of its methods directly.
      # If you think you do, look at #write_worksheet
      class Worksheet
        include Spreadsheet::Excel::Writer::Biff8
        include Spreadsheet::Excel::Internals
        include Spreadsheet::Excel::Internals::Biff8
        attr_reader :worksheet
        def initialize workbook, worksheet
          @workbook = workbook
          @worksheet = worksheet
          @io = StringIO.new "".dup
          @biff_version = 0x0600
          @bof = 0x0809
          @build_id = 3515
          @build_year = 1996
          @bof_types = {
            globals: 0x0005,
            visual_basic: 0x0006,
            worksheet: 0x0010,
            chart: 0x0020,
            macro_sheet: 0x0040,
            workspace: 0x0100
          }
        end

        ##
        # The number of bytes needed to write a Boundsheet record for this Worksheet
        # Used by Writer::Worksheet to calculate various offsets.
        def boundsheet_size
          name.size + 10
        end

        def data
          @io.rewind
          @io.read
        end

        def encode_date date
          return date if date.is_a? Numeric
          if date.is_a? Time
            date = DateTime.new date.year, date.month, date.day,
              date.hour, date.min, date.sec
          end
          base = @workbook.date_base
          value = date - base
          if base < LEAP_ERROR
            value += 1
          end
          value
        end

        def encode_rk value
          #  Bit  Mask        Contents
          #    0  0x00000001  0 = Value not changed 1 = Value is multiplied by 100
          #    1  0x00000002  0 = Floating-point value 1 = Signed integer value
          # 31-2  0xFFFFFFFC  Encoded value
          cent = 0
          int = 2
          higher = value * 100
          if (higher.is_a?(Rational) || higher.is_a?(BigDecimal) || higher.is_a?(Float)) && higher < 0xfffffffc
            cent = 1
            value = if higher == higher.to_i
              higher.to_i
            else
              higher
            end
          end
          if value.is_a?(Integer)
            ## although not documented as signed, 'V' appears to correctly pack
            #  negative numbers.
            value <<= 2
          else
            # FIXME: precision of small numbers
            int = 0
            value, = [value].pack(EIGHT_BYTE_DOUBLE).unpack("x4V")
            value &= 0xfffffffc
          end
          value | cent | int
        end

        def name
          unicode_string @worksheet.name
        end

        def need_number? cell
          if cell.is_a?(Numeric) && cell.abs > 0x1fffffff
            true
          elsif cell.is_a?(Rational) || ((cell.is_a?(BigDecimal) || cell.is_a?(Float)) && !cell.nan?)
            higher = cell * 100
            if higher == higher.to_i
              need_number? higher.to_i
            else
              test1, test2 = [cell * 100].pack(EIGHT_BYTE_DOUBLE).unpack("V2")
              test1 > 0 || need_number?(test2)
            end
          else
            false
          end
        end

        def row_blocks
          # All cells in an Excel document are divided into blocks of 32 consecutive
          # rows, called Row Blocks. The first Row Block starts with the first used
          # row in that sheet. Inside each Row Block there will occur ROW records
          # describing the properties of the rows, and cell records with all the cell
          # contents in this Row Block.
          blocks = []
          @worksheet.reject { |row| row.empty? }.each_with_index do |row, idx|
            blocks << [] if idx % 32 == 0
            blocks.last << row
          end
          blocks
        end

        def size
          @io.size
        end

        def strings
          @worksheet.each_with_object(Hash.new(0)) do |row, memo|
            row.each do |cell|
              memo[cell] += 1 if cell.is_a?(String) && !cell.empty?
            end
          end
        end

        ##
        # Write a blank cell
        def write_blank row, idx
          write_cell :blank, row, idx
        end

        def write_bof
          data = [
            @biff_version, # BIFF version (always 0x0600 for BIFF8)
            0x0010,        # Type of the following data:
            # 0x0005 = Workbook globals
            # 0x0006 = Visual Basic module
            # 0x0010 = Worksheet
            # 0x0020 = Chart
            # 0x0040 = Macro sheet
            # 0x0100 = Workspace file
            @build_id,     # Build identifier
            @build_year,   # Build year
            0x000,         # File history flags
            0x006         # Lowest Excel version that can read
            # all records in this file
          ]
          write_op @bof, data.pack("v4V2")
        end

        ##
        # Write a cell with a Boolean || Error value
        def write_boolerr row, idx
          value = row[idx]
          type = 0
          numval = 0
          if value.is_a? Error
            type = 1
            numval = value.code
          elsif value
            numval = 1
          end
          data = [
            numval, # Boolean || error value (type depends on the following byte)
            type    # 0 = Boolean value; 1 = Error code
          ]
          write_cell :boolerr, row, idx, *data
        end

        def write_calccount
          count = 100 # Maximum number of iterations allowed in circular references
          write_op 0x000c, [count].pack("v")
        end

        def write_cell type, row, idx, *args
          xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
          data = [
            row.idx, # Index to row
            idx,     # Index to column
            xf_idx  # Index to XF record (➜ 6.115)
          ].concat args
          write_op opcode(type), data.pack(binfmt(type))
        end

        def write_cellblocks row
          # BLANK ➜ 6.7
          # BOOLERR ➜ 6.10
          # INTEGER ➜ 6.56 (BIFF2 only)
          # LABEL ➜ 6.59 (BIFF2-BIFF7)
          # LABELSST ➜ 6.61 (BIFF8 only)
          # MULBLANK ➜ 6.64 (BIFF5-BIFF8)
          # MULRK ➜ 6.65 (BIFF5-BIFF8)
          # NUMBER ➜ 6.68
          # RK ➜ 6.82 (BIFF3-BIFF8)
          # RSTRING ➜ 6.84 (BIFF5/BIFF7)
          multiples, first_idx = nil
          row = row.formatted
          row.each_with_index do |cell, idx|
            cell = nil if cell == ""
            ## it appears that there are limitations to RK precision, both for
            #  Integers and Floats, that lie well below 2^30 significant bits, or
            #  Ruby's Bignum threshold. In that case we'll just write a Number
            #  record
            need_number = need_number? cell
            if multiples && (!multiples.last.is_a?(cell.class) || need_number)
              write_multiples row, first_idx, multiples
              multiples, first_idx = nil
            end
            nxt = idx + 1
            case cell
            when NilClass
              if multiples
                multiples.push cell
              elsif nxt < row.size && row[nxt].nil?
                multiples = [cell]
                first_idx = idx
              else
                write_blank row, idx
              end
            when TrueClass, FalseClass, Error
              write_boolerr row, idx
            when String
              write_labelsst row, idx
            when Numeric
              ## RK encodes Floats with 30 significant bits, which is a bit more than
              #  10^9. Not sure what is a good rule of thumb here, but it seems that
              #  Decimal Numbers with more than 4 significant digits are not represented
              #  with sufficient precision by RK
              if need_number
                write_number row, idx
              elsif multiples
                multiples.push cell
              elsif nxt < row.size && row[nxt].is_a?(Numeric)
                multiples = [cell]
                first_idx = idx
              else
                write_rk row, idx
              end
            when Formula
              write_formula row, idx
            when Date, Time
              write_number row, idx
            end
          end
          write_multiples row, first_idx, multiples if multiples
        end

        def write_colinfo bunch
          col = bunch.first
          width = col.width.to_f * 256
          xf_idx = @workbook.xf_index @worksheet.workbook, col.default_format
          opts = 0
          opts |= 0x0001 if col.hidden?
          opts |= col.outline_level.to_i << 8
          opts |= 0x1000 if col.collapsed?
          data = [
            col.idx,        # Index to first column in the range
            bunch.last.idx, # Index to last column in the range
            width.to_i,     # Width of the columns in 1/256 of the width of the zero
            # character, using default font (first FONT record in the
            # file)
            xf_idx.to_i,    # Index to XF record (➜ 6.115) for default column formatting
            opts           # Option flags:
            # Bits  Mask    Contents
            #    0  0x0001  1 = Columns are hidden
            # 10-8  0x0700  Outline level of the columns
            #               (0 = no outline)
            #   12  0x1000  1 = Columns are collapsed
          ]
          write_op opcode(:colinfo), data.pack(binfmt(:colinfo))
        end

        def write_colinfos
          cols = @worksheet.columns
          bunch = []
          cols.each_with_index do |column, idx|
            if column
              bunch << column
              if cols[idx.next] != column
                write_colinfo bunch
                bunch.clear
              end
            end
          end
        end

        def write_defaultrowheight
          data = [
            0x00, # Option flags:
            # Bit  Mask  Contents
            #   0  0x01  1 = Row height and default font height do not match
            #   1  0x02  1 = Row is hidden
            #   2  0x04  1 = Additional space above the row
            #   3  0x08  1 = Additional space below the row
            0xf2 #   Default height for unused rows, in twips = 1/20 of a point
          ]
          write_op 0x0225, data.pack("v2")
        end

        def write_defcolwidth
          # Offset  Size  Contents
          #      0     2  Column width in characters, using the width of the zero
          #               character from default font (first FONT record in the
          #               file). Excel adds some extra space to the default width,
          #               depending on the default font and default font size. The
          #               algorithm how to exactly calculate the resulting column
          #               width is not known.
          #
          #               Example: The default width of 8 set in this record results
          #               in a column width of 8.43 using Arial font with a size of
          #               10 points.
          write_op 0x0055, [8].pack("v")
        end

        def write_dimensions
          # Offset  Size  Contents
          #      0     4  Index to first used row
          #      4     4  Index to last used row, increased by 1
          #      8     2  Index to first used column
          #     10     2  Index to last used column, increased by 1
          #     12     2  Not used
          write_op 0x0200, @worksheet.dimensions.pack(binfmt(:dimensions))
        end

        def write_eof
          write_op 0x000a
        end

        ##
        # Write a cell with a Formula. May write an additional String record depending
        # on the stored result of the Formula.
        def write_formula row, idx
          xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
          cell = row[idx]
          data1 = [
            row.idx,      # Index to row
            idx,          # Index to column
            xf_idx       # Index to XF record (➜ 6.115)
          ].pack "v3"
          data2 = nil
          case value = cell.value
          when Numeric    # IEEE 754 floating-point value (64-bit double precision)
            data2 = [value].pack EIGHT_BYTE_DOUBLE
          when String
            data2 = [
              0x00,       # (identifier for a string value)
              0xffff
            ].pack "Cx5v"
          when true, false
            value = value ? 1 : 0
            data2 = [
              0x01,     # (identifier for a Boolean value)
              value,    # 0 = FALSE, 1 = TRUE
              0xffff
            ].pack "CxCx3v"
          when Error
            data2 = [
              0x02,       # (identifier for an error value)
              value.code, # Error code
              0xffff
            ].pack "CxCx3v"
          when nil
            data2 = [
              0x03,       # (identifier for an empty cell)
              0xffff
            ].pack "Cx5v"
          else
            data2 = [
              0x02,       # (identifier for an error value)
              0x2a,       # Error code: #N/A! Argument || function not available
              0xffff
            ].pack "CxCx3v"
          end
          opts = 0x03
          opts |= 0x08 if cell.shared
          data3 = [
            opts        # Option flags:
            # Bit  Mask    Contents
            #   0  0x0001  1 = Recalculate always
            #   1  0x0002  1 = Calculate on open
            #   3  0x0008  1 = Part of a shared formula
          ].pack "vx4"
          write_op opcode(:formula), data1, data2, data3, cell.data
          if cell.value.is_a?(String)
            write_op opcode(:string), unicode_string(cell.value, 2)
          end
        end

        ##
        # Write a new Worksheet.
        def write_from_scratch
          # ●  BOF Type = worksheet (➜ 5.8)
          write_bof
          # ○  UNCALCED ➜ 5.105
          # ○  INDEX ➜ 4.7 (Row Blocks), ➜ 5.59
          # ○  Calculation Settings Block ➜ 4.3
          write_calccount
          write_refmode
          write_iteration
          write_saverecalc
          # ○  PRINTHEADERS ➜ 5.81
          # ○  PRINTGRIDLINES ➜ 5.80
          # ○  GRIDSET ➜ 5.52
          # ○  GUTS ➜ 5.53
          write_guts
          # ○  DEFAULTROWHEIGHT ➜ 5.31
          write_defaultrowheight
          # ○  WSBOOL ➜ 5.113
          write_wsbool
          # ○  Page Settings Block ➜ 4.4
          # ○  Worksheet Protection Block ➜ 4.18
          write_proctection
          # ○  DEFCOLWIDTH ➜ 5.32
          write_defcolwidth
          # ○○ COLINFO ➜ 5.18
          write_colinfos
          # ○  SORT ➜ 5.99
          # ●  DIMENSIONS ➜ 5.35
          write_dimensions
          # ○○ Row Blocks ➜ 4.7
          write_rows
          # ●  Worksheet View Settings Block ➜ 4.5
          # ●  WINDOW2 ➜ 5.110
          write_window2
          # ○  SCL ➜ 5.92 (BIFF4-BIFF8 only)
          # ○  PANE ➜ 5.75
          # ○○ SELECTION ➜ 5.93
          # ○  STANDARDWIDTH ➜ 5.101
          # ○○ MERGEDCELLS ➜ 5.67
          write_merged_cells
          # ○  LABELRANGES ➜ 5.64
          # ○  PHONETIC ➜ 5.77
          # ○  Conditional Formatting Table ➜ 4.12
          # ○  Hyperlink Table ➜ 4.13
          write_pagesetup
          write_margins
          write_hyperlink_table
          # ○  Data Validity Table ➜ 4.14
          # ○  SHEETLAYOUT ➜ 5.96 (BIFF8X only)
          # ○  SHEETPROTECTION Additional protection, ➜ 5.98 (BIFF8X only)
          # ○  RANGEPROTECTION Additional protection, ➜ 5.84 (BIFF8X only)
          # ●  EOF ➜ 5.36
          write_eof
        end

        ##
        # Write record that contains information about the layout of outline symbols.
        def write_guts
          # find the maximum outline_level in rows and columns
          row_outline_level = 0
          col_outline_level = 0
          if (row = @worksheet.rows.select { |x| !x.nil? }.max_by(&:outline_level))
            row_outline_level = row.outline_level
          end
          if (col = @worksheet.columns.select { |x| !x.nil? }.max_by(&:outline_level))
            col_outline_level = col.outline_level
          end
          # set data
          data = [
            0,  # Width of the area to display row outlines (left of the sheet), in pixel
            0,  # Height of the area to display column outlines (above the sheet), in pixel
            row_outline_level + 1, # Number of visible row outline levels (used row levels+1; || 0,if not used)
            col_outline_level + 1  # Number of visible column outline levels (used column levels+1; || 0,if not used)
          ]
          # write record
          write_op opcode(:guts), data.pack("v4")
        end

        def write_hlink row, col, link
          # FIXME: only Hyperlinks are supported at present.
          cell_range = [
            row, row, # Cell range address of all cells containing this hyperlink
            col, col # (➜ 3.13.1)
          ].pack "v4"
          guid = [
            # GUID of StdLink:
            # D0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
            # (79EAC9D0-BAF9-11CE-8C82-00AA004BA90B)
            "d0c9ea79f9bace118c8200aa004ba90b"
          ].pack "H32"
          opts = 0x01
          opts |= 0x02
          opts |= 0x14 unless link == link.url
          opts |= 0x08 if link.fragment
          opts |= 0x80 if link.target_frame
          # TODO: UNC support
          options = [
            2,        # Unknown value: 0x00000002
            opts     # Option flags
            #     Bit  Mask        Contents
            #       0  0x00000001  0 = No link extant
            #                      1 = File link || URL
            #       1  0x00000002  0 = Relative file path
            #                      1 = Absolute path || URL
            # 2 and 4  0x00000014  0 = No description
            #                      1 (both bits) = Description
            #       3  0x00000008  0 = No text mark
            #                      1 = Text mark
            #       7  0x00000080  0 = No target frame
            #                      1 = Target frame
            #       8  0x00000100  0 = File link || URL
            #                      1 = UNC path (incl. server name)
          ].pack("V2")
          tail = []
          ## call internal to get the correct internal encoding in Ruby 1.9
          nullstr = internal "\000"
          unless link == link.url
            desc = internal(link).dup << nullstr
            tail.push [desc.size / 2].pack("V"), desc
          end
          if link.target_frame
            frme = internal(link.target_frame).dup << nullstr
            tail.push [frme.size / 2].pack("V"), frme
          end
          url = internal(link.url).dup << nullstr
          tail.push [
            # 6.53.2 Hyperlink containing a URL (Uniform Resource Locator)
            # These data fields occur for links which are not local files || files
            # in the local network (for instance HTTP and FTP links and e-mail
            # addresses). The lower 9 bits of the option flags field must be
            # 0.x00x.xx112 (x means optional, depending on hyperlink content). The
            # GUID could be used to distinguish a URL from a file link.
            # GUID of URL Moniker:
            # E0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
            # (79EAC9E0-BAF9-11CE-8C82-00AA004BA90B)
            "e0c9ea79f9bace118c8200aa004ba90b",
            url.size  # Size of character array of the URL, including trailing zero
            # word (us). There are us/2-1 characters in the following
            # string.
          ].pack("H32V"), url
          if link.fragment
            frag = internal(link.fragment).dup << nullstr
            tail.push [frag.size / 2].pack("V"), frag
          end
          write_op opcode(:hlink), cell_range, guid, options, *tail
        end

        def write_hyperlink_table
          # TODO: theoretically it's possible to write fewer records by combining
          #       identical neighboring links in cell-ranges
          @worksheet.each do |row|
            row.each_with_index do |cell, idx|
              if cell.is_a? Link
                write_hlink row.idx, idx, cell
              end
            end
          end
        end

        def write_iteration
          its = 0 # 0 = Iterations off; 1 = Iterations on
          write_op 0x0011, [its].pack("v")
        end

        ##
        # Write a cell with a String value. The String must have been stored in the
        # Shared String Table.
        def write_labelsst row, idx
          write_cell :labelsst, row, idx, @workbook.sst_index(self, row[idx])
        end

        ##
        # Write multiple consecutive blank cells.
        def write_mulblank row, idx, multiples
          data = [
            row.idx, # Index to row
            idx # Index to first column (fc)
          ]
          # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
          multiples.each_with_index do |blank, cell_idx|
            xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
            data.push xf_idx
          end
          # Index to last column (lc)
          data.push idx + multiples.size - 1
          write_op opcode(:mulblank), data.pack("v*")
        end

        ##
        # Write multiple consecutive cells with RK values (see #write_rk)
        def write_mulrk row, idx, multiples
          fmt = ["v2"]
          data = [
            row.idx, # Index to row
            idx # Index to first column (fc)
          ]
          # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
          multiples.each_with_index do |cell, cell_idx|
            xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
            data.push xf_idx, encode_rk(cell)
            fmt << "vV"
          end
          # Index to last column (lc)
          data.push idx + multiples.size - 1
          write_op opcode(:mulrk), data.pack((fmt << "v").join)
        end

        def write_multiples row, idx, multiples
          case multiples.last
          when NilClass
            write_mulblank row, idx, multiples
          when Numeric
            if multiples.size > 1
              write_mulrk row, idx, multiples
            else
              write_rk row, idx
            end
          end
        end

        ##
        # Write a cell with a 64-bit double precision Float value
        def write_number row, idx
          # Offset Size Contents
          # 0 2 Index to row
          # 2 2 Index to column
          # 4 2 Index to XF record (➜ 6.115)
          # 6 8 IEEE 754 floating-point value (64-bit double precision)
          value = row[idx]
          case value
          when Date, Time
            value = encode_date(value)
          end
          write_cell :number, row, idx, value
        end

        def write_op op, *args
          data = args.join
          @io.write [op, data.size].pack("v2")
          @io.write data
        end

        def write_refmode
          # • The “RC” mode uses numeric indexes for rows and columns, for example
          #   “R(1)C(-1)”, || “R1C1:R2C2”.
          # • The “A1” mode uses characters for columns and numbers for rows, for
          #   example “B1”, || “$A$1:$B$2”.
          mode = 1 # 0 = RC mode; 1 = A1 mode
          write_op 0x000f, [mode].pack("v")
        end

        ##
        # Write a cell with a Numeric || Date value.
        def write_rk row, idx
          write_cell :rk, row, idx, encode_rk(row[idx])
        end

        def write_row row
          # Offset  Size  Contents
          #      0     2  Index of this row
          #      2     2  Index to column of the first cell which
          #               is described by a cell record
          #      4     2  Index to column of the last cell which is
          #               described by a cell record, increased by 1
          #      6     2  Bit   Mask    Contents
          #               14-0  0x7fff  Height of the row, in twips = 1/20 of a point
          #                 15  0x8000  0 = Row has custom height;
          #                             1 = Row has default height
          #      8     2  Not used
          #     10     2  In BIFF3-BIFF4 this field contains a relative offset to
          #               calculate stream position of the first cell record for this
          #               row (➜ 5.7.1). In BIFF5-BIFF8 this field is not used
          #               anymore, but the DBCELL record (➜ 6.26) instead.
          #     12     4  Option flags and default row formatting:
          #                  Bit  Mask        Contents
          #                  2-0  0x00000007  Outline level of the row
          #                    4  0x00000010  1 = Outline group starts || ends here
          #                                       (depending on where the outline
          #                                       buttons are located, see WSBOOL
          #                                       record, ➜ 6.113), and is collapsed
          #                    5  0x00000020  1 = Row is hidden (manually, || by a
          #                                       filter || outline group)
          #                    6  0x00000040  1 = Row height and default font height
          #                                       do not match
          #                    7  0x00000080  1 = Row has explicit default format (fl)
          #                    8  0x00000100  Always 1
          #                27-16  0x0fff0000  If fl = 1: Index to default XF record
          #                                              (➜ 6.115)
          #                   28  0x10000000  1 = Additional space above the row.
          #                                       This flag is set, if the upper
          #                                       border of at least one cell in this
          #                                       row || if the lower border of at
          #                                       least one cell in the row above is
          #                                       formatted with a thick line style.
          #                                       Thin and medium line styles are not
          #                                       taken into account.
          #                   29  0x20000000  1 = Additional space below the row.
          #                                       This flag is set, if the lower
          #                                       border of at least one cell in this
          #                                       row || if the upper border of at
          #                                       least one cell in the row below is
          #                                       formatted with a medium || thick
          #                                       line style. Thin line styles are
          #                                       not taken into account.
          height = row.height || ROW_HEIGHT
          opts = row.outline_level & 0x00000007
          opts |= 0x00000010 if row.collapsed?
          opts |= 0x00000020 if row.hidden?
          opts |= 0x00000040 if height != ROW_HEIGHT
          if (fmt = row.default_format)
            xf_idx = @workbook.xf_index @worksheet.workbook, fmt
            opts |= 0x00000080
            opts |= xf_idx << 16
          end
          opts |= 0x00000100
          height = if height == ROW_HEIGHT
            (height * TWIPS).to_i | 0x8000
          else
            height * TWIPS
          end

          attrs = [
            row.idx,
            row.first_used,
            row.first_unused,
            height,
            opts
          ]

          return if attrs.any?(&:nil?)

          # TODO: Row spacing
          data = attrs.pack binfmt(:row)
          write_op opcode(:row), data
        end

        def write_rowblock block
          # ●● ROW Properties of the used rows
          # ○○ Cell Block(s) Cell records for all used cells
          # ○  DBCELL Stream offsets to the cell records of each row
          block.each do |row|
            write_row row
          end
          block.each do |row|
            write_cellblocks row
          end
        end

        def write_rows
          row_blocks.each do |block|
            write_rowblock block
          end
        end

        def write_saverecalc
          # 0 = Do not recalculate; 1 = Recalculate before saving the document
          write_op 0x005f, [1].pack("v")
        end

        def write_window2
          # This record contains additional settings for the document window
          # (BIFF2-BIFF4) || for the window of a specific worksheet (BIFF5-BIFF8).
          # It is part of the Sheet View Settings Block (➜ 4.5).
          # Offset  Size  Contents
          #      0     2  Option flags:
          #               Bits  Mask    Contents
          #                  0  0x0001  0 = Show formula results
          #                             1 = Show formulas
          #                  1  0x0002  0 = Do not show grid lines
          #                             1 = Show grid lines
          #                  2  0x0004  0 = Do not show sheet headers
          #                             1 = Show sheet headers
          #                  3  0x0008  0 = Panes are not frozen
          #                             1 = Panes are frozen (freeze)
          #                  4  0x0010  0 = Show zero values as empty cells
          #                             1 = Show zero values
          #                  5  0x0020  0 = Manual grid line colour
          #                             1 = Automatic grid line colour
          #                  6  0x0040  0 = Columns from left to right
          #                             1 = Columns from right to left
          #                  7  0x0080  0 = Do not show outline symbols
          #                             1 = Show outline symbols
          #                  8  0x0100  0 = Keep splits if pane freeze is removed
          #                             1 = Remove splits if pane freeze is removed
          #                  9  0x0200  0 = Sheet not selected
          #                             1 = Sheet selected (BIFF5-BIFF8)
          #                 10  0x0400  0 = Sheet not active
          #                             1 = Sheet active (BIFF5-BIFF8)
          #                 11  0x0800  0 = Show in normal view
          #                             1 = Show in page break preview (BIFF8)
          #      2     2  Index to first visible row
          #      4     2  Index to first visible column
          #      6     2  Colour index of grid line colour (➜ 5.74).
          #               Note that in BIFF2-BIFF5 an RGB colour is written instead.
          #      8     2  Not used
          #     10     2  Cached magnification factor in page break preview (in percent)
          #               0 = Default (60%)
          #     12     2  Cached magnification factor in normal view (in percent)
          #               0 = Default (100%)
          #     14     4  Not used
          flags = 0x0536  # Show grid lines, sheet headers, zero values. Automatic
          # grid line colour, Remove slits if pane freeze is removed,
          # Sheet is active.
          if @worksheet.selected
            flags |= 0x0200
          end
          flags |= 0x0080 # Show outline symbols,
          # but if [Row|Column]#outline_level = 0 the symbols are not shown.

          if @worksheet.has_frozen_panel?
            # See:
            # OpenOffice.org's Documentation of the Microsoft Excel File FormatExcel
            # Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003
            # https://www.openoffice.org/sc/excelfileformat.pdf
            # section 5.110.2
            flags |= 0x0008 # Panes are frozen (freeze)
          end
          data = [flags, 0, 0, 0, 0, 0].pack binfmt(:window2)

          write_op opcode(:window2), data
          if @worksheet.has_frozen_panel?
            # See:
            # OpenOffice.org's Documentation of the Microsoft Excel File FormatExcel
            # Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003
            # https://www.openoffice.org/sc/excelfileformat.pdf
            # section 5.75
            pane_data = [@worksheet.froze_left, @worksheet.froze_top, @worksheet.froze_top, @worksheet.froze_left, 3].pack binfmt(:colinfo)
            write_op opcode(:pane), pane_data
          end
        end

        def write_merged_cells
          return unless @worksheet.merged_cells.any?
          # FIXME standards say the record is limited by 1027 records at once
          # And no CONTINUE is supported

          merge_cells = @worksheet.merged_cells.dup
          while (window = merge_cells.slice!(0...1027)).any?
            count = window.size
            data = ([count] + window.flatten).pack("v2v*")
            write_op opcode(:mergedcells), data
          end
        end

        def write_pagesetup
          return unless @worksheet.pagesetup
          data = @worksheet.pagesetup[:orig_data].dup
          if @worksheet.pagesetup[:orientation]
            data[5] = (@worksheet.pagesetup[:orientation] == :landscape) ? 0 : 2
          end

          if @worksheet.pagesetup[:adjust_to]
            data[1] = @worksheet.pagesetup[:adjust_to]
          end

          write_op opcode(:pagesetup), data.pack(binfmt(:pagesetup))
        end

        def write_margins
          @worksheet.margins.each do |key, value|
            next unless [:left, :top, :right, :bottom].include?(key)
            write_op opcode(:"#{key}margin"), [value].pack(binfmt(:margin))
          end
        end

        def write_proctection
          return unless @worksheet.protected?
          # ○ PROTECT Worksheet contents: 1 = protected (➜ 5.82)
          write_op opcode(:protect), [1].pack("v")
          # ○ OBJECTPROTECT Embedded objects: 1 = protected (➜ 5.72)
          # ○ SCENPROTECT Scenarios: 1 = protected (➜ 5.91)
          # ○ PASSWORD Hash value of the password; 0 = no password (➜ 5.76)
          write_op opcode(:password), [@worksheet.password_hash].pack("v")
        end

        def write_wsbool
          bits = [
            #   Bit  Mask    Contents
            1, #     0  0x0001  0 = Do not show automatic page breaks
            #                1 = Show automatic page breaks
            0, #     4  0x0010  0 = Standard sheet
            #                1 = Dialogue sheet (BIFF5-BIFF8)
            0, #     5  0x0020  0 = No automatic styles in outlines
            #                1 = Apply automatic styles to outlines
            1, #     6  0x0040  0 = Outline buttons above outline group
            #                1 = Outline buttons below outline group
            1, #     7  0x0080  0 = Outline buttons left of outline group
            #                1 = Outline buttons right of outline group
            0, #     8  0x0100  0 = Scale printout in percent (➜ 6.89)
            #                1 = Fit printout to number of pages (➜ 6.89)
            0, #     9  0x0200  0 = Save external linked values
            #                    (BIFF3-BIFF4 only, ➜ 5.10)
            #                1 = Do not save external linked values
            #                    (BIFF3-BIFF4 only, ➜ 5.10)
            1, #    10  0x0400  0 = Do not show row outline symbols
            #                1 = Show row outline symbols
            0, #    11  0x0800  0 = Do not show column outline symbols
            #                1 = Show column outline symbols
            0, # 13-12  0x3000  These flags specify the arrangement of windows.
            #                They are stored in BIFF4 only.
            #                00 = Arrange windows tiled
            #                01 = Arrange windows horizontal
            0, #                10 = Arrange windows vertical
            #                11 = Arrange windows cascaded
            # The following flags are valid for BIFF4-BIFF8 only:
            0, #    14  0x4000  0 = Standard expression evaluation
            #                1 = Alternative expression evaluation
            0 #    15  0x8000  0 = Standard formula entries
            #                1 = Alternative formula entries
          ]
          weights = [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
          value = bits.inject { |a, b| a | (b << weights.shift) }
          write_op 0x0081, [value].pack("v")
        end
      end
    end
  end
end