最近公司項目需要Android應用讀取excel檔案内容，是以就找了相關資料，找到兩種讀取excel檔案的方法，下面為大家介紹：
一、jxl 讀取excel檔案
1.1、添加依賴：
implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'
1.2、讀取excel
public static void readExcel(Context context) {
String logFilePath = Environment.getExternalStorageDirectory() + File.separator + "Visitor" ;
File file= new File(logFilePath, "test.xls");
Log.e("yy","file="+file.getAbsolutePath());
try {
InputStream is = new FileInputStream(file);
Workbook book = Workbook.getWorkbook(is);
book.getNumberOfSheets();
Sheet sheet = book.getSheet(0);
int Rows = sheet.getRows();
for (int i = 1; i < Rows; ++i) {
String name = (sheet.getCell(0, i)).getContents();
String department = (sheet.getCell(1, i)).getContents();
String company = (sheet.getCell(2, i)).getContents();
String phone = (sheet.getCell(3, i)).getContents();
Log.e("yy","第"+i+"行資料="+name+","+department+","+company+","+phone);
}
book.close();
} catch (Exception e) {
Log.e("yy", "e"+e);
}
}
經過調試發現這種方式隻支援97-2003（xls）版本，後來又經過多次嘗試 發現下面代碼可以支援2007以上版本（xlsx）
public static String readExcelxlsx() {
String logFilePath = Environment.getExternalStorageDirectory() + File.separator + "Visitor";
File file = new File(logFilePath, "test.xlsx");
String str = "";
String v = null;
boolean flat = false;
List<String> ls = new ArrayList<String>();
try {
ZipFile xlsxFile = new ZipFile(file);
ZipEntry sharedStringXML = xlsxFile
.getEntry("xl/sharedStrings.xml");
InputStream inputStream = xlsxFile.getInputStream(sharedStringXML);
XmlPullParser xmlParser = Xml.newPullParser();
xmlParser.setInput(inputStream, "utf-8");
int evtType = xmlParser.getEventType();
Log.e("=====>", "==xmlParser====>" + xmlParser.toString());
while (evtType != XmlPullParser.END_DOCUMENT) {
switch (evtType) {
case XmlPullParser.START_TAG:
String tag = xmlParser.getName();
if (tag.equalsIgnoreCase("t")) {
ls.add(xmlParser.nextText());
Log.e("=====>", "===xmlParser===>" + ls.toString());
}
break;
case XmlPullParser.END_TAG:
break;
default:
break;
}
evtType = xmlParser.next();
}
ZipEntry sheetXML = xlsxFile.getEntry("xl/worksheets/sheet1.xml");
InputStream inputStreamsheet = xlsxFile.getInputStream(sheetXML);
XmlPullParser xmlParsersheet = Xml.newPullParser();
xmlParsersheet.setInput(inputStreamsheet, "utf-8");
int evtTypesheet = xmlParsersheet.getEventType();
while (evtTypesheet != XmlPullParser.END_DOCUMENT) {
switch (evtTypesheet) {
case XmlPullParser.START_TAG:
String tag = xmlParsersheet.getName();
Log.e("=====>", "===tag222===>" + tag);
if (tag.equalsIgnoreCase("row")) {
} else if (tag.equalsIgnoreCase("c")) {
String t = xmlParsersheet.getAttributeValue(null, "t");
if (t != null) {
flat = true;
System.out.println(flat + "有");
} else {
System.out.println(flat + "沒有");
flat = false;
}
} else if (tag.equalsIgnoreCase("v")) {
v = xmlParsersheet.nextText();
if (v != null) {
if (flat) {
str += ls.get(Integer.parseInt(v)) + " ";
} else {
str += v + " ";
}
}
}
break;
case XmlPullParser.END_TAG:
if (xmlParsersheet.getName().equalsIgnoreCase("row")
&& v != null) {
str += "\n";
}
break;
}
evtTypesheet = xmlParsersheet.next();
}
System.out.println(str);
} catch (ZipException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (XmlPullParserException e) {
e.printStackTrace();
}
if (str == null) {
str = "解析檔案出現問題";
}
return str;
}
1.3、經過上面的代碼功能是解決了，但是上面這種資料是擷取到了 但是對于後續的處理不是很友好，是以又去找了半天找到了poi讀取的方法。
二、poi讀取excel
2.1、添加依賴
網上各種說法添加的jar比較多，并且伴随各種 包沖突、65K等錯誤，最終 找到了一個外國小哥簡化版的jar包
将上面兩個jar 放進libs目錄，build 即可，文末會有下載下傳連接配接
2.2、讀取excel
/**
* 讀取excel （xls和xlsx）
* @return
*/
public static List<Map<String, String>> readExcel(String columns[]) {
String logFilePath = Environment.getExternalStorageDirectory() + File.separator + "Visitor";
File file = new File(logFilePath, "test.xls");
String filePath=file.getAbsolutePath();
Sheet sheet = null;
Row row = null;
Row rowHeader = null;
List<Map<String, String>> list = null;
String cellData = null;
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
if (wb != null) {
// 用來存放表中資料
list = new ArrayList<Map<String, String>>();
// 擷取第一個sheet
sheet = wb.getSheetAt(0);
// 擷取最大行數
int rownum = sheet.getPhysicalNumberOfRows();
// 擷取第一行
rowHeader = sheet.getRow(0);
row = sheet.getRow(0);
// 擷取最大列數
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
if(columns[j].equals(getCellFormatValue(rowHeader.getCell(j)))){
cellData = (String) getCellFormatValue(row
.getCell(j));
map.put(columns[j], cellData);
/*DecimalFormat df = new DecimalFormat("#");
System.out.println( df.format(cellData));*/
Log.e("yy","cellData="+cellData);
Log.e("yy","map="+map);
}
}
} else {
break;
}
list.add(map);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/** 擷取單個單元格資料
* @param cell
* @return
* @author lizixiang ,2018-05-08
*/
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判斷cell類型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
// 判斷cell是否為日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 轉換為日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
// 數字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
2.3、調用
FileUtil.readExcel(new String[]{"姓名","部門","公司","電話"});
String數組裡面對應我們的表頭
如下：
5.0以上版本jar
4.0版本jar
最後特别說明： 本文是采用5.0以上版本jar，本代碼是否适用于4.0版本jar并未做校驗。。