Spreadsheet to Arduino ide

Hi,
I have created and use a spreadsheet that has a massive table of data. When I input 2 values into separate input cells in the spreadsheet, the spreadsheet cross references the 2 values and generates the correct data. This is not possible with a formula. Basically, a temperature value is entered and a sensor reading is entered and a corrected sensor output is given.

I have since made a digital version of the sensor with an esp32, this gives me the current sensor value from the sensor ( lets say voltage, but this is not important what the sensor is) and a temperature sensor.

What options is there to then have these 2 values sent into the spreadsheet and the result sent back to the esp32 to display the corrected value?
Is it possible to have the spreadsheet converted to a form and this form in the code through Arduino ide? or some other way to have the spreadsheet data on the esp32 to be referenced against?
It would need to be run locally on the esp32 and to be displayed on a tft display, not over Wifi or internet as the value would need to be updated every 5s and instanly.

Any help would be much appreciated.
Sorry if I have broken any posting rules.

Welcome to the forum

Exactly how big is the Excel dataset ?
Number of rows and columns an what data type ?

Hi, 213 rows 140 columns.
cheers

And the data type ?

Just numbers 2 digit 1 decimal place

You can export the spreadsheet to a CSV file. You probably have a few options after that.

  1. Place the file in a file system like LittleFS (no idea how to do that).
  2. Copy the file to SD card and read from SD when needed.
  3. Store in the sketch; I think that the ESP32 has plenty of memory for that.

For (3)
Let's say that you have something like this


The first row contains the data for one of your variables, the first column the data for the other variable.

The csv will have the following content

,0.1,0.3,1
2.2,2.2,2.2,2.4
3.3,4.0,4.5,5.2
4.4,5.9,7.5,9.1

Using a proper editor, you can easily change it to

{,0.1,0.3,1},
{2.2,2.2,2.2,2.4},
{3.3,4.0,4.5,5.2},
{4.4,5.9,7.5,9.1},

And next change it to

const float lookup[][4] = {
  {0, 0.1, 0.3, 1},
  {2.2, 2.2, 2.2, 2.4},
  {3.3, 4.0, 4.5, 5.2},
  {4.4, 5.9, 7.5, 9.1},
};

Copy the above into your sketch.

With only one digit after the decimal dot, floats are OK to use. The [n] is required and you need to change it to the number of columns. Copy it into your sketch. You will also need to add a value for the cell in the first row and column; I did put 0 in it.

Below code demonstrates how to iterate through the two dimensional array

// macro to calculate the number of elements in any type of array
#define NUMELEMENTS(x) (sizeof(x) / sizeof(x[0]))

const float lookup[][4] = {
  {0, 0.1, 0.3, 1},
  {2.2, 2.2, 2.2, 2.4},
  {3.3, 4.0, 4.5, 5.2},
  {4.4, 5.9, 7.5, 9.1},
};

void setup()
{
  Serial.begin(115200);

  Serial.print("Number of elements in a row = ");
  Serial.println(NUMELEMENTS(lookup[0]));

  Serial.print("Number of rows = ");
  Serial.println(NUMELEMENTS(lookup));

  // display the content of the array
  for (uint16_t rowCnt = 0; rowCnt < NUMELEMENTS(lookup); rowCnt++)
  {
    for (uint16_t colCnt = 0; colCnt < NUMELEMENTS(lookup[0]); colCnt++)
    {
      // skip the first cell
      if (rowCnt == 0 && colCnt == 0)
      {
        Serial.print("\t");
        continue;
      }
      Serial.print(lookup[rowCnt][colCnt], 1);
      Serial.print("\t");
    }
    Serial.println();
  }
}

void loop()
{
}

Output:

Number of elements in a row = 4
Number of rows = 4
	0.1	0.3	1.0	
2.2	2.2	2.2	2.4	
3.3	4.0	4.5	5.2	
4.4	5.9	7.5	9.1
1 Like

Thats Great!! I was not expecting that much info, you have gone above and beyond. thank you this completely answered my question. Thanks again for your help

Not really a massive spreadsheet.
Out of interest, what spreadsheet are you using? Excel etc.? Version?
Not sure what you mean by "not possible with a formula"
I haven't come across anything in Excel at least that isn't possible one way or another.
There are plenty of built-in functions for cross referencing like LOOKUP, MATCH, CHOICE for example.

Your question is not truly related to IDE 2.0, which is the forum section you posted in. The problem to solve would be the same for any version of the IDE. So I have moved the topic to a more appropriate section.

I put the entire OUI dataset 678Kon an ESP8266 and built a search engine to navigate the 3 octet.

Sketch uses 250757 bytes (24%) of program storage space. Maximum is 1044464 bytes.
Global variables use 63704 bytes (77%) of dynamic memory, leaving 18216 bytes for local variables. Maximum is 81920 bytes.

1 Like

A beware warning. My Open Office spreadsheet program exports a CSV file with a CR/LF as an end of row marker, while Xcel exports with just a CR at the end of a line.

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.