Filling Word Doc from Mysql Database Using Php

PHP
1

Good day Mr. Moderator.
I have a challenge presently. I have a project I’m working and I’m provided with a template by my client ( a word document template with spaces like name, address etc to be filled in from the database or web form. I have worked on getting the info stored in the database but the challenge is how do I get the info from the database to fill in the placeholders in the word document so that they can download it and print it out. Any advice or suggestion will be highly appreciated. Thanks a lot.

2

Maybe use a PDF instead?
You could then create the PDF dynamically using a library such as Zend_Pdf and populate it with the relevant information from your database.
Here’s a tutorial on PHP master to get you started: http://phpmaster.com/generating-invoices-with-zend_pdf/

3

I agree with @Pullo ;

I was part of a maintenance team that had to support a web application that filled out Word documents about a year ago. The problem we ran into was pretty big, first Word is the only Office product by Microsoft that does not support multi-threading. Secondly, getting the instance to close properly and consistently was a nightmare, so we ultimately ended up with multiple instances that would all take up large amounts of memory, because Word was never designed in a Server scenario. Lastly, the changes that occurred between versions to the WordML and the likes made it a maintenance nightmare as you tried to support multiple versions.

If you can talk the client into using a PDF, there are some nice PDF frameworks out there. fpdf is the one I consistently use.

4

More than just Word I’m afraid. IMO, whatever MS Office is built on is garbage. When running more than one product in the Office line, if one of them hang, they all hang, even Microsofts built in clock / calendar on the task bar.

Probably wrote in VBA?

Stay far far away from MS Word if possible. PDFs are the way to go.

5

No, it was written in .NET using the API’s provided by Microsoft for handing Office applications. Nonetheless, I would never recommend interacting with a Microsoft Product via a web application (or desktop for that matter) ever again (not that I have recommended it).

6

@cpradio ; Ditto

7

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-examples-tools-with-wordexcel.html

I don’t know if that would work for the online version of office

8

Why not just create a styled web page to display the information? They can print that directly and you wouldn’t need to use any proprietary software, or download any files.

9

Thanks a lot everyone who have taken their time to respond to my question. I think pdf is the way to go and I’ve studied fpdf within the last 2 days to see how easy it’ll be to implement. Though the tables making up the document is quite complex but I should be able to get it done. My other worry is how to fill in the spaces in the document with result from the database. I hope fpdf supports that. Thanks a lot

10

This is what I usually do with fpdf

  1. Download fpdi
  2. Take the existing word document or template and turn it into a PDF (especially if it only contains the static content)
  3. Using fpdf with fpdi, load the template and then write whatever text you need on top of the template to produce the final PDF

Sample code:


  require_once('pdf-templates/fpdf.php');
  require_once('pdf-templates/fpdi.php');

  $pdf = new FPDI('P', 'pt', 'Letter');
  $pdf->AddPage('P', 'Letter');
  $pdf->setSourceFile('template.pdf');
  $templatePage = $pdf->importPage(1);
  $pdf->useTemplate($templatePage, 0, 0, 0, 0, true);

  $pdf->Image($itemData->qrcodeAbsolutePath, 490, 640, 0, 0, "png");

  $pdf->SetFont('Arial', 'B', 28);
  $pdf->SetTextColor(159, 31, 32);
  $pdf->SetXY(28, 180);
  $pdf->MultiCell(0, 28, $boatDescription, 0, 'C', false);

Hopefully this should get you started. I’ve had great success with this route.

11

Thanks so much Cpradio for your response. I will surely try it. Please can you put me through on the procedure of displaying the results from my mysql database in the resulting pdf and how to mail it to the concerned client right away. I know how to extract from the database but the challenge is to how input it into fpf and create the final pdf document. I understand how cell work in fpdf but I’ve not seen any example on how to use result from the database in fpdf. Thanks in advance

12

Sure, if you look at my prior code example, just replace $boatDescription, and the other variables with the $row[‘column’] of your MySQL result and you’ll be good to go.

As for how to e-mail it, that part requires a bit of work.

  1. First you will need to call the Output method using the “Save” parameter http://www.fpdf.org/en/doc/output.htm
  2. Then you will need to either a) create a mail framework that works with attachments, or b) find one that allows you to easily add attachments. I suggest PHPMailer
13

Thanks so much Cpradio. Your input has been of immense help. I’ll work on it.

14

I see you seem to have settled on generating a PDF (correct choice) but if anyone is interested I did lots of work with WordML many moons ago so know its possible - http://www.codeproject.com/Articles/14854/Learning-WordML-Basics

15

Thanks Richard, I’ll chek out your work as well. I just want to get through with this load in front of me first.

16

Whoops, sorry I wasn’t too clear.

I didn’t create the linked to content, just found it useful before

17

Disclaimer is that I work for a company that sells one of these, but nonetheless I want to share my experiences because I have been confronted with this question so often.

Using Invantive Composition (http://www.invantive.com/products/invantive-composition) you can fill Word documents (letters, legal pleadings, contracts and other things with flexible conditions) with data from a supported database platform. Currently these are Oracle, MySQL, Teradata, DB2/UDB and SQL Server). And then fully change the contents at will manually. It is intended for non-technical users. The central webservice proxies the data requests for the backend databases. Allows nested repeating groups of data (such as: “order with order lines”).

In the past, JasperReports (http://community.jaspersoft.com/project/jasperreports-library) has been used a lot to generate letters using the RTF output of JasperReports (designer tool is named iReports). It is free and works fine as long as you do not want to edit the output more than a few words and have some Java development skills. It integrates well in various websites but you need to integrate Java runtime. Just as Invantive Composition it works fine for large numbers of different reports.

As long as you can control the environment completely, you can also consider using RTF as intermediate language (not for end-users, only real developers). Save document as RTF, replace parts of the text you need to be replacable, write a webservice that accepts the parameter and dumps back the resulting RTF. Takes some time to generate more complex tables (tables are obviously something invented by the human race after the RTF specification was written :slight_smile: This approach only works with very limited number of templates and when you have sufficient developer time available to get it up and running and stabilized.

Sometimes it is also possible to use XML (docx is actually a zip with xml in it). Using the RTF approach. This one is harder upto very hard, because for instance Word adds XML-tags anywhere in the text. You really need to write a multi-stage parser for that to do it reliably.

I think when you need just a few templates and want to save out-of-pocket costs and have sufficient time, I would go for RTF in your case.

18

Thanks man. I actually used fpdf as it’ll ease the pain of printing as well. I appreciate you

19

You are welcome.