Read Excel in Android

Question

Currently I'm working on android development. As the requirement stated, the application should able to read the Excel files for data input.

As other people start with this topic, I have go through with Java Excel Api and Apache POI, but both need to have some modification to fit my requirement:

JExcel API:
- Cannot support XLSX

Apache POI:
- Support well for XLS files
- To support XLSX in Dalvik, you need to overcome 64K and javax library, or use the port version (i.e. from Andrew Kondratev)
- File size will increase 2.4MB

But do we have other option to work with Excel files in Android 4 or below?

Answer 1

For those application who need to work with full functional excel files (i.e. drawing, VBA etc... ), you should go with Apache POI, it's plain but still the best solution for that now.

However, if you just need to read the Excel, it maybe good to go with JavaScript solution. With js-xlsx library, you can transfer Excel files into JSON. And the library size is small, just 395KB (only include xlsx.core.min.js)

I believe this not the best solution:
- WebView need to work with UI Thread, it may block the UI when reading large Excel file.
- Performance issue
But you could change this to other JavaScript engine like Rhino or V8 to fix these issues.

Here is the code

Interface for callback:

public interface ExcelReaderListener {
    void onReadExcelCompleted(List<String> stringList);
}

MainActivity:

private ProgressDialog progressDialog;

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    new AlertDialog.Builder(MainActivity.this)
            .setMessage("message")
            .setTitle("title")
            .setPositiveButton(R.string.ok, new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    dialog.dismiss();

                    new FileChooser(MainActivity.this, new String[]{"xls", "xlsx"})
                            .setFileListener(new FileChooser.FileSelectedListener() {
                                @Override
                                public void fileSelected(File file) {
                                    progressDialog = new ProgressDialog(MainActivity.this);
                                    progressDialog.setTitle("title");
                                    progressDialog.setMessage("message");
                                    progressDialog.setIndeterminate(true);
                                    progressDialog.setCanceledOnTouchOutside(false);

                                    Toast.makeText(MainActivity.this, file.getName(), Toast.LENGTH_SHORT).show();
                                    String filePath = file.getAbsolutePath();
                                    ExcelReaderListener excelReaderListener = MainActivity.this;

                                    progressDialog.show();
                                    try {
                                        final WebView webView = new WebView(MainActivity.this);
                                        new JSExcelReader(filePath, webView, excelReaderListener);
                                    } catch (Exception ex) {
                                        Log.e("Import excel error", ex.getMessage());
                                    }
                                }
                            })
                            .showDialog();
                }
            })
            .show();
}

@Override
public void onReadExcelCompleted(List<String> stringList) {
    Toast.makeText(MainActivity.this, "Parse Completed", Toast.LENGTH_SHORT).show();

    if (progressDialog != null && progressDialog.isShowing()) {
        progressDialog.dismiss();
    }

    // Write into DB
    ...
}

Interface for user to select excel file:

https://rogerkeays.com/simple-android-file-chooser

JSExcelReader: (Core part to read excel and transform it to ArrayList)

public class JSExcelReader {

    private ExcelReaderListener callback;

    public JSExcelReader(String filePath, final WebView webView, ExcelReaderListener callback) {
        this.callback = callback;

        File file = new File(filePath);

        try (InputStream is = new FileInputStream(file)) {
            // convert file to Base64
            if (file.length() > Integer.MAX_VALUE)
                Log.e("File too big", "file too big");
            byte[] bytes = new byte[(int) file.length()];

            int offset = 0;
            int numRead;
            while (offset < bytes.length &&
            (numRead = is.read(bytes, offset, bytes.length - offset)) >= 0) {
                offset += numRead;
            }

            if (offset < bytes.length)
                throw new Exception("Could not completely read file");

            final String b64 = Base64.encodeToString(bytes, Base64.NO_WRAP);

            // feed the string into webview and get the result
            WebSettings webSettings = webView.getSettings();
            webSettings.setJavaScriptEnabled(true);
            webView.loadUrl("file:///android_asset/AndroidParseExcel.html");
            webView.setWebViewClient(new WebViewClient() {
                public void onPageFinished(WebView view, String url) {
                    webView.evaluateJavascript("convertFile('" + b64 + "');", new ValueCallback<String>() {
                        @Override
                        public void onReceiveValue(String value) {
                            parseJSON(value);
                        }
                    });
                }
            });
        } catch (Exception ex) {
            Log.e("Convert Excel failure", ex.getMessage());
        }
    }

    private void parseJSON(String jsonString) {
        try {
            // return value is something like "{\n\"Sheet1\":\n[\"title\"...
            // you need to remove those escape character first
            JSONObject jsonRoot = new JSONObject(jsonString.substring(1, jsonString.length() - 1)
                                                            .replaceAll("\\\\n", "")
                                                            .replaceAll("\\\\\"", "\"")
                                                            .replaceAll("\\\\\\\\\"", "'"));
            JSONArray sheet1 = jsonRoot.optJSONArray("Sheet1");
            List<String> stringList = new ArrayList<>();

            JSONObject jsonObject;
            for (int i = 0; i < sheet1.length(); i++) {
                jsonObject = sheet1.getJSONObject(i);

                stringList.add(jsonObject.optString("title"));
            }

            callback.onReadExcelCompleted(stringList);
        } catch (Exception ex) {
            Log.e("Error in parse JSON", ex.getMessage());
        }
    }
}

AndroidParseExcel.html: (You should put this and the JavaScript library into asset folder)

<html>
<script src="file:///android_asset/xlsx.core.min.js"></script>
<head></head>
<body>
</body>
<script type ="text/javascript">

    "use strict";

    var X = XLSX;

    function convertFile(b64data) {
        var wb = X.read(b64data, {type: 'base64',WTF: false});

        var result = {};
        wb.SheetNames.forEach(function(sheetName) {
            var roa = X.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
            if(roa.length > 0){
                result[sheetName] = roa;
            }
        });

        return JSON.stringify(result, 2, 2);
    }
</script>
</html>