Tag Archive เอ็กพอร์ต

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 ครับ

สร้าง excel เป็นชุดแบบหลายหน้าชีท

บางครั้งก็ต้องส่งออกข้อมูลเป็นชุดจำนวนมากหลายชีท ข้อมูลหนึ่งก็ใส่ไว้ในอีกชีทหนึ่ง ก็ใช้การแก้เล็กน้อยจากตัวอย่าง สร้าง excel จาก PHP

<?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);

/* multiple_sheets */
for ($sheetNo = 0; $sheetNo < 10; $sheetNo++) {
    $objWorkSheet = $objPHPExcel->createSheet($sheetNo);
    $objWorkSheet->setTitle('Sheet Nane '.$sheetNo);
    $objWorkSheet->setCellValue('A1', 'Add data for sheet no. '.$sheetNo);
}

/* set active sheet */
$objPHPExcel->setActiveSheetIndex(5);
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setCellValue('A4', 'Add data for sheet no. 5 after Sheet Nane '.$sheetNo);

$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. บรรทัดที่ 28 – 32 จะเป็นการสร้าง sheet ขึ้นมาใหม่ และอ้างถึงชีทนี้โดยใช้ $objWorkSheet ในการใส่ข้อมูลลงไป
  2. บรรทัดที่ 35 – 37 เราสามารถกลับมาแก้ไข sheet ที่สร้างไปแล้วโดยใช้คำสั่ง setActiveSheetIndex และ getActiveSheet จากนั้นอ้างถึงชีทนี้โดยใช้ $objWorkSheet
  3. สังเกตุว่า setActiveSheetIndex ใช้การนับลำดับ sheet เริ่มจาก 0 ไม่ใช่ 1
  4. มีชีทชือ Worksheet เกินอยู่ มันเป็น default ของตัว PHPExcel เอง จริงๆเราควรจะใช้ คำสั่ง
    $objWorkSheet = $objPHPExcel->getActiveSheet();
    $objWorkSheet->setTitle('Exports Datas');
    

    กับแผ่นนี้ แล้วใช้เหมือนกับชีทแผ่นอื่นๆ ก่อนที่จะ loop สร้าง sheet ขึ้นมาใหม่

สร้าง 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.