• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Views

5.5K

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
Community Expert ,
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.

 

Votes

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
Community Expert ,
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

Votes

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 = ""

 

 

 

Votes

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
Community Expert ,
May 16, 2020 May 16, 2020

Copy link to clipboard

Copied

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

Votes

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

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

Votes

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
New Here ,
Sep 01, 2021 Sep 01, 2021

Copy link to clipboard

Copied

Your very detailed! Would you mind offering some advice on custom scripts? They are relatively small but I want to make sure I have a good understanding.

Votes

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
Community Expert ,
Sep 02, 2021 Sep 02, 2021

Copy link to clipboard

Copied

LATEST

Hi,

 

Just a small change, but ass there is a route where it will return event.value = ""; , that could just be set before hand and then if nothing happens it will be correct so the code can become

 

//Calculation
event.value = ""
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+"
}

 

I just find that a bit neater.

Votes

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