本文介绍了在Android中阅读Excel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

目前,我正在从事android开发.按照要求,应用程序应该能够读取Excel文件进​​行数据输入.

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

正如其他人从这个主题开始一样,我已经遍历 Java Excel Api Apache POI ,但是两者都需要进行一些修改以满足我的要求:

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 :
-无法支持XLSX

JExcel API:
- Cannot support XLSX

Apache POI :
-很好地支持XLS文件
-要在Dalvik中支持XLSX,您需要克服64K和javax库,或者使用端口版本(例如,从 Andrew Kondratev )
-文件大小将增加2.4MB

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

但是我们还有其他选择可以处理Android 4或更低版本中的Excel文件吗?

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

推荐答案

对于那些需要使用功能齐全的excel文件(例如,绘图,VBA等...)的应用程序,您应该使用 Apache POI ,它很简单,但现在仍然是最好的解决方案.

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.

但是,如果您只需要阅读Excel,则最好使用JavaScript解决方案.使用 js-xlsx 库,您可以将Excel文件传输到JSON.而且库的大小很小,只有395KB(仅包含 xlsx.core.min.js )

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)

我认为这不是最好的解决方案:
- WebView 需要使用 UI Thread ,它可能会在读取大型Excel文件时阻塞UI.
-性能问题
但是您可以将其更改为其他JavaScript引擎,例如 Rhino V8 ,以解决这些问题.

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.

这是代码

回调接口:

Interface for callback:

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

MainActivity:

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
    ...
}

供用户选择Excel文件的界面:

Interface for user to select excel file:

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

JSExcelReader :(读取Excel并将其转换为ArrayList的核心部分)

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: (您应将此文件和JavaScript库放入 asset 文件夹中)

<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>

这篇关于在Android中阅读Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-18 05:39