PHP Excel – array to excel

Passing an Array ($_SESSION[‘excel_report’])

It creates a PhpExcel

Notes:
You should put the PHP Excel Library in “/utils/PHPExcel” folder otherwise you should change below

<?
session_start();
ob_start();

//----------------------------------------------------
$data = array();

if(isset($_SESSION['excel_report']) && $_SESSION['excel_report']) {
  $data = $_SESSION['excel_report'];
} else {
  die();
}

//$titolo = "TVO - " . date('Y-m-d');
$titolo = $_SESSION['excel_report_titolo'];
if(isset($_POST['titolo'])) {
  $titolo = $_POST['titolo'];
}

//--- sheet title
$titolo_sheet = $_SESSION['excel_report_titolo_foglio'];
/** Error reporting */
//error_reporting(E_ALL);

/** Include path **/
//ini_set('include_path', ini_get('include_path').';'.$_SERVER['DOCUMENT_ROOT'] . '/utils/PHPExcel/Classes/');

/** PHPExcel */
include $_SERVER['DOCUMENT_ROOT'] . '/utils/PHPExcel/Classes/' . 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include $_SERVER['DOCUMENT_ROOT'] . '/utils/PHPExcel/Classes/' . 'PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object
//echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

//--- Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Sesamo");
$objPHPExcel->getProperties()->setLastModifiedBy("Sesamo");
//$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
//$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
//$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

// Add some data
//echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$flag_array_intestazione = false;

//scrivo PADRI
$row = 2;
$column = 0;

//$start_row = count($data) + 3 + 1;
$start_row = 3 + 1;
//$row = 1;
$row = $start_row;
$column = 0;
$flag_array_intestazione = true;
foreach($data as $key => $item) {

  foreach($item as $key1 => $item1) {
    //$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, $item1);
    switch($key1) {
      case 'class':
        continue;
        break;
      case 'Code': //---force to string
        $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit($item1, PHPExcel_Cell_DataType::TYPE_STRING);
        break;
      case 'Income':
      case 'Capacity':
        //$item1 = str_replace(",",".",$item1);
        $item1 = currency2float($item1);
        // $item1 = str_replace(".","",$item1);
        // $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, $item1);
        $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit($item1, PHPExcel_Cell_DataType::TYPE_NUMERIC);
        break;
      default:
       $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, $item1);
    }

    switch($key1) {
      case 'Price':
        $char = chr(ord("A") + $column);
        $range = $char . $row;
        $objPHPExcel->getActiveSheet()->getStyle($range)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        break;
      case 'Income':
        $char = chr(ord("A") + $column);
        $range = $char . $row;
        $objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle($range)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        break;
      case 'Capacity':
        $char = chr(ord("A") + $column);
        $range = $char . $row;
        $objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode('#,##');
        $objPHPExcel->getActiveSheet()->getStyle($range)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        break;
      default:
    }

    if($flag_array_intestazione) $array_intestazione[] = $key1;
    $column++;
  }

  $argb = "FFFFFFFF";
  if ($row % 2 == 1){
    $argb = "FFE0E0E0"; //FFA0A0A0
  }
  //---row---colored
  $styleArray = array(
    'fill' => array(
      'type' => PHPExcel_Style_Fill::FILL_SOLID,
      'startcolor' => array(
        'argb' => $argb, //'FFA0A0A0'
      ),
      // 'endcolor' => array(
        // 'argb' => 'FFFFFFFF',
      // ),
    ),
  );
  $char = chr(ord("A") + $column - 1);
  $objPHPExcel->getActiveSheet()->getStyle('A'. $row .':' . $char . $row)->applyFromArray($styleArray);

  if($flag_array_intestazione) $flag_array_intestazione = false;
  $column = 0;
  $row++;
}

$end_row = $row - 1;

//---ROW HEADER
//$row = 1;
$header_row = 3;

$row = $header_row;
$column = 0;
foreach($array_intestazione as $key) {
  switch($key) {
    case 'class':
      continue;
      break;
    default:
      $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit($key, PHPExcel_Cell_DataType::TYPE_STRING);
      $column++;
  }
}

$styleArray = array(
  'font' => array(
    'bold' => true,
  ),
  // 'alignment' => array(
  // 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  // ),
  // 'borders' => array(
  // 'top' => array(
  // 'style' => PHPExcel_Style_Border::BORDER_THIN,
  // ),
  // ),
  // 'fill' => array(
  // 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
  // 'rotation' => 90,
  // 'startcolor' => array(
  // 'argb' => 'FFA0A0A0',
  // ),
  // 'endcolor' => array(
  // 'argb' => 'FFFFFFFF',
  // ),
  // ),
);

//echo "count(array_intestazione)=" .(count($array_intestazione) - 1) ."<br />";
$char = chr(ord("A") + count($array_intestazione) - 1);
//echo "char=".$char."<br />";
//die();

//---Cell Merge
$objPHPExcel->getActiveSheet()->mergeCells('A1:' . $char . '1');
//$objPHPExcel->getActiveSheet()->mergeCells('A'. ($start_row - 1).':' . $char . ($start_row-1));

//---TITLES
$objPHPExcel->getActiveSheet()->setCellValue('A1', $titolo); //'LISTA'
//$objPHPExcel->getActiveSheet()->setCellValue('A'.($start_row - 1), 'COMMESSE');
//---STYLE
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
//$objPHPExcel->getActiveSheet()->getStyle('A'.($start_row - 1))->applyFromArray($styleArray);

$styleArray = array(
  'font' => array(
    'bold' => true,
  ),
  'alignment' => array(
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  ),
  // 'borders' => array(
  // 'top' => array(
  // 'style' => PHPExcel_Style_Border::BORDER_THIN,
  // ),
  // ),
  'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    // 'rotation' => 90,
    'startcolor' => array(
     'argb' => 'FFA0A0A0',
    ),
    // 'endcolor' => array(
    // 'argb' => 'FFFFFFFF',
    // ),
  ),
);
$objPHPExcel->getActiveSheet()->getStyle('A'. $header_row .':' . $char . $header_row)->applyFromArray($styleArray);

foreach($array_intestazione as $key => $el) {
  //---AUTOSIZE COLUMN ---
  $char = chr(ord("A") + $key);
  $objPHPExcel->getActiveSheet()->getColumnDimension($char)->setAutoSize(true);
}

// Set cell B8
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'Some value');

// Get cell B8
//$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();

// 4.6.7. Explicitly set a cell's datatype
//You can set a cell's datatype explicitly by using the cell's setValueExplicit method, or the setCellValueExplicit method of a worksheet. Here's an example:
//$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC);

//$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
//$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'world!');
//$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Hello');
//$objPHPExcel->getActiveSheet()->SetCellValue('D2', 'world!');

//--- Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle($titolo_sheet); //'Commesse'

//---Borders
$styleArray = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN,
      //'color' => array('argb' => 'FFFF0000'),
    ),
  ),
);
$objPHPExcel->getActiveSheet()->getStyle('A'. $header_row .':' . $char . $end_row)->applyFromArray($styleArray);
// Save Excel 2007 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

// Echo done
//echo date('H:i:s') . " Done writing file.\r\n";

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

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

/*
// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
*/

//End, go with header.
ob_end_flush();

exit;
?>
This entry was posted in PHP, Php Excel and tagged , , . Bookmark the permalink.

1 Response to PHP Excel – array to excel

  1. I savor, cause I discovered just what I was having a look for.
    You have ended my four day long hunt! God Bless you
    man. Have a great day. Bye

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s