7

I'm using PHPExcel to write a set of images to an Excel file using PHP. Inserting the images doesn't seem to be a problem, however I'm having some difficulties setting the appropriate row height to match the image height.

I managed to use pixelsToCellDimension to calculate the correct column width, but this doesn't seem to be appropriate for calculating row height. After checking out the rest of the functions in the PHPExcel_Shared_Drawing class and Googling around, I'm left dumbfounded as to how to do this.

My code so far:

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("creator")
    ->setLastModifiedBy("modifiedby")
    ->setTitle("Title")
    ->setSubject("Subject")
    ->setDescription("Description")
    ->setKeywords("keyword1 keyword2")
    ->setCategory("category");

// Get default font
$defaultFont = $objPHPExcel->getDefaultStyle()->getFont();

[some code to generate my images and add their paths to an array]

foreach( $filePaths as $i => $filePath ) {
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    $objDrawing->setWorkSheet( $objPHPExcel->getActiveSheet() );
    $objDrawing->setName("name");
    $objDrawing->setDescription("Description");
    $objDrawing->setPath( $filePath );

    $size = getimagesize($filePath);
    $columnWidth = PHPExcel_Shared_Drawing::pixelsToCellDimension($size[0], $defaultFont);
    $rowHeight = PHPExcel_Shared_Drawing::pixelsToCellDimension($size[1], $defaultFont);

    $objDrawing->setCoordinates('A' . ($i+1) );
    $objDrawing->setOffsetX(0);
    $objDrawing->setOffsetY(0);

    // set row/column (cell) sizes to match image size
    $objPHPExcel->getActiveSheet()
       ->getColumnDimension('A')
       ->setWidth( $columnWidth );
    $objPHPExcel->getActiveSheet()
       ->getRowDimension( $i+1 )
       ->setRowHeight( $rowHeight );
}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Worksheet 1');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myMCS Scancode Export.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
pnuts
  • 58,317
  • 11
  • 87
  • 139
Jort
  • 1,401
  • 8
  • 23
  • 39

3 Answers3

3

I used pixelsToPoints for the height

$default_font = $spreadsheet->getDefaultStyle()->getFont();
$image_height = imageSY($image);
$image_width  = imageSX($image);
$image_height_pt = Drawing::pixelsToPoints($image_height);
$image_width_pt  = Drawing::pixelsToCellDimension($image_width,$default_font);

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
$drawing->setImageResource($image);
$drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);      
$drawing->setCoordinates($col.$row); 
$drawing->setHeight($image_height);
$drawing->setWorksheet($spreadsheet->getActiveSheet());
$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight($image_height_pt);
$spreadsheet->getActiveSheet()->getColumnDimension($col)->setWidth($image_width_pt);

Version 1.8.0

Aba
  • 584
  • 6
  • 11
1

If you can't use Drawing::pixelToPoint() method just use the following calculation:

$image_height * 0.75

This is the formula to convert pixel to point (used pixelToPoint method)

Silvio Ney
  • 46
  • 2
-1

I had the same problem. If possible, include the text "\n" in the picture cell. For example, add to the cell 5 times "\n" => "\n\n\n\n\n" for a 100px image height size.

$objPHPExcel->getActiveSheet()->setCellValue($colLetter.$idLine, "\n\n\n\n\n");

But it doesn't works with merges cells

I hope it will help you

squaleXxi
  • 1
  • 3