OK, so I have an Excel spreadsheet with 15 sheets full of formulae. I am under an NDA and cannot show you the spreadsheet. I am required to programmatically input values submitted by web users, calculate the outputs and return the results to the user.
Converting the spreadsheet's calculations to pure code would take far too long for the project's budget.
I have tried to use PhpSpreadsheet :-
$workbook = \PhpOffice\PhpSpreadsheet\IOFactory::load("modeller.xlsx");
$sheet = $workbook->getSheet(1);
$c13 = $sheet->getCell('C13')->getValue();
$c43 = $sheet->getCell('C43')->getValue();
print_r(['C13 Before calc' => $c13, 'C43 Before calc' => $c43]);
$c13 = $sheet->getCell('C13')->setValue(12000);
$c43 = $sheet->getCell('C43')->getCalculatedValue();
print_r(['C13 After calc' => $c13, 'C43 After calc' => $c43]);
but it can not handle the complexity - it takes ages to try and run the calculations and then falls over with Fatal error: Maximum function nesting level of '256' reached, aborting!
.
I also tried learning a bit of .NET and used EPPlus :-
var fi = new FileInfo(@"C:\dotnet\webapps\test\modeller.xlsx");
using (var p = new ExcelPackage(fi))
{
var ws = p.Workbook.Worksheets["Inputs & Outputs"];
Console.WriteLine("\tBefore calc Cell(C13).Value={0}", ws.Cells["C13"].Value);
Console.WriteLine("\tBefore calc Cell(C43).Value={0}", ws.Cells["C43"].Value);
ws.Cells["C13"].Value = 12000;
ws.Calculate();
Console.WriteLine("\tAfter calc Cell(C13).Value={0}", ws.Cells["C13"].Value);
Console.WriteLine("\tAfter calc Cell(C43).Value={0}", ws.Cells["C43"].Value);
}
and while this ran faster than PhpSpreadsheet, it was still too slow and always failed to calculate the outputs - the value was always #VALUE!
.
My last resort is using COM to directly interface with a copy of Excel and run the calculations :-
$xl = new COM("Excel.Application");
$xl->Visible = false;
$xl->DisplayAlerts = false;
$file = 'C:\\wamp\\www\\test\\modeller.xlsx';
$wb = $xl->Workbooks->Open($file);
$ws = $wb->Worksheets('Inputs & Outputs');
$c13 = $ws->Range('C12')->Value;
$c43 = $ws->Range('C43')->Value;
print_r(['C13 Before calc' => $c13, 'C43 Before calc' => $c43]);
$ws->Range('C12')->Value = 12000;
$ws->Calculate();
$c13 = $ws->Range('C12')->Value;
$c43 = $ws->Range('C43')->Value;
print_r(['C13 After calc' => $c13, 'C43 After calc' => $c43]);
$wb->Close();
This works (after ages of fiddling with Component Services) and takes only about 2 seconds on my development machine. However I am worried about the scalability of this approach - there could be hundreds of concurrent requests all opening an instance of Excel.
I really don't want to use COM. Is there another way that I have not considered?