Is it possible to write to an Excel template via PHP?

Is it possible to open a multi-sheet Excel template which resides on the webserver, populate it with data from a MySql query then save the spreadsheet to another folder and name on the webserver? The only classes I can find will create a new spreadsheet and populate it then open it for the user. I have a 10 sheet workbook with a substantial amount of formatting and a tremendous number of formulas that I would rather not have to duplicate in PHP in order to write them to a blank sheet every time.

I have googled myself half to death looking for a solution but to no avail!
Thanks in advance!

Jan 23 '08 #1

Subscribe Post Reply

7845

harshmaul

490

Expert 256MB

As long as you have the content dispoisiton working which it sounds like you have (as you can do a single work sheet, print out this xml using that.......

BTW i got this by saving an xls file as an xml file. so if the file is static then you can just use that xml as a templat and change whatever you want.

Expand|Select|Wrap|Line Numbers

  
<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:html="http://www.w3.org/TR/REC-html40">

 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>--Author name--</Author>

  <LastAuthor>--last changed by--</LastAuthor>

  <Created>2008-01-25T12:06:27Z</Created>

  <LastSaved>2008-01-25T12:07:02Z</LastSaved>

  <Company>--Company Name--</Company>

  <Version>11.9999</Version>

 </DocumentProperties>

 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>9150</WindowHeight>

  <WindowWidth>11100</WindowWidth>

  <WindowTopX>720</WindowTopX>

  <WindowTopY>270</WindowTopY>

  <ActiveSheet>1</ActiveSheet>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

 </ExcelWorkbook>

 <Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

 </Styles>

 <Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Panes>

    <Pane>

     <Number>3</Number>

     <ActiveRow>1</ActiveRow>

     <ActiveCol>1</ActiveCol>

    </Pane>

   </Panes>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

 </Worksheet>

 <Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Selected/>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

 </Worksheet>

</Workbook>

Jan 25 '08 #2

mcdermottmb

Thanks for the reply. Will this work for multi-sheet workbooks too? Would you try to pass PHP variables into the template or write the XML dynamically, inserting values as you go? I am still relatively new to programming and have no experience at all with XML. When I save my workbook to xml, I get about 4500 lines of code. I have been searching for the syntax to insert PHP variables into the XML code and open the resulting document as a workbook but everything I try seems to fail and the data displays in the browser window as raw xml. Any handholding you'd be willing to offer would be greatly appreciated :-)

Thank You

As long as you have the content dispoisiton working which it sounds like you have (as you can do a single work sheet, print out this xml using that.......

BTW i got this by saving an xls file as an xml file. so if the file is static then you can just use that xml as a templat and change whatever you want.

Expand|Select|Wrap|Line Numbers

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>--Author name--</Author>

  <LastAuthor>--last changed by--</LastAuthor>

  <Created>2008-01-25T12:06:27Z</Created>

  <LastSaved>2008-01-25T12:07:02Z</LastSaved>

  <Company>--Company Name--</Company>

  <Version>11.9999</Version>

</DocumentProperties>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>9150</WindowHeight>

  <WindowWidth>11100</WindowWidth>

  <WindowTopX>720</WindowTopX>

  <WindowTopY>270</WindowTopY>

  <ActiveSheet>1</ActiveSheet>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

</Styles>

<Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Panes>

    <Pane>

     <Number>3</Number>

     <ActiveRow>1</ActiveRow>

     <ActiveCol>1</ActiveCol>

    </Pane>

   </Panes>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

</Worksheet>

<Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Selected/>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

</Worksheet>

</Workbook>

Jan 29 '08 #3

mcdermottmb

As long as you have the content dispoisiton working which it sounds like you have (as you can do a single work sheet, print out this xml using that.......

BTW i got this by saving an xls file as an xml file. so if the file is static then you can just use that xml as a templat and change whatever you want.

Expand|Select|Wrap|Line Numbers

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>--Author name--</Author>

  <LastAuthor>--last changed by--</LastAuthor>

  <Created>2008-01-25T12:06:27Z</Created>

  <LastSaved>2008-01-25T12:07:02Z</LastSaved>

  <Company>--Company Name--</Company>

  <Version>11.9999</Version>

</DocumentProperties>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>9150</WindowHeight>

  <WindowWidth>11100</WindowWidth>

  <WindowTopX>720</WindowTopX>

  <WindowTopY>270</WindowTopY>

  <ActiveSheet>1</ActiveSheet>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

</Styles>

<Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

    <Cell><Data ss:Type="String">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Panes>

    <Pane>

     <Number>3</Number>

     <ActiveRow>1</ActiveRow>

     <ActiveCol>1</ActiveCol>

    </Pane>

   </Panes>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

</Worksheet>

<Worksheet ss:Name="--WorkSheetName--">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

    <Cell><Data ss:Type="Number">--cell content--</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Selected/>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

</Worksheet>

</Workbook>

Jan 29 '08 #4

mcdermottmb

lets try this again----- Thanks for the reply. Will this technique work with a multi-sheet workbook as well? How would you go about inserting the variables; writing the XML line by line from PHP or passing variables to an XML page and echoing them? I have tried saving my workbook as XML and uploading to my webserver but it displays in the browser as raw text as opposed to opening up Excel. It is about 4500 lines of code if that matters. I have little to no experience working with XML so any hand-holding you are willing to offer would be much appreciated.

Thanks,
Matthew

Jan 29 '08 #5

harshmaul

490

Expert 256MB

Basically you need to use "content disposition". (i.e modifying some of the headers).

Start with the below code and we can move forward from there...

You can see it work here

and heres the code...

Expand|Select|Wrap|Line Numbers

 <?php

header("Content-type: application/msexcel");

header("Content-Disposition: attachment; filename=worddoc.xml");

echo "<?xml version=\"1.0\"?>";?>

<?php

echo "<?mso-application progid=\"Excel.Sheet\"?>";

?> 

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:html="http://www.w3.org/TR/REC-html40">

 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>harjits</Author>

  <LastAuthor>harjits</LastAuthor>

  <Created>2008-01-25T12:06:27Z</Created>

  <LastSaved>2008-01-25T12:07:02Z</LastSaved>

  <Company>Coders Advocate</Company>

  <Version>11.9999</Version>

 </DocumentProperties>

 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>9150</WindowHeight>

  <WindowWidth>11100</WindowWidth>

  <WindowTopX>720</WindowTopX>

  <WindowTopY>270</WindowTopY>

  <ActiveSheet>1</ActiveSheet>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

 </ExcelWorkbook>

 <Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

 </Styles>

 <Worksheet ss:Name="1st Sheet">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="String">A</Data></Cell>

    <Cell><Data ss:Type="String">B</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="String">C</Data></Cell>

    <Cell><Data ss:Type="String">D</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Panes>

    <Pane>

     <Number>3</Number>

     <ActiveRow>1</ActiveRow>

     <ActiveCol>1</ActiveCol>

    </Pane>

   </Panes>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

 </Worksheet>

 <Worksheet ss:Name="2nd Sheet">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1">

   <Row>

    <Cell><Data ss:Type="Number">1</Data></Cell>

    <Cell><Data ss:Type="Number">2</Data></Cell>

   </Row>

   <Row>

    <Cell><Data ss:Type="Number">3</Data></Cell>

    <Cell><Data ss:Type="Number">4</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Selected/>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

 </Worksheet>

</Workbook>

Jan 29 '08 #6

mcdermottmb

You're the best! Got it working!!! Thanks a ton!

Jan 30 '08 #7

harshmaul

490

Expert 256MB

Your gratitude is appreciated

Jan 30 '08 #8

Similar topics

XSL: selecting columns in Excel XML

by: Axial | last post by:

Question: How to select columns from Excel-generated XML when some cells are empty. I've found examples where rows are to be selected, but I can't seem to extrapolate from that to selecting...

.NET Framework

Is this possible? Session sharing etc.

by: HG | last post by:

Hi gurus Sorry the rather long post... I am facing a real world problem here, and I dunno how to approach it. Don't even know if this is the right group...but since my app is ASP.NET I tried...

ASP.NET

Error occurs when MS Access open Excel document and trigger the assigned macro in Excel

by: Bon | last post by:

Hello all I create a form with three buttons in MS Access 2000. They are Open Excel Template, Save Draft and Save Final. When I click the Open Excel Template button, the Excel template will be...

Microsoft Access / VBA

Excel (in XML format) to a dataset

by: sandeshmeda | last post by:

I have a XML file that is basically a Excel file saved as XML. I need to be able to change the XML into a different format. I was thinking one approach would be to: 1. Populate the dataset...

ASP.NET

Is it possible to write in a excel template using perl?

by: ram venkat | last post by:

Hi, Im new to Perl programming and Im facing a problem while trying to write data into the excel template. Actually the data gets appended to the template (the file size increases), but while...

Perl

Searching Multiple Excel Files & Printing Report in MS Access-Is this possible?

by: Stephenoja | last post by:

Hello Guys, I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end...

Microsoft Access / VBA

XSL ".NET DataSet" to "Excel": Detect missing column

by: ShadowLocke | last post by:

Hi, Im not very experienced with XSL, this is my first run in with it. I found an XSL file that converts a dataset to an excel readable format. The problem I have is when the dataset has a...

XML

Run Excel Template

by: Peter | last post by:

VS2008 ans ASP.NET 3.5, Office 2003 What is the best way to run Excel Template from ASP.NET web page were the Excel is only installed on the client without any ActiveX? If so can someone point...

ASP.NET

Possible to build up an Excel document programmatically?

by: K Viltersten | last post by:

I understand that the new versions of MS Word, MS Excel etc. allow for creation of a document using XML tags (the technique or format is called OOXML, i think). I imagine that the idea behind...

C# / C Sharp

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server