Skip 2 first lines in Excel

Hello,

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

We currently don’t have a built-in way to skip the first two lines when using an Excel source file, as you mentioned. The first row of the sheet needs to contain column headers. As noted in the documentation, Excel files with merged cells or blank first row may cause errors like “one or more columns does not have a header name.”

We’ll explore a custom workaround with our developers, but we can’t guarantee a solution or timeline. If we find a method, we’ll share it with you.

In the meantime, if you have coding skills, please refer to our “developer’s handbook.” It’s a new section with examples of hooks, actions, and filters, and more examples will be added over time, so check back periodically.
Please note, our Support does not provide custom solutions with hooks, it is limited to only advice.

Thank you.

• This reply was modified 1 month, 3 weeks ago by wpDataTables.
• This reply was modified 1 month, 3 weeks ago by wpDataTables.

Thanks for your answer.

I did looked at the “developer’s handbook.” But the only filter I see for Excel table is wpdatatables_filter_excel_array. As mentionned before, I did try using wpdatatables_filter_excel_array filter but this filter is run to late in the process.

Any filter that run as the Excel table is loaded ? Could you check with the developers please.

Yes a custom workaround (snippet) would be welcome.

Hello,
We spoke with our developer Team about this, they advised.
We don’t currently have an available hook which could achieve this.
If you wish to do this for every source file, in our Lite Plugin version you could do a code edit in this Plugin PHP file :
..\wp-content\plugins\wpdatatables\sourceclass.wpdatatable.php\class.wpdatatable.php
Under the line 1770 :
$objWorksheet = $objPHPExcel->getActiveSheet();

Add this under that line :
$objWorksheet->removeRow(1, 2);

Our developers think that should work to achieve what you need, we just can’t guarantee it so let us know if it works as intended for your source file(s).

If you wish to achieve this via a hook, you can test adding this in your filter, instead of your line :
$objWorksheet = apply_filters(‘wpdatatables_before_get_excel_headers’, $objWorksheet, $wdtParameters)
and write a function with this line above. You can let us know if this works for your use-case, then if it does, our developers will work to add this hook in one of our upcoming updates.

I hope I managed to pass the explanations from our developers in a clear way, but let us know if any point is unclear.

Thank you.

• This reply was modified 1 month, 2 weeks ago by wpDataTables.

Hello,

Sorry for the delayed response.

It took me more time than expected to test this. I finally realized that applying the code to all tables was creating a PHP fatal error. So I had to limit this for only the tables were there is 2 extra lines before the fields header.

In order to get the TableID, I did change the hook to be added under the line 1770 to:

$objWorksheet = apply_filters(‘wpdatatables_before_get_excel_headers’, $objWorksheet, $this->getWpId(), $xls_url);

With this hook, the following code snippet does work fine :

add_filter(‘wpdatatables_before_get_excel_headers’,’releve_de_compte_skip2lines’, 10, 3 );

function releve_de_compte_skip2lines ( $objWorksheet, $tableId, $filePath ) {

????????????? $myobjWorksheet = $objWorksheet;

????????????? if ( $tableId == 102 || $tableId == 103 || $tableId == 104 || $tableId == 105) {

????????????? $myobjWorksheet->removeRow(1,2);

????????????? }

????????????? return $myobjWorksheet;

}

As you mentioned, please have your developers add the following hook under the line 1770 in one of your upcoming updates :

$objWorksheet = apply_filters(‘wpdatatables_before_get_excel_headers’, $objWorksheet, $this->getWpId(), $xls_url);

Thanks for your great help on this matter.

Hello again,
Thank you for sharing what you found with us and what you changed/edited in the function/hook, to make it work on your Site by adding the table ID parameter.

We will pass all these details to our developer Team. They will do their best to work on adding this hook in one of the upcoming updates.
We just can’t say an ETA because our QA Team also needs to do a lot of testing to ensure there are no negative effects before it gets added.


We will let you know if our devs require any additional detail from your end to clarify any point, etc.
Thanks again.

Hi,

I am waiting for the new hook “wpdatatables_before_get_excel_headers” before updating the plugin. So I do not have to modify the code every time I update.

You have been updating the plugin many time lately (See list at the bottom).. Can you verify if one of these include the new hook ? If not could you give me possible ETA.

Thanks

—————-

3.4.2.32

• Minor CSS fixes.
  Other small bug fixes and stability improvements.

3.4.2.31

• Compatibility with WordPress 6.7 version approved.
• Updated page Lite vs Premium.
  Other small bug fixes and stability improvements.

3.4.2.30

• Fixed issue with “Return to the Top” feature.
  Other small bug fixes and stability improvements.

3.4.2.29

• Added an Activation tab to Plugin settings.
• Improved newsletter subscription.
  Other small bug fixes and stability improvements.

Hello,
You are right, we did some new updates in the meantime, but this hook is still not added.
Our developers are working on it, though, so they will do their best to add the hook in one of the upcoming updates, we just can’t guarantee an exact ETA on it.

You can keep following the ChangeLog – the developers will add a specific patch note when this hook gets added.

Thank you for understanding and for all your patience.
Kind regards

• This reply was modified 1 week, 3 days ago by wpDataTables.