I want to open a xlsx file with PHP using application/vnd.openxmlformats- officedocument.spreadsheetml.sheet because COM does not work on the server.
Does someone know how to do this?
The commented section is the one I want to replace. I am doing this to force excel to calculate the values in the formulas because phpexcel is not getting the correct values.
If there a way to configure the COM to run in the server please let me know.
This is my code:
$inputFileName = 'FORMATO PEDIDO.xlsx';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objPHPexcel = PHPExcel_IOFactory::load($inputFileName);
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPexcel->getActiveSheet();
$claves = $_POST['claves'];
$checked = $_POST['productos'];
$cantidades = $_POST['pallet'];
$id_sucursal_seleccionada = $_POST['recibe_sucursal'];
$sql_query = mysql_query("SELECT * FROM sucursal WHERE Id_Sucursal = '$id_sucursal_seleccionada'");
$sql_array = mysql_fetch_array($sql_query);
$nombre_sucursal = $sql_array['Nombre'];
$i=0;
$j=22;
foreach($claves as $clave_producto)
{
if($checked[$i])
{
$cantidad = $cantidades[$i];
$objWorksheet->getCell('B'.$j)->setValue($clave_producto);
$objWorksheet->getCell('F'.$j)->setValue($cantidad);
$j++;
}
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel,$inputFileType);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save($inputFileName);
$workbook = "C:/Documents and Settings/sosam1/My Documents/Dropbox/htdocs/Merdiz/inventarios/FORMATO PEDIDO.xlsx";
$ex = new COM("Excel.application") or Die ("Did not connect");
$wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open");
$ex->Application->ActiveWorkbook->Save();
$ex->application->ActiveWorkbook->Close("False");
$objPHPexcel = PHPExcel_IOFactory::load($inputFileName);
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPexcel->getActiveSheet();
Have you tried the code from- http://phpexcel.codeplex.com/
Excel is not supported in a non-interactive scenario like the one you describe.
There are a myriad technical reasons why it is problematic. basically, Excel was designed, programmed and tested with the assumption that there would be a human being at the end of the keyboard. Running it on a server might work 50 times and then freeze or crash with no warning. I've seen it happen.
There is also a contractual/licensing issue. Even if you make it work good enough for your purposes, the license for Office basically requires that every user of the web site must also have a valid licenses to the same version of Excel that you are running on the server. That simply makes it impossible to use Excel on a site that is available from the Internet, although you could swing it if your site is an intranet site and the users are licensed properly.
You could also use Excel Services which is a subset of Excel that is supported server-side. This is a feature available in certain versions and editions of SharePoint. Obviously, this is viable only if you have the budget, or if you already have SharePoint anyway.
See official Microsoft guidelines:
http://support.microsoft.com/kb/257757
See also this answer: