Tag Archive export

PHPExcel แสดงตัวเลขเป็นวันที่

×Warning! ใช้วิธีในบทความ PHPExcel: จัดรูปแบบ format ข้อมูล จะดีกว่าวิธีนี้

เขียนระบบส่งออกข้อมูล ทดสอบดูโดยใช้ LibreOffice Calc ปกติดี แต่ใน Microsoft Excel 2013 กลับแสดงตัวเลขทั้งหมดเป็นวันที่

งานรีบ (ตลอด) ไม่อยากเขียนเงื่อนไขให้เช็กว่าช่องนี้คอลัมน์นั้นเป็นตัวหนังสือ หรือว่าตัวเลข ก็ตั้งให้ที่ column เป็น text ไปเลยละกัน

<?php

error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);

include 'config.php';

/* PHPExcel_IOFactory - Reader */
include '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

/* Set default style */
$defaultStyle = $objPHPExcel->getDefaultStyle();

$defaultStyle->getFont()
    ->setName('Arial')
    ->setSize(11);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* Set document properties */
$title = 'Subscription_Datas_' . date('Y-m-d_H-i');
$objPHPExcel->getProperties()->setCreator('Pitt Phunsanit')
    ->setCategory('Subscription Datas')
    ->setDescription($title)
    ->setKeywords('Subscription Datas ' . date('Y-m-d'))
    ->setSubject($title)
    ->setTitle($title);

/* create new sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setTitle('Subscription Datas');

$objWorkSheet->setCellValue('A1', 'subscription_id');
$objWorkSheet->setCellValue('B1', 'first name');
$objWorkSheet->setCellValue('C1', 'last name');
$objWorkSheet->setCellValue('D1', 'email');
$objWorkSheet->setCellValue('E1', 'mobile');
$objWorkSheet->setCellValue('F1', 'major');
$objWorkSheet->setCellValue('G1', 'congratulations year');
$objWorkSheet->setCellValue('H1', 'date create');

$sql = "SELECT *
FROM subscriptions
WHERE enable = 1
ORDER BY sort ASC, skill_id ASC;";
$query = $conn->prepare($sql);
$query->execute();
$results = $query->fetchAll(PDO::FETCH_ASSOC);
$colNo = -1;
$rowNo = 1;

foreach ($results[0] as $key => $value) {
    $colNo++;
    $colStrings[$key] = $column = PHPExcel_Cell::stringFromColumnIndex($colNo);
    $objWorkSheet->setCellValue($column . $rowNo, $key);
}
$objPHPExcel->getActiveSheet()->getStyle('A1:' . $column . '1')->getFont()->setBold(true);

foreach ($results as $result) {
    $rowNo++;
    foreach ($result as $key => $value) {
        $objWorkSheet->setCellValueExplicit($colStrings[$key] . $rowNo, $value, PHPExcel_Cell_DataType::TYPE_STRING);
    }
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
$objWriter->save('php://output');

ที่แก้จริงๆ คือ ใช้

$objWorkSheet->setCellValueExplicit($colStrings[$key] . $rowNo, $value, PHPExcel_Cell_DataType::TYPE_STRING);

กรอกข้อมูลพร้อมกำหนดชนิดของข้อมูลใน column จะ formula, inline, null, numeric, string ที่ต้องการ จะใช้ชนิดอื่นก็ใช้ตามคู่มือ Class: PHPExcel_Cell_DataType

โปรแกรมจัดการ mysql ง่ายๆ เร็วๆ

การจัดการ database คู่บุญ PHP อย่าง MySql หรือ mariadb ร้อยทั้งร้อย และทุกโฮสต์เตรียมเอาไว้ให้ใช้คือ phpMyAdmin แต่มันตัวอ้วนใหญ่ขึ้นทุกๆปี ตามลูกเล่นที่มากขึ้น ถ้ารีบเอางานขึ้นหรือเตรียมไว้เป็นช่องทางฉุกเฺฉินขอแนะนำ Adminer เป็นมายเอสคิวแอล management ที่ตัวเล็กกว่าเยอะ ฟรี ที่สำคัญคือมีไฟล์แค่ไฟล์เดียว

การติกตั้งแค่โหลดมากจากเว็บ เปลี่ยนชื่อซะหน่อยเพื่อความปลอดภัย ให้เป็นชื่อแบบที่เรารู้อยู่แค่คนเดียว แล้ว ftp ขึ้นไป เรียกใช้ได้เลย อาจจะไม่ถนัดเท่าตัวเดิมแต่การติดตั้ง script import export data แบ็คอัพแก้ตารางก็ทำได้ดี สะดวกพอใช้

Command Save Query Results to file

ถ้าต้อง query ทุกๆ วัน หรือ ทุกๆ ชั่วโมง แล้วเอาผลลัพธ์มาเก็บไว้ จะดีมั๋ยที่ทำเป็น schedule ไว้แล้วให้ทุกสิ่งทุกอย่างมันดำเนินไปตามที่มันควรจะเป็น โดยที่เราไม่ต้องทำเองทุกวัน

  1. สร้างไฟล์ที่เก็บ query ไว้ก่อนโดยผม save query จากเรื่อง สร้าง Data Dictionary แค่คลิก เป็นไฟล์ชื่อ DataDictionary.sql
  2. เปิด command ขึ้นมาโดยพิมพ์ sqlcmd ตามด้วย
    sqlcmd Utility
    Option ความหมาย ตัวอย่าง
    -S [protocol:]server[instance_name][,port] connection ของ sql server ของผมคือ MAGI\SQLEXPRESS
    -d db_name ชื่อ database
    -E (use trusted connection)
    -i input_file
    -o output_file ไฟล์ผลลัพธ์คือ OutFileName.txt

    สิศิรวมแล้วคือ

    sqlcmd -S MAGI\SQLEXPRESS -d test -E -i DataDictionary.sql -o OutFileName.txt
  3. หลังเอ็นเทอร์ไฟล์ OutFileName.txt จะถูกสร้างขี้นมาอยู่ที่เดียวกับไฟล์ DataDictionary.sql
  4. นำไปดัดแปลงตามความเหมาะสมกับงานที่ทำครับ

อ่านเพิ่มเติม

สร้าง grid ใน laravel แบบ advance

การใช้ Nayjest Grids แบบ advance เพื่อที่จะสามารถค้นหา filter ข้อมูล, export ไฟล์ ออกมาในรูปแบบ .csv และ excel ได้

ติดตั้งโดย

  1. เปิดไฟล์ composer.json เพิ่ม
    ...
            "laravelcollective/html": "^5",
            "maatwebsite/excel": "~2.1.0",
            "nayjest/grids": "^1.3.1"
    ...
    
  2. Command
    composer update
  3. เปิดไฟล์ \config\app.php เพิ่ม
        'providers' => [
    ...
            'Collective&#92;Html&#92;HtmlServiceProvider',
            'Maatwebsite&#92;Excel&#92;ExcelServiceProvider',
            'Nayjest&#92;Grids&#92;ServiceProvider',
    ...
    
        'aliases' => [
    ...
            'Excel' => 'Maatwebsite&#92;Excel&#92;Facades&#92;Excel',
            'Form' => 'Collective&#92;Html&#92;FormFacade',
            'Grids' => 'Nayjest&#92;Grids&#92;Grids',
            'HTML' => 'Collective&#92;Html&#92;HtmlFacade',
            'Input' => 'Illuminate&#92;Support&#92;Facades&#92;Input',
    ...
    ]

สร้าง grid โดย

  1. <?php
    
    namespace App\Http\Controllers;
    
    use App\user;
    use HTML;
    use Nayjest\Grids\Components\Base\RenderableRegistry;
    use Nayjest\Grids\Components\ColumnHeadersRow;
    use Nayjest\Grids\Components\ColumnsHider;
    use Nayjest\Grids\Components\CsvExport;
    use Nayjest\Grids\Components\ExcelExport;
    use Nayjest\Grids\Components\FiltersRow;
    use Nayjest\Grids\Components\Filters\DateRangePicker;
    use Nayjest\Grids\Components\HtmlTag;
    use Nayjest\Grids\Components\Laravel5\Pager;
    use Nayjest\Grids\Components\OneCellRow;
    use Nayjest\Grids\Components\RecordsPerPage;
    use Nayjest\Grids\Components\RenderFunc;
    use Nayjest\Grids\Components\ShowingRecords;
    use Nayjest\Grids\Components\TFoot;
    use Nayjest\Grids\Components\THead;
    use Nayjest\Grids\EloquentDataProvider;
    use Nayjest\Grids\FieldConfig;
    use Nayjest\Grids\FilterConfig;
    use Nayjest\Grids\Grid;
    use Nayjest\Grids\GridConfig;
    
    class UsersController extends Controller
    {
    
        public function Index()
        {
    
            $query = (new User)
                ->query();
    
            $grid = new Grid(
                (new GridConfig)
                    ->setDataProvider(
                        new EloquentDataProvider($query)
                    )
                    ->setName('UserIndex') /* grid unque id */
                    ->setPageSize(20)
                    ->setColumns([
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('ID')
                            ->setName('id') /* field */
                            ->setSortable(true)
                            ->setSorting(Grid::SORT_DESC)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Name')
                            ->setName('name')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Email')
                            ->setName('email')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Update Information')
                            ->setName('count_update')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->setLabel('Created')
                            ->setName('created_at')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->setLabel('Last Update')
                            ->setName('updated_at')
                            ->setSortable(true),
                    ])
                    ->setComponents([
                        (new THead)
                            ->setComponents([
                                (new ColumnHeadersRow),
                                (new FiltersRow)
                                    ->addComponents([
                                        (new RenderFunc(function () {
                                            return HTML::style('js/daterangepicker/daterangepicker-bs3.css')
                                            . HTML::script('js/moment/moment-with-locales.js')
                                            . HTML::script('js/daterangepicker/daterangepicker.js')
                                                . "<style>
                                                    .daterangepicker td.available.active,
                                                    .daterangepicker li.active,
                                                    .daterangepicker li:hover {
                                                        color:black !important;
                                                        font-weight: bold;
                                                    }
                                               </style>";
                                        }))
                                            ->setRenderSection('filters_row_column_created_at'),
                                        (new DateRangePicker)
                                            ->setName('created_at')
                                            ->setRenderSection('filters_row_column_created_at')
                                            ->setDefaultValue(['1990-01-01', date('Y-m-d H:i:s')]),
                                        (new DateRangePicker)
                                            ->setName('created_at')
                                            ->setRenderSection('filters_row_column_updated_at')
                                            ->setDefaultValue(['1990-01-01', date('Y-m-d H:i:s')]),
                                    ])
                                ,
                                (new OneCellRow)
                                    ->setRenderSection(RenderableRegistry::SECTION_END)
                                    ->setComponents([
                                        new RecordsPerPage,
                                        new ColumnsHider,
                                        (new CsvExport)
                                            ->setFileName('usersExport_' . date('Y-m-d'))
                                        ,
                                        (new ExcelExport())
                                            ->setFileName('usersExport_' . date('Y-m-d'))
                                        ,
                                        (new HtmlTag)
                                            ->setContent('<span class="glyphicon glyphicon-refresh"></span> Filter')
                                            ->setTagName('button')
                                            ->setRenderSection(RenderableRegistry::SECTION_END)
                                            ->setAttributes([
                                                'class' => 'btn btn-success btn-sm',
                                            ]),
                                    ]),
                            ])
                        ,
                        (new TFoot)
                            ->setComponents([
                                (new OneCellRow)
                                    ->setComponents([
                                        new Pager,
                                        (new HtmlTag)
                                            ->setAttributes(['class' => 'pull-right'])
                                            ->addComponent(new ShowingRecords)
                                        ,
                                    ]),
                            ])
                        ,
                    ])
            );
            $grid = $grid->render();
    
            return view('users.index', [
                'grid' => $grid,
            ]);
        }
    
    }
    
  2. สร้าง view \resources\views\users\index.blade.php
    @extends('layouts.app')
    
    @section('title', 'Users')
    
    @section('main-content') <a href="{{ url('/excels/form') }}">
    <button class="btn btn-success btn-sm"><span class="glyphicon glyphicon-upload"></span> Import Datas</button>
    </a> @if(!empty($text))
    <div class="container">{!! $text !!}</div>
    @endif
    <?=$grid;?>
    @stop
  3. สร้าง route โดยไปที่ \app\Http\routes.php
    ...
    Route::get('/users/index', 'UsersController@index');
    ...

ทดลองโดยเรียก /users/index ในเครื่องผมคือ http://localhost/laravel52/public/users/index grid แสดงข้อมูลออกมาแล้วแต่ จะส่งออกเป็นไฟล์ csv หรือ excel ยังไงละ ใจเย็นๆครับ อ่าน สร้าง grid ใน laravel แบบ advance ครับ

สร้างไฟล์ csv จาก phpexcel

phpexcel สร้างไฟล์ excel ได้สะดวกมาก แต่ไม่ใช่ทุกระบบจะสามารถ import ข้อมูลจากไฟล์แบบเอ็กเซลได้ การส่งออกไฟล์แบบตัวอักษรล้วนๆ และใช้เครื่องหมายคั่นแบบไฟล์ csv จึงยังจำเป็นอยู่

ที่สำคัญคือสามารถแก้หรือเพิ่มเงื่อนไขจาก code ส่งออกข้อมูลเป็น excel โดย phpexcel ที่มีอยู่เดิม โดยดัดแปลงเล็กน้อยในส่วน header และการ write data

<?php

/* PHPExcel_IOFactory - Reader */
include 'PHPOffice/PHPExcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

/* Set default style */
$defaultStyle = $objPHPExcel->getDefaultStyle();

$defaultStyle->getFont()
    ->setName('Arial')
    ->setSize(11);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* Set document properties */
$title = 'Exports_Datas_' . date('Y-m-d_H:i');
$objPHPExcel->getProperties()->setCreator('Pitt Phunsanit')
    ->setCategory('Exports Datas')
    ->setDescription($title)
    ->setKeywords('Exports Datas ' . date('Y-m-d'))
    ->setSubject($title)
    ->setTitle($title);

/* rename sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setTitle('Exports Datas');

for ($rowNo = 1; $rowNo < 10; $rowNo++) {
    for ($colNo = 0; $colNo < 5; $colNo++) {

        $insert = rand(0, 1);
        if($insert == 1) {

            $colString = PHPExcel_Cell::stringFromColumnIndex($colNo);

            $coordinate = $colString . $rowNo;

            $objWorkSheet->setCellValue($coordinate, 'ส่งออกข้อมูล -> '.$coordinate);
        }
    }
}

header('Cache-Control: max-age=0');
header('Content-Encoding: UTF-8');
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment;filename="'.$title.'.csv"');
echo "\xEF\xBB\xBF"; /* UTF-8 BOM */

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

/* set Delimiter, (defaults ,) */
//$objWriter->setDelimiter('|');
//$objWriter->setEnclosure('');
/* Set line ending (defaults PHP_EOL) */
//$objWriter->setLineEnding('0x0d0a');

$objWriter->save('php://output');

ถ้าสังเกตุจะมี $insert = rand(0, 1); และ if($insert == 1) { ทำให้บาง cell ไม่มีข้อมูล เพื่อที่จะแสดงให้เห็นว่าการใช้ phpexcel จะดีกว่าการใช้ echo ส่งออกข้อมูลแบบที่ง่ายที่สุด (CSV) ในกรณีที่บางคอลัมข้อมูลไม่มี เราไม่จำเป็นต้อง echo ค่าว่าง หรือมานับ , ว่าครบมั๋ย phpexcel จะจัดการให้เอง

ถ้าเปลี่ยนรูปแบบอื่นเช่น

  • ต้องการใช้เครื่องหมายอื่นเช่น pipe | ก็สามารถกำหนดโดยใช่ setDelimiter
  • ต้องการ wrap ข้อมูลก็ใช้ setEnclosure จะปิดหัว ปิดท้ายข้อมูลแต่ละ cell ให้
  • ต้องการใช้เครื่องมายอื่นในการแบ่งข้อมูลออกเป็นชุดๆ ก็ setLineEnding

เขียนครั้งเดียวส่งออกข้อมูลได้ 2 รูปแบบ ส่วนคำสั่งที่ไม่ได้ใช้ในรูปแบบ csv เช่น setTitle ก็ไม่ทำให้มี error ดีจริงๆ phpexcel

ส่งออกข้อมูลแบบที่ง่ายที่สุด (CSV)

วิธีที่ส่งออกข้อมูลที่ง่ายที่สุด และอิมพอร์ตเข้าโปรแกรมต่างๆได้มากที่สุดคือ comma-separated values (CSV) แปลง่ายๆว่า ไฟล์ตารางที่คั่นด้วยคอมม่า แปลบ้านๆไปอีกคือ text file ที่ข้อมูลถูกแบ่งโดย , หรือเครื่องหมายอื่นเช่น pipe | โดยข้อมูลต่ละ record จะแบ่งโดยการขึ้นบรรทัดใหม่ (ใน php คือ \n)

เขียนง่ายๆ แค่มีการเพิ่ม header เข้าไปว่ามันเป็น csv นะและ echo ธรรมดาๆ

<?php

$filename = 'Exports_Datas_' . date('Y-m-d_H:i');

header('Cache-Control: max-age=0');
header('Content-Encoding: UTF-8');
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment;filename="'.$filename.'.csv"');
echo "\xEF\xBB\xBF"; /* UTF-8 BOM */

/* random add datas */
for($a = 0; $a < 10; $a++) {
	$datas = [];
	for($b = 0; $b < 5; $b++) {
		$datas[$b] = 'ส่งออกข้อมูล '.rand(1, 100);
	}
	echo implode(',', $datas)."\n";
}

หลังจากเรียกไฟล์ php ก็จะเห็นว่ามีการให้ download ขึ้นมาให้ save ลองคลิกเปิดดูถ้าเครื่องมี excel อยู่ก็จะเปิดอ่านข้อมูลได้ทันที หรือจะใช้ text editor เปิดขึ้นมาดูก็ได้ ทดลองเปลี่ยนเป็นให้ดึงข้อมูลจาก database ดูครับ

สร้าง excel จาก PHP

วิธีส่งออกข้อมูลที่ ยูเซอร์สดวกที่จะเอาไปใช้ต่อมากที่สุดคือ ส่งออกมาเป็นเอกซ์เซล เพราะามารถเอาไปใช้ได้ทันที คุ้นเคยอยู่แล้ว ไม่รู้สึกว่ายุ่งยากในการใช้งาน

ภาษา PHP สามารถส่งออกข้อมูลเป็น excel ได้ง่ายตามขั้นตอนดังนี้

  1. โหลดตัว PHPExcel มาจาก PHPExcel
  2. แตกไฟล์ออกมา
  3. เขียน code ตามตัวอย่าง
<?php

/* PHPExcel_IOFactory - Reader */
include 'PHPOffice/PHPExcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

/* Set default style */
$defaultStyle = $objPHPExcel->getDefaultStyle();

$defaultStyle->getFont()
    ->setName('Arial')
    ->setSize(11);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* Set document properties */
$title = 'Exports_Datas_' . date('Y-m-d_H:i');
$objPHPExcel->getProperties()->setCreator('Pitt Phunsanit')
    ->setCategory('Exports Datas')
    ->setDescription($title)
    ->setKeywords('Exports Datas ' . date('Y-m-d'))
    ->setSubject($title)
    ->setTitle($title);

/* create new sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setTitle('Exports Datas');

for ($rowNo = 1; $rowNo < 10; $rowNo++) {
    for ($colNo = 0; $colNo < 5; $colNo++) {

        $colString = PHPExcel_Cell::stringFromColumnIndex($colNo);

        $coordinate = $colString . $rowNo;

        $objWorkSheet->setCellValue($coordinate, 'Add Data To '.$coordinate);
    }
}

$objWriter =PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
$objWriter->save('php://output');

อธิบาย

  1. บรรทัดที่ 4 เรียกใช้ตัว libarry PHPExcel
  2. บรรทัดที่ 8 – 16 จะกำหนดค่า default ของไฟล์ excel จะไม่มีก็ได้
  3. บรรทัดที่ 18 – 25 ใส่ข้อมูลพื้นฐานให้ไฟล์ excel จะไม่มีก็ได้ แต่ถ้ามีจะมีประโยชน์ในการค้นหาไฟล์นี้ในภายหลัง
  4. บรรทัดที่ 28, 29 เป็นการเปลี่ยนชื่อ sheet ให้สื่อความหมาย ไม่มีก็ได้เช่นกัน
  5. บรรทัดที่ 31 – 40 จะเป็นส่วนที่ใส่ข้อมูลลงใน sheet ในการใช้งานจริงๆ จะเป็น loop ที่ดึงข้อมูลจาก database มากรอก
  6. บรรทัดที่ 42 – 49 เป็นส่วนที่เขียนออกมาเป็นไฟล์ และให้ download ไฟล์ออกมา

Note: อย่า echo หรือแสดงข้อมูลไม่งั้นจะมี error ประมาณ Excel cannot open the file ‘xxx.xlsx’ because the file format of file extension is not valid. Verify that the file has been corrupted and that the file extension matches the format of the file.