smallseo.info

PHPExcel

A pure PHP library for reading and writing spreadsheet files

how to use phpexcel to read data and insert into database?

I have a php application where I want to read data from excel, Insert into database and then generate pdf reports for specific users. I searched over the internet a lot but nothing specific given about both things. if someone could provide a tutorial or something, that would be a real help.


Source: (StackOverflow)

Set Background cell color in PHPExcel

How to set specific color to active cell when creating XLS document in PHPExcel?


Source: (StackOverflow)

phpexcel to download

hello i am new to phpexcel, and i was wondering if there is some way send the excel i have created to the clients download without saving it on my server or to delete it right after he downloads it

i am trying to create an "export button" on a page that will give the user a "pop-up" with the excel that he wants that i have just created.

now after i create the table i do :

$objXLS->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
$objXLS->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);

$objXLS->getActiveSheet()->setTitle('Test Stats');

$objXLS->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objXLS, 'Excel5');
$objWriter->save(__DIR__."/test1.xls");

but that saves it to my server

thank you


Source: (StackOverflow)

how to make New lines in a cell using phpexcel

i have problem with php excel,

i want to make new line in one cell but i can't, i have tried using \n or <br /> but itsn't work. this my code:

$objPHPExcel->getActiveSheet()->setCellValue('H5', 'Hello\nWorld'); // i need this show in two line
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

fyi: my format excel is xls not xlsx. many thanks :)


Source: (StackOverflow)

Alternative for PHP_excel

Is there any alternative for PHP_excel which can "Export to XLSX/XLS" file in a customized format?

This is a General Reference question for the tag


Source: (StackOverflow)

How to find out how many rows and columns to read from an Excel file with PHPExcel?

With the following code, I am able to read the cells out of an Excel file with PHPExcel.

I currently manually define how many rows and columns to read.

Is there a way that PHPExcel can tell me how many rows and columns I have to read to get all the data out of the worksheet, e.g. even if some rows and columns are left blank?

$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);

$objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
$objReader->setLoadSheetsOnly(array($sheet_name));
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("data/" . $file_name);

echo '<table border="1">';
for ($row = 1; $row < $number_of_rows; $row++) {
    echo '<tr>';
    for ($column = 0; $column < $number_of_columns; $column++) {
        $value = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
        echo '<td>';
        echo $value . '&nbsp;';
        echo '</td>';
    }
    echo '</tr>';
}
echo '</table>';

Solution:

Thanks, Mark, here's the full solution with those functions:

$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);

$objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
$objReader->setLoadSheetsOnly(array($sheet_name));
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("data/" . $file_name);

$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

echo 'getHighestColumn() =  [' . $highestColumm . ']<br/>';
echo 'getHighestRow() =  [' . $highestRow . ']<br/>';

echo '<table border="1">';
foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    echo '<tr>';
    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            $value = $cell->getCalculatedValue();
            echo '<td>';
            echo $value . '&nbsp;';
            echo '</td>';
        }
    }
    echo '</tr>';
}
echo '</table>';

alt text


Source: (StackOverflow)

CSV Export/Import with PHPExcel

Could you please guide me or provide me with some sample codes for performing CSV export and import using the PHPExcel library?

Excel export and import is fine but I need CSV export/import as well. I have other means of CSV export and import, but can it be done via PHPExcel also?


Source: (StackOverflow)

PHP Excel - Correct format for strings / numbers beginning with zero?

I'm trying to use PHP to create a file containing a list of phone numbers. It's working OK however if the phone number begins with zero, the digit is dropped from the excel file.

Does anyone know how to set the formatting correctly so that it remains in place?

Any advice appreciated.

Thanks.


Source: (StackOverflow)

Set Font Color, Font Face and Font Size in PHPExcel

I'm working in PHPExcel. I'm beginner.When I'm using following code and its working fine.

$phpExcel = new PHPExcel();

$phpExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true)
                                ->setName('Verdana')
                                ->setSize(10)
                                ->getColor()->setRGB('6F6F6F');

But when I'm using following code and not getting expected result as above.

$phpFont = new PHPExcel_Style_Font();
$phpFont->setBold(true);
$phpFont->setName('Verdana');
$phpFont->setSize(15);

$phpColor = new PHPExcel_Style_Color();
$phpColor->setRGB('FF0000');  

$phpExcel->getActiveSheet()->getStyle('A1')->setFont( $phpFont );
$phpExcel->getActiveSheet()->getStyle('A1')->getFont()->setColor( $phpColor );

Please help me what am I doing wrong in above code.

Thank you in advance!


Source: (StackOverflow)

Setting width of spreadsheet cell using PHPExcel

I'm trying to set the width of a cell in an Excel document generated with PHPExcel with:

$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setWidth('10');
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setAutoSize(false);

but that does not works.

What is the method that I need to call here?


Source: (StackOverflow)

PHPExcel very slow - ways to improve?

I am generating reports in .xlsx using PHPExcel. It was okay in the initial testing stages with small data sets (tens of rows, 3 sheets), but now when using it on a real production data with over 500 rows in each sheet, it becomes incredibly slow. 48 seconds to generate a file, and when running a report that combines more information, the whole thing fails with Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041. Sometimes it's in another PHPExcel file, so I doubt the exact location is that relevant.

Ideally, I would want to speed it up somehow, if possible. If not, then at least increase the execution limit for this script.

The only suggestions I have seen so far was to style in ranges instead of individual cells. Unfortunately, I already do my styling in ranges and it is rather minimal too. Any other suggestions?


Source: (StackOverflow)

How to PHPExcel set auto-columns width

I'm working with PHPExcel to export data for download. When open downloaded files, with cells have big number, it show "#######" instead of value number. I'm tried setAutoSize() for every columns then call $sheet->calculateColumnWidths() but it still not changes. I see calculateColumnWidths() at here, @Mark Baker says "calculateColumnWidths() increase the value by perhaps 5% to try and ensure that the entire column fits". If number length in cell exceed 5%, it seems doen's resolved the problem

UPDATE This is my function to auto size columns:

   function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) {
        if (empty($toCol) ) {//not defined the last column, set it the max one
            $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
        }
        for($i = $fromCol; $i <= $toCol; $i++) {
            $sheet->getColumnDimension($i)->setAutoSize(true);
        }
        $sheet->calculateColumnWidths();
    }

Source: (StackOverflow)

How to set auto height in phpexcel?

I am using phpexcel to generate excel document with PHP.

the question is, how to make auto height row using phpexcel? many thanks :)


Source: (StackOverflow)

PHPExcel Make first row bold

I am trying to make cells in first row are bold.

This is the method I have created for that purpose.

function ExportToExcel($tittles,$excel_name)
 {
  $objPHPExcel = new PHPExcel();
  $objRichText = new PHPExcel_RichText();
  // Set properties
  $objPHPExcel->getProperties()->setCreator("SAMPLE1");
  $objPHPExcel->getProperties()->setLastModifiedBy("SAMPLE1");
  $objPHPExcel->getProperties()->setTitle("SAMPLE1");
  $objPHPExcel->getProperties()->setSubject("SAMPLE1");
  $objPHPExcel->getProperties()->setDescription("SAMPLE1");


  // Add some data
  $objPHPExcel->setActiveSheetIndex(0);

  $letters = range('A','Z');
  $count =0;
  $cell_name="";
  foreach($tittles as $tittle)
  {
   $cell_name = $letters[$count]."1";
   $count++;
   $value = $tittle;
   $objPHPExcel->getActiveSheet()->SetCellValue($cell_name, $value);
   // Make bold cells
   $objPHPExcel->getActiveSheet()->getStyle($cell_name)->getFont()->setBold(true);
  }
  // Save Excel 2007 file
  $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
  //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
  $objWriter->save($excel_name.".xlsx");
 }

The problem is in output excel file the cells are not bold.


Source: (StackOverflow)

PHPExcel how to set cell value dynamically

How to set cell/column value dynamically using PHPExcel library?

I am fetching result set from MySQL database and I want to write data in excel format using PHPExcel library. Looking at example

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'cell value here');

indicates that we have to hard code cell/column reference As 'A1', then it writes to cell/column A1. How I can increment cell/column and/or row reference based on rows and corresponding column values from result set?

Please guide.


Source: (StackOverflow)