CODEWITHSUNDEEP
javascriptphphtmlmysqlphpexcel
xplody
xplody

Reputation: 103

center all and adjust excel column according to data length

how do i adjust the column width according to the data length in my excel report using PHPexcel?....and how make all the datas in the excel centered?

enter image description here

current code:

<?php
if (!isset($_POST['send'])) { ?>
    <!DOCTYPE html>
    <html>
    <head>
    <title>test</title>
    </head>
    <body>
<?php } else {
    require_once 'C:\xampp\htdocs\test\Classes\PHPExcel\IOFactory.php';
    $filename = 'file.xlsx';
    $title = $_POST['title'];
    mysql_connect("localhost","root","") or die ("cant connect!");
    mysql_select_db("test") or die ("cant find database!");

    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader->setReadDataOnly(true);

    $objPHPExcel = $objReader->load($filename);
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

    $result = mysql_query("SELECT * FROM score");
    if(isset($_POST['send'])){

        $headings = array(
            'ID', 
            'NAME',
            'SCORE 1',
            'SCORE 2',
            'OTHER QUALITIES',
            'INTERVIEW',
            'TOTAL',
            'AIC',
            'BATCHCODE',
        );
        $objPHPExcel->getActiveSheet()->fromArray($headings, null, 'A1');
        $row = 2;
        while( $rows = mysql_fetch_row($result)){
            $objPHPExcel->getActiveSheet()->fromArray($rows, null, 'A' . $row);
            $row++;
        }
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
}
if (!isset($_POST['send'])) { ?>

    <form id="form1" name="form1" method="post" action="" >
    <input name="title" type="text" id="title" value="title" />
    <input type="submit" name="send" value="send to excel" id="send" />
    </form>
    </body>
    </html>
<?php }

i want is...the column will automatically adjust according to data length stored...and make the datas in center.

Upvotes: 0

Views: 1076

Answers (1)

Mark Baker
Mark Baker

Reputation: 212522

Section 4.6.28 of the developer documentation (entitled "Setting a column’s width") shows how to set a column width, with an example of setting it to automatically adjust its width to fit the data:

If you want PHPExcel to perform an automatic width calculation, use the following code. PHPExcel will approximate the column with to the width of the widest column value.

$objPHPExcel->getActiveSheet()
    ->getColumnDimension('E')->setAutoSize(true);

Alignment (such as CENTER) can be set following the instructions in section 4.6.18 (entitled "Formatting cells") of the developer documentation

$objPHPExcel->getActiveSheet()
    ->getStyle('C2:I5')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Upvotes: 1

Related Questions