getColumnDimension

Topics: Developer Forum, User Forum
May 16, 2009 at 11:00 PM

I am trying to read in a spreadsheet and display it via HTML

Is there a way to determine the size of the Excel column and then set the <TD>

element

for ($col = 0; $col <= $highestColumnIndex; ++$col) {

$columnsize = $objPHPExcel->getActiveSheet()->getColumnDimension('$col')

  echo '<td width=' . $columnsize . '>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";

  }

  echo '</tr>' . "\n";

 

I know I am missing something because $objPHPExcel->getActiveSheet()->getColumnDimension('$col') is a object and I can not covert it to a string

but I am trying to the HTML columns to match the spreadsheet cell widths

Developer
May 17, 2009 at 12:22 AM

Instead of this:

$columnsize = $objPHPExcel->getActiveSheet()->getColumnDimension('$col')

Try something like this:

$columnsize = ($width = $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->getWidth()) > 0
    ? $width : 10;

Here 10 is just some fallback width for columns with no explicitly set width (they will return a width of -1).

 

May 17, 2009 at 1:35 AM
Edited May 17, 2009 at 1:47 AM

All the columns are defaulting to whatever value the fallback setting is ie 10

I am not sure why its unable to determine the width of the excel column and

put that in the $columnsize variable for use in the <td> element? 

 

// Data Loop
for ($row = 2; $row <= $highestRow; ++$row) {
  echo '<tr>' . "\n";

  for ($col = 0; $col <= $highestColumnIndex; ++$col)
  {

   $columnsize = ($width = $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->getWidth()) > 0 ? $width : 10;
   echo '<td width=' . $columnsize . '>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '&nbsp</td>' . "\n";    

  }

  echo '</tr>' . "\n";
}

 

HTML SOURCE

<td width=10>490000004&nbsp</td>
<td width=10>6/08&nbsp</td>
<td width=10>39611&nbsp</td>
<td width=10>39624&nbsp</td>
<td width=10>6&nbsp</td>
<td width=10>&nbsp</td>
<td width=10>TM1&nbsp</td
Developer
May 17, 2009 at 1:47 AM

Sounds strange. Do the columns in the sheet have different widths? Or have you set a default width for all columns?

 

May 17, 2009 at 1:50 AM

The column widths in the spreadsheet are  (11.14 , 12.43 , 13.29 , 12.14 , 15.57 , 10.29 , 9.14)

Developer
May 17, 2009 at 2:01 AM

I can't see whether you are retrieving data from the active sheet or some other sheet.

Can you try to replace this

$objPHPExcel->getActiveSheet()

with this in your loop

$objWorksheet

If this doesn't work, try to send me the Excel file (erik at phpexcel dot net).

 

Developer
May 17, 2009 at 2:22 AM

[File received]

The problem is that you have this:

$objReader->setReadDataOnly(true);


Column widths are ignored when you use that.

You could also try to use PHPExcel_Writer_HTML to convert your Excel file to HTML?

 

May 17, 2009 at 2:27 AM
Kind of an object rookie - how would I go about using the Writer to accomplish this?
Also without setReadOnly(true) I burn more RAM than my hoster will let me allocate
and this is only a sample dataset and not even the full spreadsheet


From: [email removed]
To: [email removed]
Date: Sat, 16 May 2009 18:22:49 -0700
Subject: Re: getColumnDimension [PHPExcel:56548]

From: koyama
[File received]
The problem is that you have this:

$objReader->setReadDataOnly(true);


Column widths are ignored when you use that.

You could also try to use PHPExcel_Writer_HTML to convert your Excel file to HTML?



Hotmail® has ever-growing storage! Don’t worry about storage limits. Check it out.
May 17, 2009 at 2:37 AM
I also chose to it the way I was I was because I need to write a header sort function
and also try to develop a filter routine in HTML so I emulate much of the sheet functionality


From: [email removed]
To: [email removed]
Date: Sat, 16 May 2009 18:22:49 -0700
Subject: Re: getColumnDimension [PHPExcel:56548]

From: koyama
[File received]
The problem is that you have this:

$objReader->setReadDataOnly(true);


Column widths are ignored when you use that.

You could also try to use PHPExcel_Writer_HTML to convert your Excel file to HTML?



Hotmail® has a new way to see what's up with your friends. Check it out.
Developer
May 17, 2009 at 2:43 AM

Do like this to convert from xlsx to HTML:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $reader->load('headings.xlsx');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->save('write.html');

Also without setReadOnly(true) I burn more RAM than my hoster will let me allocate
and this is only a sample dataset and not even the full spreadsheet

Use latest source code from here:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

I tried with the file you sent me, here is the difference (both tests with full styles):

Latest source code (Change Set 27516)
- read took: 4 seconds
- write took: 2 seconds
- Peak memory usage: 12.5 MB

PHPExcel 1.6.7
- read took: 24 seconds
- write took: 11 seconds
- Peak memory usage: 58.5 MB

May 17, 2009 at 2:58 AM
Still not respecting the columns... using new code and commented out the
setReadDataOnly(true)

http://www.daguild.org/soramin/soramin.php



<?php
error_reporting(E_ALL);
ini_set('memory_limit', '768M');
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');
include 'PHPExcel.php';
include 'PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
// $objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("headings.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

echo ' ' . "\n";
//Header ONLY Loop
echo ' ' . "\n";
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
echo ' ' . "\n";
}
echo ' ' . "\n";
// Data Loop
for ($row = 2; $row <= $highestRow; ++$row) {
echo ' ' . "\n";
for ($col = 0; $col <= $highestColumnIndex; ++$col)
{
$columnsize = ($width = $objWorksheet->getColumnDimensionByColumn($col)->getWidth()) > 0 ? $width : 10;
echo ' ' . "\n";
}
echo ' ' . "\n";
}
echo '
' . $objWorksheet->getCellByColumnAndRow($col, 1)->getValue() . '
' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . ' 
' . "\n";
// Echo memory peak usage
echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB";
?>



From: [email removed]
To: [email removed]
Date: Sat, 16 May 2009 18:43:10 -0700
Subject: Re: getColumnDimension [PHPExcel:56548]

From: koyama
Do like this to convert from xlsx to HTML:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $reader->load('headings.xlsx');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->save('write.html');

Also without setReadOnly(true) I burn more RAM than my hoster will let me allocate
and this is only a sample dataset and not even the full spreadsheet
Use latest source code from here:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

I tried with the file you sent me, here is the difference (both tests with full styles):

Latest source code (Change Set 27516)
- read took: 4 seconds
- write took: 2 seconds
- Peak memory usage: 12.5 MB

PHPExcel 1.6.7
- read took: 24 seconds
- write took: 11 seconds
- Peak memory usage: 58.5 MB


Hotmail® goes with you. Get it on your BlackBerry or iPhone.
Developer
May 17, 2009 at 3:18 AM
Still not respecting the columns... using new code and commented out the

Column widths are actually picked up. It is correct that getWidth() does not return exactly what you see in the dialog. There is an explanation for that if you have time to learn:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=43265

Best advice it to adjust columns in you Excel file by experimenting until they come out right in the HTML. Pixel-perfection is hard to achieve with HTML/CSS and may not even be wanted.