* Limba de dezvoltare: PHP 5.3+
* Subiecte acoperite: MySQL, PHP, PHPExcel, DOP
* Dificultate Nivel: Mediu
* Timp de încheiere: 1 oră
* Fișiere atașate
Un fișier CSV (Valoare separată prin virgulă sau "Valori separate prin virgulă") este de obicei suficient pentru a exporta datele MySQL într-o foaie de calcul Excel. Cu toate acestea, fișierele CSV conțin numai date. Într-un tabel Excel real, există formate, formule și uneori chiar elemente grafice. Ie Aici diferența este egală cu diferența dintre transferul obișnuit de date și raportul profesional.
Acest ghid vă spune cum să utilizați PHP-componente (care sunt distribuite cu open source) pentru a crea foi de calcul Excel reale de indicii MySQL SELECT. Interesat? Atunci să începem!
Interacțiunea de bază între tabelele de date și foile de calcul
Valoarea SQL SELECT returnează un set de rezultate sau, mai exact, o selecție de coloane cu nume în celulele care conțin date. Rezultatul PHP / MySQL, fiecare celulă poate fi (și de multe ori așa se întâmplă) este reprezentat ca o matrice asociativă unde matrice cheie - numele coloanei.
O foaie de calcul Excel este un număr de pagini în care puteți vedea rânduri și coloane numerotate ordonate în ordine alfabetică.
Metoda constă în faptul că o singură valoare a fost stabilită SQL o pagină de operare, care poate fi realizată prin potrivirea numelui coloanei într-un tablou asociativ, care a fost returnat comandă de recuperare DOP, iar coloanele de litere în tabel.
Pasul 1: aflați cum să denumiți corect numele de coloane în ordine alfabetică
Punctul cheie al acestei tehnici este algoritmul de numire a coloanelor, care vă permite să denumiți corect coloanele în ordine alfabetică. După cum am menționat mai devreme, în foile de calcul Excel, coloanele sunt identificate prin litere, iar liniile sunt definite prin numere. Dacă interogarea conține mai puțin de 26 de coloane, puteți opri pur și simplu litera Z. Cu toate acestea, multe interogări conțin mult mai multe coloane.
Schema numelor coloanelor din Excel
Când numele coloanelor ajung la litera Z, o altă literă se adaugă la nume, adică A ... Z, AA, AB ... AZ. După AZ, BA va merge (după cum puteți vedea mai jos).
Algoritm pentru denumirea coloanelor
Următorul cod PHP ne arată cum să denumim corect coloanele de la A la ZZ.
Acest algoritm nu va merge mai departe decât ZZ. Algoritmul este executat (într-o formă puțin diferită) folosind un fișier suplimentar în funcția MySqlExcelBuilder :: mapColumns definită de utilizator.
Pasul 2: Formatați și testați valorile SQL
Diagrama de mai jos ne arată o interacțiune simplificată între client și comandă. SQL a crea valori pentru schema de mai jos sunt incluse în arhivat în zip format xls_sample.sql fișier, care este atașat la manualul.
"Valoare rapidă și murdară" SELECT
De obicei, aici puteți să extrageți rapid datele din valoarea SQL în tabele:
Principalul avantaj al acestei metode este de a economisi timp. Deși rezultatele sunt rareori acceptabile.
PHPMyAdmin este un instrument de administrare a bazelor de date MySQL, care este adesea furnizat împreună cu planul de găzduire. Pentru a vă structura și a testa paginile cu foi de calcul, puteți utiliza diferitele instrumente SQL care vin cu PHPMyAdmin.
Următoarea imagine de ecran ne arată rezultatul interogării de mai sus:
Dacă vă uitați la coloanele mai aproape, puteți vedea că numele lor sunt destul de semnificative și convenabile pentru programatori, dar acest lucru este puțin probabil să fie atractiv pentru utilizatorii de afaceri.
Numele de coloane sunt scrise cu o literă mică și în loc de spații vedem sublinierea de jos.
Avem un rezultat mai atractiv din valoarea SELECT
Trebuie să formatăm valoarea SELECT astfel încât să arate gravă și atractivă. Prin urmare, utilizând instrumentul PHPMyAdmin, editați valoarea SQL astfel încât numele coloanelor să fie cuvinte reale și să fie afișate numai coloanele de care aveți nevoie. Valoarea SQL reformatată arată cam așa:
Aceasta ca rezultat:
Prototipul de mai sus ne arată cum poate arăta o foaie de calcul.
Pasul 3: Afișați rezultatele MySQL pe o pagină de calcul tabelar
clasa MySqlExcelBuilder include funcționalitatea necesară pentru a adăuga valoare la pagina SQL foaia de calcul Excel folosind DOP și PHPExcel. Clasa completă se află într-un fișier zip însoțitor.
Această clasă vă permite să plasați un număr aleatoriu de rezultate SQL pe pagini cu un nume dat. Următorul fragment de cod afișează date importante pentru noi.
* $ pdo este un obiect de date din PHP folosit pentru a crea o interogare bazei de date.
* $ phpExcel este un obiect PHPExcel folosit pentru a crea și gestiona o foaie de calcul.
* $ sql_pages - păstrează valoarea SQL și informații despre numele și formatarea paginii.
* Constructorul (nu este prezentat) inițializează datele de la PDO și PHPExcel.
Pregătirea fiecărei pagini
Următoarea imagine a unei foi de calcul este un prototip creat în Excel pentru a ne arăta cum ar trebui să pară o foaie de calcul.
Funcția add_page este utilizată pentru a adăuga valoarea SQL în pagini numite:
sql_pages păstrează informațiile pe care dorim să le folosim pentru a pune sql pe pagină.
Exemplu de utilizare a unei funcții personalizate
Acest cod este un exemplu de utilizare a funcției personalizate add_page:
Următoarea imagine ne arată modul în care funcția add_page personalizată ar trebui definită pe foaia de calcul.
Pasul 4: Dezvoltați o foaie de calcul
Dacă înțelegeți cum să gestionați o foaie de calcul Excel cu un mouse și tastatură, probabil că veți conduce rapid PHPExcel. PHPExcel se bazează pe principiul gestionării unui model de foaie de calcul când se utilizează comenzi similare cu comenzile pe care le atribuiți Excel. În documentația pentru dezvoltatori pe PHPExcel totul este pictat în detaliu.
Funcția custom getExcel ()
Funcția custom getExcel () utilizează PHPExcel pentru a crea tabele de lucru separate din valorile SQL pe care le-ați definit în pasul 3. După crearea tabelelor de lucru, obiectul PHPExcel revine la tabelul solicitat. Mai jos puteți vedea o descriere a celor 4 departamente principale ale funcției utilizator getExcel.
A. Repetarea pe pagini
Buclele principale ale acestei funcții definite de utilizator se repetă pe paginile care au fost adăugate anterior prin add_page. În fiecare repetare, se creează o pagină ulterioară în obiectul phpExcel și apoi se adaugă datele. Funcția createSheet personalizată din PHPExcel este utilizată pentru a crea foi de lucru noi pentru fiecare pagină adăugată anterior.
Imaginea de mai jos ne arată modul în care codul interacționează cu tabelul. Coloana_map este discutată în continuare în manual.
B. Logica "Începutul paginii"
La începutul fiecărei pagini există un format special. Mai întâi, pentru fiecare pagină din baza de date, se extrage un șir, care efectuează sarcinile necesare pentru formatarea din partea de sus a paginii. Aceasta implică funcția mapColumns definită de utilizator, despre care am discutat la pasul # 1. PHPExcel, ca Excel, utilizează o pereche de litere pentru a specifica o anumită celulă. În MySqlExcelBuilder, aceasta este tratată ca o cheie celulară (cellKey). Cheia celulară este creată prin conectarea antetului coloanei cu numărul de linii.
Unele aspecte suplimentare care ar trebui luate în considerare în fragmentul de cod de mai sus:
* setCellValue - face valoarea reală în câmp. Rețineți că celula face parte din foaia de lucru. O celulă particulară este definită de variabila cellKey.
* getStyle - acest parametru returnează date pentru atributul de stil al unei anumite celule, astfel încât această valoare poate fi controlată.
* getColumnDimension este metoda obiectului din foaia de lucru. Lățimea coloanei este legată de variabila col.
B. Completarea datelor
Datorită lucrărilor pregătitoare și indicării coloanelor, procesul va completa, de fapt, celula corespunzătoare cu datele corespunzătoare. Ne uităm la foaia de lucru pentru o coloană de date specifică, creăm o cheie de celule și apoi adăugăm valoarea la celulă.
D. Adăugăm formule
Ultima parte a getExcel () ne arată cum să adăugăm formule în foaia de lucru PHPExcel. În acest caz, totul se referă la coloană. PHPExcel face formule în celule așa cum le-ați pune într-o foaie de lucru în Excel. Valoarea celulelor începe cu semnul egal (=), iar apoi urmărește formula. În acest caz, avem suma mai multor celule. Privim mai departe:
Iată codul:
Pasul 5: Atingeți ultimele sfaturi
După ce ați primit deja o foaie de calcul Excel completată cu baza de date MySQL și getExcel, este timpul să adăugați atingerile finale. În exemplul nostru, setăm titlul fiecărei foi de lucru.
Pasul 6: Salvați fișierul
PHPExcel folosește producția de obiecte pentru a crea un element care să înscrie datele în tabelele dvs. în formatul corect. În acest caz, am folosit Excel5, deoarece chiar și programele mai vechi îl pot citi. Acest raport devine automat disponibil pentru un public mai larg.
Următoarea imagine ne arată imaginea finală a proiectului nostru. O tabelă cu două pagini umplută cu date din baze de date și formatată cu anteturi personalizate:
Odată ce datele din baza de date se află în obiectul PHPExcel, puteți utiliza alte funcții ale clasei PHPExcel pentru a reda formate suplimentare, a adăuga mai multe formule, a le salva în formate diferite și multe altele.
De exemplu, clasa MySqlExcelBuilder poate fi extinsă pentru a utiliza funcțiile PHPExcel pentru a popula un șablon de foaie de calcul existent cu date din baza de date MySQL. Din moment ce PDO este interfața bazei de date, DSN-ul din constructorul MySqlExcelBuilder poate fi schimbat fără probleme cu alte baze de date.
Vă mulțumim că ați fost cu noi!