How to update the excel cell as Number instead of text.

[AnkuK]

Hi ,

I am trying to save some values in the excel sheet as numbers that will later be used in Formulas. I am using SpreadsheetDecoder in Dart . The problem is , I am able to save the data , but the data saved is in Text format and so it being text i am not able to use in Formula that need Number. The code I am using is provided below.

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

File file = File('FileToUpdate.xlsx');
List requiredRow;
SpreadsheetDecoder decoder;
Uint8List bytes;
/*
Row number (rowNum) for various values in excel sheet
'aValue':14 , int
'bValue':15 int
'cValue':16 double
*/
int rowNum = 14;
int newValue = 5;

void main(List args) {
bytes = file.readAsBytesSync();
decoder = SpreadsheetDecoder.decodeBytes(bytes, update: true);
final table = decoder.tables['SheetName'];
requiredRow = table.rows[rowNum];
var requiredValue = requiredRow[0] as String;
print(requiredValue);

setValue(newValue);

final valueStored = getValue();
print(valueStored);

}

void setValue(int value) {
decoder.updateCell('SheetToUpdate', 1, rowNum, value);
File(join('FileToUpdate.xlsx'))
..createSync(recursive: true)
..writeAsBytesSync(decoder.encode());
}

String getValue() {
String returnValue = requiredRow[1].toString();
return returnValue;
}

[sestegra]

This feature isn't available yet.

The code that manages this is here.
https://github.com/sestegra/spreadsheet_decoder/blob/master/lib/src/xlsx.dart#L517

I won't have time to implement now.

[DerBasler]

@sestegra according to you is probably more related to https://github.com/sestegra/spreadsheet_decoder/blob/master/lib/src/xlsx.dart#L558 ;)
I am trying to implement the feature but I struggle to find the right structure excel demands.
Can you send me the source you used to look it up? I used https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.celltype?view=openxml-2.8.1.
My solution atm is adding:

  static XmlElement _createCell(int columnIndex, int rowIndex, dynamic value) {
    final cellType = value.runtimeType == String ? 'InlineString' : 'n';
    
    var attributes = <XmlAttribute>[
      XmlAttribute(
          XmlName('r'), '${numericToLetters(columnIndex + 1)}${rowIndex + 1}'),          
      XmlAttribute(XmlName('t'), cellType),
    ];
    final bool hasValue = value == null;
    var children = hasValue
        ? <XmlElement>[]
        : _createChildrenForType(value);
    return XmlElement(XmlName('c'), attributes, children);
  }

  static Iterable<XmlNode> _createChildrenForType(dynamic value) {
    final Type valueType = value.runtimeType;
    if (valueType == String) {
        return [XmlElement(XmlName('is'), [], [
          XmlElement(XmlName('t'), [], [XmlText(value.toString())])
        ])];
    };

    return [XmlElement(XmlName('v'), [], [XmlText(value.toString())])];
  }

This produces an output like described in http://officeopenxml.com/SScontentOverview.php

            <c r="B2" t="InlineString">
                <is>
                    <t>MyString</t>
                </is>
            </c>
            <c r="C2" t="n">
                <v>1234567890.0</v>
            </c>

But excel can not handle it.
Any help would be appreciated.

[DerBasler]

Hey @sestegra could you throw me a 🦴 and send me into right direction. Unfortunately I am pretty stuck right now 😕
Thx

[MalikSamiAwan]

@AnkuK @sestegra @DerBasler

Facing same issue...
Any solutions?

[DerBasler]

Hey @MalikSamiAwan no unfortunately I had no time to dig in any further but let me know if you have more luck.