How to export data to an excel file using PHPExcel

Question

I have taken the source code from limesurvey and have added the PHPExcel library to my limesurvey code to export data to an excel file after you click a link  Currently the excel file opens with some dummy data in it with no problems  I need to be able to add data dynamically from the web server after a user types in survey information  I have looked into some sites I have found but I havent had much luck  Can anyone help me out    EDIT   lt  php   dbhost   mysql     your MySQL Server   dbuser    survey     your MySQL User Name   dbpass    password     your MySQL Password   dbname    database      your MySQL Database Name of which database to use this   tablename    questions     your MySQL Table Name which one you have to create excel file     your mysql query here   we can edit this for your requirement   sql    Select   from  table       create  code for connecting to mysql   Connect    mysql connect  dbhost   dbuser   dbpass   or die  Couldn t connect to MySQL  lt br gt     mysql error       lt br gt     mysql errno        select database   Db    mysql select db  dbname   Connect   or die  Couldn t select database  lt br gt     mysql error      lt br gt     mysql errno        execute query   result    mysql query  sql  Connect   or die  Couldn t execute query  lt br gt     mysql error      lt br gt     mysql errno       error reporting E ALL     require once     Classes PHPExcel php     objPHPExcel   new PHPExcel         Set the active Excel worksheet to sheet 0    objPHPExcel- gt setActiveSheetIndex 0         Initialise the Excel row number    rowCount   1        start of printing column names as names of MySQL fields      column    A    for   i   1   i  lt  mysql num fields  result    i              objPHPExcel- gt getActiveSheet  - gt setCellValue  column  rowCount  mysql field name  result  i         column         end of adding column names     start while loop to get data     rowCount   2     while  row   mysql fetch row  result               column    A       for  j 1   j lt mysql num fields  result   j                      if  isset  row  j                    value   NULL             elseif   row  j                         value   strip tags  row  j               else                 value                    objPHPExcel- gt getActiveSheet  - gt setCellValue  column  rowCount   value            column                  rowCount           Redirect output to a client   s web browser  Excel5   header  Content-Type  application vnd ms-excel     header  Content-Disposition  attachment filename  results xls      header  Cache-Control  max-age 0      objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel5      objWriter- gt save  php   output

User · Answer

Work 100   maybe not relation to creator answer but i share it for users have a problem with export mysql query to excel with phpexcel  Good Luck   require     phpexcel PHPExcel php     require     phpexcel PHPExcel Writer Excel5 php      filename    userReport     your file name       objPHPExcel   new PHPExcel                              Add column headings START                             objPHPExcel- gt setActiveSheetIndex 0                  - gt setCellValue  A1    username                   - gt setCellValue  B1    city name                               Add data entries START                          get result array from class  You can replace your sql code with this line   result    get report clas- gt get user report      set variable for count table fields   num row   1  foreach   result as  value       user name    value  username       c code    value  city name       num row             objPHPExcel- gt setActiveSheetIndex 0                  - gt setCellValue  A   num row   user name                   - gt setCellValue  B   num row   c code                                 Autoresize column width depending upon contents START                            foreach range  A   B   as  columnID             objPHPExcel- gt getActiveSheet  - gt getColumnDimension  columnID - gt setAutoSize true              objPHPExcel- gt getActiveSheet  - gt getStyle  A1 B1  - gt getFont  - gt setBold true        Make heading font bold                                Add color to heading START                                 objPHPExcel- gt getActiveSheet                       - gt getStyle  A1 B1                       - gt getFill                       - gt setFillType PHPExcel Style Fill  FILL SOLID                      - gt getStartColor                       - gt setARGB  99ff99              objPHPExcel- gt getActiveSheet  - gt setTitle  userReport      give title to sheet          objPHPExcel- gt setActiveSheetIndex 0           header  Content-Type  application vnd ms-excel            header  Content-Disposition  attachment Filename  filename xls            header  Cache-Control  max-age 0             objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel5             objWriter- gt save  php   output

User · Answer

If you ve copied this directly  then   - gt setCellValue  B2   Ackermann      should be  - gt setCellValue  B2    Ackermann      In answer to your question   Get the data that you want from limesurvey  and use setCellValue   to store those data values in the cells where you want to store it   The Quadratic php example file in  Tests might help as a starting point  it takes data from an input form and sets it to cells in an Excel workbook   EDIT  An extremely simplistic example      Create your database query  query    SELECT   FROM myDataTable         Execute the database query  result   mysql query  query  or die mysql error         Instantiate a new PHPExcel object  objPHPExcel   new PHPExcel        Set the active Excel worksheet to sheet 0  objPHPExcel- gt setActiveSheetIndex 0       Initialise the Excel row number  rowCount   1      Iterate through each result from the SQL query in turn    We fetch each database result row into  row in turn while  row   mysql fetch array  result            Set cell An to the  name  column from the database  assuming you have a column called name            where n is the Excel row number  ie cell A1 in the first row       objPHPExcel- gt getActiveSheet  - gt SetCellValue  A   rowCount   row  name             Set cell Bn to the  age  column from the database  assuming you have a column called age            where n is the Excel row number  ie cell A1 in the first row       objPHPExcel- gt getActiveSheet  - gt SetCellValue  B   rowCount   row  age             Increment the Excel row counter      rowCount            Instantiate a Writer to create an OfficeOpenXML Excel  xlsx file  objWriter   new PHPExcel Writer Excel2007  objPHPExcel       Write the Excel file to filename some excel file xlsx in the current directory  objWriter- gt save  some excel file xlsx       EDIT  2  Using your existing code as the basis     Instantiate a new PHPExcel object   objPHPExcel   new PHPExcel         Set the active Excel worksheet to sheet 0   objPHPExcel- gt setActiveSheetIndex 0        Initialise the Excel row number   rowCount   1       start of printing column names as names of MySQL fields    column    A   for   i   1   i  lt  mysql num fields  result    i             objPHPExcel- gt getActiveSheet  - gt setCellValue  column  rowCount  mysql field name  result  i         column        end of adding column names      start while loop to get data    rowCount   2    while  row   mysql fetch row  result              column    A       for  j 1   j lt mysql num fields  result   j                      if  isset  row  j                   value   NULL            elseif   row  j                        value   strip tags  row  j              else                value                   objPHPExcel- gt getActiveSheet  - gt setCellValue  column  rowCount   value            column                 rowCount            Redirect output to a client   s web browser  Excel5   header  Content-Type  application vnd ms-excel     header  Content-Disposition  attachment filename  Limesurvey Results xls      header  Cache-Control  max-age 0      objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel5      objWriter- gt save  php   output

User · Answer

this- gt load- gt library  excel     file name    Demo    arrHeader   array  Name    Mobile     arrRows   array 0  gt array  Name   gt  Jayant   Mobile   gt 54545   1  gt array  Name   gt  Jayant1    Mobile   gt 44454   2  gt array  Name   gt  Jayant2   Mobile   gt 111222   3  gt array  Name   gt  Jayant3    Mobile   gt 99999     this- gt excel- gt getActiveSheet  - gt fromArray  arrHeader     A1     this- gt excel- gt getActiveSheet  - gt fromArray  arrRows   header  Content-Type  application vnd ms-excel      mime type header  Content-Disposition  attachment filename     file name         tell browser what s the file name header  Cache-Control  max-age 0      no cache  objWriter   PHPExcel IOFactory  createWriter  this- gt excel   Excel5       objWriter- gt save  php   output

User · Answer

Try the below complete example for the same   lt  php    objPHPExcel   new PHPExcel       query1    SELECT   FROM employee      exec1   mysql query  query1  or die   Error in Query1  mysql error        serialnumber 0      Set header with temp array    tmparray  array  Sr Number   Employee Login   Employee Name        take new main array and set header array in it     sheet  array  tmparray      while   res1   mysql fetch array  exec1            tmparray  array         serialnumber    serialnumber   1      array push  tmparray  serialnumber        employeelogin    res1  employeelogin        array push  tmparray  employeelogin        employeename    res1  employeename        array push  tmparray  employeename          array push  sheet  tmparray          header  Content-type  application vnd ms-excel       header  Content-Disposition  attachment  filename  name xlsx        worksheet    objPHPExcel- gt getActiveSheet      foreach  sheet as  row   gt   columns        foreach  columns as  column   gt   data             worksheet- gt setCellValueByColumnAndRow  column   row   1   data                  make first row bold    objPHPExcel- gt getActiveSheet  - gt getStyle  A1 I1  - gt getFont  - gt setBold true      objPHPExcel- gt setActiveSheetIndex 0      objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel2007       objWriter- gt save str replace   php     xlsx     FILE        gt

User · Answer

I currently use this function in my project after a series of googling to download excel file from sql statement          sql   sql query e g  select   from mytablename          filename   name of the file to download          function queryToExcel  sql   fileName    name xlsx                        initialise excel column name                    currently limited to queries with less than 27 columns          columnArray   array  A    B    C    D    E    F    G    H    I    J    K    L    M    N    O    P    Q    R    S    T    U    V    W    X    Y    Z                       Execute the database query                  result    mysql query  sql  or die mysql error                         Instantiate a new PHPExcel object                  objPHPExcel   new PHPExcel                       Set the active Excel worksheet to sheet 0                  objPHPExcel- gt setActiveSheetIndex 0                      Initialise the Excel row number                  rowCount   1         fetch result set column information                  finfo   mysqli fetch fields  result      initialise columnlenght counter                  columnlenght   0                  foreach   finfo as  val       set column header values                       objPHPExcel- gt getActiveSheet  - gt SetCellValue  columnArray  columnlenght       rowCount   val- gt name                        make the column headers bold                  objPHPExcel- gt getActiveSheet  - gt getStyle  columnArray 0   1    columnArray  columnlenght   1  - gt getFont  - gt setBold true                     rowCount                       Iterate through each result from the SQL query in turn                    We fetch each database result row into  row in turn                  while   row   mysqli fetch array  result  MYSQL NUM                         for   i   0   i  lt   columnLenght   i                               objPHPExcel- gt getActiveSheet  - gt SetCellValue  columnArray  i     rowCount   row  i                                               rowCount                         set header information to force download                 header  Content-type  application vnd ms-excel                    header  Content-Disposition  attachment  filename       fileName                            Instantiate a Writer to create an OfficeOpenXML Excel  xlsx file                            Write the Excel file to filename some excel file xlsx in the current directory                                  objWriter   new PHPExcel Writer Excel2007  objPHPExcel                       Write the Excel file to filename some excel file xlsx in the current directory                  objWriter- gt save  php   output

[php] How to export data to an excel file using PHPExcel

Examples related to php

Examples related to phpexcel