[Duckington]

Hi,

I need to write a script which reads lots of spreadsheets and does stuff with the data, but the problem is they are all tabbed spreadsheets and I've only ever used the simpler CSV spreadsheets with no tabs.

For example, I would need to take the data on the first tab, do some things with it. Then for all the other tabs, take the title of the tab, then insert/check/etc... data into the relevant table, based on the title of the tab.

Does anyone have any tutorials or class scripts or anything that would be of help with this? Because I have no idea at the moment how to go about it


Thanks.

[codeprada]

If your data is separated by tabs then what's wrong with splitting the data via the \t character?

[Duckington]

Not that sort of tab. I'm talking about the excel workbooks, you know down the bottom you have different tabs, like you can have tabbed browser windows.

[macosxnerd101]

You might want to look at a library like PHPExcel for this.

[Duckington]

Had a look at PHPExcel and am trying to use it at the moment.

I'm having a real problem though with simply the logic of how I'm going to do all this.

Basically, I've been given 55 spreadsheets, each spreadsheet has a number of different workbooks in them (tabs).

I've attached a few examples, because otherwise you won't have a clue what I'm talking about.

Basically, for the first workbook which should be called "Course Details", I need to get the first row into an array, which I can do.

Then I have to loop through all the "Unit" and get all that information into an array (I'll be actually doing things with the data later, but at the moment I'm just trying to work out how to select it all correctly). First problem here is that the spreadsheets vary so much. Some have Unit num, unit code, unit title, unit credits, unit award, unit diploma and unit certificate, whereas others might leave some of those out. So my script I am creating has to try and work out which column is actually what, but the title of the columns is not always written the same.

Secondly, I then have to make the loop of rows stop once it's done all the units. Sometimes below that will be Award details, sometimes it'll say "Btec" or various other things, I need to ignore that and move onto the next workbook, but since the row iterator returns all the unempty rows, I'm not sure how to do that

There are many other issues, but I'll stop there for the moment, since there's no point going onto the other ones until I can work all this stuff out.


The code I have so far, just trying to select the right data, is:

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.6, 2011-02-27
 */

error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once 'PHPExcel/IOFactory.php';






/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
/**  Advise the Reader that we only want to load cell data  **/
$objReader->setReadDataOnly(true);
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load("Btecs/Btec Level 1 Diploma Art and Design (all).xlsx");



$info = array();
$units = array();

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet):

	$worksheetName = $worksheet->getTitle();

	// Loop Rows
	foreach ($worksheet->getRowIterator() as $row):
	
	if(!is_null($row))
	{
	
				
		$rowNumber = $row->getRowIndex();
		$unit = array();
		
		
		$cellIterator = $row->getCellIterator();
		$cellIterator->setIterateOnlyExistingCells(true); // Loop all cells, even if it is not set
		
		// Loop Cells
		foreach ($cellIterator as $cell):
		
			if (!is_null($cell)) 
			{
			
				$cellName = $cell->getCoordinate();
				$cellData = $cell->getCalculatedValue();
				
				switch($worksheetName)
				{
				
					case 'Course Details':
				
					
						if($rowNumber == 1)
						{
					
					
							switch($cellName)
							{
							
								case 'A1':
								$info['TrackingType'] = $cellData;
								break;
								
								case 'B1':
								$info['TrackingLevel'] = $cellData;
								break;
								
								case 'C1':
								$info['TrackingSubtype'] = $cellData;
								break;
								
								case 'D1':
								$info['TrackingCourse'] = $cellData;
								break;
							
							
							
							}
							
						}	
						elseif($rowNumber > 3)
						{
						
							$unitNumCell = "A$rowNumber";
							$unitCodeCell = "B$rowNumber";
							$unitTitleCell = "C$rowNumber";
							$unitCreditsCell = "D$rowNumber";
							$unitAwardCell = "E$rowNumber";
							$unitCertificateCell = "F$rowNumber";
							$unitDiplomaCell = "G$rowNumber";
							
							
							switch($cellName)
							{
							
								case $unitNumCell:
								$unit['UnitNum'] = $cellData;
								break;
								
								case $unitCodeCell:
								$unit['UnitCode'] = $cellData;
								break;
								
								case $unitTitleCell:
								$unit['UnitTitle'] = $cellData;
								break;
								
								case $unitCreditsCell:
								$unit['UnitCredits'] = $cellData;
								break;
								
								case $unitAwardCell:
								$unit['UnitAward'] = $cellData;
								break;
								
								case $unitCertificateCell:
								$unit['UnitCertificate'] = $cellData;
								break;
								
								case $unitDiplomaCell:
								$unit['UnitDiploma'] = $cellData;
								break;
		
							
							}
							
							
							
						
						}
						
						
						
					
					
					break;
				// End Course Details Sheet
				}

			}
			
		endforeach;
		if(!empty($unit))
		{
		print_r($unit);
		}
	}
		
	endforeach;
	
endforeach;


print_r($info);

I am looping first through the workbooks, then in each workbook looping through the rows, then in each row looping through the cells.

A major issue is that because the spreadsheets aren't all formatted in the same way, I can forsee myself having massive if/else statements trying to work out exactly what on earth each cell is for.


I know that's a lot of info to throw at you, but would anyway have any ideas as to how I could proceed? Because I have no idea at the moment, without going through all 55 spreadsheets and basically re-writing them. But in that case I may as well just enter the data into the database manually, rather than do a script in the first place..



THanks a lot if you can help.


Edit: It won't let me uploaded spreadsheets as attachments, so I've linked to them here:

http://www.filedropp...artanddesignall
http://www.filedropp...cengineeringall

This post has been edited by Duckington: 22 September 2011 - 02:50 AM