开发者

php_excel07- How to make grow the height of cell based on cell data(xls)

In my application i need to export to xls file in a predefined format.

so I just integrated php_excel2007. I am using one template with a predefined format.

The problem here is the cell data may change dynamically. if data is much bigger than cell height then data is collapsing.

So is ther anyway to incr开发者_运维知识库ease the height of cell based on content of the cell(in XLX not xlsx)?


The only way is as described in this answer to your previous question on this topic: setting the row height to autofit, and the cell alignment to wrap. This should work the same way, whether you use the Excel5 Writer or the Excel2007 Writer.

$objPHPExcel = new PHPExcel();

// Set some long string values in some cells
$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("This is a large block of text,\ncomprising several lines,\nthat will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A2')->setValue("This is a large block of text that will NOT be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('B1')->setValue("This is another large block of text without any line breaks, that will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A3')->setValue("This is another large block of text,\ncomprising several lines,\nthat will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A4')->setValue("This is another large block of text without any line breaks, that will be set to autofit but not wrap.");
// Fix the column width to a reasonable size
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
//  Set text wrap for cells A1 and B1. This forces the text to wrap, but doesn't adjust the height of the row
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
// Set rows 1, 3 and 4 to autofit the height to the size of text
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(-1);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('testAutoHeight.xls');

EDIT

Added comments to the code sample to explain what each called method actually does


setRowHeight to -1 for make auto height (based on cell data)

// auto-size on row 1

$objWorksheet->getRowDimension('1')->setRowHeight(-1);


<?php
error_reporting(E_ALL);
require_once ROOT.'/PHPExcel.php';

function down($details)
{
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objPHPExcel = $objReader->load(ROOT."/templates/project_report1.xls");

    // Set properties
    $p_i=5;$alpa="B";$row_no=5;$mil="";$d_cel="B";
    $objPHPExcel->setActiveSheetIndex()
                ->setCellValue('K1', $details['report_details']['cur_time']);
    $objPHPExcel->setActiveSheetIndex()
                ->setCellValue('C2', 'REPORT START DATE:'.$details['report_details']['start_date'])
                ->setCellValue('H2', $details['report_details']['details'])
                ->setCellValue('C3', 'SHOWING:'.$details['report_details']['showing']);
    foreach($details as $p_name=>$date){
        //thisis to display date at the top 
        foreach($date as $p1=>$m_name1){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($d_cel.'4', $p1);$d_cel++;//to display date in the top
        }
        break;
    }

    foreach($details as $p_name=>$date){
        if($p_name=="report_details")break;
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$p_i, $p_name);
        $objPHPExcel->getActiveSheet(0)->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A5'), 'A'.$p_i );
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$p_i)->getAlignment()->setWrapText(true);    

        foreach($date as $p=>$m_name){
            $mil=$tic=$st=" ";

            foreach($m_name as $name=>$val){

            if($name=="milestone")
            foreach($val as $in_det=>$det){
                if($det && isset($det['start_date']))
                    $mil.=$det['name']."\n".$det['start_date']."\n";
                else
                    $mil.=$det['name'];
            }
            if($name=="ticket")
                foreach($val as $in_det=>$det){                             
                    if($det)
                        $tic.=$det['name']." ".$det['start_date']."\n";
                }
            if($name=="task")
                foreach($val as $in_det=>$det){
                    if($det)
                        $st.=$det['name']." ".$det['start_date']."\n";
                }
            }
            $summary=$mil.$tic.$st;
            $objPHPExcel->getActiveSheet(0)->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('B5'), $alpa.$p_i );
            $objPHPExcel->getActiveSheet(0)->getRowDimension($p_i)->setRowHeight();
            $objPHPExcel->getActiveSheet(0)->getStyle('A'.$p_i.':'.'M'.$p_i)->getAlignment()->setWrapText(true);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle($alpa.$p_i)->getFont()->setSize(5);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($alpa.$p_i, $summary);
            $alpa++;
        }
        $alpa="B";
        $p_i++;
    }
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setTitle('Report');
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet

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

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}
?>
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜