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

6
回复
653
查看
[复制链接]

434

主题

1117

帖子

467

安币

手工艺人

发表于 2018-3-27 11:54:32 | 显示全部楼层 |阅读模式

            

        本文实例讲述了android开发实现的导出数据库到excel表格功能。分享给大家供大家参考,具体如下:

        之前一直在电脑上用excel表格记录家庭帐单,不久前重装系统不小心干掉了,伤心了好久,那可是我记了五年的帐单呀!这段时间用的是随手记,好用但是不太符合我的习惯,所以我自己写了一个小小的帐单记录app,app小到只有一个activity。当然更多的需求我正在研发中,呵呵!现在已经完成了把每天记录的数据保存到sqilte数据库中,然后可以导出到excel表格。代码也是借助网上的一些资料写成的,代码也比较容易,只需要用到一个jxl.jar包,感谢网友的帮助。

        贴上主要代码,再附上文件包:

        mainactivity.java:

[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:

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

        dbhelper.java:

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

        excelutils.java:

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

        附:完整源码点击此处本站下载

        更多关于android相关内容感兴趣的读者可查看本站专题:《android文件操作技巧汇总》、《android视图view技巧总结》、《android编程之activity操作技巧总结》、《android布局layout技巧总结》、《android开发入门与进阶教程》、《android资源操作技巧汇总》及《android控件用法总结》






        

11

主题

9473

帖子

971

安币

代码手工艺人

Rank: 4

发表于 2018-3-27 18:15:03 | 显示全部楼层
每次我都积极回帖的,想要安币~

4

主题

9730

帖子

791

安币

代码手工艺人

Rank: 4

发表于 2018-3-28 01:45:55 | 显示全部楼层
感谢分享,安卓巴士有你更精彩:lol

497

主题

1240

帖子

1030

安币

手工艺人

发表于 2018-3-28 11:53:20 | 显示全部楼层
帮帮顶顶!!

0

主题

9447

帖子

2402

安币

Android大神

Rank: 6Rank: 6

发表于 2018-3-28 20:54:37 | 显示全部楼层
每次我都积极回帖的,想要安币~

449

主题

1210

帖子

1949

安币

手工艺人

发表于 2018-3-29 09:40:13 | 显示全部楼层
楼主是好人,回个帖会有安币吗?

350

主题

1032

帖子

778

安币

手工艺人

发表于 2018-3-29 23:10:47 | 显示全部楼层
帮帮顶顶!!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

领先的中文移动开发者社区
18620764416
7*24全天服务
意见反馈:1294855032@qq.com

扫一扫关注我们

Powered by Discuz! X3.2© 2001-2019 Comsenz Inc.( 粤ICP备15117877号 )