writing values in cells who do not contain any values in them

[Sacchid]

Will there be any new method added to write cells ? Because of restriction placed on updateCell only those cells with existing values can be replaced. What to do when have to write cells which do not have any value?

[sestegra]

The restriction that you point out is to check indexes boundaries.

You could use insertRow and/or insertColumn method(s) before updating cell. See example/read_spreadsheet.dart

[Sacchid]

I am getting RangeError: Invalid value: Not in range 0..2, inclusive: 3 on decoder..insertColumn(workSheet,3); where : decoder.tables[workSheet].maxCols is 2.

Here I want to write value in 3rd column. (I know that current file only has values till column 2, so is there any way I can add values in those column/cells?)

[sestegra]

Columns and rows indexes are "computer" indexes. So, if you have N column; column index goes from 0 to N-1.

In your case, 3rd column index is equal to 2.

[Sacchid]

Take a look at this example Here, decoder..insertColumn(workSheet, 2); decoder..updateCell(workSheet, 2, 0, "c"); should update A3 as "c" Instead it adds c to last column every time program runs e.g. a,b => a,b,c => a,b,c,c

[sestegra]

So, insertColumn method is similar to the manual action you could do with a spreadsheet editor (i.e. right-click on a column and select Insert column here action)

As a result, each time you call insertColumn or do the action Insert column here in a spreadsheet editor, a new column is inserted.

Moreover, in you next discussion with the community, I suggest you to use code using fenced code block instead of a screenshot.

[Sacchid]

I added screenshot for showing the output of code. Cropped image only to include console output - As,

decoder..insertColumn(workSheet, 2);
decoder..updateCell(workSheet, 2, 0, "c");

should add c at cell A3 as columnIndex is 2, instead it adds c to last column irrespective of it's index

Here, above mentioned code is appending c to last column each time it's run, instead of updating c to cell A3

Here's the GitHub link of code and poly1.xlsx (excel file which I'm currently using)

[sestegra]

First of all, I would suggest you again to copy your code, logs and errors as text instead of screenshots.

In fact, a screenshot is not relevant to describe your problem. On top of it, screenshots are bigger than text

• text size < 1kB
• screenshot size > 16kB

Moreover, text on screenshot

• couldn't be indexed by a search engine for future users looking for the same issue
• couldn't be easily copied without OCR.

Then, spreadsheet_decoder is working as expected. I can't help you more. See the following code and output.

import 'dart:io';
import 'package:spreadsheet_decoder/spreadsheet_decoder.dart';

void dumpTable(SpreadsheetTable table, int rows) {
  print("table ${table.maxCols} columns x ${table.maxRows} rows");
  for (var row = 0; row < rows; row++) {
    var data = "| ";
    for (var column = 0; column < table.maxCols; column++) {
      data += "${table.rows[row][column]} | ";
    }
    print("$row = $data");
  }
  print("");
}

void main(List<String> arguments) {
  var bytes = File("poly1.xlsx").readAsBytesSync();
  var decoder = SpreadsheetDecoder.decodeBytes(bytes, update: true);
  var sheet = 'Sheet1';
  var table = decoder.tables[sheet];

  print("Loaded table");
  dumpTable(table, 3);

  // Insert column after last column
  var maxCols = table.maxCols;
  decoder.insertColumn(sheet, maxCols);
  print("After column insertion");
  dumpTable(table, 3);

  // Update cell at first row in inserted column
  decoder.updateCell(sheet, maxCols, 0, 'c');
  print("After row update");
  dumpTable(table, 3);

  File("poly1.xlsx").writeAsBytesSync(decoder.encode());
}

First run

Loaded table
table 2 columns x 70 rows
0 = | a | b |
1 = | 18.507305 | 73.806131 |
2 = | 18.508689 | 73.806131 |

Afer column insertion
table 3 columns x 70 rows
0 = | a | b | null |
1 = | 18.507305 | 73.806131 | null |
2 = | 18.508689 | 73.806131 | null |

After row update
table 3 columns x 70 rows
0 = | a | b | c |
1 = | 18.507305 | 73.806131 | null |
2 = | 18.508689 | 73.806131 | null |

Second run

Loaded table
table 3 columns x 70 rows
0 = | a | b | c |
1 = | 18.507305 | 73.806131 | null |
2 = | 18.508689 | 73.806131 | null |

After column insertion
table 4 columns x 70 rows
0 = | a | b | c | null |
1 = | 18.507305 | 73.806131 | null | null |
2 = | 18.508689 | 73.806131 | null | null |

After row update
table 4 columns x 70 rows
0 = | a | b | c | c |
1 = | 18.507305 | 73.806131 | null | null |
2 = | 18.508689 | 73.806131 | null | null |