AW: [PHP-WIN] Insert range in Excel

Hi Louis, sorry, no way (at least, directly). This is because PHP COM does not support "multi dimensional variant arrays". These are req'd for passing range value lists. (Maybe you want to drop a request to the devlist?) But as transfer one by one is hilariously slow, you could go one of four ways: a) transfer as CSV file and import via COM b) if a row of the range is less than 32k chars in text representation, you may use the expansion feature: convert it to tab separated text, stuff it into the first cell and expand it, like so: // You might want to load the appropriate type library if you have to use // excel constants on the interface. Do that by setting value // com.autoregister_typelib = true directly in PHP.INI file // As of PHP-4.3.2: // ini_set('com.autoregister_typelib', true); does NOT work !! // if you use register_typelib() you will get orphaned excel instances hanging around! $excel = new COM('Excel.Application') or die('Unable to load Excel'); // this is because we want by all means try to get rid of the excel instance we were using, // even if the script dies for any reason: // (function shutdown() releases the COM objects, closes and releases Excel) register_shutdown_function('shutdown'); print "Loaded Excel version {$excel->Version}\n"; // Open (Add new) Workbook... $excel->Workbooks->Open('existing_xls-file') or $excel->Workbooks->Add(); if($excel->Application->WorkBooks->Count() == 1) { // if not absolutely necessary we cut recalculation for speed // xlCalculationManual is a symbol that needs the typelib ! $excel->Application->Calculation = xlCalculationManual; // crucical: suppress user interaction and error dialogs! Excel itself doesn't show up anyway if not forced. $excel->Application->DisplayAlerts = False; // grab the first workbook in list (the only one here) $wb = $excel->Workbooks(1); // Also possible to get a Workbook by name: // $wb = $excel->Workbooks('Mappe1.xls'); // optional: create new sheet to put data in (will result in an error if WB already has Sheet4) $wb->Worksheets->Add->Name = 'Sheet4'; // select sheet to put data in $ws = $wb->Worksheets('Sheet4'); // here you usually test for $ws being an object to assure you get sthing useful $num_cols = YOUR_COLUMN_COUNT; $ num_rows = YOUR_ROW_COUNT; // Column headers, not very imaginative ... for($col = 1; $col <= $num_cols; $col++) { $c = $ws->Cells(1, $col); $c->Value = 'Column '. $col; }; // ... content, neither for ($row = 2; $row <= $num_rows+1; $row++) { // caveat: transferring as text a line at a time compared to // single cell transfer is roghly as many times faster as you have columns // BUT: a line may not exceed some 32700 (it's a little less than 2**15) chars // and the data may not contain tabs // and this is just for data; no bulk transfer for formulas that I know of $line = ""; for ($col = 1; $col <= $num_cols; $col++) { // youst filling in position number as content $line .= $row* $col . "\t"; } $c = $ws->Cells($row, 1); // the whole line is stuffed into the first cell $c->Value = $line; } // Now we take the column ... $c = $ws->Range($ws->Cells(2, 1), $ws->Cells($num_rows+1, 1)); // ... and expand it into the sheet $c->TextToColumns(); } // save wb and close excel c) As Gryffyn Trevor pointed out in this list not long ago there's another choice in case you have a modern Excel at hand (V10+ at least, I think): There's a HTML/XML format for excel sheets. These may be opened by a browser to render and by Excel to fully control it. So you may produce such a text in the first place. Will be a lot faster than COMing around... d) If you have data that can be calculated from a seed (consecutives or any other building rule) you may automate "auto fill in". Do that by recording a macro when creating by hand and translate the upcoming macro. HTH -- Sven Schnitzke > -----Ursprüngliche Nachricht----- > Von: Louis Young [SMTP:louis@kwikpay.co.za] > Gesendet am: Freitag, 14. Januar 2005 14:34 > An: php-windows@lists.php.net; php-gurus@yahoogroups.com > Betreff: [PHP-WIN] Insert range in Excel > > Hi guys > > I managed to get this far with my Excel spreadsheet: > > // Set the workbook to use and it's sheet > $workbook=$_GLOBALS["templatedir"]."\ElectricityTransactions.XLT"; > $sheet="Sheet1"; > > //Instantiate the spreadsheet component. > $ex = new COM("Excel.sheet") or Die ("Did not connect"); > > //Open the workbook that we want to use. > $wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open"); > > //Create a copy of the workbook, so the original workbook will be preserved. > $ex->Application->ActiveWorkbook->SaveAs($_GLOBALS["reportdir"]."\rpt_electrans_".trim($Outlet_session).".xls"); > //$ex->Application->Visible = 1; #Uncomment to make Excel visible. > > //Read and write to a cells in the sheet > $sheets = $wkb->Worksheets($sheet); //Select the sheet > $sheets->activate; //Activate it > > $cell = $sheets->Cells(1,2) ; //Select the cell (Row Column number) > $cell->activate; //Activate the cell > $cell->value = "ELECTRICITY TRANSACTIONS Outlet ".trim($Outlet_session); > //Change it > > But now how would I insert a range i.e., simulate in Excel the following: > > Select range B8:H8. Right click and select insert. > > Any ideas? > > Cheers > Louis > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >