PHP writing to MS Excel

herbert_tan

1) I have faced some problems when using PHP to create MS Excel files and adding data to the file.

It will give me an error "Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft Office Excel Description: Cannot access read-only document '_eqx-2006-03-03.xls'.' in C:\Attendance\createFile.php:66 Stack trace: #0 C:\Attendance\createFile.php(66): variant->SaveAs('C:\Attendance_...') #1 {main} thrown in C:\Attendance\createFile.php on line 66"

Below is a copy of my code.

//create a new excel file
$xls = new COM("Excel.sheet") or die("Did not connect");
$title = $POST['Title'];
$date = $POST['DateStart'];
$workbook = "C:\Attendance_" . $title . "-" . $date . ".xls";
$wkb = fopen($workbook,'w+') or die("Failed to Open Workbook");
$sheet = "_" . $title . "-" . $date;
echo $sheet;
$sheets = $xls->application->Workbooks->Open($workbook)->Worksheets($sheet);
$sheets->activate;

$cell = $sheets->Cells(2,2);
$cell->activate;
$cell->value = 15000;
$xls->Application->ActiveWorkbook->SaveAs("C:\Attendance_" . $title . "-" . $date . ".xls");
fclose($wkb);

I don't know what is wrong with my code. How to make it not a "read-only document "?

2) I have some output from a barcode reader which will display in the textbox on my broswer. What can i compare the output line by line with my data inside my Excel file?

For example,
I got 10 names displayed in the text box, and I want to compare the name 1 by 1 with the data in my EXcel file and marked as "present" in the same row as the name.

Hope i have explained clearly. I am new to PHP, hope anyone can help me with this.

Thank you.

tsinka

Hi,

what's the reason for the
$wkb = fopen($workbook,'w+') or die("Failed to Open Workbook");
line in your code ?

fopen locks the file for writing so your com access to the file will fail because of the lock (the file is locked until fclose is called at the end of the script).

Thomas

herbert_tan

Hi Thomas,

I put in this line of code to create a new file. Or is there any method on how i should create a new file and add data into the file?

Oh... I am not aware that fopen locks the file for writing. I am new to PHP.

How should i go about writing it?

Thanks,

Herbert

tsinka

In this case the file gets locked because you're opening in "w+" mode.

I think a better way is to check if the file exists at all with the php file_exists function. If it exists than open it and work with it. If it doesn't exist then create a completely new document using your COM object and finally save it as a new file. Don't use fopen and fclose.

Check out the comment of a dot kulikov at pool4tool dot com in the php manual. The comment is about something else but it also shows how to create a new Excel document.

Thomas

herbert_tan

Thanks Thomas.

I have managed to create the file successfully without errors following the methods you taught me and solved my first problem.

I still got another problem that is I have a few lines of data in a text box, how can i compare the data line by line with my data in the Excel file.

For example,
I got 10 names displayed in the text box, and I want to compare the name 1 by 1 with the data in my EXcel file and marked as "present" in the same row as the name.

Can someone please help me.

Thank you.