0

My app saves the data from the uploaded excel file in Room, after that search in a selected column (by the user ) for keyword then show all row for the cell which has this keyword.

it reads small Excel files successfully but it stuck if I uploaded file more than 2 Mb at this line

Workbook myWorkBook = WorkbookFactory.create(inputStream);

and this error details ..

 java.lang.OutOfMemoryError
Failed to allocate a 40 byte allocation with 32 free bytes and 32B until OOM, max allowed footprint 402653184, growth limit 402653184

I followed this http://blog.kondratev.pro/2015/08/reading-xlsx-on-android-4-and-hopefully.html to code it.


tried Reading line by line:

InputStreamReader inputStreamReader =  new InputStreamReader(inputStream, StandardCharsets.UTF_8);
                        reader = new BufferedReader(inputStreamReader);
                        String line = null; 
                        while((line = reader.readLine())!= null){
                            Log.d(APP_LOGS, line);
                        }

but the result is:

PK��$��O������������������������������xl/worksheets/sheet1.xml��ێ�F�� C��-̠۫U�"�h�M�h�h�F
D: >���ڞ�r3������s!��|��s���T�u��wY&*��������'�:u�i��Rnݟ�v��~�\�~��R6N+P�[��4�����(��^�J�핽�Eܴ���Օ�q�
D: ��>�8+�^a�!j����TȲ�E��㦍_���P+��\�%Z�j߬UJm�ē�Dv��,P�@�~?UO�dզx�����22�{��z�x21n3��}.��+"��������"�����!��"�}/���(0Cdx��6���m�� ��͏�U{��g���ߙ�ԓc��6}S��v�-ݺ�k���_;���INu���dv86�:�N*��)o^T�O�6ǶFV��wu1�tթ'*�����ǜ�Yٿ�����_�
D: ��G�a$0��#x��@|:B�bF0[!��f�Ħ������(n��F���o������V��ڛW�����x������w�4�x�;�yG�w�I6^��L��!]3��/V�`}��M��/�^��W�Ir?6�<�3��%�<_�������'���D�$"�D�o%"˻B>�@��~j2�Y���2ò��nf.̸��F�[�,{��p���%\�,Q���>w��.�B��"f."���tY"����ß�P�gh�Y�h�ܱ�<��܊[�<�L���h�ܱ7�< ߌ=#�cU��rGz|� lKcKzY�����q��-m=���J4T�H�[��-m��U�У�F�j#fK[�lU"�h�Ѹ�(���
D: �*z��h\b�mikm�J�-.7�k��Dz(m�}�������Q�"Y~������ڈ���H���B9�Q���m���P2JC�`(%��d0���!P2J�@�(%C����!P2J�B�P(
D: %C�d(����P2J�B�P(%àd���aP2J�A�0(%àdB(�J&��   �dB(�J&��   �dB(�J�C�p(%ád8����P2J�C�p(%#�d����P2JF@(%#�d���h��ח���揪���뿆�=և���7մ�[��t�J5R��n�2N�I.�M��:�w�U
D: ���z�T9Jg�l�_6�n�i�ĕ���:�d����,ݺ�[ڇ5?���PK<�r�����������PK��$��O������������������������#������xl/worksheets/_rels/sheet1.xml.rels��M��0������Y���t[�[���m,��޾^t`
D: ]�RO����,��q�)x��@���M~0p�7[P��;��gwط͉g��F�)�*�c�q�(v�D�Ⱦl���eLF�?���_�S��tt5���?v����!���>�y�.�Z����ـֿ���EF��Lj]|������PK�j�������3����PK��$��O������������������������!������xl/drawings/worksheetdrawing1.xml��]n�0��U�iZC^�N0�%n�����~�J6i{m�?���nt��Db|#�z��]#�o��(8��`��F\��n��5�ϼ�"�{^��}��ZJV=:�2�Ӵ
D: � ��:�   �IvVΫ�E��{ĸ�&�������Mׄ�5
D: �A��8!�b��f଩�Q=P���3��6�*��)�HB�<    8����R���_���O�,�Czȇ�&^��eFwh��ȸ8��ݱ*�6�(+l�^ޭ̳"�_PKbi���������PK��$��O������������������������������docProps/app.xml�RAn�0|A� �SJ��0(��"�5`'=o��E�"   �V���R2��
D: z�m83��+Ld��E�*E�ބ��C-��_n>��|.x��  I��wj�B���� �j�1ǵ�d:�VY�YiC��1dh[k�!�_=z��ey'���ln�(Ή��7�   f�G��S�yZmB���V;7Y�-8B%_    ��0�{6�V�4R�ǤZ�,x�#����V�mgv�.'�#����\� ^z/����ɐ��Q.E2����쐾�[H�����s�J\t܍��7�����j�=�}x���yvפ�u���_��v!�cn����t��̞�����8���fM
D: Z.>
D: :%/���|�]]ݭ��e9fN��5���PK���k���������PK��$��O������������������������������docProps/core.xml���n�0��`�@zm����&n�j&K�ewM9h3Z��y�T������ے-����:��ф�hJ��9zݬ�9����u�!G8�(�2a�h,<�ƀ�\D�1ar���0��؁�.  �a�X�}x�[l���[�BfX��%���،FtR�bT���A)0Ԡ@{�iB���*ws��\�J���M�����l�6i�
D: �)~_?�[���J��*�S&,pe�8���*&��ǔ�$ݐ9#)��>2�g}/<>7�X�Ø�G�plm׳c��K͝_��$��&Ʋ����m�,�^�=�a����PK�V}�6����l����PK��$��O������������������������������xl/theme/theme1.xml�Yˎ�6�����D�-�1�'�3i3��I�,i��S�@R3c�d�M�i�M��(�h���cHЦQ�a���$m�>���X$Ͻ<��<W�\�rp��4�Z�K�P����'a׺s2�k[����u�9�֕��.�}�i��}ص"!�}����MQ"Ǧ��P�&��3�6&v�q�vqbU��u��t�}4�~�D�N"PH�<�)�@c��f'9A�`AuDPn����c��Z�`V���r!��S|,p
D: Iג�� :�}p�^a�Ͷ���Y��c�$7,p����������F�Qs���@ߗ+�m`�~�?�*�*/

How to solve it, please ? and how to read sheet by sheet from the same file ?

thanx

Tarek
  • 146
  • 3
  • 15
  • Most likely, it is not solvable. Your app is attempting to use 400MB of heap space. You will not be able to do that on many devices. Talk to the developers of whatever library you are using and see if they have any options for reducing the heap usage. – CommonsWare Nov 09 '19 at 20:26
  • Then you are using [Apache POI](https://poi.apache.org/). [This page](https://poi.apache.org/help/index.html) describes how you can get help in using that library. – CommonsWare Nov 09 '19 at 20:53
  • first of all, this is not a tutorial! secondly, it's 4 years old. be careful, it sounds really hacky and messy. it's basically an instruction from an unsecure website to copy huge amounts of code, that you don't know into your project. and to get around the limit of 65k methods. Nowadays you would never copy huge libs into your project anyways, you would just import them through maven or similar. – nulldroid Nov 09 '19 at 20:55
  • @CommonsWare thank you, i will check it – Tarek Nov 09 '19 at 21:06
  • @levdev sure, could you suggest one of the best libs to read Excel files? – Tarek Nov 09 '19 at 21:07
  • Depends on your use case. Do you just need to read data or macros as well? – nulldroid Nov 09 '19 at 21:11
  • @levdev the app saves the data from the uploaded excel file in Room, after that search in a selected column (by the user ) for keyword then show all row for the cell which has this keyword – Tarek Nov 09 '19 at 21:18
  • then you would be much better of, reading line for line, like a csv-file. Maybe write your own function instead of using a huge library. It shouldn't be too complicated, see this example: https://stackoverflow.com/questions/16672074/import-csv-file-to-sqlite-in-android – nulldroid Nov 09 '19 at 21:25
  • Thank you bro, I will give it a try :) – Tarek Nov 09 '19 at 21:28
  • @bitjuggler recently I tried to read line by line but the result is strange letters, please check the update for the question. – Tarek Jan 10 '20 at 21:48
  • 1
    hey you can try this https://github.com/kiranbjm/xlsxparser/tree/master – Kiran Benny Joseph Jan 26 '20 at 09:45

2 Answers2

2

Check this library for parsing large xlsx files. Full doc is here

just add

repositories {
        maven {
            url "https://jitpack.io"
        }
    }

And

implementation 'com.github.kiranbjm:xlsxparser:1.01'

to your app level gradle file

and you can use it with

InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx"));
Workbook workbook = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .open(is);            // InputStream or File for XLSX file (required)
Kiran Benny Joseph
  • 6,755
  • 4
  • 38
  • 57
0

Try adding android:largeHeap="true" to your <application> tag in AndroidManifest.xml. However, if this is for an app distributed to users, you should consider alternative means. Most people will not appreciate your app taking up all of their device's memory. Better would be to use a server-based solution to parse the file, and let the query only the data it needs to display the result.

Chris
  • 1,180
  • 1
  • 9
  • 17
  • I added it already but also not solved this issue, could you explain more about "a server-based solution", please ? thank you – Tarek Nov 09 '19 at 20:40
  • If it's still not working, it's because your Excel file is just too large. A server-based solution would have a server that parses the xml file, and the app connects to it and requests only the data it needs. It's hard to give details about what that would look like since you don't say how you are using this Excel file. – Chris Nov 09 '19 at 20:43
  • Sure, but that tells me nothing about your Excel file. Are these files that the user selects for converting, or that you have control over? What kind of data is in the files and how are you displaying it? – Chris Nov 09 '19 at 20:52
  • the app saves the data from the uploaded excel file in Room, after that search in a selected column (by the user ) for keyword then show all row for the cell which has this keyword .. – Tarek Nov 09 '19 at 21:09
  • Then your server should allow for Excel uploads, save the data to a database, and the app should be able to query the data on the server using a search query. If there's a lot of data, use paging to limit the amount of data requested – Chris Nov 09 '19 at 21:23
  • If I used Firebase server to upload the file, how can I read it's data to continue this procedure ? – Tarek Nov 09 '19 at 21:27
  • That's kind of beyond the scope of this question. Depending on what your server programming language is, you'll need to find an appropriate Excel parsing library to handle the uploaded file. – Chris Nov 09 '19 at 22:35
  • Excuse me bro, I am beginner in Android and Java, don't know too much about servers..could you give me an example or tutorial to this way ? thank you – Tarek Nov 12 '19 at 17:32
  • That's a big topic and not related to Android. There's a lot of different kinds of server implementations. But here's some tutorials to get started using Java: https://www.baeldung.com/spring-boot – Chris Nov 12 '19 at 17:48