Hi,

Is is possible to access some of the other PHPExcel functions/attributes with this module?

For example, how do you call setLoadSheetsOnly($sheetnames)?

or

getTitle() to get worksheet titles?

or

getHighestColumn()?

Is there a way to access these functions using the module API?

Thanks

CommentFileSizeAuthor
#6 COBie-UK-2012-example1.xls65 bytesgmak
#6 Screenshot_31_05_2013_22_43.png89.42 KBgmak

Comments

wadmiraal’s picture

wadmiraal commented
Status: Active » Fixed

Hi,

The module hooks provide access to pretty much all the export/import functionality you need. It depends at what stage of the export/import you "hook" in.

You can find more info on the hooks here: https://drupal.org/node/1415076

Let me know if you need more help.

gmak’s picture

gmak commented

wadmiraal,

could you give me an example of how to access the sheetname on an import?

I am trying to import an excel file with multiple worksheets (each with unique columns). What I'd like to be able to do is step through the worksheets, and create a node for each of the rows in a sheet where each worksheet writing to a different node type. (hope that makes sense)

Ideally, I'd like to be able to grab the column name (from the header row) and cell value for each row.

Many thanks

wadmiraal’s picture

wadmiraal commented

If you import a file, the worksheet names will be the first keys on the data array. The data array has three levels:
Worksheet (string key)
Row (numerical key)
Column (string key if $keyed_by_header parameter is TRUE)

Isn't that what you want ?

wadmiraal’s picture

wadmiraal commented
Version: 7.x-3.5 » 7.x-3.x-dev
Category: support » feature
Status: Fixed » Active

Oh no, wait. That's not the case... But it soon will be ;-). Wait a few minutes.

wadmiraal’s picture

wadmiraal commented
Status: Active » Fixed

It's on 3.6.

gmak’s picture

gmak commented
StatusFileSize
new Screenshot_31_05_2013_22_43.png89.42 KB
new COBie-UK-2012-example1.xls65 bytes

I must be missing something...

I've got the following:

module_load_include('inc', 'phpexcel');
	
$datafile = drupal_realpath($file->uri);
	
$result = phpexcel_import($datafile);
	
foreach($result as $val){
         dpm($val[0]);
	}

This is clearly loading my test file (see attached), which has 20+ worksheets.

Shouldn't my dpm($val[0]); be spitting out the names of the worksheets, if they are the first keys in the data array?

Also, if I simply call dpm($result) I don't see worksheet names appearing at all. What I get is a series of arrays that are all numbered, but no names (see screenshot).

Do I need something more to get the names to be returned in the data array?

Thanks,

wadmiraal’s picture

wadmiraal commented

Sorry, should have mentioned I also updated the documntation.

phpexcel_import takes 3 params (now). The first is the file path. The second is optional and defaults to true. It keys the cell values with the column header. The third defaults to false (as not to break existing sites). If you set the third one to true, it will key by worksheet name, which is what you want.

gmak’s picture

gmak commented

This is great! I've managed to get pretty much what I need. Two more questions:

1. What is the second parameter (that defaults to TRUE)?

2. Is is possible to access the PHPExcel_IOFactory::identify() function in the module API? I note that the PHPExcel library carries this function which allows you to get the file type from the file and set the Reader appropriately. It would be good to be able to access this so that you can allow users to upload different filetypes (xls, xlsx, csv, etc).

Many, many thanks!

wadmiraal’s picture

wadmiraal commented

The second parameter (if TRUE) makes the import ignore the first row (the "header") and uses these cell values as keys for corresponding cells in the next rows.

So if you have a CSV like:

Col 1;   Col 2;   Col3;
Value 1; Value 2; Value 3

With the second parameter TRUE, it will be imported like this:

array(
  'Sheet name' => array(
    array(
       'Col 1' => 'Value 1',
       'Col 2' => 'Value 2',
       'Col 3' => 'Value 3',
    )
  )
);

If the second parameter is FALSE, it will look like this:

array(
  'Sheet name' => array(
    array(
       'Col1',
       'Col 2',
       'Col 3',
    ),
    array(
       'Value 1',
       'Value 2',
       'Value 3',
    )
  )
);

As for the PHPExcel_IOFactory, it is already used internally for importing files. So you can import all files that PHPExcel supports (xls, xlsx, csv, xml, etc).

gmak’s picture

gmak commented

wadmiral,

First, I want to thank you for your help with my questions. I'm learning a great deal about both PHPExcel and Drupal.

Second, I'm still trying to understand how to access some of the functions of PHPExcel through the module.

In PHPExcel, it is possible to load specific worksheets via something like:

$inputFileName = './sampleData/example1.xls'; 
$sheetnames = array('Data Sheet #1','Data Sheet #3');
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetnames);
$objPHPExcel = $objReader->load($inputFileName);

I'm afraid I don't understand the hook system for the module that would allow me to pass an array of Worksheet Names to the phpexcel_import() function.

Is this possible? Could you provide a quick example of how to access some of these type of functions?

I think if you can give me a basic example, then I can probably figure out the rest (he says, with some possibly false optimism).

Many thanks

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

wadmiraal’s picture

wadmiraal commented

For future reference: #10 was answered here: #2024309: Importing a specific worksheet.

jincy_k’s picture

jincy_k commented
Issue summary: View changes

Hi,

I am using this module to import data and create nodes. I could not read headers from an .xlsx file with leading space in the value.
I tried to trim space using php trim function,but no luck.

The second issue I got now is with reading data from another .xlsx file which is also having the same format as the previous one used for importing (some headers and corresponding column values). The data could not be read like this

array(
'Sheet name' => array(
array(
'Header 1' => 'Col 1',
'Header 2' => 'Col 2',
'Header 3' => 'Col 3',
)
)
);

I wanted the result as a key-value pair array (column header as keys and cell values as values).
Actually it was reading in my desired way (as a key value pair) when I exported one .xlsx file.
When I tried testing with another file with same format using same code, result is not in the expected way.
I used the function
phpexcel_import($path);

and tried by giving the other parameters also
phpexcel_import($path,TRUE,TRUE);
phpexcel_import($path,TRUE,FALSE);

Please help me to fix both these issues. I am using Drupal 7.59

Regards
Jincy

jincy_k’s picture

jincy_k commented

Please give me a solution for this issue.