1

I am generating a xl file using perl spreadsheet, it contains 'vlookup' function for one column, the value which generated from the vlookup function is correct but I want to remove this formula and keep its value only,

How to do this?

I'm using

  • Excel 2007 SP2
  • Spreadsheet::WriteExcel: 2.40
BenMorel
  • 34,448
  • 50
  • 182
  • 322
user1573690
  • 361
  • 1
  • 7
  • 21
  • Calculate the value in your perl script and insert that instead? Not sure what else you might mean here. – Richard Huxton Aug 25 '14 at 10:58
  • Actually my vlookup funtion like this 'VLOOKUP(B2,config!$B$4:$C$13,2,FALSE)'. 'config!$B$4:$C$13' values reading from a input xl file and write calculated data to new XL file and add 'config' sheet of the input file to newly created file, So its not easy to do it in perl scrip – user1573690 Aug 25 '14 at 11:35
  • If you don't want excel to evaluate the function and don't want to do it in perl either, then I'm not sure where you intend to evaluate it. – Richard Huxton Aug 25 '14 at 12:09

1 Answers1

1

=VLOOKUP is an Excel function. That is, Excel knows how to calculate its result. Perl doesn't.

If you want to replace the VLOOKUP function invocation in the Excel worksheet with a value calculated by your Perl program, you are going to have to write a Perl function VLOOKUP whose behavior matches exactly with Excel's VLOOKUP, and invoke it whenever you find a cell containing VLOOKUP.

On the other hand, if, instead of using Spreadsheet::WriteExcel, you use Win32::OLE, you could replace the cell with its value.

Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339