Android 操作excel功能实例代码

学习app对excel的读写控制

1.界面设计

<?xml version="1.0" encoding="utf-8"?>
<relativelayout xmlns:android="http://schemas.android.com/apk/res/android"
  xmlns:tools="http://schemas.android.com/tools"
  android:id="@+id/activity_main"
  android:layout_width="match_parent"
  android:layout_height="match_parent"
  android:paddingbottom="@dimen/activity_vertical_margin"
  android:paddingleft="@dimen/activity_horizontal_margin"
  android:paddingright="@dimen/activity_horizontal_margin"
  android:paddingtop="@dimen/activity_vertical_margin"
  tools:context="com.npmaster.myexcel.mainactivity">
  <linearlayout
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:id="@+id/childlayout"
    android:layout_alignparenttop="true"
    android:layout_alignparentleft="true"
    android:layout_alignparentstart="true">
    <tablelayout
      android:layout_width="match_parent"
      android:layout_height="wrap_content"
      android:background="#ffcccccc"
      android:layout_margin="1dp">
    <!-- line1 -->
    <tablerow
      android:layout_width="match_parent"
      android:layout_height="20dp"
      android:background="#ffcccccc"
      android:layout_margin="0.5dp">
      <textview
        android:layout_width="0.0dp"
        android:layout_height="wrap_content"
        android:layout_weight="1"
        android:text="工参数据导入"
        android:id="@+id/tvtip" />
      <textview
        android:text="默认路径"
        android:layout_width="0.0dp"
        android:layout_height="wrap_content"
        android:layout_weight="2"
        android:id="@+id/tvpath" />
    </tablerow>
      <!---->
      <tablerow
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:background="#ffffcc99"
        android:layout_margin="0.5dp">
        <button
          android:text="导入数据"
          android:layout_width="0.0dp"
          android:layout_height="match_parent"
          android:id="@+id/btimport"
          android:textsize="12dp"
          android:layout_weight="1"/>
        <button
          android:text="清理数据"
          android:layout_width="0.0dp"
          android:layout_height="match_parent"
          android:id="@+id/btclear"
          android:textsize="12dp"
          android:layout_weight="1"/>
      </tablerow>
    </tablelayout>
    <android.support.v7.widget.recyclerview
      android:scrollbars="vertical"
      android:layout_width="match_parent"
      android:layout_height="wrap_content"
      android:divider="#ffff0000"
      android:dividerheight="10dp"
      android:id="@+id/rcvengineerparameters"/>
  </linearlayout>
</relativelayout>

<?xml version="1.0" encoding="utf-8"?>
<linearlayout xmlns:android="http://schemas.android.com/apk/res/android"
  xmlns:app="http://schemas.android.com/apk/res-auto"
  android:orientation="vertical"
  android:layout_width="match_parent"
  android:layout_height="wrap_content">
  <tablelayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:background="#ffcccccc"
    android:layout_margin="0dp">
    <tablerow
      android:layout_width="fill_parent"
      android:layout_height="wrap_content"
      android:background="#ffffcc99"
      android:layout_margin="0.5dp">
      <textview
        android:text="ci"
        android:layout_width="0.0dp"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:textsize="12dp"
        android:id="@+id/tvcid" />
      <textview
        android:text="cellname:"
        android:layout_width="0.0dp"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:textsize="12dp"
        android:id="@+id/tvcellname" />
      <textview
        android:text="longtitude"
        android:layout_width="0.0dp"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:textsize="12dp"
        android:id="@+id/tvlongtitude" />
      <textview
        android:text="attitude:"
        android:layout_width="0.0dp"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:textsize="12dp"
        android:id="@+id/tvattitude" />
      <textview
        android:text="azimuth:"
        android:layout_width="0.0dp"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:textsize="12dp"
        android:id="@+id/tvazimuth" />
    </tablerow>
  </tablelayout>
</linearlayout>

2. 代码设计

package com.npmaster.myexcel;
import android.os.environment;
import android.support.v7.app.appcompatactivity;
import android.os.bundle;
import android.view.view;
import android.widget.button;
import android.widget.textview;
import android.support.v7.widget.defaultitemanimator;
import android.support.v7.widget.linearlayoutmanager;
import android.support.v7.widget.orientationhelper;
import android.support.v7.widget.recyclerview;
import java.io.file;
import java.io.fileinputstream;
import java.io.inputstream;
import java.util.arraylist;
import java.util.list;
import android.util.log;
import android.widget.toast;
import jxl.*;
import jxl.write.label;
import jxl.write.writablesheet;
import jxl.write.writableworkbook;
import static android.support.v7.recyclerview.r.styleable.recyclerview;
public class mainactivity extends appcompatactivity {
  public static final int import_file_success = 0;
  public static final int import_file_not_exists= 1;
  public static final int import_file_fail = 2;
  private static final string log_tag = "nplog";
  private string npmasterdir = "/npmaster/";
  private string filepname = "my.xls";
  private string absfullpath = "";
  private static final int default_sheet = 0;
  public list<engineercellinfo> cfgcelllist = null;
  public engineercellinfo defaultitem;
  private engineerrecycleviewadapter myrecycleviewadapter;
  private recyclerview recyclerview;
  toast toast;
  button btclear,btimport;
  @override
  protected void oncreate(bundle savedinstancestate) {
    super.oncreate(savedinstancestate);
    setcontentview(r.layout.activity_main);
    /* 生成默认的title */
    initdata();
    absfullpath =environment.getexternalstoragedirectory().tostring()+npmasterdir;
    createfolder(absfullpath);
    createengineerfile(absfullpath+filepname);
    generatetestdata();
    textview tv = (textview) findviewbyid(r.id.tvpath);
    tv.settext(absfullpath+filepname);
    recyclerview = (recyclerview)findviewbyid(r.id.rcvengineerparameters);
    //线式布局类似 listview
    linearlayoutmanager layoutmanager = new linearlayoutmanager(this);
    layoutmanager.setorientation(orientationhelper.vertical);
    recyclerview.setlayoutmanager(layoutmanager);
    myrecycleviewadapter = new engineerrecycleviewadapter(mainactivity.this,cfgcelllist);
    recyclerview.setadapter(myrecycleviewadapter);
    recyclerview.setitemanimator(new defaultitemanimator());
    btclear = (button)findviewbyid(r.id.btclear);
    btimport = (button)findviewbyid(r.id.btimport);
    btclear.setonclicklistener(new myclicklistener());
    btimport.setonclicklistener(new myclicklistener());
  }
  class myclicklistener implements view.onclicklistener
  {
    @override
    public void onclick(view v)
    {
      switch(v.getid())
      {
        case r.id.btimport:
          int result =readexcel(absfullpath+filepname);
          toast = toast.maketext(mainactivity.this,string.valueof(result),toast.length_short);
          toast.show();
          myrecycleviewadapter.notifydatasetchanged();
          break;
        case r.id.btclear:
          toast = toast.maketext(mainactivity.this,"clear",toast.length_short);
          toast.show();
          break;
        default:
          break;
      }
    }
  }
  public void initdata()
  {
    cfgcelllist = new arraylist<engineercellinfo>();
    defaultitem = new engineercellinfo();
    defaultitem.item[0] = "ci";
    defaultitem.item[1] = "cellname";
    defaultitem.item[2] = "longtitude";
    defaultitem.item[3] = "attitude";
    defaultitem.item[4] = "azimuth";
    cfgcelllist.add(defaultitem);
  }
  public void generatetestdata()
  {
    engineercellinfo engineercellinfo = new engineercellinfo();
    engineercellinfo.item[0] = "134633";
    engineercellinfo.item[1] = "xx小区";
    engineercellinfo.item[2] = "103.89866";
    engineercellinfo.item[3] = "30";
    engineercellinfo.item[4] = "60";
    cfgcelllist.add(engineercellinfo);
    writetofile(absfullpath+filepname);
  }
  /* create folder if not exists*/
  public void createfolder(string strfolder)
  {
    file file = new file(strfolder);
    if (!file.exists())
    {
      if (file.mkdir())
      {
        return;
      }
    }
  }
  public void writetofile(string cfgfilename)
  {
    file file = null;
    if (cfgcelllist.size()<=1)
    {
      return;
    }
    try {
      file = new file(cfgfilename);
      if(file.exists())
      {
        writableworkbook book = workbook.createworkbook(file);
        writablesheet sheet = book.getsheet(default_sheet);
        for (int i = 1; i <cfgcelllist.size();i++)
        {
          for (int j=0;j<5;j++)
          {
            label labelci= new label( j, i, cfgcelllist.get(i).item[j]);
            sheet.addcell(labelci);
          }
        }
        book.write();
        book.close();
      }
    }
    catch(exception e)
    {
      e.printstacktrace();
    }
  }
  /* 创建工参默认文件，如果文件不存在则创建*/
  public void createengineerfile(string cfgfilename)
  {
    file file = null;
    try {
      file = new file(cfgfilename);
      if(file.exists()) {
        writableworkbook book = workbook.createworkbook(file);
        writablesheet sheet = book.createsheet("task", default_sheet);
        for (int i = 0; i <cfgcelllist.size();i++)
        {
          for (int j=0;j<5;j++)
          {
            label labelci= new label( j, i, cfgcelllist.get(i).item[j]);
            sheet.addcell(labelci);
          }
        }
        book.write();
        book.close();
      }
    }
    catch(exception e)
    {
      e.printstacktrace();
    }
  }
  /* read the cell file from engineer file */
  public int readexcel(string cfgfilename)
  {
    textview tv = (textview) findviewbyid(r.id.tvpath);
    try
    {
      file file = new file(cfgfilename);
      if (!file.exists())
      {
        return import_file_not_exists;
      }
      inputstream inputstream = new fileinputstream(file);
      workbook workbook = workbook.getworkbook(inputstream);
      sheet sheet = workbook.getsheet(0);
      /* ignore the first row*/
      for (int i = 1; i < sheet.getrows();i++)
      {
        engineercellinfo engineercellinfo = new engineercellinfo();
        for (int j = 0; j < 5;j++)
        {
          cell cell = sheet.getcell(j,i);
          engineercellinfo.item[j] = cell.getcontents();
        }
        cfgcelllist.add(engineercellinfo);
        //tv.settext(engineercellinfo.tostring());
      }
      workbook.close();
    }
    catch(exception e)
    {
      log.i(log_tag,"file to find the file");
      return import_file_fail;
    }
    return import_file_success;
  }
  public class engineercellinfo
  {
    public string item[]; //use array to store
    public engineercellinfo()
    {
      item = new string[5];
    }
    public string tostring()
    {
      return item[0]+"-"+item[1]+"-"+item[2]+"-"+item[3]+"-"+item[4];
    }
  }
}

package com.npmaster.myexcel;
import android.content.context;
import android.support.v7.widget.recyclerview;
import android.view.layoutinflater;
import android.view.view;
import android.view.viewgroup;
import android.widget.textview;
import com.npmaster.myexcel.mainactivity.engineercellinfo;
import java.util.list;
public class engineerrecycleviewadapter extends recyclerview.adapter<engineerrecycleviewadapter.myviewholder>
{
  private list<mainactivity.engineercellinfo> itemsdata;
  private context mcontext;
  private layoutinflater inflater;
  public engineerrecycleviewadapter(context context, list<engineercellinfo> itemsdata)
  {
    this.itemsdata = itemsdata;
    this.mcontext=context;
    inflater=layoutinflater.from(mcontext);
  }
  public myviewholder oncreateviewholder(viewgroup parent, int viewtype)
  {
    view view = inflater.inflate(r.layout.listitem, parent, false);
    myviewholder holder = new myviewholder(view);
    return holder;
  }
  @override
  public void onbindviewholder(final myviewholder holder, final int position)
  {
    holder.tvcid.settext(string.valueof(itemsdata.get(position).item[0]));
    holder.tvcellname.settext(string.valueof(itemsdata.get(position).item[1]));
    holder.tvlongtitude.settext(string.valueof(itemsdata.get(position).item[2]));
    holder.tvattitude.settext(string.valueof(itemsdata.get(position).item[3]));
    holder.tvazimuth.settext(string.valueof(itemsdata.get(position).item[4]));
  }
  @override
  public int getitemcount()
  {
    return itemsdata.size();
  }
  public static class myviewholder extends recyclerview.viewholder
  {
    public textview tvcid;
    public textview tvcellname;
    public textview tvlongtitude;
    public textview tvattitude;
    public textview tvazimuth;
    public myviewholder(view view)
    {
      super(view);
      tvcid = (textview)view.findviewbyid(r.id.tvcid);
      tvcellname = (textview)view.findviewbyid(r.id.tvcellname);
      tvlongtitude = (textview)view.findviewbyid(r.id.tvlongtitude);
      tvattitude = (textview) view.findviewbyid(r.id.tvattitude);
      tvazimuth = (textview) view.findviewbyid(r.id.tvazimuth);
    }
  }
}

3. 使用的jxl 开发包 和recyclerview

compile 'com.android.support:appcompat-v7:25.0.1'
compile 'com.android.support:recyclerview-v7:25.0.1'
testcompile 'junit:junit:4.12'
compile files('libs/jxl-2.6.jar')

以上所示是小编给大家介绍的android 操作excel功能实例代码，希望对大家有所帮助