Geography Data Type #1733

Open
opened 2020-02-05 16:12:59 +00:00 by tao · 3 comments
tao commented 2020-02-05 16:12:59 +00:00 (Migrated from github.com)

I'm trying to read a Geography Cell and I'm having trouble getting the value.

When I parse the excel to json, the cell is returned as {t: "e", v: 15, w: "#VALUE!"}.

How can I get the country from these linked data type cells?

I'm trying to read a [Geography Cell](https://support.office.com/en-gb/article/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877) and I'm having trouble getting the value. When I parse the excel to json, the cell is returned as `{t: "e", v: 15, w: "#VALUE!"}`. How can I get the country from these linked data type cells?
SheetJSDev commented 2020-02-05 20:21:18 +00:00 (Migrated from github.com)

Most likely the file actually stores an error in the cell and Excel recomputes the value when you open the file. Can you share a sample file?

Most likely the file actually stores an error in the cell and Excel recomputes the value when you open the file. Can you share a sample file?
tao commented 2020-02-05 22:16:38 +00:00 (Migrated from github.com)

Sure, when you view the file in Excel it shows the country with a little map prefixed.

Screenshot 2020-02-05 at 22 16 16

Countries.xlsx

Sure, when you view the file in Excel it shows the country with a little map prefixed. ![Screenshot 2020-02-05 at 22 16 16](https://user-images.githubusercontent.com/1446331/73888226-25b90280-4865-11ea-81f6-cd3e4ff1cec6.png) [Countries.xlsx](https://github.com/SheetJS/sheetjs/files/4162285/Countries.xlsx)
SheetJSDev commented 2020-02-05 22:29:37 +00:00 (Migrated from github.com)

So to be clear, this library reads whatever data is stored in the file. It doesn't attempt to perform Excel calculations (we actually have a paid component to handle calculating formula expressions, but that's not the thrust of the issue here).

For example, "Cambodia" is cell B2. The raw xml stored in the file (after prettifying with xmllint --format) for row 2 is:

    <row r="2" spans="1:3" x14ac:dyDescent="0.2">
      <c r="A2" s="2" t="s">
        <v>4</v>
      </c>
      <c r="B2" s="2" t="e" vm="1">
        <v>#VALUE!</v>
      </c>
      <c r="C2" s="2">
        <v>2017</v>
      </c>
    </row>

Cell B2 is very clearly stored with the #VALUE! error, and t="e" notes that the cell is an error cell.

Note: By default, XLSX uses a shared string table, and the "4" in cell A2 is the string at index 4 from the table (which happens to be "Activity One").
C2 is of course the literal number 2017

There may be a way to grab the name "Cambodia" from the value metadata, so we'll keep the issue open, but for now it's behaving as expected.

So to be clear, this library reads whatever data is stored in the file. It doesn't attempt to perform Excel calculations (we actually have a paid component to handle calculating formula expressions, but that's not the thrust of the issue here). For example, "Cambodia" is cell B2. The raw xml stored in the file (after prettifying with `xmllint --format`) for row 2 is: ```xml <row r="2" spans="1:3" x14ac:dyDescent="0.2"> <c r="A2" s="2" t="s"> <v>4</v> </c> <c r="B2" s="2" t="e" vm="1"> <v>#VALUE!</v> </c> <c r="C2" s="2"> <v>2017</v> </c> </row> ``` Cell B2 is very clearly stored with the `#VALUE!` error, and `t="e"` notes that the cell is an error cell. Note: By default, XLSX uses a shared string table, and the "4" in cell A2 is the string at index 4 from the table (which happens to be "Activity One"). C2 is of course the literal number 2017 There may be a way to grab the name "Cambodia" from the value metadata, so we'll keep the issue open, but for now it's behaving as expected.
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#1733
No description provided.