曾簡(jiǎn)單了解過(guò)C#，將Excel（數(shù)據(jù)庫(kù)表）表中的數(shù)據(jù)導(dǎo)入到C#中，使用C#制作的圖形化界面進(jìn)行對(duì)Excel表中數(shù)據(jù)進(jìn)行操作。
今天想試試，在Android中導(dǎo)入Excel表格進(jìn)行操作。在網(wǎng)上查閱資料，找到了jxl.jar包。
jxl.jar
jxl.jar是通過(guò)java操作excel表格的工具類(lèi)庫(kù)，是由java語(yǔ)言開(kāi)發(fā)而成的。
本篇文章使用jxl.jar在Android中操作Excel表格實(shí)現(xiàn)四個(gè)功能
- 導(dǎo)入——將外部Excel表格導(dǎo)入到Android中（Android中的數(shù)據(jù)庫(kù)中）。
- 導(dǎo)出——將在Android中的數(shù)據(jù)庫(kù)中的Excel表格數(shù)據(jù)進(jìn)行處理后，導(dǎo)出到手機(jī)的外部存儲(chǔ)中
- 清空——清空在Android中的數(shù)據(jù)庫(kù)中的Excel表格數(shù)據(jù)
- 查詢(xún)——查詢(xún)?cè)贏ndroid中的數(shù)據(jù)庫(kù)中的Excel表格數(shù)據(jù)
先看一下Demo案例效果圖：
Excel.gif
導(dǎo)入——將外部Excel表格導(dǎo)入到Android中（Android中的數(shù)據(jù)庫(kù)中）。
先看一下Excel表格數(shù)據(jù)格式。
Excel表格.png
導(dǎo)入數(shù)據(jù)——其實(shí)就是將Excel表格數(shù)據(jù)讀出，然后存儲(chǔ)到Android的數(shù)據(jù)庫(kù)中。（不再原本的Excel表格上進(jìn)行數(shù)據(jù)操作處理，而是導(dǎo)入到Android的數(shù)據(jù)庫(kù)中，如果后面有需要，直接將數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)出（為什么要導(dǎo)入到數(shù)據(jù)庫(kù)中呢，因?yàn)橐淮涡宰x完Excel表格數(shù)據(jù)后，直接寫(xiě)入到數(shù)據(jù)庫(kù)中，Android對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的處理操作簡(jiǎn)單方便，而對(duì)Excel表格進(jìn)行操作就需要使用jxl.jar包了。）。）。
先將jxl.jar包導(dǎo)入到項(xiàng)目中。
然后打開(kāi)MainActivity.java進(jìn)行編碼
查看MainActivity.java中的代碼——選擇那張Excel表格，導(dǎo)入，導(dǎo)出，清空。
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
// Excel表格的表頭
private String[] columnTitle = {"資產(chǎn)編號(hào)", "資產(chǎn)名稱(chēng)", "資產(chǎn)分類(lèi)", "國(guó)標(biāo)分類(lèi)", "實(shí)有數(shù)量", "實(shí)有原值", "實(shí)有累計(jì)折舊", "盤(pán)點(diǎn)結(jié)果", "使用狀況", "產(chǎn)品序列號(hào)",
"賬面數(shù)量", "賬面價(jià)值", "賬面累計(jì)折舊", "賬面凈值", "取得方式", "規(guī)格型號(hào)", "計(jì)量單位", "取得日期", "財(cái)務(wù)入賬日期", "價(jià)值類(lèi)型", "存放地點(diǎn)", "使用部門(mén)", "使用人",
"原資產(chǎn)編號(hào)", "備注"};
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當(dāng)前選中數(shù)據(jù)表
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();
}
/**
* 創(chuàng)建特定文件夾以存放Excel表
*/
private void initFileDir() {
mFileDir = FileUtil.createDir("YunYangData");
}
/**
* 初始化數(shù)據(jù)庫(kù)
*/
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("請(qǐng)您選擇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, "數(shù)據(jù)正在導(dǎo)出中...");
startLoading();
exportCreateExcelFromJxl();
break;
case R.id.btn_delete:
if (currentFile != null) {
deleteDbFromDataSheet(true);
} else {
Toast.makeText(this, "請(qǐng)選中要從本地?cái)?shù)據(jù)庫(kù)中刪除之前導(dǎo)入的Excel文件名稱(chēng)", 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;
}
}
/**
* 檢查數(shù)據(jù)庫(kù)中是否存在當(dāng)前Excel表名，如果存在，導(dǎo)入時(shí)是否覆蓋導(dǎo)入。
*/
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, "數(shù)據(jù)正在導(dǎo)入中...");
if (aVoid > 0) {
new QMUIDialog.MessageDialogBuilder(MainActivity.this)
.setTitle("提示")
.setMessage("本地?cái)?shù)據(jù)庫(kù)中已經(jīng)存在 "
+ StrUtil.getFileNameNoEx(currentFile.getName())
+ " Excel表，是否覆蓋導(dǎo)入？")
.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, "請(qǐng)選中要從本地?cái)?shù)據(jù)庫(kù)中覆蓋之前導(dǎo)入的Excel文件名稱(chēng)", Toast.LENGTH_LONG).show();
}
dialog.dismiss();
}
})
.show();
} else {
readExcelToDB();
}
}
}.execute();
}
/**
* 依據(jù) 所屬數(shù)據(jù)表 列進(jìn)行數(shù)據(jù)刪除
*/
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, "從本地?cái)?shù)據(jù)庫(kù)中刪除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "從本地?cái)?shù)據(jù)庫(kù)中刪除 " + currentFile.getName() + " 失敗", Toast.LENGTH_LONG).show();
}
}
stopLoading();
}
}.execute();
}
/**
* 導(dǎo)出 創(chuàng)建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
// 創(chuàng)建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 創(chuàng)建Sheet表，并寫(xiě)入數(shù)據(jù)
mDbToSchoolList.clear();
/*
導(dǎo)出之前，刪除原表，創(chuàng)建新表
編寫(xiě)導(dǎo)出LitePal語(yǔ)句，導(dǎo)出Excel表格依據(jù)26列（所屬數(shù)據(jù)表進(jìn)行導(dǎo)出）
*/
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, "導(dǎo)出成功！", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(MainActivity.this, "導(dǎo)出失?。?, Toast.LENGTH_LONG).show();
}
stopLoading();
}
}.execute();
}
/**
* 結(jié)束加載Dialog
*/
private void stopLoading() {
if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
mLoadingDialog.dismiss();
}
}
/**
* 開(kāi)始加載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數(shù)據(jù)到數(shù)據(jù)庫(kù)里（LitePal）
*/
private void readExcelToDB() {
startLoading();
new Thread(new Runnable() {
@Override
public void run() {
try {
/*
Android 讀取Assets 下的Excel文件
*/
// InputStream is = context.getAssets().open("康復(fù)所.xls");
// Workbook book = Workbook.getWorkbook(is);
Workbook book = Workbook.getWorkbook(currentFile);
// 獲取表頁(yè)數(shù)
final int bookPage = book.getNumberOfSheets();
Log.e("bookPage", "bookPage = " + bookPage);
// 獲得第一個(gè)工作表對(duì)象
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是盤(pán)點(diǎn)數(shù)量
導(dǎo)入的時(shí)候把實(shí)有數(shù)量給盤(pán)點(diǎn)數(shù)量
導(dǎo)出的時(shí)候把盤(pán)點(diǎn)數(shù)量給實(shí)有數(shù)量
*/
// 導(dǎo)入Db——這里先直接賦值為0
String inventoryData = "0";
// 26是所屬數(shù)據(jù)表
// String ownershipDataSheet = "康復(fù)所";
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, "導(dǎo)入成功", Toast.LENGTH_SHORT).show();
}
});
} catch (Exception e) {
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
Toast.makeText(MainActivity.this, "導(dǎo)入失敗", Toast.LENGTH_SHORT).show();
}
});
}
}
}).start();
}
/**
* 數(shù)據(jù)保存到數(shù)據(jù)庫(kù)中并且去空行
*/
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("請(qǐng)您選擇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;
}
}
}
}
代碼功能，注釋寫(xiě)的很清楚。
數(shù)據(jù)庫(kù)使用的庫(kù)為 LitePal 。
創(chuàng)建Excel表格，使用了 ZzExcelCreator Excel表格生成工具，該庫(kù)本質(zhì)上也是使用jxl.jar
導(dǎo)入——需要注意的是jxl.jar讀寫(xiě)數(shù)據(jù)格式都是字符型數(shù)據(jù)。
/**
* 讀取excel數(shù)據(jù)到數(shù)據(jù)庫(kù)里（LitePal）
*/
private void readExcelToDB() {
startLoading();
new Thread(new Runnable() {
@Override
public void run() {
try {
/*
Android 讀取Assets 下的Excel文件
*/
// InputStream is = context.getAssets().open("康復(fù)所.xls");
// Workbook book = Workbook.getWorkbook(is);
Workbook book = Workbook.getWorkbook(currentFile);
// 獲取表頁(yè)數(shù)
final int bookPage = book.getNumberOfSheets();
Log.e("bookPage", "bookPage = " + bookPage);
// 獲得第一個(gè)工作表對(duì)象
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是盤(pán)點(diǎn)數(shù)量
導(dǎo)入的時(shí)候把實(shí)有數(shù)量給盤(pán)點(diǎn)數(shù)量
導(dǎo)出的時(shí)候把盤(pán)點(diǎn)數(shù)量給實(shí)有數(shù)量
*/
// 導(dǎo)入Db——這里先直接賦值為0
String inventoryData = "0";
// 26是所屬數(shù)據(jù)表
// String ownershipDataSheet = "康復(fù)所";
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, "導(dǎo)入成功", Toast.LENGTH_SHORT).show();
}
});
} catch (Exception e) {
runOnUiThread(new Runnable() {
@Override
public void run() {
stopLoading();
Toast.makeText(MainActivity.this, "導(dǎo)入失敗", Toast.LENGTH_SHORT).show();
}
});
}
}
}).start();
}
導(dǎo)出——?jiǎng)?chuàng)建新的Excel表格，將數(shù)據(jù)庫(kù)中的數(shù)據(jù)按照導(dǎo)入時(shí)的數(shù)據(jù)格式導(dǎo)出到剛創(chuàng)建的新的Excel表格中。
/**
* 導(dǎo)出 創(chuàng)建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask<String, Void, Integer>() {
@Override
protected Integer doInBackground(String... params) {
try {
// 創(chuàng)建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 創(chuàng)建Sheet表，并寫(xiě)入數(shù)據(jù)
mDbToSchoolList.clear();
/*
導(dǎo)出之前，刪除原表，創(chuàng)建新表
編寫(xiě)導(dǎo)出LitePal語(yǔ)句，導(dǎo)出Excel表格依據(jù)26列（所屬數(shù)據(jù)表進(jìn)行導(dǎo)出）
*/
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, "導(dǎo)出成功！", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(MainActivity.this, "導(dǎo)出失??！", 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("頁(yè)簽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數(shù)據(jù)類(lèi)型轉(zhuǎn)換為int數(shù)據(jù)類(lèi)型，取掉小數(shù)點(diǎn)|判空操作
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, "無(wú)盈虧", arial10formatnobg));
} else if (tp > 0) {
sheet.addCell(new Label(7, j + 1, "盤(pán)虧", arial10formatnobg));
} else if (tp < 0) {
sheet.addCell(new Label(7, j + 1, "盤(pán)盈", 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表格都是有填寫(xiě)說(shuō)明的
WritableSheet sheet3 = writebook.createSheet("填寫(xiě)說(shuō)明", 2);
sheet3.addCell(new Label(0, 0, "這是填寫(xiě)說(shuō)明的表", arial10formatnobg));
writebook.write();
Log.e("writeObjListToExcel", "有需要導(dǎo)出的數(shù)據(jù)! ");
} catch (Exception e) {
Log.e("writeObjListToExcel", "進(jìn)來(lái)創(chuàng)建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", "沒(méi)有有需要導(dǎo)出的數(shù)據(jù)! ");
}
}
從數(shù)據(jù)庫(kù)導(dǎo)出數(shù)據(jù)到外部的Excel時(shí)，需要注意兩點(diǎn)
- （一）填寫(xiě)說(shuō)明這個(gè)sheet表
- （二）隱藏表——表是隱藏的，一般都是表回到大系統(tǒng)的時(shí)候會(huì)有這個(gè)。不能忽略，如果忽略，那么表回歸大系統(tǒng)的時(shí)候就會(huì)報(bào)錯(cuò)。
如何查看隱藏表：
hide.png
點(diǎn)擊鼠標(biāo)左鍵取消隱藏后，選擇需要顯示的sheet表以及查看有多少?gòu)埍浑[藏的表。
hidetwo.png
我們可以清楚的看到hidesheet表被隱藏了。那么我們?cè)趯?dǎo)出時(shí)，也需要?jiǎng)?chuàng)建和hidesheet表一模一樣的表。然后導(dǎo)出。
在ExcelUtils.writeSchoolListToExcel()方法中加上
/*
隱藏表
表是隱藏的，一般都是表回到大系統(tǒng)的時(shí)候會(huì)有這個(gè)。
不能忽略，如果忽略，那么表回歸大系統(tǒng)的時(shí)候就會(huì)報(bào)錯(cuò)。
*/
// 隱藏表
sheet2.setHidden(true);
sheet2.addCell(new Label(0, 0, "這是隱藏的表", arial10formatnobg));
// 一般的Excel表格都是有填寫(xiě)說(shuō)明的
WritableSheet sheet3 = writebook.createSheet("填寫(xiě)說(shuō)明", 2);
sheet3.addCell(new Label(0, 0, "這是填寫(xiě)說(shuō)明的表", arial10formatnobg));
這樣之后表回歸大系統(tǒng)進(jìn)行統(tǒng)一查看的時(shí)候，就不會(huì)報(bào)錯(cuò)了。
清空——可以根據(jù)需求清空某些數(shù)據(jù)，這里按照所屬數(shù)據(jù)表進(jìn)行數(shù)據(jù)清空。
/**
* 依據(jù) 所屬數(shù)據(jù)表 列進(jìn)行數(shù)據(jù)刪除
*/
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, "從本地?cái)?shù)據(jù)庫(kù)中刪除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "從本地?cái)?shù)據(jù)庫(kù)中刪除 " + currentFile.getName() + " 失敗", Toast.LENGTH_LONG).show();
}
}
stopLoading();
}
}.execute();
}
導(dǎo)出后的文件放在外部存儲(chǔ)外面（導(dǎo)入的Excel文件放在YunYangData文件夾下）。
導(dǎo)出.png
查詢(xún)——QueryDataActivity.java，按照全部，盤(pán)虧，無(wú)盈虧三個(gè)Tab進(jìn)行分類(lèi)。
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();
}
/**
* 根據(jù)inventoryResults（盤(pán)點(diǎn)結(jié)果）去數(shù)據(jù)庫(kù)中查找對(duì)應(yīng)數(shù)據(jù)
*/
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數(shù)據(jù)庫(kù)中無(wú)數(shù)據(jù)");
mTitles[0] = "全部";
mTitles[1] = "盤(pán)虧";
mTitles[2] = "無(wú)盈虧";
return 0;
} else {
// 全部
final int count = LitePal
.where("ownershipDataSheet = ?", ownershipDataSheetName)
.count(School.class);
// 減去行1
mInts[0] = count - 1;
// 盤(pán)虧
mInts[1] = LitePal
.where("inventoryResults = ? and ownershipDataSheet = ?", "盤(pán)虧", ownershipDataSheetName)
.count(School.class);
// 無(wú)盈虧
mInts[2] = LitePal
.where("inventoryResults = ? and ownershipDataSheet = ?", "無(wú)盈虧", ownershipDataSheetName)
.count(School.class);
mTitles[0] = "全部（" + mInts[0] + "）";
mTitles[1] = "盤(pán)虧（" + mInts[1] + "）";
mTitles[2] = "無(wú)盈虧（" + mInts[2] + "）";
return 1;
}
} catch (Exception e) {
e.printStackTrace();
mTitles[0] = "全部";
mTitles[1] = "盤(pán)虧";
mTitles[2] = "無(wú)盈虧";
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中三個(gè)Tab——FragmentDish.java
/**
* 作者 yunyang
* 時(shí)間 2019/1/7 9:38
* 文件 DataConversion
* 描述 盤(pán)的碎片（全部——盤(pán)虧——無(wú)盈盤(pán)）
*/
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:
// 盤(pán)虧
findDbLitePal("盤(pán)虧");
break;
case 2:
// 無(wú)盈虧
findDbLitePal("無(wú)盈虧");
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(), "數(shù)據(jù)正在加載中...");
mFragmentDishAdapter = new DishFragmentAdapter(getActivity(), mDbToSchoolList);
LinearLayoutManager manager = new LinearLayoutManager(getActivity());
mRecyclerView.setLayoutManager(manager);
mRecyclerView.setAdapter(mFragmentDishAdapter);
}
/**
* 結(jié)束加載Dialog
*/
private void stopLoading() {
if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
mLoadingDialog.dismiss();
}
}
/**
* 開(kāi)始加載Dialog
*/
private void startLoading() {
if (mLoadingDialog != null && !mLoadingDialog.isShowing()) {
mLoadingDialog.show();
}
}
/**
* 根據(jù)inventoryResults（盤(pán)點(diǎn)結(jié)果）去數(shù)據(jù)庫(kù)中查找對(duì)應(yīng)數(shù)據(jù)
*
* @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();
}
}
查詢(xún)頁(yè)面就是查找數(shù)據(jù)庫(kù)中的數(shù)據(jù)，然后進(jìn)行數(shù)據(jù)集顯示到RecyclerView的Item上。
ExcelQuery.gif
使用此案例，當(dāng)App安裝到手機(jī)上，就會(huì)在外部存儲(chǔ)中創(chuàng)建YunYangData文件夾，然后可以把固定的文件格式的Excel文件放入其中。使用App進(jìn)行處理。（也可以先創(chuàng)建YunYangData文件夾，然后把固定的文件格式的Excel文件放入其中，打開(kāi)App進(jìn)行操作）。
