在 Android 中读取 Excel [英] Read Excel in Android
问题描述
目前我正在从事 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.
这是代码
回调接口:
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
...
}
用户选择excel文件的界面:
https://rogerkeays.com/simple-android-file-chooser
JSExcelReader:(读取excel并将其转换为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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
