Android开发实现的导出数据库到Excel表格功能【附源码下载】
2019年04月20日
作者：佚名
本文实例讲述了Android开发实现的导出数据库到Excel表格功能。分享给大家供大家参考，具体如下：
之前一直在电脑上用Excel表格记录家庭帐单，不久前重装系统不小心干掉了，伤心了好久，那可是我记了五年的帐单呀！这段时间用的是随手记，好用但是不太符合我的习惯，所以我自己写了一个小小的帐单记录APP，App小到只有一个Activity。当然更多的需求我正在研发中，呵呵！现在已经完成了把每天记录的数据保存到Sqilte数据库中，然后可以导出到excel表格。代码也是借助网上的一些资料写成的，代码也比较容易，只需要用到一个jxl.jar包，感谢网友的帮助。
贴上主要代码，再附上文件包：
MainActivity.java：
package com.ldm.familybill;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Environment;
import android.text.TextUtils;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.ldm.db.DBHelper;
import com.ldm.excel.ExcelUtils;
@SuppressLint("SimpleDateFormat")
public class MainActivity extends Activity implements OnClickListener {
private EditText mFoodEdt;
private EditText mArticlesEdt;
private EditText mTrafficEdt;
private EditText mTravelEdt;
private EditText mClothesEdt;
private EditText mDoctorEdt;
private EditText mRenQingEdt;
private EditText mBabyEdt;
private EditText mLiveEdt;
private EditText mOtherEdt;
private EditText mRemarkEdt;
private Button mSaveBtn;
private File file;
private String[] title = { "日期","食物支出","日用品项","交通话费","旅游出行","穿着支出","医疗保健","人情客往","宝宝专项","房租水电","其它支出","备注说明" };
private String[] saveData;
private DBHelper mDbHelper;
private ArrayList<ArrayList<String>>bill2List;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewsById();
mDbHelper = new DBHelper(this);
mDbHelper.open();
bill2List=new ArrayList<ArrayList<String>>();
}
private void findViewsById() {
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
public void onClick(View v) {
if (v.getId() == R.id.family_bill_save) {
saveData = new String[] { new SimpleDateFormat("yyyy-MM-dd").format(new Date()),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)) {
ContentValues values = new ContentValues();
values.put("time",new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
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());
long insert = mDbHelper.insert("family_bill",values);
if (insert > 0) {
initData();
}
}
else {
Toast.makeText(this,"请填写任意一项内容",Toast.LENGTH_SHORT).show();
}
}
}
@SuppressLint("SimpleDateFormat")
public void initData() {
file = new File(getSDPath() + "/Family");
makeDir(file);
ExcelUtils.initExcel(file.toString() + "/bill.xls",title);
ExcelUtils.writeObjListToExcel(getBillData(),getSDPath() + "/Family/bill.xls",this);
}
private ArrayList<ArrayList<String>> getBillData() {
Cursor mCrusor = mDbHelper.exeSql("select * from family_bill");
while (mCrusor.moveToNext()) {
ArrayList<String> beanList=new ArrayList<String>();
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();
return bill2List;
}
public static void makeDir(File dir) {
if (!dir.getParentFile().exists()) {
makeDir(dir.getParentFile());
}
dir.mkdir();
}
public String getSDPath() {
File sdDir = null;
boolean sdCardExist = Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED);
if (sdCardExist) {
sdDir = Environment.getExternalStorageDirectory();
}
String dir = sdDir.toString();
return dir;
}
private boolean canSave(String[] data) {
boolean isOk = false;
for (int i = 0; i < data.length; i++) {
if (i > 0 && i < data.length) {
if (!TextUtils.isEmpty(data[i])) {
isOk = true;
}
}
}
return isOk;
}
}
CreateExcel.java：
package com.ldm.excel;
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import android.os.Environment;
public class CreateExcel {
// 准备设置excel工作表的标题
private WritableSheet sheet;
/**创建Excel工作薄*/
private WritableWorkbook wwb;
private String[] title = { "日期","备注说明" };
public CreateExcel() {
excelCreate();
}
public void excelCreate() {
try {
/**输出的excel文件的路径*/
String filePath = Environment.getExternalStorageDirectory() + "/family_bill";
File file = new File(filePath,"bill.xls");
if (!file.exists()) {
file.createNewFile();
}
wwb = Workbook.createWorkbook(file);
/**添加第一个工作表并设置第一个Sheet的名字*/
sheet = wwb.createSheet("家庭帐务表",0);
}
catch (Exception e) {
e.printStackTrace();
}
}
public void saveDataToExcel(int index,String[] content) throws Exception {
Label label;
for (int i = 0; i < title.length; i++) {
/**Label(x,y,z)其中x代表单元格的第x+1列，第y+1行,单元格的内容是y
* 在Label对象的子对象中指明单元格的位置和内容
* */
label = new Label(i,title[i]);
/**将定义好的单元格添加到工作表中*/
sheet.addCell(label);
}
/*
* 把数据填充到单元格中
* 需要使用jxl.write.Number
* 路径必须使用其完整路径，否则会出现错误
*/
for (int i = 0; i < title.length; i++) {
Label labeli = new Label(i,index,content[i]);
sheet.addCell(labeli);
}
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
}
}
DBHelper.java：
package com.ldm.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "ldm_family"; // DB name
private Context mcontext;
private DBHelper mDbHelper;
private SQLiteDatabase db;
public DBHelper(Context context) {
super(context,DB_NAME,null,11);
this.mcontext = context;
}
public DBHelper(Context context,String name,CursorFactory factory,int version) {
super(context,name,factory,version);
}
/**
* 用户第一次使用软件时调用的操作，用于获取数据库创建语句（SW）,然后创建数据库
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table if not exists family_bill(id integer primary key,time text,food text,use text,traffic text,travel text,clothes text,doctor text,laiwang text,baby text,live text,other text,remark text)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion) {
}
/* 打开数据库,如果已经打开就使用，否则创建 */
public DBHelper open() {
if (null == mDbHelper) {
mDbHelper = new DBHelper(mcontext);
}
db = mDbHelper.getWritableDatabase();
return this;
}
/* 关闭数据库 */
public void close() {
db.close();
mDbHelper.close();
}
/**添加数据 */
public long insert(String tableName,ContentValues values) {
return db.insert(tableName,values);
}
/**查询数据*/
public Cursor findList(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit) {
return db.query(tableName,columns,selection,selectionArgs,groupBy,having,orderBy,limit);
}
public Cursor exeSql(String sql) {
return db.rawQuery(sql,null);
}
}
ExcelUtils.java：
package com.ldm.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import android.content.Context;
import android.widget.Toast;
public class ExcelUtils {
public static WritableFont arial14font = null;
public static WritableCellFormat arial14format = null;
public static WritableFont arial10font = null;
public static WritableCellFormat arial10format = null;
public static WritableFont arial12font = null;
public static WritableCellFormat arial12format = null;
public final static String UTF8_ENCODING = "UTF-8";
public final static String GBK_ENCODING = "GBK";
public static void format() {
try {
arial14font = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
arial14format = new WritableCellFormat(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 = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
arial10format = new WritableCellFormat(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 = new WritableFont(WritableFont.ARIAL,12);
arial12format = new WritableCellFormat(arial12font);
arial12format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
}
catch (WriteException e) {
e.printStackTrace();
}
}
public static void initExcel(String fileName,String[] colName) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("家庭帐务表",0);
sheet.addCell((WritableCell) new Label(0,fileName,arial14format));
for (int col = 0; col < colName.length; col++) {
sheet.addCell(new Label(col,colName[col],arial10format));
}
workbook.write();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (workbook != null) {
try {
workbook.close();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList,String fileName,Context c) {
if (objList != null && objList.size() > 0) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName),workbook);
WritableSheet sheet = writebook.getSheet(0);
for (int j = 0; j < objList.size(); j++) {
ArrayList<String> list=(ArrayList<String>) objList.get(j);
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(i,j+1,list.get(i),arial12format));
}
}
writebook.write();
Toast.makeText(c,"保存成功",Toast.LENGTH_SHORT).show();
}
catch (Exception e) {
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();
}
}
}
}
}
public static Object getValueByRef(Class cls,String fieldName) {
Object value = null;
fieldName = fieldName.replaceFirst(fieldName.substring(0,1),fieldName.substring(0,1).toUpperCase());
String getMethodName = "get" + fieldName;
try {
Method method = cls.getMethod(getMethodName);
value = method.invoke(cls);
}
catch (Exception e) {
e.printStackTrace();
}
return value;
}
}
附：完整源码点击此处本站下载。
更多关于Android相关内容感兴趣的读者可查看本站专题：《Android文件操作技巧汇总》、《Android视图View技巧总结》、《Android编程之activity操作技巧总结》、《Android布局layout技巧总结》、《Android开发入门与进阶教程》、《Android资源操作技巧汇总》及《Android控件用法总结》
希望本文所述对大家Android程序设计有所帮助。
您可能感兴趣的文章:
- android通过jxl读excel存入sqlite3数据库
- Android导入现有的数据库方法示例
- android实现raw文件夹导入数据库代码
- Android应用读取Excel文件的方法
- Android操作Excel文件的功能实现
- android读取assets中Excel表格并显示
- Android开发实现生成excel的方法详解
- Android开发实现读取excel数据并保存为xml的方法
- Android SQLite数据库增删改查操作的使用详解
- Android使用SQLite数据库的简单实例
- Android操作SQLite数据库(增、删、改、查、分页等)及ListView显示数据的方法详解
以上是来客网为你收集整理的Android开发实现的导出数据库到Excel表格功能【附源码下载】全部内容，希望文章能够帮你解决Android开发实现的导出数据库到Excel表格功能【附源码下载】所遇到的程序开发问题。
如果觉得来客网网站内容还不错，欢迎将来客网网站推荐给程序员好友。
