Post by JNeuhoff » Wed Nov 26, 2008 9:38 pm

I have released an updated version of the Export/Import module at http://www.opencart.com/contribution/in ... tion_id/34.

It now supports the 'min_qty' DB field which was introduced in OpenCart 0.7.9, and it also fixes an Import bug for products not belonging to any categories.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by motumba » Thu Dec 11, 2008 8:17 pm

Hi, very useful tool,

I am trying to export and getting an error message that the PEAR.php file is missing

(Warning: require_once(PEAR.php) [function.require-once]: failed to open stream: No such file or directory in F:\public_html\opcart0794-253\admin\Spreadsheet\Excel\Writer.php on line 35)

Is it something that should be part of the contibutions or do I need to install it from somewhere else?

Thanks.

New member

Posts

Joined
Sat Nov 08, 2008 9:30 pm

Post by JNeuhoff » Thu Dec 11, 2008 9:28 pm

You need to install the PEAR libraries for your PHP because the Export/Import uses some of them. See http://pear.php.net/manual/en/installation.php for more details. Chances are that PEAR is already pre-installed on your web host's account.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by motumba » Thu Dec 11, 2008 9:30 pm

Thanks J, I will have a look

New member

Posts

Joined
Sat Nov 08, 2008 9:30 pm

Post by motumba » Thu Dec 11, 2008 10:39 pm

Ok, I installed PEAR and now all works beautifully! - As I said great tool!

Unfortunately, wherever I have product/category names in Greek I get the usual Βιβλία (rather than "Βιβλία") - I 've had that problem before with Excel(2003) and Greek...

Is there something you could perhaps change in the code, or does anyone know any ways around this problem?

Cheers!

New member

Posts

Joined
Sat Nov 08, 2008 9:30 pm

Post by JNeuhoff » Thu Dec 11, 2008 11:24 pm

Unfortunately, wherever I have product/category names in Greek I get the usual Βιβλία (rather than "Βιβλία") - I 've had that problem before with Excel(2003) and Greek...
Can you e-mail me a backup copy of your Greek database tables: 'category', 'category_description', 'product', 'product_description' and 'product_to_category'? Then I can try to replicate this error.

I think it may have to use something like the Worksheet::setInputEncoding method to ensure proper encoding. It's documented at
http://pear.php.net/manual/en/package.f ... coding.php

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by JNeuhoff » Thu Dec 11, 2008 11:50 pm

I just tested it with the Greek word 'Βιβλία' in one of the product's name and description field and exported it to a spreadsheet where it shows up indeed as a 'Βιβλία' because it's an UTF-8 encoded sequence where the Greek characters are encoded in 2 bytes each. The Import works fine, gets it back correctly into the OpenCart database.

So the issue is how to convince the spreadsheet software like Excel and OpenOffice Calc to correctly display UTF-8 encoded characters. Need to look into this.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by JNeuhoff » Fri Dec 12, 2008 12:01 am

OK, try the following changes in file 'admin/controller/export.php' in function 'download':

Code: Select all

	function download() {
		$response =& $this->locator->get('response');
		$database =& $this->locator->get('database');
		$url      =& $this->locator->get('url');
		$session  =& $this->locator->get('session');
		$language =& $this->locator->get('language');
		$languageId = $language->languages[$language->code]['language_id'];
		
		if ($this->validate()) {
			ini_set("memory_limit","128M");
			set_time_limit( 1800 );
			//set_time_limit( 60 );

			// set HTTP response header 
			$response->setheader('Pragma', 'public');
			$response->setheader('Expires', '0');
			$response->setheader('Content-Description', 'File Transfer');
			$response->setheader('Content-Type', 'application/vnd.ms-excel');
			//$response->setheader('Content-Disposition', 'attachment; filename=backup_categories.xml');
			$response->setheader('Content-Transfer-Encoding', 'binary');
			
			// Get all image names and image titles
			$imageNames = array();
			$imageTitles = array();
			$query  = "SELECT image.image_id, image.filename, image_description.title FROM image ";
			$query .= "INNER JOIN image_description ON image_description.image_id=image.image_id ";
			$query .= "WHERE image_description.language_id=$languageId;";
			$result = $database->query( $query );
			if ($result) {
				while ($row = mysql_fetch_assoc($result)) {
					$imageNames[$row['image_id']] = $row['filename'];
					$imageTitles[$row['image_id']] = $row['title'];
				}
			}
			
			// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
			require_once "Spreadsheet/Excel/Writer.php";
			
			// Creating a workbook
			$workbook = new Spreadsheet_Excel_Writer();
			$workbook->setVersion(8); // Use Excel97/2000 Format
			$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
			$boxFormat =& $workbook->addFormat(array('vAlign' => 'vequal_space'));
			$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
			$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@"));
			
			// sending HTTP headers
			$workbook->send('backup_categories_products.xls');
			
			// Creating the categories worksheet
			$worksheet =& $workbook->addWorksheet('Categories');
			$worksheet->setInputEncoding ( 'UTF-8' );
			$this->populateCategoriesWorksheet( $worksheet, $database, $imageNames, $imageTitles, $languageId, $boxFormat, $textFormat );
			$worksheet->freezePanes(array(1, 1, 1, 1));

			// Get all additional product images
			$imageNames = array();
			$imageTitles = array();
			$query  = "SELECT DISTINCT ";
			$query .= "  product.product_id, ";
			$query .= "  image.image_id, ";
			$query .= "  image.filename, ";
			$query .= "  image_description.title ";
			$query .= "FROM product ";
			$query .= "INNER JOIN product_to_image ON product_to_image.product_id=product.product_id ";
			$query .= "LEFT JOIN image ON image.image_id=product_to_image.image_id ";
			$query .= "LEFT JOIN image_description ON image_description.image_id=image.image_id ";
			$query .= "  AND image_description.language_id=$languageId ";
			$query .= "ORDER BY product_id, image_id; ";
			$result = $database->query( $query );
			if ($result) {
				while ($row = mysql_fetch_assoc($result)) {
					$productId = $row['product_id'];
					$imageId = $row['image_id'];
					$imageName = $row['filename'];
					$imageTitle = $row['title'];
					if (!isset($imageTitles[$productId])) {
						$imageTitles[$productId] = array();
						$imageTitles[$productId][$imageId] = $imageTitle;
					}
					else {
						$imageTitles[$productId][$imageId] = $imageTitle;
					}
					if (!isset($imageNames[$productId])) {
						$imageNames[$productId] = array();
						$imageNames[$productId][$imageId] = $imageName;
					}
					else {
						$imageNames[$productId][$imageId] = $imageName;
					}
				}
			}

			// Creating the products worksheet
			$worksheet =& $workbook->addWorksheet('Products');
			$worksheet->setInputEncoding ( 'UTF-8' );
			$this->populateProductsWorksheet( $worksheet, $database, $imageNames, $imageTitles, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
			$worksheet->freezePanes(array(1, 1, 1, 1));
			
			// Creating the options worksheet
			$worksheet =& $workbook->addWorksheet('Options');
			$worksheet->setInputEncoding ( 'UTF-8' );
			$this->populateOptionsWorksheet( $worksheet, $database, $languageId, $priceFormat, $boxFormat );
			$worksheet->freezePanes(array(1, 1, 1, 1));
			
			// Let's send the file
			$workbook->close();
			
			//$response->set($database->exportCategories());
		} else {
			$response->redirect($url->href('error'));
		}
	}
	
That should fix the issue with the Greek characters. They are still being UTF-8 encoded, but the spreadsheet software now knows it too and should correctly display. I tested it with Open Office Calc, and it works fine with Greek words.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by motumba » Fri Dec 12, 2008 5:12 pm

This works perfectly with Greek characters in MS Excel too - Thanks for this addition!

Note to other users of Greek: I also had to change the FCKConfig.IncludeGreekEntities from True to False in /admin/javascript/fckeditor/myconfig.js to have proper Greek characters stored in the database (i.e. φ rather than φ), to display them correctly in the description column of the exported file.

New member

Posts

Joined
Sat Nov 08, 2008 9:30 pm

Post by hm2k » Fri Dec 12, 2008 8:30 pm

You probably want to use \r\n instead of \n for inline returns.

Attachments

???
newlines.png

UK Web Hosting


User avatar
Global Moderator

Posts

Joined
Tue Mar 11, 2008 9:06 am
Location - UK

Post by JNeuhoff » Fri Dec 12, 2008 11:31 pm

I can't reproduce your error. I am using a Linux server, and a Windows-based Firefox browser, with which I downloaded the spreadsheet. I then opened it up in MS Excel 2003. How exactly did you get this newline problem?

Attachments

???
export-import-newline.png

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by Qphoria » Fri Dec 12, 2008 11:52 pm

I've also always had the new line "box". I've just ignored it, but it's always been there
This is with both my local windows server and my hosted linux server

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by JNeuhoff » Sat Dec 13, 2008 12:37 am

If you PM me the login details of your hosted Linux server I can try an Export to see whether this error is reproducible.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member
Online

Posts

Joined
Wed Dec 05, 2007 3:38 am

Who is online

Users browsing this forum: No registered users and 9 guests