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.