Solved

Create Excel file using PHP / ASP

Posted on 2004-10-06
8
776 Views
Last Modified: 2010-05-18
hi, i have a page that displays information queried from a database (mdb / mysql), i'd like to create an excel files based from these records. i'd like to know how can i use the excel object and put the information in the cells.

thanks.
0
Comment
Question by:nolram
8 Comments
 
LVL 32

Expert Comment

by:ldbkutty
ID: 12243440
0
 

Expert Comment

by:taranki
ID: 12244905
Depending on the vesion of excel, you can actually just format the data in HTML tables, and put an .xls file extension on it,  you can write this locally and redirect the user to it, or change the response.contentType  and stream it to the browser.

Example (in ASP):
<%
      Response.Buffer = true
      Response.ContentType = "application/vnd.ms-excel"
%>
<table>
      <tr>
            <td>Column 1</td>
            <td><strong>Column 2</strong></td>
            <td>Column 3</td>
      </tr>
      <tr>
            <td>Some Data</td>
            <td><strong>4</strong></td>
            <td>test</td>
      </tr>

</table>

/tim
0
 
LVL 2

Assisted Solution

by:mohitraj
mohitraj earned 20 total points
ID: 12245708
Example ASP:

To create an Excel file, of the data you have on the html/asp page, u need to write a client side script. The following code is vbscript

sub openExcel()
      ' Bind to Excel object.
      On Error Resume Next
      Err.Clear
       Set objExcel = CreateObject("Excel.Application")
      If Err.Number <> 0 Then
            Err.Clear
            Wscript.Echo "Excel application not found."
            Wscript.Quit
      End If
      On Error GoTo 0
      'Set the visibility to true
      objExcel.Visible = true
      'let user control the excel
    objExcel.UserControl = true
      'Add a work book
    objExcel.Workbooks.add
      'open a defualt sheet called sheet1
      objExcel.Sheets ("Sheet1").Select      
      'set defualt properties of the excel
      
end sub

Then you can set various properties of the individual cells or select a range and apply the setting on it.
Actions like:

objExcel.sheets(1).select       
objExcel.ActiveSheet.Cells(1,18).Value="Mohit"

Use can set the properties of the cells or the assign formulas like:
objRange.Formula="=SUM("A11:B20")"

If you need any property to set, which is difficult to find usuallly, do get back to me.

Mohit Raj
0
 
LVL 1

Expert Comment

by:apg88
ID: 12255747
Here it is in short(php):

//******************start code***********************
// Define the tab and carriage return characters:
      $tab = "\t";      //chr(9);
      $cr = "\n";            //chr(13);

// To separate the items by columns, you just put a tab in-between each one.
$data = "column1" . $tab . "col2" . $tab; \\etc....

// When you want to jump to the next row put a $cr at the end (it acts like a line break)

$data .= $cr;

//You can  continue to ad rows like that

// Then just save the $data into a text file named "file.xls"

$fp = fopen($filename,"a"); // $fp is now the file pointer to file $filename
                  
                  if($fp){
                        
                        fwrite($fp,$data);                                  //Write information to the file
                        fclose($fp);                                           // Close the file
                        echo "File saved successfully! <br> " . $continue;
                  } else {
                        echo "Error saving file! <br>" . $continue;
                  }


//******************end code***********************

Just open the file with excel and you are done.




I made a script to store form submissions to an excel file.
You can see how I created the excel file there and an actual working example.
http://www.apg88.com/apgForm/
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Assisted Solution

by:sumanraj
sumanraj earned 25 total points
ID: 12257613
Here is the code in javascript, just u have to pass the, "id" of the table tag it is enough it will generate u an Excel file...

function converTabletoExcel(sTableId)
{
        var mySource = document.all(sTableId);
        var myRows = mySource.rows.length;
        var To = 0
      var from = 0
      oExcel = new ActiveXObject("Excel.Application");
      oExcel.Workbooks.Add;
      oExcel.Selection.Font.Name = "Arial";
      oExcel.Selection.Font.FontStyle = "Regular";
      oExcel.Selection.Font.Strikethrough = false;
      oExcel.Selection.Font.Superscript = false;
      oExcel.Selection.Font.Subscript = false;
      oExcel.Selection.Font.OutlineFont = false;
      oExcel.Selection.Font.Shadow = false;            
      if (myRows != "0")
      {
            var myCols = mySource.rows(0).cells.length;

              for (i=0; i < myCols; i++)
              {
                  oExcel.ActiveSheet.Cells(1,i+1).Font.Bold= true;
                  oExcel.ActiveSheet.Cells(1,i+1).Font.Size= 10;            
            }

            myArray = new Array(myRows)
            
            for (i=0; i < myRows; i++)
            {
                  myArray[i] = new Array(myCols)
                  
                    var nWidth;
                  var nTwidth;
                  var nColno;
                  var sName;
                  nWidth=15;
                  sName =parseInt(i) + 1;
                        if (sName == "1")                                    
                              oExcel.ActiveSheet.Columns("A:A").ColumnWidth = nWidth;
                        if (sName == "2")      
                              oExcel.ActiveSheet.Columns("B:B").ColumnWidth = nWidth;
                        if (sName == "3")      
                              oExcel.ActiveSheet.Columns("C:C").ColumnWidth = nWidth;
                        if (sName == "4")      
                              oExcel.ActiveSheet.Columns("D:D").ColumnWidth = nWidth;
                        if (sName == "5")      
                              oExcel.ActiveSheet.Columns("E:E").ColumnWidth = nWidth;
                        if (sName == "6")      
                              oExcel.ActiveSheet.Columns("F:F").ColumnWidth = nWidth;
                        if (sName == "7")      
                              oExcel.ActiveSheet.Columns("G:G").ColumnWidth = nWidth;
                        if (sName == "8")      
                              oExcel.ActiveSheet.Columns("H:H").ColumnWidth = nWidth;
                        if (sName == "9")      
                              oExcel.ActiveSheet.Columns("I:I").ColumnWidth = nWidth;
                        if (sName == "10")      
                              oExcel.ActiveSheet.Columns("J:J").ColumnWidth = nWidth;
                        if (sName == "11")      
                              oExcel.ActiveSheet.Columns("K:K").ColumnWidth = nWidth;
                        if (sName == "12")      
                              oExcel.ActiveSheet.Columns("L:L").ColumnWidth = nWidth;
                        if (sName == "13")      
                              oExcel.ActiveSheet.Columns("M:M").ColumnWidth = nWidth;
                        if (sName == "14")      
                              oExcel.ActiveSheet.Columns("N:N").ColumnWidth = nWidth;
                        if (sName == "15")      
                              oExcel.ActiveSheet.Columns("O:O").ColumnWidth = nWidth;
                        if (sName == "16")      
                              oExcel.ActiveSheet.Columns("P:P").ColumnWidth = nWidth;
                        if (sName == "17")      
                              oExcel.ActiveSheet.Columns("Q:Q").ColumnWidth = nWidth;
                        if (sName == "18")      
                              oExcel.ActiveSheet.Columns("R:R").ColumnWidth = nWidth;
                        if (sName == "19")      
                              oExcel.ActiveSheet.Columns("S:S").ColumnWidth = nWidth;
                        if (sName == "20")      
                              oExcel.ActiveSheet.Columns("T:T").ColumnWidth = nWidth;
                        if (sName == "21")      
                              oExcel.ActiveSheet.Columns("U:U").ColumnWidth = nWidth;
                        if (sName == "22")      
                              oExcel.ActiveSheet.Columns("V:V").ColumnWidth = nWidth;
                        if (sName == "23")      
                              oExcel.ActiveSheet.Columns("W:W").ColumnWidth = nWidth;
                        if (sName == "24")      
                              oExcel.ActiveSheet.Columns("X:X").ColumnWidth = nWidth;
                        if (sName == "25")      
                              oExcel.ActiveSheet.Columns("Y:Y").ColumnWidth = nWidth;
                        if (sName == "26")      
                              oExcel.ActiveSheet.Columns("Z:Z").ColumnWidth = nWidth;      
                  }
var sData;
            for (i=0; i < myRows; i++) {
             myArray[i] = new Array(myCols)
             for (j=0; j < myCols; j++) {
                  sData= document.all(sTableId).rows(i).cells(j).innerHTML;
                if (sData.substring(0,1) =="<")
                    {
                     if (sData.indexOf("<IMG") >= 0 || sData.indexOf("<INPUT") >= 0)
                     {
                         sData = "" ;
                     }      
                     else if (sData.indexOf("<A") >= 0)
                     {                
                            if (sData.indexOf("<IMG") > 0 || sData.indexOf("<INPUT") > 0 )
                                    sData = ""
                              else
                              {
                                    To = sData.indexOf(">")
                                    From=sData.lastIndexOf("<")
                                    sData=sData.substring(To+1,From)
                              }                  
                     }      
                   }
                  else if(sData.indexOf("<IMG") >= 0 )
                   {
                      To = sData.indexOf("<IMG")
                       sData=sData.substring(0,To-1)
                   }
                   oExcel.ActiveSheet.Cells(i+1,j+1).Value =sData
                  }
            }
            oExcel.visible=true;      
            }
}
0
 
LVL 1

Expert Comment

by:apg88
ID: 12262608
The problem with the object is that it is not supported by all browsers. if you use OpenOffice or another non-microsoft spreadsheet program, you wont be able to use the thing with the object, but if you create a file for the user to download, any program can open it.
0
 
LVL 2

Expert Comment

by:mohitraj
ID: 12273917
In fact This is the correct way to create  an Excel file and the code posted above was inteded for such purpose only. The process should be to create the Excel file on the server and then prompt the user to download the file created. Bcoz in this way MS Excel is reqd only on the server and not on the client side. ANd the downloaded file can be opened in any application capable of supporting MS Excel files. But one must be craefull about accessing the enwly created Excel file. Therefore FSO should be used to access the file after it has been created and closed. Otherwise the file wont be created properly.

Does any one require the code to handle the file after creation, plz write back

Mohit Raj
0
 
LVL 1

Accepted Solution

by:
apg88 earned 70 total points
ID: 12478993
Here is is in a better form.
This way nothing stays on the server and the user is prompted to download the file.
Excel does not ned to be installed on the server for this to work, neither does the user have to have excel. You can open this file with openOffice or even notepad and it will work.

NOTE: the <?php and headers must be the first 5 lines of your code, if not the browser will treat it as an html file.

******************start code***********************
<?php
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=votes.xls");
header("Pragma: no-cache");
header("Expires: 0");




// Define the tab and carriage return characters:
     $tab = "\t";     //chr(9);
     $cr = "\n";          //chr(13);

// To separate the items by columns, you just put a tab in-between each one.
$data = "column1" . $tab . "col2" . $tab; \\etc....

// When you want to jump to the next row put a $cr at the end (it acts like a line break)

$data .= $cr;

//You can  continue to ad rows like that

//Then just print the $data into a the document and it will prompt the user to download the file
   
?>
******************end code***********************

I hope this helps.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now