README.md in rxl-0.4.1 vs README.md in rxl-0.5.0
- old
+ new
@@ -58,11 +58,12 @@
```
Bear in mind the limitations of reading cell formats. Everything is read as a string other than:
* cells formatted as dates are converted to a DateTime object with the time portion set to midnight
* cells formatted as times are converted to a DateTime object with the date portion set to 31/12/1899 - unless the cell has a date prefix in which case this is carried in (this will be read as a date format as per below parsing rules)
-* numbers (including floats and percentages) where the cell format is number or percentage are read in as integers - trailing zeroes are cropped from floats in this case and percentages are converted to numeric format (eg 100% = 1)
+* percentages are converted to numeric format (eg 100% = 1)
+* to account for floats, which lose any trailing zeroes, decimal point information is retained in the `:decimals` value
* formulas are not read from cells with date and time formats
Within these limitations the cell hash's :format holds the best analysis of the original cell format but as there's no way to extract all of the format information directly from the sheet some information may need to be refurbished as required after import via Rxl.
Further to the above, these rules are applied by Rxl when parsing cells:
@@ -150,26 +151,63 @@
}
```
#### Cell specification
-All cells are written with the format set to general except those with a number format specified
+##### Output formatting
+All cells are written with the format set to general by default
+
+If the value is a DateTime object then use `:date_format` with a date format as per the below examples, the default is 'dd/mm/yyyy'
+
+If `:format` is given as `:percentage` where the value is a number or float then the format defaults to a matching format (eg '0' for 1, '0.0' for 0.1), override using `:number_format` if trailing zeroes are required or rounding is needed
+
Specify the number format according to https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68?ui=en-US&rs=en-US&ad=US
Examples:
-| value | number format | resulting cell format | resulting cell value |
+| value | number_format | resulting cell format | resulting cell value |
|--------------|---------------|-----------------------|----------------------|
-| 0 | 0 | number | 0 |
-| 0.49 | 0 | number | 0 |
-| 0.5 | 0 | number | 1 |
-| 0 | 0.00 | number | 0.00 |
-| 0 | 0% | percentage | 0% |
-| 1 | 0% | percentage | 100% |
+| 0 | '0' | number | 0 |
+| 0.49 | '0' | number | 0 |
+| 0.5 | '0' | number | 1 |
+| 0 | '0.00' | number | 0.00 |
+| 0.5 | '0.00' | number | 0.50 |
+| 0 |' 0%' | percentage | 0% |
+| 1 | '0%' | percentage | 100% |
+| 0.101 | '0.00%' | percentage | 10.10% |
+| 1 | '0.00%' | percentage | 100.00% |
+
+| value | date_format | resulting cell format | resulting cell value |
+|--------------|---------------|-----------------------|----------------------|
| '01/01/2000' | 'dd/mm/yyyy' | date | 01/01/2000 |
+| '01/01/2000' | 'dd-mmm-yyyy' | date | 01-Jan-2000 |
+| '01:00:00' | 'hh:mm:ss' | time | 01:00:00 |
+##### Cell formatting
+
+TODO: add more cell formatting
+
+```ruby
+{
+ 'A1' => {
+ value: 'some_text',
+ font_name: 'Arial',
+ font_size: 8,
+ bold: true,
+ h_align: :center,
+ v_align: :center,
+ fill: 'a1b2c3',
+ border: { top: 'thin', bottom: 'thin', left: 'thin', right: 'thin' }
+ }
+}
+```
+
+##### Formulas
+
+Formulas are read and written from/to the `:formula` value and on write supercede and value specified, however due to some unknown issue, formulas which were written via this gem can then be re-read but the cell value will be empty
+
#### Write Validation
The following rules are validated for write_file:
* The hash_workbook must be a hash (NB if empty a blank file will be created with a single sheet called "Sheet1")
@@ -178,10 +216,9 @@
* The hash_worksheet keys must be strings of valid Excel cell id format
* The hash_worksheet values must be hashes (specifying cells)
* The hash_cell keys must conform the the cell specification as below
-* If a formula is provided the value must be nil or an empty string
* If a number format is provided the value must be consistent with it
Cells are specified as hashes following this example: