曾简单了解过C#，将Excel（数据库表）表中的数据导入到C#中，使用C#制作的图形化界面进行对Excel表中数据进行操作。
今天想试试，在Android中导入Excel表格进行操作。在网上查阅资料，找到了jxl.jar包。
jxl.jar
jxl.jar是通过java操作excel表格的工具类库，是由java语言开发而成的。
本篇文章使用jxl.jar在Android中操作Excel表格实现四个功能
- 导入——将外部Excel表格导入到Android中（Android中的数据库中）。
- 导出——将在Android中的数据库中的Excel表格数据进行处理后，导出到手机的外部存储中
- 清空——清空在Android中的数据库中的Excel表格数据
- 查询——查询在Android中的数据库中的Excel表格数据
先看一下Demo案例效果图：
Excel.gif
导入——将外部Excel表格导入到Android中（Android中的数据库中）。
先看一下Excel表格数据格式。
Excel表格.png
导入数据——其实就是将Excel表格数据读出，然后存储到Android的数据库中。（不再原本的Excel表格上进行数据操作处理，而是导入到Android的数据库中，如果后面有需要，直接将数据库中的数据导出（为什么要导入到数据库中呢，因为一次性读完Excel表格数据后，直接写入到数据库中，Android对数据库中的数据的处理操作简单方便，而对Excel表格进行操作就需要使用jxl.jar包了。）。）。
先将jxl.jar包导入到项目中。
然后打开MainActivity.java进行编码
查看MainActivity.java中的代码——选择那张Excel表格，导入，导出，清空。
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
// Excel表格的表头
private String[] columnTitle = {"资产编号", "资产名称", "资产分类", "国标分类", "实有数量", "实有原值", "实有累计折旧", "盘点结果", "使用状况", "产品序列号",
"账面数量", "账面价值", "账面累计折旧", "账面净值", "取得方式", "规格型号", "计量单位", "取得日期", "财务入账日期", "价值类型", "存放地点", "使用部门", "使用人",
"原资产编号", "备注"};
private AppCompatButton btn_import;
private AppCompatButton btn_export;
private AppCompatButton btn_delete;
private AppCompatButton btn_query;
// 加载中的Dialog
private LoadingDialog mLoadingDialog;
private List<School> mSchoolListToDb;
private List<School> mDbToSchoolList;
private List<School> mSchoolsExcelName;
private Set<String> mHashSet;
private File mFileDir;
private Vector<File> mSubFileVector;
// Spinner当前选中数据表
private File currentFile;
private AppCompatTextView select_text_db_file_excel;
private SlideFromBottomPopup mSlideFromBottomPopup;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
initView();
initFileDir();
initData();
}
/**
* 创建特定文件夹以存放Excel表
*/
private void initFileDir() {
mFileDir = FileUtil.createDir("YunYangData");
}
/**
* 初始化数据库
*/
private void initData() {
mSchoolListToDb = new ArrayList<>();
mDbToSchoolList = new ArrayList<>();
LitePal.getDatabase();
mSchoolsExcelName = new ArrayList<>();
mHashSet = new HashSet<>();
mSubFileVector = new Vector<File>();
mSlideFromBottomPopup =
new SlideFromBottomPopup(MainActivity.this, mFileDir.getAbsolutePath());
}
/**
* 初始化控件
*/
private void initView() {
btn_import = (AppCompatButton) findViewById(R.id.btn_import);
btn_export = (AppCompatButton) findViewById(R.id.btn_export);
btn_delete = (AppCompatButton) findViewById(R.id.btn_delete);
btn_query = (AppCompatButton) findViewById(R.id.btn_query);
btn_import.setOnClickListener(this);
btn_export.setOnClickListener(this);
btn_delete.setOnClickListener(this);
btn_query.setOnClickListener(this);
select_text_db_file_excel = (AppCompatTextView) findViewById(R.id.select_text_db_file_excel);
select_text_db_file_excel.setOnClickListener(this);
if (select_text_db_file_excel.getText().toString().equals("请您选择Excel文件")) {
checkFirstBtnEnabled(false);
}
}
private void checkBtnEnabled(boolean flag) {
btn_import.setEnabled(flag);
btn_export.setEnabled(flag);
btn_query.setEnabled(flag);
btn_delete.setEnabled(flag);
}
private void checkBtnEnabledFlag(boolean flag) {
btn_import.setEnabled(flag);
}
private void checkFirstBtnEnabled(boolean flag) {
btn_import.setEnabled(flag);
btn_export.setEnabled(flag);
btn_query.setEnabled(flag);
btn_delete.setEnabled(flag);
}
private List<School> shenqi;
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_import:
checkCurrentFileNameFromDb();
break;
case R.id.btn_export:
mLoadingDialog = new LoadingDialog(this, "数据正在导出中...");
startLoading();
exportCreateExcelFromJxl();
break;
case R.id.btn_delete:
if (currentFile != null) {
deleteDbFromDataSheet(true);
} else {
Toast.makeText(this, "请选中要从本地数据库中删除之前导入的Excel文件名称", Toast.LENGTH_LONG).show();
}
break;
case R.id.btn_query:
Intent intentQueryData = new Intent(MainActivity.this, QueryDataActivity.class);
startActivity(intentQueryData);
break;
case R.id.select_text_db_file_excel:
mSlideFromBottomPopup.newPopupBottomShow();
break;
default:
break;
}
}
/**
* 检查数据库中是否存在当前Excel表名，如果存在，导入时是否覆盖导入。
*/
private void checkCurrentFileNameFromDb() {
new AsyncTask<Void, Void, Integer>() {
@Override
protected Integer doInBackground(Void... voids) {
final int count = LitePal
.where("ownershipDataSheet = ?", currentFile.getName())
.count(School.class);
return count;
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
mLoadingDialog = new LoadingDialog(MainActivity.this, "数据正在导入中...");
if (aVoid > 0) {
new QMUIDialog.MessageDialogBuilder(MainActivity.this)
.setTitle("提示")
.setMessage("本地数据库中已经存在 "
+ StrUtil.getFileNameNoEx(currentFile.getName())
+ " Excel表，是否覆盖导入？")
.addAction("取消", new QMUIDialogAction.ActionListener() {
@Override
public void onClick(QMUIDialog dialog, int index) {
dialog.dismiss();
}
})
.addAction("确定", new QMUIDialogAction.ActionListener() {
@Override
public void onClick(QMUIDialog dialog, int index) {
if (currentFile != null) {
deleteDbFromDataSheet(false);
readExcelToDB();
} else {
Toast.makeText(MainActivity.this, "请选中要从本地数据库中覆盖之前导入的Excel文件名称", Toast.LENGTH_LONG).show();
}
dialog.dismiss();
}
})
.show();
} else {
readExcelToDB();
}
}
}.execute();
}
/**
* 依据 所属数据表 列进行数据删除
*/
private void deleteDbFromDataSheet(final boolean flag) {
startLoading();
new AsyncTask<Void, Void, Integer>() {
@Override
protected Integer doInBackground(Void... params) {
try {
LitePal
.deleteAll(
School.class, "ownershipDataSheet = ?",
currentFile.getName());
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
if (aVoid == 1) {
if (flag) {
Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 失败", Toast.LENGTH_LONG).show();
}
}
stopLoading();
}
}.execute();
}
/**
* 导出 创建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
// 创建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 创建Sheet表，并写入数据
mDbToSchoolList.clear();
/*
导出之前，删除原表，创建新表
编写导出LitePal语句，导出Excel表格依据26列（所属数据表进行导出）
*/
mDbToSchoolList = LitePal.findAll(School.class);
ExcelUtils.writeSchoolListToExcel(mDbToSchoolList, FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), MainActivity.this);
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
if (aVoid == 1) {
Toast.makeText(MainActivity.this, "导出成功！", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(MainActivity.this, "导出失败！", Toast.LENGTH_LONG).show();
}
stopLoading();
}
}.execute();
}
/**
* 结束加载Dialog
*/
private void stopLoading() {
if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
mLoadingDialog.dismiss();
}
}
/**
* 开始加载Dialog
*/
private void startLoading() {
if (mLoadingDialog != null && !mLoadingDialog.isShowing()) {
mLoadingDialog.show();
}
}
@Override
public void onStart() {
super.onStart();
EventBus.getDefault().register(this);
}
@Override
protected void onStop() {
super.onStop();
EventBus.getDefault().unregister(this);
stopLoading();
}
/**
* 读取excel数据到数据库里（LitePal）
*/
private void readExcelToDB() {
startLoading();
new Thread(new Runnable() {
@Override
public void run() {
try {
/*
Android 读取Assets 下的Excel文件
*/
// InputStream is = context.getAssets().open("康复所.xls");
// Workbook book = Workbook.getWorkbook(is);
Workbook book = Workbook.getWorkbook(currentFile);
// 获取表页数
final int bookPage = book.getNumberOfSheets();
Log.e("bookPage", "bookPage = " + bookPage);
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
int Rows = sheet.getRows();
School schoolData = null;
for (int i = 1; i < Rows; ++i) {
String assetNumber = (sheet.getCell(0, i)).getContents();
String assetName = (sheet.getCell(1, i)).getContents();
String assetClassification = (sheet.getCell(2, i)).getContents();
String nationalStandardClassification = (sheet.getCell(3, i)).getContents();
String actualNumberOf = (sheet.getCell(4, i)).getContents();
String actualValue = (sheet.getCell(5, i)).getContents();
String actualAccumulatedDepreciation = (sheet.getCell(6, i)).getContents();
String inventoryResults = (sheet.getCell(7, i)).getContents();
String useStatus = (sheet.getCell(8, i)).getContents();
String serialNumber = (sheet.getCell(9, i)).getContents();
String physicalCountQuantity = (sheet.getCell(10, i)).getContents();
String bookValue = (sheet.getCell(11, i)).getContents();
String bookDepreciation = (sheet.getCell(12, i)).getContents();
String netBookValue = (sheet.getCell(13, i)).getContents();
String gainingMethod = (sheet.getCell(14, i)).getContents();
String specificationsAndModels = (sheet.getCell(15, i)).getContents();
String unitOfMeasurement = (sheet.getCell(16, i)).getContents();
String dateOfAcquisition = (sheet.getCell(17, i)).getContents();
String dateOfFinancialEntry = (sheet.getCell(18, i)).getContents();
String typeOfValue = (sheet.getCell(19, i)).getContents();
String storagePlace = (sheet.getCell(20, i)).getContents();
String userDepartment = (sheet.getCell(21, i)).getContents();
String user = (sheet.getCell(22, i)).getContents();
String originalAssetNumber = (sheet.getCell(23, i)).getContents();
String remark = (sheet.getCell(24, i)).getContents();
/*
25是盘点数量
导入的时候把实有数量给盘点数量
导出的时候把盘点数量给实有数量
*/
// 导入Db——这里先直接赋值为0
String inventoryData = "0";
// 26是所属数据表
// String ownershipDataSheet = "康复所";
String ownershipDataSheet = "";
if (currentFile != null) {
ownershipDataSheet = currentFile.getName();
}
schoolData = new School(assetNumber, assetName, assetClassification,
nationalStandardClassification, actualNumberOf, actualValue,
actualAccumulatedDepreciation, inventoryResults, useStatus,
serialNumber, physicalCountQuantity, bookValue,
bookDepreciation, netBookValue, gainingMethod, specificationsAndModels,
unitOfMeasurement, dateOfAcquisition, dateOfFinancialEntry,
typeOfValue, storagePlace, userDepartment, user, originalAssetNumber
, remark, inventoryData, ownershipDataSheet);
mSchoolListToDb.add(schoolData);
}
book.close();
saveDB();
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
checkBtnEnabled(true);
Toast.makeText(MainActivity.this, "导入成功", Toast.LENGTH_SHORT).show();
}
});
} catch (Exception e) {
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
Toast.makeText(MainActivity.this, "导入失败", Toast.LENGTH_SHORT).show();
}
});
}
}
}).start();
}
/**
* 数据保存到数据库中并且去空行
*/
private void saveDB() {
if (mSchoolListToDb != null) {
LitePal.saveAll(mSchoolListToDb);
// 去空行
LitePal.deleteAll(School.class, "assetNumber = ?", "");
}
}
@Subscribe(threadMode = ThreadMode.MAIN)
public void onMessageEvent(ExpandMessage expandMessage) {
final String fileName = expandMessage.getFileName();
select_text_db_file_excel.setText(fileName);
if (!select_text_db_file_excel.getText().toString().equals("请您选择Excel文件")) {
checkBtnEnabledFlag(true);
}
EventBus
.getDefault()
.postSticky(new CurrentFileName(fileName));
for (File file :
expandMessage.getSubFileVector()) {
if (file.getName().equals(fileName)) {
currentFile = file;
}
}
}
@Subscribe(threadMode = ThreadMode.MAIN)
public void onFileListsEvent(UnderWayFileLists fileLists) {
for (String str :
fileLists.getSetFiles()) {
if (!str.equals(fileLists.getFileName())) {
checkBtnEnabled(false);
checkBtnEnabledFlag(true);
} else {
checkBtnEnabled(true);
return;
}
}
}
}
代码功能，注释写的很清楚。
数据库使用的库为 LitePal 。
创建Excel表格，使用了 ZzExcelCreator Excel表格生成工具，该库本质上也是使用jxl.jar
导入——需要注意的是jxl.jar读写数据格式都是字符型数据。
/**
* 读取excel数据到数据库里（LitePal）
*/
private void readExcelToDB() {
startLoading();
new Thread(new Runnable() {
@Override
public void run() {
try {
/*
Android 读取Assets 下的Excel文件
*/
// InputStream is = context.getAssets().open("康复所.xls");
// Workbook book = Workbook.getWorkbook(is);
Workbook book = Workbook.getWorkbook(currentFile);
// 获取表页数
final int bookPage = book.getNumberOfSheets();
Log.e("bookPage", "bookPage = " + bookPage);
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
int Rows = sheet.getRows();
School schoolData = null;
for (int i = 1; i < Rows; ++i) {
String assetNumber = (sheet.getCell(0, i)).getContents();
String assetName = (sheet.getCell(1, i)).getContents();
String assetClassification = (sheet.getCell(2, i)).getContents();
String nationalStandardClassification = (sheet.getCell(3, i)).getContents();
String actualNumberOf = (sheet.getCell(4, i)).getContents();
String actualValue = (sheet.getCell(5, i)).getContents();
String actualAccumulatedDepreciation = (sheet.getCell(6, i)).getContents();
String inventoryResults = (sheet.getCell(7, i)).getContents();
String useStatus = (sheet.getCell(8, i)).getContents();
String serialNumber = (sheet.getCell(9, i)).getContents();
String physicalCountQuantity = (sheet.getCell(10, i)).getContents();
String bookValue = (sheet.getCell(11, i)).getContents();
String bookDepreciation = (sheet.getCell(12, i)).getContents();
String netBookValue = (sheet.getCell(13, i)).getContents();
String gainingMethod = (sheet.getCell(14, i)).getContents();
String specificationsAndModels = (sheet.getCell(15, i)).getContents();
String unitOfMeasurement = (sheet.getCell(16, i)).getContents();
String dateOfAcquisition = (sheet.getCell(17, i)).getContents();
String dateOfFinancialEntry = (sheet.getCell(18, i)).getContents();
String typeOfValue = (sheet.getCell(19, i)).getContents();
String storagePlace = (sheet.getCell(20, i)).getContents();
String userDepartment = (sheet.getCell(21, i)).getContents();
String user = (sheet.getCell(22, i)).getContents();
String originalAssetNumber = (sheet.getCell(23, i)).getContents();
String remark = (sheet.getCell(24, i)).getContents();
/*
25是盘点数量
导入的时候把实有数量给盘点数量
导出的时候把盘点数量给实有数量
*/
// 导入Db——这里先直接赋值为0
String inventoryData = "0";
// 26是所属数据表
// String ownershipDataSheet = "康复所";
String ownershipDataSheet = "";
if (currentFile != null) {
ownershipDataSheet = currentFile.getName();
}
schoolData = new School(assetNumber, assetName, assetClassification,
nationalStandardClassification, actualNumberOf, actualValue,
actualAccumulatedDepreciation, inventoryResults, useStatus,
serialNumber, physicalCountQuantity, bookValue,
bookDepreciation, netBookValue, gainingMethod, specificationsAndModels,
unitOfMeasurement, dateOfAcquisition, dateOfFinancialEntry,
typeOfValue, storagePlace, userDepartment, user, originalAssetNumber
, remark, inventoryData, ownershipDataSheet);
mSchoolListToDb.add(schoolData);
}
book.close();
saveDB();
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
checkBtnEnabled(true);
Toast.makeText(MainActivity.this, "导入成功", Toast.LENGTH_SHORT).show();
}
});
} catch (Exception e) {
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
Toast.makeText(MainActivity.this, "导入失败", Toast.LENGTH_SHORT).show();
}
});
}
}
}).start();
}
导出——创建新的Excel表格，将数据库中的数据按照导入时的数据格式导出到刚创建的新的Excel表格中。
/**
* 导出 创建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
// 创建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 创建Sheet表，并写入数据
mDbToSchoolList.clear();
/*
导出之前，删除原表，创建新表
编写导出LitePal语句，导出Excel表格依据26列（所属数据表进行导出）
*/
mDbToSchoolList = LitePal.findAll(School.class);
ExcelUtils.writeSchoolListToExcel(mDbToSchoolList, FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), MainActivity.this);
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
if (aVoid == 1) {
Toast.makeText(MainActivity.this, "导出成功！", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(MainActivity.this, "导出失败！", Toast.LENGTH_LONG).show();
}
stopLoading();
}
}.execute();
}
ExcelUtils.writeSchoolListToExcel()方法
public static <T> void writeSchoolListToExcel(List<School> objList,
String filePath, Context c) {
if (objList != null && objList.size() > 0) {
final int countDb = objList.size();
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(filePath));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(filePath),
workbook);
//WritableSheet sheet = writebook.createSheet("页签1", 0);
WritableSheet sheet = writebook.getSheet(0);
for (int j = 1; j < countDb; j++) {
School school = objList.get(j);
String actualNumberOf = school.getActualNumberOf();
String physicalCountQuantity = school.getPhysicalCountQuantity();
// String数据类型转换为int数据类型，取掉小数点|判空操作
actualNumberOf = StrUtil.isNullOrEmptyAndSub(actualNumberOf);
physicalCountQuantity = StrUtil.isNullOrEmptyAndSub(physicalCountQuantity);
sheet.addCell(new Label(0, j + 1, school.getAssetNumber(), arial10formatnobg));
sheet.addCell(new Label(1, j + 1, school.getAssetName(), arial10formatnobg));
sheet.addCell(new Label(2, j + 1, school.getAssetClassification(), arial10formatnobg));
sheet.addCell(new Label(3, j + 1, school.getNationalStandardClassification(), arial10formatnobg));
sheet.addCell(new Label(4, j + 1, actualNumberOf, arial10formatnobg));
sheet.addCell(new Label(5, j + 1, school.getActualValue(), arial10formatnobg));
sheet.addCell(new Label(6, j + 1, school.getActualAccumulatedDepreciation(), arial10formatnobg));
int tp = Integer.parseInt(physicalCountQuantity) - Integer.parseInt(actualNumberOf);
if (tp == 0) {
sheet.addCell(new Label(7, j + 1, "无盈亏", arial10formatnobg));
} else if (tp > 0) {
sheet.addCell(new Label(7, j + 1, "盘亏", arial10formatnobg));
} else if (tp < 0) {
sheet.addCell(new Label(7, j + 1, "盘盈", arial10formatnobg));
}
sheet.addCell(new Label(8, j + 1, school.getUseStatus(), arial10formatnobg));
sheet.addCell(new Label(9, j + 1, school.getSerialNumber(), arial10formatnobg));
sheet.addCell(new Label(10, j + 1, physicalCountQuantity, arial10formatnobg));
sheet.addCell(new Label(11, j + 1, school.getBookValue(), arial10formatnobg));
sheet.addCell(new Label(12, j + 1, school.getBookDepreciation(), arial10formatnobg));
sheet.addCell(new Label(13, j + 1, school.getNetBookValue(), arial10formatnobg));
sheet.addCell(new Label(14, j + 1, school.getGainingMethod(), arial10formatnobg));
sheet.addCell(new Label(15, j + 1, school.getSpecificationsAndModels(), arial10formatnobg));
sheet.addCell(new Label(16, j + 1, school.getUnitOfMeasurement(), arial10formatnobg));
sheet.addCell(new Label(17, j + 1, school.getDateOfAcquisition(), arial10formatnobg));
sheet.addCell(new Label(18, j + 1, school.getDateOfFinancialEntry(), arial10formatnobg));
sheet.addCell(new Label(19, j + 1, school.getTypeOfValue(), arial10formatnobg));
sheet.addCell(new Label(20, j + 1, school.getStoragePlace(), arial10formatnobg));
sheet.addCell(new Label(21, j + 1, school.getUserDepartment(), arial10formatnobg));
sheet.addCell(new Label(22, j + 1, school.getUser(), arial10formatnobg));
sheet.addCell(new Label(23, j + 1, school.getOriginalAssetNumber(), arial10formatnobg));
sheet.addCell(new Label(24, j + 1, school.getRemark(), arial10formatnobg));
}
// 一般的Excel表格都是有填写说明的
WritableSheet sheet3 = writebook.createSheet("填写说明", 2);
sheet3.addCell(new Label(0, 0, "这是填写说明的表", arial10formatnobg));
writebook.write();
Log.e("writeObjListToExcel", "有需要导出的数据! ");
} catch (Exception e) {
Log.e("writeObjListToExcel", "进来创建catch" + e.getMessage());
e.printStackTrace();
} finally {
if (writebook != null) {
try {
writebook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} else {
Log.e("writeSchoolListToExcel", "没有有需要导出的数据! ");
}
}
从数据库导出数据到外部的Excel时，需要注意两点
- （一）填写说明这个sheet表
- （二）隐藏表——表是隐藏的，一般都是表回到大系统的时候会有这个。不能忽略，如果忽略，那么表回归大系统的时候就会报错。
如何查看隐藏表：
hide.png
点击鼠标左键取消隐藏后，选择需要显示的sheet表以及查看有多少张被隐藏的表。
hidetwo.png
我们可以清楚的看到hidesheet表被隐藏了。那么我们在导出时，也需要创建和hidesheet表一模一样的表。然后导出。
在ExcelUtils.writeSchoolListToExcel()方法中加上
/*
隐藏表
表是隐藏的，一般都是表回到大系统的时候会有这个。
不能忽略，如果忽略，那么表回归大系统的时候就会报错。
*/
// 隐藏表
sheet2.setHidden(true);
sheet2.addCell(new Label(0, 0, "这是隐藏的表", arial10formatnobg));
// 一般的Excel表格都是有填写说明的
WritableSheet sheet3 = writebook.createSheet("填写说明", 2);
sheet3.addCell(new Label(0, 0, "这是填写说明的表", arial10formatnobg));
这样之后表回归大系统进行统一查看的时候，就不会报错了。
清空——可以根据需求清空某些数据，这里按照所属数据表进行数据清空。
/**
* 依据 所属数据表 列进行数据删除
*/
private void deleteDbFromDataSheet(final boolean flag) {
startLoading();
new AsyncTask<Void, Void, Integer>() {
@Override
protected Integer doInBackground(Void... params) {
try {
LitePal
.deleteAll(
School.class, "ownershipDataSheet = ?",
currentFile.getName());
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
if (aVoid == 1) {
if (flag) {
Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 失败", Toast.LENGTH_LONG).show();
}
}
stopLoading();
}
}.execute();
}
导出后的文件放在外部存储外面（导入的Excel文件放在YunYangData文件夹下）。
导出.png
查询——QueryDataActivity.java，按照全部，盘亏，无盈亏三个Tab进行分类。
public class QueryDataActivity extends AppCompatActivity {
private static final String POSITION = "POSITION";
private QueryDataAdapter mSectionsPagerAdapter;
private ViewPager mViewPager;
private TabLayout mTabLayout;
private List<Fragment> mFragments;
private String[] mTitles;
private int[] mInts;
private String ownershipDataSheetName;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_query_data);
EventBus.getDefault().register(this);
initView();
initData();
}
private void initData() {
findDbLitePal();
}
/**
* 根据inventoryResults（盘点结果）去数据库中查找对应数据
*/
private void findDbLitePal() {
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
if (LitePal.count(School.class) <= 0) {
Log.e("Excel", "LitePal数据库中无数据");
mTitles[0] = "全部";
mTitles[1] = "盘亏";
mTitles[2] = "无盈亏";
return 0;
} else {
// 全部
final int count = LitePal
.where("ownershipDataSheet = ?", ownershipDataSheetName)
.count(School.class);
// 减去行1
mInts[0] = count - 1;
// 盘亏
mInts[1] = LitePal
.where("inventoryResults = ? and ownershipDataSheet = ?", "盘亏", ownershipDataSheetName)
.count(School.class);
// 无盈亏
mInts[2] = LitePal
.where("inventoryResults = ? and ownershipDataSheet = ?", "无盈亏", ownershipDataSheetName)
.count(School.class);
mTitles[0] = "全部（" + mInts[0] + "）";
mTitles[1] = "盘亏（" + mInts[1] + "）";
mTitles[2] = "无盈亏（" + mInts[2] + "）";
return 1;
}
} catch (Exception e) {
e.printStackTrace();
mTitles[0] = "全部";
mTitles[1] = "盘亏";
mTitles[2] = "无盈亏";
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
mFragments.add(FragmentDish.newInstance(0));
mFragments.add(FragmentDish.newInstance(1));
mFragments.add(FragmentDish.newInstance(2));
if (mFragments.size() > 0 && mTitles.length > 0) {
mSectionsPagerAdapter = new QueryDataAdapter(getSupportFragmentManager(), mFragments, mTitles);
mViewPager.setAdapter(mSectionsPagerAdapter);
mViewPager.setOffscreenPageLimit(3);
mTabLayout.setupWithViewPager(mViewPager);
setupTabLayout(mTabLayout);
}
}
}.execute();
}
private void setupTabLayout(TabLayout tabLayout) {
tabLayout.setTabMode(TabLayout.MODE_FIXED);
tabLayout.setTabGravity(TabLayout.GRAVITY_FILL);
tabLayout.setupWithViewPager(mViewPager);
}
private void initView() {
mViewPager = (ViewPager) findViewById(R.id.container);
mTabLayout = (TabLayout) findViewById(R.id.tabs);
mFragments = new ArrayList<>();
mTitles = new String[3];
mInts = new int[3];
}
@Override
public void onSaveInstanceState(Bundle outState) {
super.onSaveInstanceState(outState);
outState.putInt(POSITION, mTabLayout.getSelectedTabPosition());
}
@Override
protected void onRestoreInstanceState(Bundle savedInstanceState) {
super.onRestoreInstanceState(savedInstanceState);
mViewPager.setCurrentItem(savedInstanceState.getInt(POSITION));
}
@Override
protected void onDestroy() {
super.onDestroy();
EventBus.getDefault().unregister(this);
}
@Subscribe(threadMode = ThreadMode.MAIN, sticky = true)
public void onCurrentFileNameEvent(CurrentFileName currentFile) {
ownershipDataSheetName = currentFile.getFileName();
}
}
QueryDataActivity.java中三个Tab——FragmentDish.java
/**
* 作者 yunyang
* 时间 2019/1/7 9:38
* 文件 DataConversion
* 描述 盘的碎片（全部——盘亏——无盈盘）
*/
public class FragmentDish extends Fragment {
private RecyclerView mRecyclerView;
private DishFragmentAdapter mFragmentDishAdapter;
private LoadingDialog mLoadingDialog;
private List<School> mDbToSchoolList;
private String ownershipDataSheetName;
public static Fragment newInstance(int tab) {
Bundle bundle = new Bundle();
FragmentDish fragmentWin = new FragmentDish();
bundle.putInt("tab", tab);
fragmentWin.setArguments(bundle);
return fragmentWin;
}
@Nullable
@Override
public View onCreateView(LayoutInflater inflater, @Nullable ViewGroup container, @Nullable Bundle savedInstanceState) {
View view = inflater.inflate(R.layout.fragment_dish, container, false);
initView(view);
return view;
}
private void initView(View view) {
initRecy(view);
Bundle bundle = getArguments();
if (bundle != null) {
final int tab = bundle.getInt("tab");
switch (tab) {
case 0:
// 全部
findDbLitePal("全部");
break;
case 1:
// 盘亏
findDbLitePal("盘亏");
break;
case 2:
// 无盈亏
findDbLitePal("无盈亏");
break;
default:
break;
}
}
}
private void initRecy(View view) {
mRecyclerView = (RecyclerView) view.findViewById(R.id.query_fragment_recy_dish);
mDbToSchoolList = new ArrayList<>();
mLoadingDialog = new LoadingDialog(getActivity(), "数据正在加载中...");
mFragmentDishAdapter = new DishFragmentAdapter(getActivity(), mDbToSchoolList);
LinearLayoutManager manager = new LinearLayoutManager(getActivity());
mRecyclerView.setLayoutManager(manager);
mRecyclerView.setAdapter(mFragmentDishAdapter);
}
/**
* 结束加载Dialog
*/
private void stopLoading() {
if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
mLoadingDialog.dismiss();
}
}
/**
* 开始加载Dialog
*/
private void startLoading() {
if (mLoadingDialog != null && !mLoadingDialog.isShowing()) {
mLoadingDialog.show();
}
}
/**
* 根据inventoryResults（盘点结果）去数据库中查找对应数据
*
* @param string
*/
private void findDbLitePal(String string) {
startLoading();
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
mDbToSchoolList.clear();
if (!StrUtil.isEmpty(ownershipDataSheetName)) {
final int countDb = LitePal.count(School.class);
if (countDb > 0) {
if (params[0].equals("全部")) {
mDbToSchoolList = LitePal
.where("ownershipDataSheet = ?", ownershipDataSheetName)
.find(School.class);
mDbToSchoolList.remove(0);
} else {
mDbToSchoolList = LitePal
.where(
"ownershipDataSheet = ? and inventoryResults = ?",
ownershipDataSheetName, params[0])
.find(School.class);
}
return 1;
}
}
return 0;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
@Override
protected void onPostExecute(Integer aVoid) {
super.onPostExecute(aVoid);
if (aVoid == 1) {
if (mFragmentDishAdapter != null) {
mFragmentDishAdapter.setDataNotify(mDbToSchoolList);
}
} else {
}
stopLoading();
}
}.execute(string);
}
@Override
public void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
EventBus.getDefault().register(this);
}
@Override
public void onDestroy() {
super.onDestroy();
EventBus.getDefault().unregister(this);
}
@Subscribe(threadMode = ThreadMode.MAIN, sticky = true)
public void onCurrentFileNameEvent(CurrentFileName currentFile) {
ownershipDataSheetName = currentFile.getFileName();
}
}
查询页面就是查找数据库中的数据，然后进行数据集显示到RecyclerView的Item上。
ExcelQuery.gif
使用此案例，当App安装到手机上，就会在外部存储中创建YunYangData文件夹，然后可以把固定的文件格式的Excel文件放入其中。使用App进行处理。（也可以先创建YunYangData文件夹，然后把固定的文件格式的Excel文件放入其中，打开App进行操作）。
网友评论