将数据导入与 导出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);
