Solved

Create Excel file using PHP / ASP

Posted on 2004-10-06
8
782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
"Go Fund Me" type plugin 5 66
Create animated movies for web page 18 167
Company website 6 65
Google Tag Manager - Add Trigger Using Div class 22 99
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

734 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