Android开发实现的导出数据库到Excel表格功能【附源码下载】

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[]  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;  }}

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[]  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, 0, 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();  }}

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, null, 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);  }}

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, 0, fileName, arial14format));      for (int col = 0; col < colName.length; col++) {        sheet.addCell(new Label(col, 0, 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;  }}