iTop - IT Service Management & CMDB / Tickets / #2110 Unable to export in .xlsx format using API call

#2110 Unable to export in .xlsx format using API call

Milestone: 3.0.2-1

Status: more-info-needed

Owner: Pierre Goiffon

Labels: None

Resolution:

Component: User Interface

Priority: Critical

Version: 3.0.1

Type: defect

Updated: 2022-09-21

Created: 2022-09-15

Creator: Kirushoth Kirubaharan

Private: No

Hello,

When making an API call from iTop, to export an excel sheet (.xlsx), the file seems to be corrupt and not working as intended. The file is created, but we are unable to open (see attachment)

Here is the URL we're using to make the call:
https://cms-sgc.dev.global.gc.ca/uat/web/webservices/export-v2.php?login_mode=basic&charset=ISO-8859-15&format=xlsx&expression=SELECT%20Location&fields=name,floc,id

Please note that CSV and PDF format are working as intended. We're only noticing this issue with XLSX.

1 Attachments

MicrosoftTeams-image.png

Discussion

Pierre Goiffon - 2022-09-15

Hello,
This might be data dependent... Can you share the generated XLSX file ? Did you try with a query giving less results ?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Pierre Goiffon - 2022-09-15

status: new --> more-info-needed
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Hipska - 2022-09-15

Are there by any chance some Locations with special characters not supported in ISO-8859-15?

Do you have the same problem by selecting a single Location with no special characters? Use &expression=SELECT%20Location%20WHERE%20id=<SOME_ID>

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Kirushoth Kirubaharan - 2022-09-15
  
  I have the same issue when filtering based on id (See attachment)
  
  Location Export (2).xlsx
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-15

See attached export

Location Export (1).xlsx

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-15

We've never had this issue in the past. I don't believe this is an issue with the size of data. Like I said, we're able to export the same results in different formats - CSV, PDF

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Pierre Goiffon - 2022-09-16

I was more thinking of a specific data causing the error than a size problem : encoding issue for example (Hipska pointed out the ISO Latin-9 charset for example).

The XLSX cannot be opened nor repaired by Excel.

I tried to find online validators but no luck :( Every URL I found are dated at least 10 years back, and aren't valid anymore :( Seems something can be done with the SDK ?

If I rename the file to .zip, I get an error when unzipping... It might be what is causing the error ?
Zipping again the uncompressed file and renaming the zip file to .xlsx : I can now open the file in Excel !
So yes, really seems the issue is located on the compressing part of the process...

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Hipska - 2022-09-16

Or server updated / configuration changed causing the browser to unzip the content while downloading?

👍
1

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Pierre Goiffon - 2022-09-16
  
  A reverse proxy could indeed, good idea !
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-16

So this seems to be out-of-box issue, if the file is being "unzipped" while downloading. How do we resolve this?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-16

We noticed that on iTop 2.7.4 the export worked as expected. Here are our other configs:
PHP 7.3
mariadb 10.3

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Pierre Goiffon - 2022-09-19
  
  Hello,
  What do you mean "worked as expected" ? You had the issue on another iTop instance ?
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-16

I believe this is an iTop specific issue

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Pierre Goiffon - 2022-09-19

You may try an export directly on the server using the CLI tool ?

The XLSX files are generated using application/xlsxwriter.class.php which uses itself the ZipArchive class.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-20

I was able to export an XLSX file on an iTop 2.7.4 instance. However, on an instance with 3.0.1 installed, I was unable to export in XLSX:
https://cms-sgc.dev.global.gc.ca/uat/web/webservices/export-v2.php?login_mode=basic&charset=ISO-8859-15&format=xlsx&expression=SELECT%20Location&fields=name,floc,id

Last edit: Kirushoth Kirubaharan 2022-09-20

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Pierre Goiffon - 2022-09-21
  
  Hello,
  Please give more details...
  Are you talking about 2 distinct instances ?
  Did you try to run the export using CLI ?
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kirushoth Kirubaharan - 2022-09-21

Yes, I'm referring to two distinct instances - one that had 2.7.4 installed and 3.0.1 installed on the other.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Log in to post a comment.