/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", PHP_EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s'), " Set document properties", PHP_EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Generate an image
echo date('H:i:s'), " Generate an image", PHP_EOL;
$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');
$textColor = imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor);
// Add a drawing to the worksheet
echo date('H:i:s'), " Add a drawing to the worksheet", PHP_EOL;
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
echo date('H:i:s'), " Write to Excel2007 format", PHP_EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', __FILE__), PHP_EOL;
// Echo memory peak usage
echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", PHP_EOL;
// Echo done
echo date('H:i:s'), " Done writing files", PHP_EOL;
public function getExcel()
{
//load our new PHPExcel library
$this->load->library('excel');
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle('REKAPITULASI KEGIATAN PP P2TL');
//set cell A1 content with some text
$this->excel->getActiveSheet()->setCellValue('B1', 'PT PLN (PERSERO) DISTRIBUSI');
$this->excel->getActiveSheet()->setCellValue('B2', 'JAWA BARAT DAN BANTEN');
$this->excel->getActiveSheet()->setCellValue('B3', 'AREA BEKASI - RAYON BEKASI KOTA');
$this->excel->getActiveSheet()->setCellValue('A4', 'REKAPITULASI KEGIATAN PP P2TL PT .YASA EKPANSIA SEJAHTERA');
$this->excel->getActiveSheet()->setCellValue('A5', 'BULAN JUNI TAHUN 2015');
$this->excel->getActiveSheet()->setCellValue('A8', 'NO. URUT');
// Ukuran Kolom
$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(4);
// Ukuran Baris
$this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
//change the font size
$this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(8);
$this->excel->getActiveSheet()->getStyle('B2')->getFont()->setSize(8);
$this->excel->getActiveSheet()->getStyle('B3')->getFont()->setSize(8);
$this->excel->getActiveSheet()->getStyle('A4')->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('A5')->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('A8')->getFont()->setSize(9);
// make the font become bold
$this->excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
//merge cell A1 until D1
$this->excel->getActiveSheet()->mergeCells('B1:E1');
$this->excel->getActiveSheet()->mergeCells('B2:E2');
$this->excel->getActiveSheet()->mergeCells('B3:E3');
$this->excel->getActiveSheet()->mergeCells('A4:P4');
$this->excel->getActiveSheet()->mergeCells('A5:P5');
// Merge Row
$this->excel->getActiveSheet()->mergeCells('A8:A11');
// Inserting Image
$gdImage = imagecreatefromjpeg(base_url() . 'assets/logopln.jpg');
// Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n";
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setWidth(25);
$objDrawing->setHeight(60);
$objDrawing->setCoordinates('A1');
$objDrawing->setWorksheet($this->excel->getActiveSheet());
//set aligment to center for that merged cell (A1 to D1)
$this->excel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$filename = 'just_some_random_name.xls';
//save our workbook as this file name
header('Content-Type: application/vnd.ms-excel');
//mime type
header('Content-Disposition: attachment;filename="' . $filename . '"');
//tell browser what's the file name
header('Cache-Control: max-age=0');
//no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
}
include 'PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Reader/Excel2007.php';
//copiamos el excel de plantilla a su salido final
$fileName = "./matriculas/Matricula" . time() . ".xlsx";
copy("./matriculas/template.xlsx", $fileName);
//tomamos el archivo original y lo copiamos como una plantilla
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($fileName);
//agregamos el logo del colegio
$gdImage = imagecreatefromjpeg('./matriculas/LogoColegio.jpg');
// Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n";
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('LogoColegio');
$objDrawing->setDescription('LogoColegio');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(180);
$objDrawing->setCoordinates("D1");
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Set properties
$objPHPExcel->getProperties()->setCreator("Isaac Reyes");
$objPHPExcel->getProperties()->setLastModifiedBy("Isaac Reyes");
$objPHPExcel->getProperties()->setTitle("Archivo de Matricula");
$objPHPExcel->getProperties()->setSubject("Archivo de Matricula");
$objPHPExcel->getProperties()->setDescription("Archivo de Matricula");
$objPHPExcel->setActiveSheetIndex(0);
// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
protected function writeDatamatrix(Deposit $deposit, $path = null)
{
if (!$path) {
$path = dirname($this->xlsFile) . '/' . $deposit->getNumber() . '.png';
}
$key = $this->getKey($deposit);
if (!function_exists('getDataMatrix')) {
require_once __DIR__ . '/Barcode.php';
}
$im = getDataMatrix($key);
$objDrawing = new \PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('DATAMATRIX');
$objDrawing->setDescription('POST DATAMATRIX');
$objDrawing->setImageResource($im);
$objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
$objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
$objDrawing->setWorksheet($this->objExcel->getActiveSheet());
$objDrawing->setCoordinates('BE18');
$objDrawing->setHeight(140);
$objDrawing->setWidth(140);
$objDrawing->setResizeProportional(100);
return $path;
}
/**
* 导出excel表格
*
* @param $datas 二维数据
* @param $name excel表格的名称，不包含.xlsx
* 填充表格的数据
* @example $datas['title'] = array('col1','col2','col3','col4');
* $datas['result'] = array(array('v11','v12','v13','v14')
* array('v21','v22','v23','v24'));
* @return 直接浏览器输出excel表格 注意这个函数前不能有任何形式的输出
*
*/
function arrayToExcel($datas, $name = '', $output = 'php://output')
{
resetTimeMemLimit();
if (empty($name)) {
$name = 'export_' . date("Y_m_d_H_i_s");
}
// 便于处理大的大型excel表格，存储在磁盘缓存中
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator('icc');
$objPHPExcel->getProperties()->setLastModifiedBy('icc');
$objPHPExcel->getProperties()->setTitle($name);
$objPHPExcel->getProperties()->setSubject($name);
$objPHPExcel->getProperties()->setDescription($name);
$objPHPExcel->setActiveSheetIndex(0);
$total = count($datas['title']);
for ($i = 0; $i < $total; $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimension(excelTitle($i))->setAutoSize(true);
$objPHPExcel->getActiveSheet()->SetCellValue(excelTitle($i) . '1', $datas['title'][$i]);
}
$i = 2;
foreach ($datas['result'] as $data) {
$j = 0;
foreach ($data as $cell) {
// 判断是否为图片，如果是图片，那么绘制图片
if (is_array($cell) && isset($cell['type']) && $cell['type'] == 'image') {
$coordinate = excelTitle($j) . $i;
$cellName = isset($cell['name']) ? $cell['name'] : '';
$cellDesc = isset($cell['desc']) ? $cell['desc'] : '';
$cellType = isset($cell['type']) ? $cell['type'] : '';
$cellUrl = isset($cell['url']) ? $cell['url'] : '';
$cellHeight = isset($cell['height']) ? intval($cell['height']) : 0;
if ($cellType == 'image') {
if ($cellHeight == 0) {
$cellHeight = 20;
}
$image = imagecreatefromstring(file_get_contents($cellUrl));
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName($cellName);
$objDrawing->setDescription($cellDesc);
$objDrawing->setImageResource($image);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight($cellHeight);
$objDrawing->setCoordinates($coordinate);
// 填充到某个单元格
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight($cellHeight);
} else {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($coordinate, $cellName, PHPExcel_Cell_DataType::TYPE_STRING);
}
// 添加链接
$objPHPExcel->getActiveSheet()->getCell($coordinate)->getHyperlink()->setUrl($cellUrl);
$objPHPExcel->getActiveSheet()->getCell($coordinate)->getHyperlink()->setTooltip($cellName . ':' . $cellDesc);
} else {
if (is_array($cell)) {
$objPHPExcel->getActiveSheet()->setCellValueExplicit(excelTitle($j) . $i, json_encode($cell), PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->getActiveSheet()->setCellValueExplicit(excelTitle($j) . $i, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
$j++;
}
$i++;
}
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
if ($output === 'php://output') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save($output);
exit;
}
$objWriter->save($output);
return true;
}
function fn_price_list_print_product_data($product, &$worksheet, $row, &$width, $selected_fields, $styles, $options_variants = array())
{
$col = 'A';
foreach ($selected_fields as $field => $active) {
$worksheet->getStyle($col . $row)->applyFromArray($row % 2 == 0 ? $styles['field_simple_odd'] : $styles['field_simple']);
if ($field == 'image') {
$image_data = fn_image_to_display($product['main_pair'], Registry::get('settings.Thumbnails.product_lists_thumbnail_width'), Registry::get('settings.Thumbnails.product_lists_thumbnail_height'));
if (!empty($image_data)) {
$mime_type = fn_get_file_type($image_data['absolute_path']);
$src = $image_data['absolute_path'];
$image_width = $image_data['width'];
$image_height = $image_data['height'];
if ($mime_type == 'image/gif' && function_exists('imagecreatefromgif')) {
$img_res = imagecreatefromgif($src);
} elseif ($mime_type == 'image/jpeg' && function_exists('imagecreatefromjpeg')) {
$img_res = imagecreatefromjpeg($src);
} elseif ($mime_type == 'image/png' && function_exists('imagecreatefrompng')) {
$img_res = imagecreatefrompng($src);
} else {
$img_res = false;
}
if ($img_res) {
if (!isset($width[$col]) || $width[$col] < $image_width) {
$width[$col] = $image_width * IMAGE_WIDTH_PERCENT;
}
$img_descr = $image_data['alt'];
$drawing = new PHPExcel_Worksheet_MemoryDrawing();
$drawing->setName($img_descr);
$drawing->setDescription($img_descr);
$drawing->setImageResource($img_res);
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$drawing->setHeight($image_height);
$drawing->setWorksheet($worksheet);
$worksheet->getRowDimension($row)->setRowHeight($image_height * IMAGE_HEIGHT_PERCENT);
$drawing->setCoordinates($col . $row);
}
}
} else {
if ($field == 'price') {
$product[$field] = fn_format_price($product[$field], CART_PRIMARY_CURRENCY, null, false);
}
if (!isset($width[$col]) || $width[$col] < strlen($product[$field])) {
$width[$col] = strlen($product[$field]);
}
if (!empty($options_variants) && $field == 'product') {
$options = array();
foreach ($options_variants as $option_id => $variant_id) {
$option = $product['product_options'][$option_id]['option_name'] . ': ' . $product['product_options'][$option_id]['variants'][$variant_id]['variant_name'];
$options[] = $option;
if ($width[$col] < strlen($option)) {
$width[$col] = strlen($options);
}
}
$options = implode("\n", $options);
$worksheet->setCellValue($col . $row, $product['product'] . "\n" . $options);
} elseif ($field == 'price') {
$worksheet->getCell($col . $row)->setValueExplicit($product[$field], PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$worksheet->setCellValue($col . $row, $product[$field]);
}
}
$col++;
}
return true;
}
