Android操作Excel文件的功能实现

importjava.io.File;

importjava.io.IOException;

importjava.io.InputStream;

importjava.util.ArrayList;

importjava.util.List;

importjava.util.zip.ZipEntry;

importjava.util.zip.ZipException;

importjava.util.zip.ZipFile;

importorg.xmlpull.v1.XmlPullParser;

importorg.xmlpull.v1.XmlPullParserException;

importandroid.util.Log;

importandroid.util.Xml;

importjxl.Sheet;

importjxl.Workbook;

importjxl.write.Label;

importjxl.write.WritableSheet;

importjxl.write.WritableWorkbook;

publicclassExcelUtil {

privatefinalstaticString TAG ="ExcelUtil";

publicstaticList> read(String file_name) {

String extension = file_name.lastIndexOf(".") == -1?"": file_name

.substring(file_name.lastIndexOf(".") +1);

if("xls".equals(extension)) {// 2003

Log.d(TAG,"read2003XLS, extension:"+ extension);

returnread2003XLS(file_name);

}elseif("xlsx".equals(extension)) {

Log.d(TAG,"read2007XLSX, extension:"+ extension);

returnread2007XLSX(file_name);

}else{

Log.d(TAG,"不支持的文件类型, extension:"+ extension);

returnnull;

}

}

publicstaticList> read2003XLS(String path) {

List> dataList =newArrayList>();

try{

Workbook book = Workbook.getWorkbook(newFile(path));

// book.getNumberOfSheets(); //获取sheet页的数目

// 获得第一个工作表对象

Sheet sheet = book.getSheet(0);

intRows = sheet.getRows();

intCols = sheet.getColumns();

Log.d(TAG,"当前工作表的名字:"+ sheet.getName());

Log.d(TAG,"总行数:"+ Rows +", 总列数:"+ Cols);

List

String val =null;

for(inti =0; i

booleannull_row =true;

for(intj =0; j

// getCell(Col,Row)获得单元格的值，注意getCell格式是先列后行，不是常见的先行后列

Log.d(TAG, (sheet.getCell(j, i)).getContents() +"t");

val = (sheet.getCell(j, i)).getContents();

if(val ==null|| val.equals("")) {

val ="null";

}else{

null_row =false;

}

objList.add(val);

}

Log.d(TAG,"n");

if(null_row !=true) {

dataList.add(objList);

null_row =true;

}

objList =newArrayList

}

book.close();

}catch(Exception e) {

Log.d(TAG, e.getMessage());

}

returndataList;

}

publicstaticList> read2007XLSX(String path) {

List> dataList =newArrayList>();

String str_c ="";

String v =null;

booleanflat =false;

Listls =newArrayList();

try{

ZipFile xlsxFile =newZipFile(newFile(path));

ZipEntry sharedStringXML = xlsxFile.getEntry("xl/sharedStrings.xml");

if(sharedStringXML ==null) {

Log.d(TAG,"空文件:"+ path);

returndataList;

}

InputStream inputStream = xlsxFile.getInputStream(sharedStringXML);

XmlPullParser xmlParser = Xml.newPullParser();

xmlParser.setInput(inputStream,"utf-8");

intevtType = xmlParser.getEventType();

while(evtType != XmlPullParser.END_DOCUMENT) {

switch(evtType) {

caseXmlPullParser.START_TAG:

String tag = xmlParser.getName();

if(tag.equalsIgnoreCase("t")) {

ls.add(xmlParser.nextText());

}

break;

caseXmlPullParser.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");

intevtTypesheet = xmlParsersheet.getEventType();

List

String val =null;

booleannull_row =true;

while(evtTypesheet != XmlPullParser.END_DOCUMENT) {

switch(evtTypesheet) {

caseXmlPullParser.START_TAG:

String tag = xmlParsersheet.getName();

if(tag.equalsIgnoreCase("row")) {

}elseif(tag.equalsIgnoreCase("c")) {

String t = xmlParsersheet.getAttributeValue(null,"t");

if(t !=null) {

flat =true;// 字符串型

// Log.d(TAG, flat + "有");

}else{// 非字符串型，可能是整型

// Log.d(TAG, flat + "没有");

flat =false;

}

}elseif(tag.equalsIgnoreCase("v")) {

v = xmlParsersheet.nextText();

if(v !=null) {

if(flat) {

str_c += ls.get(Integer.parseInt(v)) +" ";

val = ls.get(Integer.parseInt(v));

null_row =false;

}else{

str_c += v +" ";

val = v;

}

objList.add(val);

}

}

break;

caseXmlPullParser.END_TAG:

if(xmlParsersheet.getName().equalsIgnoreCase("row") && v !=null) {

str_c +="n";

if(null_row !=true) {

dataList.add(objList);

null_row =true;

}

objList =newArrayList

}

break;

}

evtTypesheet = xmlParsersheet.next();

}

Log.d(TAG, str_c);

}catch(ZipException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}catch(XmlPullParserException e) {

e.printStackTrace();

}

if(str_c ==null) {

str_c ="解析文件出现问题";

Log.d(TAG, str_c);

}

returndataList;

}

publicstaticintwriteExcel(String file_name, List> data_list) {

try{

WritableWorkbook book = Workbook.createWorkbook(newFile(file_name));

WritableSheet sheet1 = book.createSheet("sheet1",0);

for(inti =0; i

List

for(intj =0; j

Label label =newLabel(j, i, obj_list.get(j).toString());

sheet1.addCell(label);

}

}

book.write();

book.close();

}catch(Exception e) {

e.printStackTrace();

return-1;

}

return0;

}