学习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); 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); } 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(); } } 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); 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功能实例代码，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！