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')