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@xxxxxxxxxxxxx]
> Gesendet am: Freitag, 14. Januar 2005 14:34
> An: php-windows@xxxxxxxxxxxxx; php-gurus@xxxxxxxxxxxxxxx
> Betreff: 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
>
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
