How to use phpexcel to read data and insert into database

Question

I have a php application where I want to read data from excel  Insert into database and then generate pdf reports for specific users  I searched a lot but nothing specific given about both things

User · Answer

Inci framework  you can do download like so   function clubDownload  clubname          this- gt load- gt library  excel          object   new PHPExcel         object- gt setActiveSheetIndex 0        this- gt load- gt model  Members student model         query    this- gt db- gt query  SELECT   FROM student WHERE  clubname      order by id desc         resultdatanew  query- gt result array         page     this- gt uri- gt segment 3      this- gt uri- gt segment 3    1        object- gt getActiveSheet  - gt getStyle  A1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000          object- gt getActiveSheet  - gt getStyle  B1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000           object- gt getActiveSheet  - gt getStyle  C1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000          object- gt getActiveSheet  - gt getStyle  D1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000           object- gt getActiveSheet  - gt getStyle  E1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000          object- gt getActiveSheet  - gt getStyle  F1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000         object- gt getActiveSheet  - gt getStyle  G1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000          object- gt getActiveSheet  - gt getStyle  H1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000         object- gt getActiveSheet  - gt getStyle  I1  - gt getFont  - gt setBold true                              - gt setName  Verdana                               - gt setSize 10                              - gt getColor  - gt setRGB  330000         headerStyle   array                   fill    gt  array                           type    gt  PHPExcel Style Fill  FILL SOLID                           color    gt  array  rgb   gt  CCE5FF                                        font    gt  array                           bold    gt  true                                     object- gt getActiveSheet  - gt getStyle  A1    I1  - gt applyFromArray  headerStyle        table columns   array  id    studentid    passport    lastname    firstname   university   commencing   clubname  added date         column   0      foreach  table columns as  field             object- gt getActiveSheet  - gt setCellValueByColumnAndRow  column  1   field        column               excel row   2          foreach  resultdatanew as  row                           id  row  id                     studentid  row  studentid                     passport  row  passport                     lastname  row  last name                     firstname  row  first name                     passport  row  university                     commencing  row  commencing                     email id  row  email id                     added date  row  added date                       object- gt getActiveSheet  - gt setCellValueByColumnAndRow 0   excel row  id                     object- gt getActiveSheet  - gt setCellValueByColumnAndRow 1   excel row   studentid                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 2   excel row   passport                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 3   excel row   lastname                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 4   excel row   firstname                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 5   excel row   passport                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 6   excel row    commencing                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 7   excel row   email id                    object- gt getActiveSheet  - gt setCellValueByColumnAndRow 8   excel row   added date                      excel row        object writer   PHPExcel IOFactory  createWriter  object   Excel5    header  Content-Type  application vnd ms-excel    header  Content-Disposition  attachment filename  club    clubname  -  date  Y-m-d       xls     object writer- gt save  php   output

User · Answer

In order to read data from microsoft excel 2007 by codeigniter just create a helper function excel helper php and add the following in         require once APPPATH  libraries phpexcel PHPExcel php         require once APPPATH  libraries phpexcel PHPExcel IOFactory php         in controller add the following code to read spread sheet by active sheet        initialize php excel first        ob end clean           define cachemethod       cacheMethod     PHPExcel CachedObjectStorageFactory  cache to phpTemp        cacheSettings   array  memoryCacheSize    gt   20MB           set php excel settings      PHPExcel Settings  setCacheStorageMethod               cacheMethod  cacheSettings                    arrayLabel   array  A   B   C   D   E              set object reader      objectReader   PHPExcel IOFactory  createReader  Excel2007         objectReader- gt setReadDataOnly true         objPHPExcel    objectReader- gt load    forms test xlsx         objWorksheet    objPHPExcel- gt setActiveSheetIndexbyName  Sheet1          starting   1       end        3      for  i    starting  i lt   end   i                  for  j 0  j lt count  arrayLabel   j                             display each cell value            echo  objWorksheet- gt getCell  arrayLabel  j   i - gt getValue                          or dump data       sheetData    objPHPExcel- gt getActiveSheet  - gt toArray null true true true        var dump  sheetData           see also the following link      http   blog mayflower de 561-Import-and-export-data-using-PHPExcel html      ----------- import in another style around 5000 records ------       this- gt benchmark- gt mark  code start              change php ini limits             cacheMethod   PHPExcel CachedObjectStorageFactory   cache to phpTemp       cacheSettings   array    memoryCacheSize     gt   50MB        PHPExcel Settings  setCacheStorageMethod  cacheMethod   cacheSettings              create excel object of reader      objReader   PHPExcel IOFactory  createReader  Excel2007           objReader- gt setReadDataOnly true              load forms tashkil where the file exists      objPHPExcel    objReader- gt load    forms 5000records xlsx               set active sheet to read data      worksheet     objPHPExcel- gt setActiveSheetIndexbyName  Sheet1           highestRow            worksheet- gt getHighestRow       e g  10      highestColumn         worksheet- gt getHighestColumn       e g  F       highestColumnIndex   PHPExcel Cell  columnIndexFromString  highestColumn        nrColumns            ord  highestColumn  - 64       worksheetTitle        worksheet- gt getTitle         echo   lt br gt The worksheet    worksheetTitle   has        echo  nrColumns     columns  A-     highestColumn             echo   and      highestRow     row        echo   lt br gt Data   lt table border  1  gt  lt tr gt          ----- loop from all rows -----     for   row   1   row  lt    highestRow      row                 echo   lt tr gt            echo   lt td gt    row   lt  td gt              --- read each excel column for each row ----         for   col   0   col  lt   highestColumnIndex      col                         if  row    1                                   show column name with the title                    ----- get value ----                  cell    worksheet- gt getCellByColumnAndRow  col   row                    val    cell- gt getValue                       dataType   PHPExcel Cell DataType  dataTypeForValue  val                   echo   lt td gt      val       row   X    col       lt  td gt                              else                               if  col    9                                          ----- get value ----                      cell    worksheet- gt getCellByColumnAndRow  col   row                        val    cell- gt getValue                           dataType   PHPExcel Cell DataType  dataTypeForValue  val                       echo   lt td gt zone      val    lt  td gt                                      else if  col    13                                         date   PHPExcel Shared Date  ExcelToPHPObject  worksheet- gt getCellByColumnAndRow  col   row - gt getValue   - gt format  Y-m-d                        echo   lt td gt    dateprovider  date  dr      lt  td gt                                      else                                          ----- get value ----                      cell    worksheet- gt getCellByColumnAndRow  col   row                        val    cell- gt getValue                           dataType   PHPExcel Cell DataType  dataTypeForValue  val                       echo   lt td gt      val    lt  td gt                                                      echo   lt  tr gt              echo   lt  table gt         this- gt benchmark- gt mark  code end         echo  Total time    this- gt benchmark- gt elapsed time  code start    code end              this- gt load- gt view  error

User · Answer

Using the PHPExcel library to read an Excel file and transfer the data into a database      Include PHPExcel IOFactory include  PHPExcel IOFactory php     inputFileName      sampleData example1 xls        Read your Excel workbook try        inputFileType   PHPExcel IOFactory  identify  inputFileName        objReader   PHPExcel IOFactory  createReader  inputFileType        objPHPExcel    objReader- gt load  inputFileName     catch Exception  e        die  Error loading file    pathinfo  inputFileName PATHINFO BASENAME         e- gt getMessage            Get worksheet dimensions  sheet    objPHPExcel- gt getSheet 0     highestRow    sheet- gt getHighestRow      highestColumn    sheet- gt getHighestColumn         Loop through each row of the worksheet in turn for   row   1   row  lt    highestRow   row              Read a row of data into an array      rowData    sheet- gt rangeToArray  A     row          highestColumn    row                                      NULL                                      TRUE                                      FALSE           Insert row data array into your database of choice here     Anything more becomes very dependent on your database  and how you want the data structured in it

User · Answer

Using the PHPExcel library  the following code will do   require once dirname   FILE           Classes PHPExcel IOFactory php        objReader   PHPExcel IOFactory  createReader  Excel2007     objReader- gt setReadDataOnly true     optional   objPHPExcel    objReader- gt load   DIR     YourExcelFile xlsx     objWorksheet    objPHPExcel- gt getActiveSheet      i 1  foreach   objWorksheet- gt getRowIterator   as  row          column A Value    objPHPExcel- gt getActiveSheet  - gt getCell  A i  - gt getValue     column A       you can add your own columns B  C  D etc         inset  column A Value value in DB query here       i

User · Answer

if  this- gt mng auth- gt get language     en                                    excel- gt getActiveSheet  - gt setRightToLeft false                                else                                          excel- gt getActiveSheet  - gt setRightToLeft true                               styleArray   array                   borders    gt  array                       allborders    gt  array                           style    gt  PHPExcel Style Border  BORDER THIN                               color    gt  array  argb    gt   00000000                                                                          SET property            objPHPExcel- gt getActiveSheet  - gt getStyle  A1 M10001  - gt applyFromArray  styleArray          objPHPExcel- gt getActiveSheet  - gt getStyle  A1 M10001  - gt getAlignment  - gt setWrapText true       objPHPExcel- gt getActiveSheet  - gt getStyle  A1   chr 65 count  fields -1   query- gt num rows   - gt applyFromArray  styleArray       objPHPExcel- gt getActiveSheet  - gt getStyle  A1   chr 65 count  fields -1   query- gt num rows   - gt getAlignment  - gt setWrapText true

User · Answer

if  query                      try to export to excel the whole data ---           initialize php excel first           ob end clean              --- create php excel object ---          objPHPExcel   new PHPExcel              define cachemethod         ini set  memory limit    3500M             cacheMethod     PHPExcel CachedObjectStorageFactory  cache to phpTemp           cacheSettings   array  memoryCacheSize    gt   800MB              set php excel settings         PHPExcel Settings  setCacheStorageMethod                   cacheMethod  cacheSettings                             objPHPExcel- gt getProperties  - gt setTitle  export  - gt setDescription  none              objPHPExcel- gt setActiveSheetIndex 0               Field names in the first row          fields    query- gt list fields             col   0          foreach   fields as  field                         objPHPExcel- gt getActiveSheet  - gt setCellValueByColumnAndRow  col  1   field                col                          Fetching the table data          row   2          foreach  query- gt result   as  data                         col   0              foreach   fields as  field                                 objPHPExcel- gt getActiveSheet  - gt setCellValueByColumnAndRow  col   row   data- gt  field                    col                                row                        objPHPExcel- gt setActiveSheetIndex 0             redirect to cleint browser         header  Content-Type  application vnd openxmlformats-officedocument spreadsheetml sheet            header  Content-Disposition  attachment filename Provinces xlsx            header  Cache-Control  max-age 0              objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel2007             objWriter- gt save  php   output

User · Answer

Here is a very recent answer to this question from the file  07reader php   lt  php   error reporting E ALL   ini set  display errors   TRUE   ini set  display startup errors   TRUE    define  EOL   PHP SAPI     cli     PHP EOL     lt br   gt      date default timezone set  Europe London         Include PHPExcel IOFactory    require once     Classes PHPExcel IOFactory php     if   file exists  05featuredemo xlsx          exit  Please run 05featuredemo php first     EOL      echo date  H i s       Load from Excel2007 file    EOL   callStartTime   microtime true     objPHPExcel   PHPExcel IOFactory  load  05featuredemo xlsx      callEndTime   microtime true    callTime    callEndTime -  callStartTime  echo  Call time to read Workbook was     sprintf    4f   callTime      seconds    EOL     Echo memory usage echo date  H i s       Current memory usage       memory get usage true    1024   1024      MB    EOL    echo date  H i s       Write to Excel2007 format    EOL   callStartTime   microtime true     objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel2007     objWriter- gt save str replace   php     xlsx     FILE        callEndTime   microtime true    callTime    callEndTime -  callStartTime   echo date  H i s       File written to     str replace   php     xlsx   pathinfo   FILE    PATHINFO BASENAME     EOL  echo  Call time to write Workbook was     sprintf    4f   callTime      seconds    EOL     Echo memory usage echo date  H i s       Current memory usage       memory get usage true    1024   1024      MB    EOL       Echo memory peak usage echo date  H i s       Peak memory usage       memory get peak usage true    1024   1024      MB    EOL      Echo done echo date  H i s       Done writing file    EOL  echo  File has been created in     getcwd     EOL

[php] How to use phpexcel to read data and insert into database?

Examples related to php

Examples related to phpexcel