Android 导入与导出Excel文件

1、引入Excel 依赖包

在app的build.gradle里面添加依赖包:

implementation ‘net.sourceforge.jexcelapi:jxl:2.6.12’
2、编写ExcelUtils 工具类

package com.rfid.uhfsdktest.util;

import android.Manifest;
import android.app.Activity;
import android.content.pm.PackageManager;
import android.support.v4.app.ActivityCompat;
import android.util.Log;

import com.rfid.uhfsdktest.bean.InventoryBuffer;
import com.rfid.uhfsdktest.bean.OperateTagBuffer;
import com.rfid.uhfsdktest.R;
import com.rfid.uhfsdktest.UHFApplication;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
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 jxl.write.biff.RowsExceededException;

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";

private final static String[] mContent = new String[]{"ID", "EPC", "PC",
        UHFApplication.getContext().getResources().getString(R.string.real_list_times),
        "RSSI",UHFApplication.getContext().getString(R.string.real_list_freq),
        UHFApplication.getContext().getString(R.string.tag_inventory_time)};

private final static String[] mOperateData = new String[] {
        "ID","PC","CRC","EPC",UHFApplication.getContext().getString(R.string.data),
        UHFApplication.getContext().getString(R.string.data_length),
        UHFApplication.getContext().getString(R.string.list_antenna_port),
        UHFApplication.getContext().getString(R.string.access_list_times)
};

private final static String FILE_SUFFIX = ".xls";

// Storage Permissions
private static final int REQUEST_EXTERNAL_STORAGE = 1;
private static String[] PERMISSIONS_STORAGE = {
        Manifest.permission.READ_EXTERNAL_STORAGE,
        Manifest.permission.WRITE_EXTERNAL_STORAGE
};

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 write(String inputFile)  {

    try {
        inputFile = inputFile.trim() + FILE_SUFFIX;

        File file = new File(inputFile);

        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "EN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);

        workbook.createSheet("Report", 0);

        WritableSheet excelSheet = workbook.getSheet(0);

        workbook.write();

        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    } 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("tags", 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();
            }
        }
    }

}

public static void writeOperateTagToExcel (String fileName, List<OperateTagBuffer.OperateTagMap> maps) {
    fileName = fileName.trim() + FILE_SUFFIX;
    initExcel(fileName,mOperateData);
    writeOperateTagListToExcel(maps,fileName);
}

public static void writeTagToExcel(String fileName, List<InventoryBuffer.InventoryTagMap> maps) {

    fileName = fileName.trim() + FILE_SUFFIX;
    initExcel(fileName,mContent);
    writeObjListToExcel(maps,fileName);
}

@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList,String fileName) {

    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 = new ArrayList<String>();
                InventoryBuffer.InventoryTagMap map = ((InventoryBuffer.InventoryTagMap)(objList.get(j)));
                list.add(j + 1 + "");
                list.add(map.strEPC);
                list.add(map.strPC);
                list.add(map.nReadCount + "");
                list.add((Integer.parseInt(map.strRSSI) - 129) + "dBm");
                list.add(map.strFreq);
                list.add(new SimpleDateFormat("yyyy/MM/dd").format(map.mDate) + " " + new SimpleDateFormat("kk:mm:ss").format(map.mDate));

                for (int i = 0; i < list.size(); i++) {
                    sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                }
            }
            writebook.write();
        } 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();
                }
            }
        }

    }

}

@SuppressWarnings("unchecked")
public static <T> void writeOperateTagListToExcel(List<T> objList, String fileName) {
    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 = new ArrayList<String>();
                OperateTagBuffer.OperateTagMap map = ((OperateTagBuffer.OperateTagMap)(objList.get(j)));
                list.add(j + 1 + "");
                list.add(map.strPC);
                list.add(map.strCRC);
                list.add(map.strEPC);
                list.add(map.strData);
                list.add(map.nDataLen + "");
                list.add(map.btAntId + "");
                list.add(map.nReadCount + "");
                for (int i = 0; i < list.size(); i++) {
                    sheet.addCell(new Label(i, j + 1, list.get(i),arial12format));
                }
            }
            writebook.write();
        } 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;
}

/**
 * Checks if the app has permission to write to device storage
 *
 * If the app does not has permission then the user will be prompted to grant permissions
 *
 * @param activity
 */
public static void verifyStoragePermissions(Activity activity) {
    // Check if we have write permission
    int permission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE);

    if (permission != PackageManager.PERMISSION_GRANTED) {
        // We don't have permission so prompt the user
        ActivityCompat.requestPermissions(
                activity,
                PERMISSIONS_STORAGE,
                REQUEST_EXTERNAL_STORAGE
        );
    }
}


public static void readExcel(String filepath) {
    try {

        /**
         * 后续考虑问题,比如Excel里面的图片以及其他数据类型的读取
         **/
        InputStream is = new FileInputStream(filepath);

        Workbook book = Workbook.getWorkbook(new File(filepath));

        book.getNumberOfSheets();
        // 获得第一个工作表对象
        Sheet sheet = book.getSheet(0);
        int Rows = sheet.getRows();
        int Cols = sheet.getColumns();
        System.out.println("当前工作表的名字:" + sheet.getName());
        System.out.println("总行数:" + Rows);
        System.out.println("总列数:" + Cols);
        for (int i = 0; i < Cols; ++i) {
            for (int j = 0; j < Rows; ++j) {
                // getCell(Col,Row)获得单元格的值
                System.out.print((sheet.getCell(i, j)).getContents() + "\t");
            }
            System.out.print("\n");
        }
        // 得到第一列第一行的单元格
        Cell cell1 = sheet.getCell(0, 0);
        String result = cell1.getContents();
        System.out.println(result);
        book.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

}

3、调用方式

3.1、导出excel调用方式：

private List<InventoryBuffer.InventoryTagMap> mTags = new ArrayList<InventoryBuffer.InventoryTagMap>();
final String file = SDCardRootDir.getAbsolutePath() + “/” + mDirectory.getText().toString() + “/” + mFileName.getText().toString() ;
ExcelUtils.writeTagToExcel(file,data);
3.2、导入excel调用方式：

String filePath = Environment.getExternalStorageDirectory() + File.separator +“test.xls” ;
ExcelUtils.readExcel(filePath);