EXCEL - data - PHPBuilder Forums

EXCEL - data

rid243

Hi everyone,

I know this post is not suppose to be here but none of the other forums are of help. If you can just point me in the right direction I'd appreciate it greatly.

Here's the case:

There are 2 seperate spreadsheets

I need something to go through the unique IDs in the "Datasource" & find a match in the "End" spreadsheet & then insert the number "1" in the corresponding time period (matching Period with Jan-07 or which ever it's suppose to represent).

The Period is written in the following format:

122007 for December 2007
12008 for January 2008

One thing, the datasource has dates for both 2007 & 2008.

The end spreadsheet has 2007 in one worksheet & 2008 in another.

One other thing, basically if there's an ID & timeperiod in the datasource, it means that this person has submitted information so we tick that off in the end spreadsheet with a 1 in the relevant time period.

the bold are titles in the columns in the spreadsheet

thanks!

bpat1434

I think what you need is the ExcelReader on SourceForge.

Some example code (from their SVN repo) is here:

<?php

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('jxlrwtest.xls');
$data->sheets[0]['cells'][$i][$j]; // Data from $i row $j column (A1, B2, C76..)

I'm sure they have more documentation, but that can at least get you reading the Excel sheet. After that, it's just a matter of taking that data, reading the proper column & row and splitting the date string so the last 4 are always in tact, and the first one or two is the month:

$year = substr($data->sheet[0]['cells'][$i][$j], 0, -4);
$month = str_replace($year, '', $data->sheet[0]['cells'][$i][$j]);

// Alternatively, you could use a RegExp here:
preg_match('~^([0-9]{1,2})([0-9]{4})$~', $data->sheet[0]['cells'][$i][$j], $matches);
array_shift($matches);
$year = $matches[1];
$months = $matches[0];

Then it's just a matter of plugging those numbers into the proper area of your other sheet.

rid243

Hey,

Thanks for much that.

I'm a bit confused.. I'm not sure how to fill in the numbers...

The files I'm talking about are in the below URL:

http://ozres.com/excel/analysis.xls
http://ozres.com/excel/workbook.csv

I need to run through the workbook.csv file & fill in the analysis.xls workbook.

So the first one in workbook.csv is: 6713,,122007,SA/NT,0

I need the script to figure out that it needs to find:

1) Detect that 122007 means December 2007 so it needs to go into sheet "2006-2007" of the analysis.xls
2) Find the 6713 in the ID section (which is on row 101), then after this is go across to the corresponding time period (Dec-07 on column Q)
3) The result is the number "1" being written on Q101

Note on point 1: if it read 122008, it would go into the 2007-2008 section. Although the 2007-2008 sheet has 07 on it, it's meant to be 08.

Cheers!

bpat1434

Well, that is application logic.

Something like....

$lines = explode("\n", file_get_contents('workbook.csv'));
$info = array();

foreach($lines as $line)
{
    list($id, $null, $date, $text, $extra) = explode(',', $line);

$year = substr($date, 0, -4); // Get just "2007" from the "date" column
$month = str_replace($year, '', $date);  // Get the month
$info[] = array('month'=>$month,
                'year'=>$year,
                'id'=>$id);
}

Now, you need to either (1) Loop through info inserting data as needed to the proper sheet(s) or do that in place of adding it to the array in the code above.

Short of actually coding this for you (which would cost $$ and is against forum rules) I'm pretty-much at the end. You should have enough to get this going. Just utilize the Excel class from my previous post and their documentation to work out how to add / edit items in the spreadsheet. What I've given you here is how to read the CSV and get the info you want.

Hope that helps.

rid243

Hey,

Thank you kindly for replying!

I didn't manage to find any documentation & I've been looking for a while..

Nothing on how to write to excel files... I've looked through some of the source codes as well but I've had trouble finding it

Can you help guide me find it?

Thanks!

bpat1434

Yeah, their documentation is really lacking... When I came up with the example code for you, I just ended up opening up example.php from SVN and looking at what they did. That file is fairly well documented.

Unfortunately with that package there's no "Save" function. You could look into the PEAR package Spreadsheet Excel Writer.

What others have suggested on other forums for people wanting to read & edit a file is to read the file using the PHP Excel Reader (linked previously in topic) and then edit those values as needed, then use the PEAR Spreadsheet Excel Writer to "overwrite" those changes to that workbook.

Unfortunately there's no "easy" way to do this.