Android开发实现的导出数据库到Excel表格功能【附源码下载】
本文实例讲述了Android开发实现的导出数据库到Excel表格功能。分享给大家供大家参考，具体如下：
之前一直在电脑上用Excel表格记录家庭帐单，不久前重装系统不小心干掉了，伤心了好久，那可是我记了五年的帐单呀！这段时间用的是随手记，好用但是不太符合我的习惯，所以我自己写了一个小小的帐单记录APP，App小到只有一个Activity。当然更多的需求我正在研发中，呵呵！现在已经完成了把每天记录的数据保存到Sqilte数据库中，然后可以导出到excel表格。代码也是借助网上的一些资料写成的，代码也比较容易，只需要用到一个jxl.jar包，感谢网友的帮助。
贴上主要代码，再附上文件包：
MainActivity.java：
packagecom.ldm.familybill;
importjava.io.File;
importjava.text.SimpleDateFormat;
importjava.util.ArrayList;
importjava.util.Date;
importandroid.annotation.SuppressLint;
importandroid.app.Activity;
importandroid.content.ContentValues;
importandroid.database.Cursor;
importandroid.os.Bundle;
importandroid.os.Environment;
importandroid.text.TextUtils;
importandroid.view.View;
importandroid.view.View.OnClickListener;
importandroid.widget.Button;
importandroid.widget.EditText;
importandroid.widget.Toast;
importcom.ldm.db.DBHelper;
importcom.ldm.excel.ExcelUtils;
@SuppressLint("SimpleDateFormat")
publicclassMainActivityextendsActivityimplementsOnClickListener{
privateEditTextmFoodEdt;
privateEditTextmArticlesEdt;
privateEditTextmTrafficEdt;
privateEditTextmTravelEdt;
privateEditTextmClothesEdt;
privateEditTextmDoctorEdt;
privateEditTextmRenQingEdt;
privateEditTextmBabyEdt;
privateEditTextmLiveEdt;
privateEditTextmOtherEdt;
privateEditTextmRemarkEdt;
privateButtonmSaveBtn;
privateFilefile;
privateString[]title={"日期","食物支出","日用品项","交通话费","旅游出行","穿着支出","医疗保健","人情客往","宝宝专项","房租水电","其它支出","备注说明"};
privateString[]saveData;
privateDBHelpermDbHelper;
privateArrayList>bill2List;
@Override
protectedvoidonCreate(BundlesavedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewsById();
mDbHelper=newDBHelper(this);
mDbHelper.open();
bill2List=newArrayList>();
}
privatevoidfindViewsById(){
mFoodEdt=(EditText)findViewById(R.id.family_bill_food_edt);
mArticlesEdt=(EditText)findViewById(R.id.family_bill_articles_edt);
mTrafficEdt=(EditText)findViewById(R.id.family_bill_traffic_edt);
mTravelEdt=(EditText)findViewById(R.id.family_bill_travel_edt);
mClothesEdt=(EditText)findViewById(R.id.family_bill_clothes_edt);
mDoctorEdt=(EditText)findViewById(R.id.family_bill_doctor_edt);
mRenQingEdt=(EditText)findViewById(R.id.family_bill_laiwang_edt);
mBabyEdt=(EditText)findViewById(R.id.family_bill_baby_edt);
mLiveEdt=(EditText)findViewById(R.id.family_bill_live_edt);
mOtherEdt=(EditText)findViewById(R.id.family_bill_other_edt);
mRemarkEdt=(EditText)findViewById(R.id.family_bill_remark_edt);
mSaveBtn=(Button)findViewById(R.id.family_bill_save);
mSaveBtn.setOnClickListener(this);
}
@Override
publicvoidonClick(Viewv){
if(v.getId()==R.id.family_bill_save){
saveData=newString[]{newSimpleDateFormat("yyyy-MM-dd").format(newDate()),mFoodEdt.getText().toString().trim(),mArticlesEdt.getText().toString().trim(),mTrafficEdt.getText().toString().trim(),mTravelEdt.getText().toString().trim(),mClothesEdt.getText().toString().trim(),mDoctorEdt.getText().toString().trim(),mRenQingEdt.getText().toString().trim(),mBabyEdt.getText().toString().trim(),mLiveEdt.getText().toString().trim(),mOtherEdt.getText().toString().trim(),mRemarkEdt.getText().toString().trim()};
if(canSave(saveData)){
ContentValuesvalues=newContentValues();
values.put("time",newSimpleDateFormat("yyyy-MM-dd").format(newDate()));
values.put("food",mFoodEdt.getText().toString());
values.put("use",mArticlesEdt.getText().toString());
values.put("traffic",mTrafficEdt.getText().toString());
values.put("travel",mTravelEdt.getText().toString());
values.put("clothes",mClothesEdt.getText().toString());
values.put("doctor",mDoctorEdt.getText().toString());
values.put("laiwang",mRenQingEdt.getText().toString());
values.put("baby",mBabyEdt.getText().toString());
values.put("live",mLiveEdt.getText().toString());
values.put("other",mOtherEdt.getText().toString());
values.put("remark",mRemarkEdt.getText().toString());
longinsert=mDbHelper.insert("family_bill",values);
if(insert>0){
initData();
}
}
else{
Toast.makeText(this,"请填写任意一项内容",Toast.LENGTH_SHORT).show();
}
}
}
@SuppressLint("SimpleDateFormat")
publicvoidinitData(){
file=newFile(getSDPath()+"/Family");
makeDir(file);
ExcelUtils.initExcel(file.toString()+"/bill.xls",title);
ExcelUtils.writeObjListToExcel(getBillData(),getSDPath()+"/Family/bill.xls",this);
}
privateArrayList>getBillData(){
CursormCrusor=mDbHelper.exeSql("select*fromfamily_bill");
while(mCrusor.moveToNext()){
ArrayListbeanList=newArrayList();
beanList.add(mCrusor.getString(1));
beanList.add(mCrusor.getString(2));
beanList.add(mCrusor.getString(3));
beanList.add(mCrusor.getString(4));
beanList.add(mCrusor.getString(5));
beanList.add(mCrusor.getString(6));
beanList.add(mCrusor.getString(7));
beanList.add(mCrusor.getString(8));
beanList.add(mCrusor.getString(9));
beanList.add(mCrusor.getString(10));
beanList.add(mCrusor.getString(11));
beanList.add(mCrusor.getString(12));
bill2List.add(beanList);
}
mCrusor.close();
returnbill2List;
}
publicstaticvoidmakeDir(Filedir){
if(!dir.getParentFile().exists()){
makeDir(dir.getParentFile());
}
dir.mkdir();
}
publicStringgetSDPath(){
FilesdDir=null;
booleansdCardExist=Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED);
if(sdCardExist){
sdDir=Environment.getExternalStorageDirectory();
}
Stringdir=sdDir.toString();
returndir;
}
privatebooleancanSave(String[]data){
booleanisOk=false;
for(inti=0;i0&&i
CreateExcel.java：
packagecom.ldm.excel;
importjava.io.File;
importjxl.Workbook;
importjxl.write.Label;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
importandroid.os.Environment;
publicclassCreateExcel{
//准备设置excel工作表的标题
privateWritableSheetsheet;
/**创建Excel工作薄*/
privateWritableWorkbookwwb;
privateString[]title={"日期","食物支出","日用品项","交通话费","旅游出行","穿着支出","医疗保健","人情客往","宝宝专项","房租水电","其它支出","备注说明"};
publicCreateExcel(){
excelCreate();
}
publicvoidexcelCreate(){
try{
/**输出的excel文件的路径*/
StringfilePath=Environment.getExternalStorageDirectory()+"/family_bill";
Filefile=newFile(filePath,"bill.xls");
if(!file.exists()){
file.createNewFile();
}
wwb=Workbook.createWorkbook(file);
/**添加第一个工作表并设置第一个Sheet的名字*/
sheet=wwb.createSheet("家庭帐务表",0);
}
catch(Exceptione){
e.printStackTrace();
}
}
publicvoidsaveDataToExcel(intindex,String[]content)throwsException{
Labellabel;
for(inti=0;i
DBHelper.java：
packagecom.ldm.db;
importandroid.content.ContentValues;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteDatabase.CursorFactory;
importandroid.database.sqlite.SQLiteOpenHelper;
publicclassDBHelperextendsSQLiteOpenHelper{
publicstaticfinalStringDB_NAME="ldm_family";//DBname
privateContextmcontext;
privateDBHelpermDbHelper;
privateSQLiteDatabasedb;
publicDBHelper(Contextcontext){
super(context,DB_NAME,null,11);
this.mcontext=context;
}
publicDBHelper(Contextcontext,Stringname,CursorFactoryfactory,intversion){
super(context,name,factory,version);
}
/**
*用户第一次使用软件时调用的操作，用于获取数据库创建语句（SW）,然后创建数据库
*/
@Override
publicvoidonCreate(SQLiteDatabasedb){
Stringsql="createtableifnotexistsfamily_bill(idintegerprimarykey,timetext,foodtext,usetext,traffictext,traveltext,clothestext,doctortext,laiwangtext,babytext,livetext,othertext,remarktext)";
db.execSQL(sql);
}
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
}
/*打开数据库,如果已经打开就使用，否则创建*/
publicDBHelperopen(){
if(null==mDbHelper){
mDbHelper=newDBHelper(mcontext);
}
db=mDbHelper.getWritableDatabase();
returnthis;
}
/*关闭数据库*/
publicvoidclose(){
db.close();
mDbHelper.close();
}
/**添加数据*/
publiclonginsert(StringtableName,ContentValuesvalues){
returndb.insert(tableName,null,values);
}
/**查询数据*/
publicCursorfindList(StringtableName,String[]columns,Stringselection,String[]selectionArgs,StringgroupBy,Stringhaving,StringorderBy,Stringlimit){
returndb.query(tableName,columns,selection,selectionArgs,groupBy,having,orderBy,limit);
}
publicCursorexeSql(Stringsql){
returndb.rawQuery(sql,null);
}
}
ExcelUtils.java：
packagecom.ldm.excel;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.lang.reflect.Method;
importjava.util.ArrayList;
importjava.util.List;
importjxl.Workbook;
importjxl.WorkbookSettings;
importjxl.write.Label;
importjxl.write.WritableCell;
importjxl.write.WritableCellFormat;
importjxl.write.WritableFont;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
importjxl.write.WriteException;
importandroid.content.Context;
importandroid.widget.Toast;
publicclassExcelUtils{
publicstaticWritableFontarial14font=null;
publicstaticWritableCellFormatarial14format=null;
publicstaticWritableFontarial10font=null;
publicstaticWritableCellFormatarial10format=null;
publicstaticWritableFontarial12font=null;
publicstaticWritableCellFormatarial12format=null;
publicfinalstaticStringUTF8_ENCODING="UTF-8";
publicfinalstaticStringGBK_ENCODING="GBK";
publicstaticvoidformat(){
try{
arial14font=newWritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
arial14format=newWritableCellFormat(arial14font);
arial14format.setAlignment(jxl.format.Alignment.CENTRE);
arial14format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
arial10font=newWritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
arial10format=newWritableCellFormat(arial10font);
arial10format.setAlignment(jxl.format.Alignment.CENTRE);
arial10format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
arial10format.setBackground(jxl.format.Colour.LIGHT_BLUE);
arial12font=newWritableFont(WritableFont.ARIAL,12);
arial12format=newWritableCellFormat(arial12font);
arial12format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
}
catch(WriteExceptione){
e.printStackTrace();
}
}
publicstaticvoidinitExcel(StringfileName,String[]colName){
format();
WritableWorkbookworkbook=null;
try{
Filefile=newFile(fileName);
if(!file.exists()){
file.createNewFile();
}
workbook=Workbook.createWorkbook(file);
WritableSheetsheet=workbook.createSheet("家庭帐务表",0);
sheet.addCell((WritableCell)newLabel(0,0,fileName,arial14format));
for(intcol=0;colvoidwriteObjListToExcel(ListobjList,StringfileName,Contextc){
if(objList!=null&&objList.size()>0){
WritableWorkbookwritebook=null;
InputStreamin=null;
try{
WorkbookSettingssetEncode=newWorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in=newFileInputStream(newFile(fileName));
Workbookworkbook=Workbook.getWorkbook(in);
writebook=Workbook.createWorkbook(newFile(fileName),workbook);
WritableSheetsheet=writebook.getSheet(0);
for(intj=0;jlist=(ArrayList)objList.get(j);
for(inti=0;i
附：完整源码点击此处本站下载。
更多关于Android相关内容感兴趣的读者可查看本站专题：《Android文件操作技巧汇总》、《Android视图View技巧总结》、《Android编程之activity操作技巧总结》、《Android布局layout技巧总结》、《Android开发入门与进阶教程》、《Android资源操作技巧汇总》及《Android控件用法总结》
希望本文所述对大家Android程序设计有所帮助。