How do I read excel file in assets?

Question

In the code below I'm trying to read a xls file and do whatever is necessary classes through the JXL api, but when converting to Workbook.getWorkbook (dbInputStream) exception occurs

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import android.content.Context;

public class ReadExcel {

    public static List<ChaveEloCoordenada> read(Context context) {
        List<ChaveEloCoordenada> list = new ArrayList<ChaveEloCoordenada>();
        try {
            InputStream dbInputStream = context.getAssets().open("file.xls", Context.MODE_WORLD_READABLE);

            int cols = 9;
            Cell[] row;

            Cell cell;

            Workbook w;

            ChaveEloCoordenada chave = null;

            w = Workbook.getWorkbook(dbInputStream);//error here

            Sheet sheet = w.getSheet(0);

            for (int r = 1; r < sheet.getRows(); r++) {
                chave = new ChaveEloCoordenada();
                row = sheet.getRow(r);
                if (row != null) {
                    for (int c = 0; c < cols; c++) {
                        cell = sheet.getCell(c, r);
                        if (cell != null) {
                            if (c == 0) {
                                chave.setBarramento(cell.getContents());
                            } else if (c == 1) {
                                chave.setCoordX(cell.getContents());
                            } else if (c == 2) {
                                chave.setCoordY(cell.getContents());
                            } else if (c == 3) {
                                chave.setPlaca(cell.getContents());
                            } else if (c == 4) {
                                chave.setTipo(cell.getContents());
                            } else if (c == 5) {
                                chave.setSe(cell.getContents());
                            } else if (c == 6) {
                                chave.setAlim(cell.getContents());
                            } else if (c == 7) {
                                chave.setElo(cell.getContents());
                            } else if (c == 8) {
                                chave.setTipoElo(cell.getContents());
                            }
                        }
                    }
                    list.add(chave);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return list;
    }

}

Any idea how to fix this problem?

Thanks!

Edit

Below is the exception

java.io.IOException
at android.content.res.AssetManager.readAsset(Native Method)
at android.content.res.AssetManager.access$700(AssetManager.java:36)
at android.content.res.AssetManager$AssetInputStream.read(AssetManager.java:571)
at jxl.read.biff.File.<init>(File.java:91)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at jxl.Workbook.getWorkbook(Workbook.java:253)

If I put the above code in a java project works normally!

What exception are you getting. Can you provide more details of the problem? — Seb Boulet, Oct 10 '12 at 21:49
I edited the post, take a look and see if it helps. Just get this exception! — Everton Barros, Oct 10 '12 at 22:19
Are you able to get the assets correctly? Are you calling ReadExcel.read from an activity? Does the following returns non null? AssetManager mngr = myContext.getAssets(); and where are you keeping the .xls files? I hope you do it in "assets" directory and not inside res — Durairaj Packirisamy, Oct 10 '12 at 22:24
Well, let me explain the problem better. I have an xsl file which has more than 50 thousand record what I need to do is that when changing the version of the database from 11 to 12 for example, must run in the method @ Override public void ONUPGRADE (SQLiteDatabase db, int oldVersion, NewVersion int) { ... } an algorithm to read these file and insert records in the database. My class that creates the database is extending the SQLiteOpenHelper — Everton Barros, Oct 10 '12 at 22:41
Does this help: http://stackoverflow.com/questions/9544737/read-file-from-assets — Kev, Oct 11 '12 at 22:19

score 2 · Accepted Answer · answered Oct 15 '12 at 23:16

Thanks to everyone who tried to help me!

The problem was that the file I was trying to read exceeded the size supported by the android. The excel file had 9.4 mb and it seems the android files read only a little more than 1MB in the assets folder.

So the solution I found that maybe was not the best, manually copy the data from xls file to a txt file, compress this file with gzip and then read the compressed file and then read the txt file and thus save all information I needed (over 50,000 records) in the database! Follow the code below:

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
           scriptChaveEloCoordenada(db);        
    }

public void scriptChaveEloCoordenada(SQLiteDatabase db) {

        List<ChaveEloCoordenada> list = ReadExcel.read(context);

        for (ChaveEloCoordenada chave : list) {
            db.execSQL(insertChaveEloCoordenadas(chave.getBarramento(), chave.getCoordX(),chave.getCoordY(),chave.getPlaca(),
                                                 chave.getTipo(),chave.getSe(),chave.getAlim(),chave.getElo(),chave.getTipoElo()));
        }       

    }

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.GZIPInputStream;

import business.coordinate;
import android.content.Context;

    public class ReadExcel {

        public static List<ChaveEloCoordenada> read(Context context) {
            List<ChaveEloCoordenada> list = new ArrayList<ChaveEloCoordenada>();
            ChaveEloCoordenada chave = null;
            String line;
            int i = 0;
            String split[] = null;

            try {
                InputStream is = context.getAssets().open("file.zip");

                InputStream gzipStream = new GZIPInputStream(is);
                Reader decoder = new InputStreamReader(gzipStream, "UTF-8");
                BufferedReader buffered = new BufferedReader(decoder);

                while((line = buffered.readLine()) != null) {
                    i++;
                    chave = new ChaveEloCoordenada();
                    split = line.split("\t");
                    chave.setBarramento(split[0]);
                    chave.setCoordX(split[1]);
                    chave.setCoordY(split[2]);
                    chave.setPlaca(split[3]);
                    chave.setTipo(split[4]);
                    chave.setSe(split[5]);
                    chave.setAlim(split[6]);
                    if (split.length > 7 ) {
                        chave.setElo(split[7]);
                        chave.setTipoElo(split[8]);
                    }
                    list.add(chave);
                }
                buffered.close();
                System.out.println("TOTAL = " + i);
            }
            catch (final IOException ioe) {
                System.err.println("Unhandled exception:");
                ioe.printStackTrace();
                return list;
            }

            return list;
        }

    }

score 0 · Answer 2 · answered Jan 14 '15 at 05:09

0

I dont think its because of the size, the line

w = Workbook.getWorkbook(dbInputStream);

works only with the File object not with the InputStream object which you are tying to use

answered Jan 14 '15 at 05:09

Sandeep Tengale

152
4
13

I'm trying to read the xlsx file which is in assests/excel folder. Please give me some hint on how to read it & pass to getWorkbook(). – Pranav Mahajan Jan 24 '15 at 13:28
there is inputStream constructor for getWorkbook – itzhar Oct 06 '15 at 12:22

How do I read excel file in assets?

Edit

2 Answers2