How to extract data from xlsx file received from API and process it in the code itself?

Question

excel

Hello. I'm developing API auto-testing on node.js and I'm having this difficulty. I have a specific API which returns an xlsx file. But when I try to read data from the body of the response, I get something like this:

?S[Content_Types].xml���n�0E%�J
]TUE`�DzE*�מ
��(�}'�T�V�3w�YM��+�hc�Ű���Ɔy- >f/�(�T0������x4�$��{֢%JR�n�+�b���&f���y.��
5y;�IA��:1=A� ������κ*%g�"�%W���;�*��5�ڄ7,��]���*
�K/s�&[�ǔAlȻ0Q/=�Tй0e�,� da�s�2�*φ��S��d���~,:f8 � �"�V/��?
[���S��;����+栍;2�[email protected]_ ��x�����+��g����;B���/����>������7P傠�Gf?S
_rels/.rels���j�0
�_���8���n/e���h���Ė��-}�������A�B��-ף���q00�jP,�.4�vϳGPY088 ��#eX���4����v1��@+��ζ%���H�t�� ����ajH
����Կ3�U���1�5�� �w�6l��Ld�M��vY��8�/��&�xk��#7�%��t����&��!
�[K�P�P��R�D���'ړ�C�oꙐ>y��PTx���f?SdocProps/app.xmlM��
�0D�~EȽ��ADҔ��A? ��6�lB�J? ����0���ͯ�)�@��׍H6���V>��$;�SC
�GS�b����l�&�e��L!y�%��49��`_���4G���F��J��Wg
~�P�|wؑf?SdocProps/ core.xmlm�]K�0��J�}��C�B�!�@PXQ�
ɱ-6$�n�azard�
�]��9'o�=�}��5f9��� 7m��]��(Da����
!�m]Jǥ���ց�=�<&p�*���8!Av�E�aR�j�1]}K��o�RPzN4D� Ddfn1�R�E���0
�$0�a9#?l�ÿs����/�8������#�w���Yo��K�uyRs�ADP( x<�T �wnv�.h�2��ئ��l�Y�R�_���l}}�
���o&ny.ɟ��OP,�rankf?SdocProps/custom.xml��KO�0� ��
�w׎C� I*��g�W��?do��G%�^F;Z�7#m�zU�����G
�2�����]�9F� �yz������I+�hM�8�%l�_�j��o�1ҍV�Ó�p�l�/\����}N��~���6N>� � �lƢ� |�x��Y��������P��a��f?Sxl/sharedStrings.xml��1
�0��S��6�ZI� [email protected]�������u��@Aϐ�Ⱥ�*����g����V� ��qP��N�R� u��Yw�'��,JM�m�#�*-7��~s�CC�3k�1!�fB%�B��2�Jl�5���2!��I� �!Q�Ԙ3��Y�����v/w�'�D��k�ww�G_��Ґ����6����
xl/styles.xml���n� � �>bop2TQ��P)U�RWb�6*�����ӤS�Nw�s���3 ֐ Z��gH�2P��/X�f?S
���t��(l ��������ҝx�[email protected]$ɀ��}��3c���ʰr`:i��2��w,
�
�T��R#�voc �;c�iE��Û��E<|��4Iɣ����F#��n���B�z�F���y�j3y��yҥ� jt>���2��Lژ�!6�2F�OY��[email protected]�!���G��������1�t��y��p��" n�� ��u�����a�ΦDi�9���%I��9��}���cK��T��$?������`J������7 ���o��f��M|P�[email protected]?Sxl/workbook.xml���N�0D�|��wj��8��J�!Q�o�Mc5���i �|�T��֣y;;r���'��j�@P0Ѻp��~��?���k����Ϣ�!i�sk)���cZőBq��s�| �idB�z���V�Qztn 5�'#v�3�ͧ��o!L�R6�nL��4{ea1S��4t8$�6����~�I
j��p �LГ����]��kg5��[email protected]����jNX��r��Pl5e��f?Sxl/_rels/workbook.xml.rels��Mk�0
�=��{��v �KW\VS��hc���──�)����A��Y���4��+����瘏��Z^F���5FϮ��Pg뢨�f?Sxl/worksheets/ sheet1.xmle�[O�0�˿����@;�$��{�\��:Q�>MU��`��Cg��xzł����a�[email protected])_� ��Ua6�Eқ�l=��ULʦ�yD)������E]��|ˀ9Sm���q
���Q�Yڍ��N�m�$��=m�6Z� T����q��:����9���n��W֤<�D��)l�L�����#_f7Io��7Ȩ����7��_4����
��:-������rҭ�Fѽ�R�%��`�!sP�|/61Y2��(���f�:�v�k�rJ�2��
���� ſ���gr*�o���/��i�9mz�\K�Y�l�
X�b�Q�=�C%�@ʼn=�Y|͞�lz��c6�f �1��0A����v��7�je<�-��t��Ü��ZԌ���-T8tq�j��i6����P��f?S傠�G �[Content_Types].xmf?STx����
�_rels/.relf?S�|wؑ��docProps/app.xmf?S,����docProps/core.xmf?S��a���� docProps/custom.xmf?S��/X��xl/sharedStrings.xmf?xl/styles.xmf?Sl5e��]xl/workbook.xmf?Sg뢨�4� xl/_rels/workbook.xml.relf? S��*�
xl/worksheets/sheet1.xmlPK

�+

As I understand it, I get a zip archive, which is the xlsx file I need, but I can neither read it as excel nor process it as zip. Tell me, how can I read this file in the code itself, extract the necessary data and then work with them?

The API request is sent via axios:

downloadTemplateByModelId(id, authorization = config.mappingCrudOptions.admin) {
    const headers = (authorization !== false) ? { authorization } : {};

    headers['content-type'] = 'application/xml';
    headers['content-disposition'] = `attachment; filename=${id}_product-reference-template.xlsx`;
    return this.get(`/v1/models/${id}/product-reference-template`, { headers });
  }

In the code of the test itself, I simply store all the response received as a result of this method:

response = await mappingCrud.downloadTemplateByModelId(testData[0].Model_ID);

When viewing this API through Swagger, its html code shows:

<a href="blob:https://***/ca043dcb-0971-49b7-a382-3a5c98f1501f" download="1103_product-reference-template.xlsx">Download file</a>

and in itself there is a Download button, by which a normal xlsx file is loaded and opened. When downloading via the direct link " https://*** ....", a zip archive is downloaded, with which you can safely work both with the archive and rename it to xlsx for excel

Answer 1

Because excel is an archive and you need to work with it as with a binary file.
Content-type is usually application/octet-stream