Reading an Excel file in PHP

Question

I m trying to read an Excel file  Office 2003   There is an Excel file that needs to be uploaded and its contents parsed   Via Google  I can only find answers to these related  and insufficient topics   generating Excel files  reading Excel XML files  reading Excel CSV files  or incomplete abandoned projects  I own Office 2003 so if I need any files from there  they are available  It s installed on my box but isn t and can t be installed on my shared host   Edit  so far all answers point to PHP-ExcelReader and or this additional article about how to use it

User · Answer

I have used following code to read  xls and xlsx          include  PHPExcel IOFactory php         location  sample-excel-files xlsx         objPHPExcel   PHPExcel IOFactory  load  location        sheet    objPHPExcel- gt getSheet 0        total rows    sheet- gt getHighestRow         total columns    sheet- gt getHighestColumn         set excel query all array        for  row  2   row  lt    total rows   row               singlerow    sheet- gt rangeToArray  A     row          total columns    row  NULL  TRUE  FALSE            single row  singlerow 0            set excel query  store id    single row 0            set excel query  employee uid    single row 1            set excel query  opus id    single row 2            set excel query  item description    single row 3           if  single row 4                          set excel query  opus transaction date    date  Y-m-d   PHPExcel Shared Date  ExcelToPHP  single row 4                        set excel query  opus transaction num    single row 5            set excel query  opus invoice num    single row 6            set excel query  customer name    single row 7            set excel query  mobile num    single row 8            set excel query  opus amount    single row 9            set excel query  rq4 amount    single row 10            set excel query  difference    single row 11            set excel query  ocomment    single row 12            set excel query  mark delete    single row 13           if  single row 14                          set excel query  upload date    date  Y-m-d   PHPExcel Shared Date  ExcelToPHP  single row 14                        set excel query all    set excel query            print r  set excel query all

User · Answer

Read XLSX  Excel 97-2003  https   github com shuchkin simplexls  if    xls   SimpleXLS  parse  book xls           print r   xls- gt rows        else       echo SimpleXLS  parseError        Read XLSX  Excel 2003   https   github com shuchkin simplexlsx  if    xlsx   SimpleXLSX  parse  book xlsx           print r   xlsx- gt rows        else       echo SimpleXLSX  parseError        Output   Array        0     Array                        0     ISBN              1     title              2     author              3     publisher              4     ctry                1     Array                        0     618260307              1     The Hobbit              2     J  R  R  Tolkien              3     Houghton Mifflin              4     USA               CSV php reader https   github com shuchkin simplecsv

User · Answer

It depends on how you want to use the data in the excel file  If you want to import it into mysql  you could simply save it as a CSV formatted file and then use fgetcsv to parse it

User · Answer

Try this     I have used following code to read  xls and xlsx        lt  php     include  excel reader php            include the class      excel   new PhpExcelReader          creates object instance of the class      excel- gt read  excel file xls         reads and stores the excel file data         Test to see the excel data stored in  sheets property     echo   lt pre gt        var export  excel- gt sheets        echo   lt  pre gt         or    echo   lt pre gt        print r  excel- gt sheets        echo   lt  pre gt      Reference http   coursesweb net php-mysql read-excel-file-data-php pc

User · Answer

I use PHP-ExcelReader to read xls files  and works great

User · Answer

You have 2 choices as far as I know    Spreadsheet Excel Reader  which knows the Office 2003 binary format PHPExcel  which knows both Office 2003 as well as Excel 2007  XML    Follow the link  and you ll see they upgraded this library to PHPSpreadSheet    PHPExcel uses Spreadsheet Excel Reader for the Office 2003 format   Update  I once had to read some Excel files but I used the Office 2003 XML format in order to read them and told the people that were using the application to save and upload only that type of Excel file

User · Answer

I m using below excel file url  https   github com inventorbala Sample-Excel-files blob master sample-excel-files xlsx  Output   Array                0    gt  Array                                store id    gt  3716                  employee uid    gt  664368                  opus id    gt  zh901j                  item description    gt  PRE ATT  75 PNLS 90EXP                  opus transaction date    gt  2019-10-18                  opus transaction num    gt  X2MBV1DJKSLQW                  opus invoice num    gt  O3716IN3409                  customer name    gt  BILL PHILLIPS                  mobile num    gt  4052380136                  opus amount    gt  75                  rq4 amount    gt  0                  difference    gt  -75                  ocomment    gt  Re-Upload  We need RQ4 transaction for October   If you re unable to provide the October invoice  it will be counted as EPin shortage                   mark delete    gt  0                  upload date    gt  2019-10-20                         1    gt  Array                                store id    gt  2710                  employee uid    gt  75899                  opus id    gt  dc288t                  item description    gt  PRE ATT  50 PNLS 90EXP                  opus transaction date    gt  2019-10-18                  opus transaction num    gt  XJ90419JKT9R9                  opus invoice num    gt  M2710IN868                  customer name    gt  CALEB MENDEZ                  mobile num    gt  6517672079                  opus amount    gt  50                  rq4 amount    gt  0                  difference    gt  -50                  ocomment    gt  No Response   Re-Upload                  mark delete    gt  0                  upload date    gt  2019-10-20                         2    gt  Array                                store id    gt  0136                  employee uid    gt  70167                  opus id    gt  fv766x                  item description    gt  PRE ATT  50 PNLS 90EXP                  opus transaction date    gt  2019-10-18                  opus transaction num    gt  XQ57316JKST1V                  opus invoice num    gt  GONZABP25622                  customer name    gt  FAUSTINA CASTILLO                  mobile num    gt  8302638628                  opus amount    gt  100                  rq4 amount    gt  50                  difference    gt  -50                  ocomment    gt  Re-Upload  We have been charged in opus for  100  Provide RQ4 invoice number for remaining amount                  mark delete    gt  0                  upload date    gt  2019-10-20                         3    gt  Array                                store id    gt  3264                  employee uid    gt  23723                  opus id    gt  aa297h                  item description    gt  PRE ATT  25 PNLS 90EXP                  opus transaction date    gt  2019-10-19                  opus transaction num    gt  XR1181HJKW9MP                  opus invoice num    gt  C3264IN1588                  customer name    gt  SOPHAT VANN                  mobile num    gt  9494668372                  opus amount    gt  70                  rq4 amount    gt  25                  difference    gt  -45                  ocomment    gt  No Response   Re-Upload                  mark delete    gt  0                  upload date    gt  2019-10-20                         4    gt  Array                                store id    gt  4166                  employee uid    gt  568494                  opus id    gt  ab7598                  item description    gt  PRE ATT  40 RTR                  opus transaction date    gt  2019-10-20                  opus transaction num    gt  X8F58P3JL2RFU                  opus invoice num    gt  I4166IN2481                  customer name    gt  KELLY MC GUIRE                  mobile num    gt  6189468180                  opus amount    gt  40                  rq4 amount    gt  0                  difference    gt  -40                  ocomment    gt  Re-Upload  The invoice number that you provided  I4166IN2481  belongs to September transaction   We need RQ4 transaction for October   If you re unable to provide the October invoice  it will be counted as EPin shortage                   mark delete    gt  0                  upload date    gt  2019-10-21                         5    gt  Array                                store id    gt  4508                  employee uid    gt  552502                  opus id    gt  ec850x                  item description    gt   30 RTR                  opus transaction date    gt  2019-10-20                  opus transaction num    gt  XPL7M1BJL1W5D                  opus invoice num    gt  M4508IN6024                  customer name    gt  PREPAID CUSTOMER                  mobile num    gt  6019109730                  opus amount    gt  30                  rq4 amount    gt  0                  difference    gt  -30                  ocomment    gt  Re-Upload  The invoice number you provided  M4508IN7217  belongs to a different phone number   We need RQ4 transaction for the phone number in question   If you re unable to provide the RQ4 invoice for this transaction  it will be counted as EPin shortage                   mark delete    gt  0                  upload date    gt  2019-10-21                         6    gt  Array                                store id    gt  3904                  employee uid    gt  35818                  opus id    gt  tj539j                  item description    gt  PRE  45 PAYG PINLESS REFILL                  opus transaction date    gt  2019-10-20                  opus transaction num    gt  XM1PZQSJL215F                  opus invoice num    gt  N3904IN1410                  customer name    gt  DORTHY JONES                  mobile num    gt  3365982631                  opus amount    gt  90                  rq4 amount    gt  45                  difference    gt  -45                  ocomment    gt  Re-Upload  Please email the details to Treasury and confirm                  mark delete    gt  0                  upload date    gt  2019-10-21                         7    gt  Array                                store id    gt  1820                  employee uid    gt  59883                  opus id    gt  cb9406                  item description    gt  PRE ATT  25 PNLS 90EXP                  opus transaction date    gt  2019-10-20                  opus transaction num    gt  XTBJO14JL25OE                  opus invoice num    gt  SEVIEIN19013                  customer name    gt  RON NELSON                  mobile num    gt  8653821076                  opus amount    gt  25                  rq4 amount    gt  5                  difference    gt  -20                  ocomment    gt  Re-Upload  We have been charged in opus for  25  Provide RQ4 invoice number for remaining amount                  mark delete    gt  0                  upload date    gt  2019-10-21                         8    gt  Array                                store id    gt  0178                  employee uid    gt  572547                  opus id    gt  ms5674                  item description    gt  PRE  45 PAYG PINLESS REFILL                  opus transaction date    gt  2019-10-21                  opus transaction num    gt  XT29916JL4S69                  opus invoice num    gt  T0178BP1590                  customer name    gt  GABRIEL LONGORIA JR                  mobile num    gt  4322133450                  opus amount    gt  45                  rq4 amount    gt  0                  difference    gt  -45                  ocomment    gt  Re-Upload  Please email the details to Treasury and confirm                  mark delete    gt  0                  upload date    gt  2019-10-22                         9    gt  Array                                store id    gt  2180                  employee uid    gt  7842                  opus id    gt  lm854y                  item description    gt   30 RTR                  opus transaction date    gt  2019-10-21                  opus transaction num    gt  XC9U712JL4LA4                  opus invoice num    gt  KETERIN1836                  customer name    gt  PETE JABLONSKI                  mobile num    gt  9374092680                  opus amount    gt  30                  rq4 amount    gt  40                  difference    gt  10                  ocomment    gt  Re-Upload  Credit the remaining balance to customers account in OPUS and email confirmation to Treasury                  mark delete    gt  0                  upload date    gt  2019-10-22                                           63    gt  Array                                store id    gt  0175                  employee uid    gt  33738                  opus id    gt  ph5953                  item description    gt  PRE ATT  40 RTR                  opus transaction date    gt  2019-10-21                  opus transaction num    gt  XE5N31DJL51RA                  opus invoice num    gt  T0175IN4563                  customer name    gt  WILLIE TAYLOR                  mobile num    gt  6822701188                  opus amount    gt  40                  rq4 amount    gt  50                  difference    gt  10                  ocomment    gt  Re-Upload  Credit the remaining balance to customers account in OPUS and email confirmation to Treasury                  mark delete    gt  0                  upload date    gt  2019-10-22

User · Answer

Here is the simple code using COM object in PHP class Excel ReadWrite       private  XLSHandle      private  WrkBksHandle      private  xlBook       function   construct              this- gt XLSHandle   new COM  excel application   or die  ERROR  Unable to instantaniate COM  r n                function   destruct              if already existing file is opened         if  this- gt WrkBksHandle    null                            this- gt WrkBksHandle- gt Close True               unset  this- gt WrkBksHandle                this- gt XLSHandle- gt Workbooks- gt Close                        if created new xls file         if  this- gt xlBook    null                         this- gt xlBook- gt Close True               unset  this- gt xlBook                       Quit Excel Application          this- gt XLSHandle- gt Quit            unset  this- gt XLSHandle              public function OpenFile  FilePath                 this- gt WrkBksHandle    this- gt XLSHandle- gt Workbooks- gt Open  FilePath              public function ReadData  RowNo   ClmNo                Value    this- gt XLSHandle- gt ActiveSheet- gt Cells  RowNo   ClmNo - gt Value         return  Value               public function SaveOpenedFile                  this- gt WrkBksHandle- gt Save                                                                                                             Function Name - WriteToXlsFile   will write data based on row and column numbers        Param -  CellData- cell data        Param -  RowNumber- xlsx file row number        Param -  ColumnNumber- xlsx file column numbers                                                                                             function WriteToXlsFile  CellData   RowNumber   ColumnNumber              try                  this- gt XLSHandle- gt ActiveSheet- gt Cells  RowNumber  ColumnNumber - gt Value    CellData                      catch Exception  e                  throw new Exception  Error - Unable to write data to xlsx sheet                                                                                                                           Function Name - CreateXlsFileWithClmName   will initialize xls file with column Names        Param -  XlsColumnNames- Array of columns data        Param -  XlsColumnWidth- Array of columns width                                                                                                    function CreateXlsFileWithClmNameAndWidth  WorkSheetName    Raman    XlsColumnNames   null   XlsColumnWidth   null                Hide MS Excel application window         this- gt XLSHandle- gt Visible   0           Create new document         this- gt xlBook    this- gt XLSHandle- gt Workbooks- gt Add              Create Sheet 1         this- gt xlBook- gt Worksheets 1 - gt Name    WorkSheetName          this- gt xlBook- gt Worksheets 1 - gt Select          if  XlsColumnWidth    null                         XlsColumnWidth   array  A1   gt 15  B1   gt 20              foreach  XlsColumnWidth as  Clm  gt  Width                                Set Columns Width                 this- gt XLSHandle- gt ActiveSheet- gt Range  Clm      Clm - gt ColumnWidth    Width                                   if  XlsColumnNames    null                         XlsColumnNames   array  FirstColumnName   gt 1   SecondColumnName   gt 2              foreach  XlsColumnNames as  ClmName  gt  ClmNumber                                 Cells Row Column                  this- gt XLSHandle- gt ActiveSheet- gt Cells 1  ClmNumber - gt Value    ClmName                  this- gt XLSHandle- gt ActiveSheet- gt Cells 1  ClmNumber - gt Font- gt Bold   True                  this- gt XLSHandle- gt ActiveSheet- gt Cells 1  ClmNumber - gt Interior- gt ColorIndex    15                                   56 is for xls 8     public function SaveCreatedFile  FileName   FileFormat   56                 this- gt xlBook- gt SaveAs  FileName   FileFormat              public function MakeFileVisible                  Hide MS Excel application window enter code here          this- gt XLSHandle- gt Visible   1           end of EXCEL class

[php] Reading an Excel file in PHP

Examples related to php

Examples related to import-from-excel