android jxl.jar 使用,使用jxl.jar在Android中操作Excel表格——重中之重——对隐藏表的处理...
曾簡單了解過C#，將Excel(數(shù)據(jù)庫表)表中的數(shù)據(jù)導入到C#中，使用C#制作的圖形化界面進行對Excel表中數(shù)據(jù)進行操作。
今天想試試，在Android中導入Excel表格進行操作。在網(wǎng)上查閱資料，找到了jxl.jar包。
jxl.jar
jxl.jar是通過java操作excel表格的工具類庫，是由java語言開發(fā)而成的。
本篇文章使用jxl.jar在Android中操作Excel表格實現(xiàn)四個功能
導入——將外部Excel表格導入到Android中(Android中的數(shù)據(jù)庫中)。
導出——將在Android中的數(shù)據(jù)庫中的Excel表格數(shù)據(jù)進行處理后，導出到手機的外部存儲中
清空——清空在Android中的數(shù)據(jù)庫中的Excel表格數(shù)據(jù)
查詢——查詢在Android中的數(shù)據(jù)庫中的Excel表格數(shù)據(jù)
先看一下Demo案例效果圖：
Excel.gif
導入——將外部Excel表格導入到Android中(Android中的數(shù)據(jù)庫中)。
先看一下Excel表格數(shù)據(jù)格式。
Excel表格.png
導入數(shù)據(jù)——其實就是將Excel表格數(shù)據(jù)讀出，然后存儲到Android的數(shù)據(jù)庫中。(不再原本的Excel表格上進行數(shù)據(jù)操作處理，而是導入到Android的數(shù)據(jù)庫中，如果后面有需要，直接將數(shù)據(jù)庫中的數(shù)據(jù)導出(為什么要導入到數(shù)據(jù)庫中呢，因為一次性讀完Excel表格數(shù)據(jù)后，直接寫入到數(shù)據(jù)庫中，Android對數(shù)據(jù)庫中的數(shù)據(jù)的處理操作簡單方便，而對Excel表格進行操作就需要使用jxl.jar包了。)。)。
先將jxl.jar包導入到項目中。
然后打開MainActivity.java進行編碼
查看MainActivity.java中的代碼——選擇那張Excel表格，導入，導出，清空。
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
// Excel表格的表頭
private String[] columnTitle = {"資產(chǎn)編號", "資產(chǎn)名稱", "資產(chǎn)分類", "國標分類", "實有數(shù)量", "實有原值", "實有累計折舊", "盤點結果", "使用狀況", "產(chǎn)品序列號",
"賬面數(shù)量", "賬面價值", "賬面累計折舊", "賬面凈值", "取得方式", "規(guī)格型號", "計量單位", "取得日期", "財務入賬日期", "價值類型", "存放地點", "使用部門", "使用人",
"原資產(chǎn)編號", "備注"};
private AppCompatButton btn_import;
private AppCompatButton btn_export;
private AppCompatButton btn_delete;
private AppCompatButton btn_query;
// 加載中的Dialog
private LoadingDialog mLoadingDialog;
private List mSchoolListToDb;
private List mDbToSchoolList;
private List mSchoolsExcelName;
private Set mHashSet;
private File mFileDir;
private Vector mSubFileVector;
// Spinner當前選中數(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ù)庫
*/
private void initData() {
mSchoolListToDb = new ArrayList<>();
mDbToSchoolList = new ArrayList<>();
LitePal.getDatabase();
mSchoolsExcelName = new ArrayList<>();
mHashSet = new HashSet<>();
mSubFileVector = new Vector();
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 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ù)正在導出中...");
startLoading();
exportCreateExcelFromJxl();
break;
case R.id.btn_delete:
if (currentFile != null) {
deleteDbFromDataSheet(true);
} else {
Toast.makeText(this, "請選中要從本地數(shù)據(jù)庫中刪除之前導入的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;
}
}
/**
* 檢查數(shù)據(jù)庫中是否存在當前Excel表名，如果存在，導入時是否覆蓋導入。
*/
private void checkCurrentFileNameFromDb() {
new AsyncTask() {
@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ù)正在導入中...");
if (aVoid > 0) {
new QMUIDialog.MessageDialogBuilder(MainActivity.this)
.setTitle("提示")
.setMessage("本地數(shù)據(jù)庫中已經(jīng)存在 "
+ 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, "請選中要從本地數(shù)據(jù)庫中覆蓋之前導入的Excel文件名稱", Toast.LENGTH_LONG).show();
}
dialog.dismiss();
}
})
.show();
} else {
readExcelToDB();
}
}
}.execute();
}
/**
* 依據(jù) 所屬數(shù)據(jù)表 列進行數(shù)據(jù)刪除
*/
private void deleteDbFromDataSheet(final boolean flag) {
startLoading();
new AsyncTask() {
@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, "從本地數(shù)據(jù)庫中刪除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "從本地數(shù)據(jù)庫中刪除 " + currentFile.getName() + " 失敗", Toast.LENGTH_LONG).show();
}
}
stopLoading();
}
}.execute();
}
/**
* 導出 創(chuàng)建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask() {
@Override
protected Integer doInBackground(String... params) {
try {
// 創(chuàng)建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 創(chuàng)建Sheet表，并寫入數(shù)據(jù)
mDbToSchoolList.clear();
/*
導出之前，刪除原表，創(chuàng)建新表
編寫導出LitePal語句，導出Excel表格依據(jù)26列(所屬數(shù)據(jù)表進行導出)
*/
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數(shù)據(jù)到數(shù)據(jù)庫里(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);
// 獲取表頁數(shù)
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是盤點數(shù)量
導入的時候把實有數(shù)量給盤點數(shù)量
導出的時候把盤點數(shù)量給實有數(shù)量
*/
// 導入Db——這里先直接賦值為0
String inventoryData = "0";
// 26是所屬數(shù)據(jù)表
// 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();
}
/**
* 數(shù)據(jù)保存到數(shù)據(jù)庫中并且去空行
*/
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;
}
}
}
}
代碼功能，注釋寫的很清楚。
數(shù)據(jù)庫使用的庫為 LitePal 。
創(chuàng)建Excel表格，使用了 ZzExcelCreator Excel表格生成工具，該庫本質上也是使用jxl.jar
導入——需要注意的是jxl.jar讀寫數(shù)據(jù)格式都是字符型數(shù)據(jù)。
/**
* 讀取excel數(shù)據(jù)到數(shù)據(jù)庫里(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);
// 獲取表頁數(shù)
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是盤點數(shù)量
導入的時候把實有數(shù)量給盤點數(shù)量
導出的時候把盤點數(shù)量給實有數(shù)量
*/
// 導入Db——這里先直接賦值為0
String inventoryData = "0";
// 26是所屬數(shù)據(jù)表
// 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();
}
導出——創(chuàng)建新的Excel表格，將數(shù)據(jù)庫中的數(shù)據(jù)按照導入時的數(shù)據(jù)格式導出到剛創(chuàng)建的新的Excel表格中。
/**
* 導出 創(chuàng)建Excel表格，使用Jxl
*/
private void exportCreateExcelFromJxl() {
new AsyncTask() {
@Override
protected Integer doInBackground(String... params) {
try {
// 創(chuàng)建Excel表格
ExcelUtils
.initExcel(FileUtil
.createFile(currentFile.getName())
.getAbsolutePath(), columnTitle);
// 創(chuàng)建Sheet表，并寫入數(shù)據(jù)
mDbToSchoolList.clear();
/*
導出之前，刪除原表，創(chuàng)建新表
編寫導出LitePal語句，導出Excel表格依據(jù)26列(所屬數(shù)據(jù)表進行導出)
*/
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 void writeSchoolListToExcel(List 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數(shù)據(jù)類型轉換為int數(shù)據(jù)類型，取掉小數(shù)點|判空操作
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", "有需要導出的數(shù)據(jù)! ");
} catch (Exception e) {
Log.e("writeObjListToExcel", "進來創(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", "沒有有需要導出的數(shù)據(jù)! ");
}
}
從數(shù)據(jù)庫導出數(shù)據(jù)到外部的Excel時，需要注意兩點
(一)填寫說明這個sheet表
(二)隱藏表——表是隱藏的，一般都是表回到大系統(tǒng)的時候會有這個。不能忽略，如果忽略，那么表回歸大系統(tǒng)的時候就會報錯。
如何查看隱藏表：
hide.png
點擊鼠標左鍵取消隱藏后，選擇需要顯示的sheet表以及查看有多少張被隱藏的表。
hidetwo.png
我們可以清楚的看到hidesheet表被隱藏了。那么我們在導出時，也需要創(chuàng)建和hidesheet表一模一樣的表。然后導出。
在ExcelUtils.writeSchoolListToExcel()方法中加上
/*
隱藏表
表是隱藏的，一般都是表回到大系統(tǒng)的時候會有這個。
不能忽略，如果忽略，那么表回歸大系統(tǒng)的時候就會報錯。
*/
// 隱藏表
sheet2.setHidden(true);
sheet2.addCell(new Label(0, 0, "這是隱藏的表", arial10formatnobg));
// 一般的Excel表格都是有填寫說明的
WritableSheet sheet3 = writebook.createSheet("填寫說明", 2);
sheet3.addCell(new Label(0, 0, "這是填寫說明的表", arial10formatnobg));
這樣之后表回歸大系統(tǒng)進行統(tǒng)一查看的時候，就不會報錯了。
清空——可以根據(jù)需求清空某些數(shù)據(jù)，這里按照所屬數(shù)據(jù)表進行數(shù)據(jù)清空。
/**
* 依據(jù) 所屬數(shù)據(jù)表 列進行數(shù)據(jù)刪除
*/
private void deleteDbFromDataSheet(final boolean flag) {
startLoading();
new AsyncTask() {
@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, "從本地數(shù)據(jù)庫中刪除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
}
} else {
if (flag) {
stopLoading();
Toast.makeText(MainActivity.this, "從本地數(shù)據(jù)庫中刪除 " + 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 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(盤點結果)去數(shù)據(jù)庫中查找對應數(shù)據(jù)
*/
private void findDbLitePal() {
new AsyncTask() {
@Override
protected Integer doInBackground(String... params) {
try {
if (LitePal.count(School.class) <= 0) {
Log.e("Excel", "LitePal數(shù)據(jù)庫中無數(shù)據(jù)");
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 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(), "數(shù)據(jù)正在加載中...");
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();
}
}
/**
* 根據(jù)inventoryResults(盤點結果)去數(shù)據(jù)庫中查找對應數(shù)據(jù)
*
* @param string
*/
private void findDbLitePal(String string) {
startLoading();
new AsyncTask() {
@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();
}
}
查詢頁面就是查找數(shù)據(jù)庫中的數(shù)據(jù)，然后進行數(shù)據(jù)集顯示到RecyclerView的Item上。
ExcelQuery.gif
使用此案例，當App安裝到手機上，就會在外部存儲中創(chuàng)建YunYangData文件夾，然后可以把固定的文件格式的Excel文件放入其中。使用App進行處理。(也可以先創(chuàng)建YunYangData文件夾，然后把固定的文件格式的Excel文件放入其中，打開App進行操作)。
此Demo代碼下載
總結
以上是生活随笔為你收集整理的android jxl.jar 使用,使用jxl.jar在Android中操作Excel表格——重中之重——对隐藏表的处理...的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python opencv创建图像_Op
- 下一篇： python去停用词用nltk_使用nl