Laravel simple installation and use of Excel

Source: Internet
Author: User

  Background operation of the database often between the data and Excel import and export operations, from my experience to see the recent Guide list is not hundreds, the past shell script has not satisfied the current needs, so just met the Laravel Excel this powerful tool, However, the information on the Internet may be too many causes before the installation has some problems, so make a special record to avoid later detour.

Before I import the Excel data into the database through the shell script processing, first copy the contents of Excel into a TXT document, note that all Excel units must fill the content (null value is filled with ' empty ' or ' none '), then all the data can not be wrapped. and remove the TXT file such as ', ' and spaces these symbols, and finally use the ReadLine method, with the ' ' delimiter to get the data for each column:

CatTemp.txt|awk 'nr>1'| whileRead Line Domydate=`Echo$line |awk-F'    ' '{printf ("%s", $)}'' Myin=`Echo$line |awk-F'    ' '{printf ("%s", $ $)}'' Myout=`Echo$line |awk-F'    ' '{printf ("%s", $ $)}'' Remark=`Echo$line |awk-F'    ' '{printf ("%s", $4)}'"Mysql-hxxx-uxxx-pxxx--default-character-set=utf8 '-n-e"
Insert into T_record (Mydate,myin,myout,remark) VALUES (' $myDate ', ' $myIn ', ' $myOut ', ' $remark ');
" Done

 This method is more complex, the requirements for TXT is relatively high, once the data volume is larger or there is a line of the situation is very easy to make mistakes, just the project they are now developing to use the Laravel framework, so simply all with its own Excel to complete the process of optimizing the Guide table.

Installation:

Laravel Excel is a tool that can perform both Excel data import and Excel export, and its official documentation can refer to Http://www.maatwebsite.nl/laravel-excel/docs. Before installing this plug-in to ensure that your computer or server installed composer, not installed can refer to HTTP://WWW.JIANSHU.COM/P/256547B495C2, When you enter composer at the command line, there are several large composer that indicate that the installation was successful.

First open the project to add plug-in Composer.json file, because I was using Lavarel4 so in the Require section add ' maatwebsite/excel ': ' ~1.3 ', if it is Laravel5 add ' Maatwebsite/excel ': ' ~2.1.0 ', then execute the composer Update command to download the desired folder to your project. It is important to note that if you only want to update Excel, this plugin can execute the composer Update Maatwebsite/excel command directly, because executing composer update will update all the content that needs to be updated again, the network is poor, It's a nightmare to take a long time, so it's recommended that you update whichever thing you want, and don't update it all at once.

 After the success of the update, in your vendor directory will appear under the Maatwebsite and Phpoffice folders, if not continue to go back to update it (try the domestic mirror). Then introduce two lines of description in the config/app.php:

Add ' Maatwebsite\excel\excelserviceprovider ' to the providers array

Add ' Excel ' to ' maatwebsite\excel\facades\excel ' in aliases

  Finally execute PHP artisan config:publish maatwebsite/excel command at the root of the project, if it is 5 execute PHP artisan vendor:publish to complete the entire installation process, in fact, the whole process is not complicated, As soon as you see those two folders appear, there is a ten.

Use:

First, add use Maatwebsite\excel\facades\excel to the specific controller's PHP file;

You can then implement the export import function by creating an Excel object or by calling the methods in the Excel class directly

//Create object import data (class Word directly using the Excel::load () method)$excel= App::make (' Excel ');//Excel Class$excel->load ("/temp.xlsx",function($reader)//Reader reads Excel content{    $reader=$reader->getsheet (0);//Excel First sheet sheet    $results=$reader-ToArray (); unset($results[0]);//Remove the table header    if($results)    {        foreach($results  as $key=$value)        {            $data= []; $data[' mydate '] =$value[0] = =NULL? ‘‘:Trim($value[0]); $data[' myin '] =$value[1] = =NULL? ‘‘ :Trim($value[1]); $data[' myout '] =$value[2] = =NULL? ‘‘ :Trim($value[2]); $data[' remark '] =$value[3] = =NULL? ‘‘ :Trim($value[3]); $res= db::table (' T_record ')->insertgetid ($data); }    }});
//Use the class method to export Excel
//create a file and export$test= ' first position '; Excel:: Create ("new.xlsx",function($excel) Use($test){ //Create sheet $excel->sheet (' Sheet1 ',function($sheet) Use($test) { //fill in the contents of each cell $sheet->cell (' A1 ',function($cell) Use($test) { $cell->setvalue ($test); }); });})->export (' xls ');//read a file and exportExcel::load ("New.xlsx",function($excel) { //Read Sheet $excel->sheet (' Sheet1 ',function($sheet) { //Modify the contents of each cell $sheet->cell (' A1 ',function($cell) { $cell->setvalue (' 1 '); }); });})->export (' xls ');

In addition to this, for example, set the title for Excel, font size, cell size, background color, width, height, horizontal, vertical center, margin, padding, and so on, so that you feel that you are not just exporting an Excel but writing a html+css page, The specific requirements feature can refer to its API documentation. Well, the future of Excel and database interaction without worry!!

Laravel simple installation and use of Excel

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.