Implementation

VBA, C#, and Fortran have their own idiosyncrasies for parsing and outputting data.

I’ve written JavaScript functions to simplify the translation, some of which I’ve documented below.

VBA → JavaScript

VBA queries worksheets like this:

Set C = New Class1
C.Connection
strSQL = "Select biology, path from [Description$] where id=""" + idStr + """"
C.rs2.Open strSQL, C.cn

This selects the Biology and Path columns from rows in the Description worksheet, where ID is equal to the variable idStr.

I’m using SheetJS to read each worksheet into an array of objects, where each key represents a column name.

Note that in kansas inputs.xlsx, the worksheet’s Biology and Path column names are in proper case, and the ID column name is in uppercase, which doesn’t match the query’s lowercase column names. This presents a challenge for JavaScript, which has case-sensitive keys.

Initially, I considered updating all queries to match the column names that they refer to. However, I noticed that the case of column names might vary between workbooks. For example, in kansas inputs.xlsx, the Description worksheet includes these columns:

WeatherID

Hybrid

VarietyFile

soilName

But in AGMIPET2Sim, the same columns are labeled:

weatherID

hybrid

varietyfile

SoilName

To eliminate the case-sensitivity problem, I used Proxy.

C# Parsing datafiles → JavaScript

The C# code would open a text file and parse it line-by-line, splitting on whitespace for assignment to variables – changing strings to numeric as needed.

In JavaScript, I wrote a readFile() function, which reads a file, splits it on whitespace, casting strings to numeric where possible, and returning a 2-dimensional array.

That allows us to do this:

const data = readFile(layerFile);
  
const [SurfaceIntervalRatio, FirstSurfaceInterval, InternalIntervalRatio, FirstInternalInterval] = data[1];
const [RowSpacing] = data[3];
const [PlantingDepth, xRootExtent, rootweightperslab] = data[5];
const [BottomBC, GasBCTop, GasBCBottom] = data[8];

… rather than this.

C# DataTables → JavaScript

C# DataTables are interesting, because they represent a kind of “database” in memory.

They are similar to a 2-dimensional array, except you can refer to columns by their name or by their index.

To emulate this functionality in JavaScript, I used Proxy.

That allows us to do this:

const ParseGridFile = (GridFile) => {
  const data = readFile(GridFile);
  const [_, node, element] = data[2];

  const dtNode = dataTable(data.slice(4, node + 4), [
    'Node',
    'X',
    'Y',
    'MatNum',
    'NodeArea',
    'RTWT0',    // WSun RTWT0 represents relative number of root density (no relationship with any physical) 
    'RTWT1',    // WSun RTWT1 represents the multiplication of RTWT0 and node area
    'RTWT'      // WSun RTWT represents the root density which read by SPUDSIM
  ]);
 
  const dtElem4Grid = dataTable(data.slice(node + 6, element + node + 6), [
    'Element',
    'TL',
    'BL',
    'BR',
    'TR',
    'MatNum'
  ]);

  return [dtNode, dtElem4Grid];
} // ParseGridFile

… rather than this.

Fortran format → JavaScript

Fortran has a unique way of formatting output.

For example, the code below outputs IJ * (Numlin - 1) + j and BC as integers right-justified 5 characters, followed by the string ' 0 1 ', followed by GasBCBot right-justified 5 characters, and finally Width formatted to 2 decimals and right-justified 10 characters:

The JavaScript equivalent would be something like this:

Besides the tedium of formatting data like this, it’s difficult to read, and it would be time-consuming to troubleshoot.

For this reason, I wrote a format() function, which allows us to do this:

The first parameter to format() is an array of expressions, and the second parameter is the Fortran format, but wrapped in a template literal so that single quotes can be used.