Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Excel file using PHP / ASP

Posted on 2004-10-06
8
Medium Priority
?
784 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 60 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.
Suggested Courses

688 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