/    Sign up×
Community /Pin to ProfileBookmark

Converting Data to Excel

Hi,

I have a customer with a website who wants to be able to have employees who are out in the field fill out their time sheets online. He currently uses Excel for the timesheets. The server is Linux, so the code would have to be written in PHP or PERL.

I have done very little PHP programming, and that was a few years ago. Could someone give me a general outline of what would be needed to convert his excel spreadsheet to a form that could be filled out online and then take that data and convert it back to an excel spreadsheet that he can work with to run payroll?

Thanks in advance for any suggestions!

Jen

to post a comment
PHP

6 Comments(s)

Copy linkTweet thisAlerts:
@SrWebDeveloperMar 01.2010 — I'm not going to sugar coat it for you, this isn't impossible but if you want to directly read/write Excel files using PHP there are only two solutions I recommend -- and really not novice level. OOP knowledge is a plus.

First, a project for simple Excel documents (does not support functions, styling cells and other advanced formatting in Excel) which is easy to use and stable:

PHP-Excel

Next is an excellent series of classes I've used which handles the advanced features with ease and is extremely powerful engine using Microsoft's OpenXML and PHP.

PHPExcel (without the dash)

This is one of things where customizing someone else's code is the way to go, even for experienced developers. The whole concept of sharing open source classes is what makes PHP so attractive, anyway.

I forgot to mention that with most of these scripts you initiate a class object to import the legacy spreadsheet, then the cell data is available to you as multi-dimensional associative arrays or XML for manipulation using your own code in combination with wrapper class methods. Then you invoke other methods to import, thus saving the updated spreadsheet. The second linked script I provided supports numerous Excel formats.

Warning: Some people here might post example code such as the tutorial found here which outlines how PHP actually generates the spreadsheet cells the proper way (which isn't hard). That's all good and fine for simple projects, but such code will require extensive re-working if your client expects more. And they usually do. Not a good use of your time considering your lack of experience with PHP, in my opinion.

-jim
Copy linkTweet thisAlerts:
@jen518authorMar 01.2010 — Hmmm, thanks but it looks like I'm going to have to outsource it.

I was "hoping" that it would be as simple as having him convert it to .csv and upload it, then use PHP to read the .csv file and output to html form where the employee could fill it in. Then php could do calculations and write back to the .csv file that could be downloaded and converted back to Excel. I guess the problem seems to be the formatting, since any formulas could be dealt with on the server prior to writing back to the .csv file.

. . . however, I'm sure I'm not seeing the whole picture because of a serious lack of experience with all of this, SO ...

I guess I'll start looking for someone to outsource it to.

Thanks very much for your insight.

jen
Copy linkTweet thisAlerts:
@NogDogMar 01.2010 — Depending on exactly what you need to do, it may be that outputting the data as a CSV (comma-separated value) file may be all you need. Such files are easily read by Excel (and other spreadsheet programs), but you lose the ability to embed formulas or styling. If this is satisfactory, the [url=http://php.net/fputcsv]fputcsv[/url]() function can be used.
Copy linkTweet thisAlerts:
@SrWebDeveloperMar 02.2010 — PHPExcel supports output formats of CSV, HTML and even PDF along with ExcelML directly - and it's 100% object oriented and very easy to use in terms of OOP scripts. It just a PHP developer needs to know basic OOP in order to use it without getting too lost. It's easier to learn and use classes and methods written by others than it is to code it from scratch and hope the limitations don't piss off the client (in the future, if not now, i.e. scalability). My .02
Copy linkTweet thisAlerts:
@jiabanMar 02.2010 — [url=http://tipard.com/mts-converter.html]Tipard MTS Converter[/url], simple to operate and multifunctional MTS file Converter, allows you to convert MTS video to AVI, 3GP, WMV, FLV, MP4, MPG and all other videos that you need. The MTS Converter multiple inputting formats would surprise you much, like convert TS, M2TS, Tod, Mod and other video files to your devices or upload to Internet for sharing with friends. Furthermore, This MTS file Converter adds the highlight to create music from videos and audios for your music listening.

[img]http://tipard.com/guide/mts-converter.jpg[/img]

This converter also has several features. Take 5 of them for example:

  • 1. Full and accurate support for HD videos and general videos

    Tipard MTS Converter supports broad HD videos and general videos, like convert MTS to other video/audio formats that your player compatible, convert TS, M2TS, MP4, 3GP, AVI, WMV, FLV, SWF, MOV, and something else.


  • 2. Ability to convert video or convert audio to music

    Get music from videos, and convert among AAC, AC3, AIFF, AMR, AU, FLAC, MP3, M4A, MP2, OGG, WAV, WMA audios.


  • 3. High level in video editing capabilities

    Create different video effect by adjusting the Brightness, Contrast, Saturation and Volume of the video.


  • 4. Powerful solution to trim, crop and merge video

    Provide the simple to operate way to trim you need segment of the MTS file, crop the video play region to suit your players, and merge several video clips into one file for your successive enjoyment.


  • 5. Personified ouput video/audio settings

    Easily set the video Encoder, Resolution, Frame Rate, Video Bitrate. You can also directly input your own resolution as the form of "XXX*XXX"; set the Audio Encoder, Sample Rate, Channels, Audio Bitrate. You also can save all the output settings as your preference, which is saved in the user-defined column automatically.
  • Copy linkTweet thisAlerts:
    @jen518authorMar 02.2010 — Thank you NogDog and SrWebDeveloper for your suggestions. I'm going to play around with it a little bit to see what I can come up with once I get more details of his requirements, which for now don't seem very complex. He does have formulas in there that would need to be calculated prior to outputting to .csv again, but it's basic totalling hours per job, per day and per week.

    When I tried converting the excel to .csv and then converting back, it wasn't a pretty site. ? But maybe the worksheet design could be modified so that it's more legible in .csv format. I do know that I'm not qualified to do any OOP so that route would have to be outsourced.

    Thanks again.

    jen

    p.s. Hopefully a moderator can remove the spam message posted by jiaban.
    ×

    Success!

    Help @jen518 spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 BETA 5.19,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,

    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,
    )...