Convert excel formula into Javascript

New Here ,
May 15, 2020 May 15, 2020

Copy link to clipboard

Copied

Hello everyone,

I have excel spreadsheet that I'd like to convert into a fillable PDF but I don't know how to write in Javascript, can someone please help me ?

Here is what I do in Excel :

 - There is three variables Var A, Var B and Var B (picture 1)

Capture1.PNG

 - Depending of theses variables there is a first calculation (picture 2)

Capture2.PNG

 - And to finish a last calculation that show the result in one cell (picture 3)

Capture3.PNG

For information "SI" means "IF" and "ET" means "AND"

Thank you very much for your help 

TOPICS
Acrobat SDK and JavaScript, Create PDFs, Edit and convert PDFs, How to, PDF forms

Views

809

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Most Valuable Participant ,
May 15, 2020 May 15, 2020

Copy link to clipboard

Copied

This is very basic JS syntax. "IF" in JS is "if" and "AND" in JS is "&&".

You can learn more about it in multiple locations, such as here: https://www.w3schools.com/js/

 

Note that while the basic syntax is the same, though, Acrobat and web JavaScript implementations have a different set of objects, properties and methods.

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 15, 2020 May 15, 2020

Copy link to clipboard

Copied

I would suggest you consider learning JavaScript, or hiring an expert to help you with this conversion.

https://www.pdfscripting.com/public/main.cfm

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 16, 2020 May 16, 2020

Copy link to clipboard

Copied

First, you need to assign the values of all three fields to variables:

 

 

var a = this.getField("name of field A").value
var b = this.getField("name of field B").value
var c = this.getField("name of field C").value

 

 

Then for the calculation, I noticed that your first formula only considers var b to be equal to 2  and var c to be <= 35so:

 

 

if ((b == 2)&&(c <= 35)){
        //insert code here
}

 

 

you will nest the next if inside the first one:

 

 

if ((b == 2)&&(c <= 35)){
      if ((a >= 0)&&(a < 1339)) event.value = "Profil D"
      else if ((a >= 1340)&&(a < 1674)) event.value = "Profil C"
      else if ((a >= 1675)&&(a < 2344)) event.value = "Profil B"
      else if ((a >= 2345)&&(a < 3684)) event.value = "Profil A"
      else if (a >= 3685) event.value = "Profil A+"
      else event.value = ""
}
else event.value = ""

 

 

So the full code (assuming only integer will show in your fields) in the calculate script of your answer field (D9 in your example):

 

 

//Assign variables
var a = this.getField("name of field A").value
var b = this.getField("name of field B").value
var c = this.getField("name of field C").value

//Calculation
if ((b == 2)&&(c <= 35)){
      if ((a >= 0)&&(a < 1339)) event.value = "Profil D"
      else if ((a >= 1340)&&(a < 1674)) event.value = "Profil C"
      else if ((a >= 1675)&&(a < 2344)) event.value = "Profil B"
      else if ((a >= 2345)&&(a < 3684)) event.value = "Profil A"
      else if (a >= 3685) event.value = "Profil A+"
      else event.value = ""
}
else event.value = ""

 

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Most Valuable Participant ,
May 16, 2020 May 16, 2020

Copy link to clipboard

Copied

Remove all instances of "var" from the code, except for the first ones.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 16, 2020 May 16, 2020

Copy link to clipboard

Copied

LATEST

that's a rookie mistake, lol, i'll correct this

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines