五：讀取Excel · Android實戰技巧

最近有這么個需求，發現有現成的開源庫jxl可以完美實現我的小需求。參考兩篇文章： [利用Java操作Excel](http://www.ibm.com/developerworks/cn/java/l-javaExcel/) [官方blog教程](http://www.andykhan.com/jexcelapi/tutorial.html) 源碼：**jexcel**api.sourceforge.net/? 直接練習一下，用javac編譯： ~~~ ?import?java.io.*;?? ?import?jxl.*;?? ??? ?public?class?Test?? ?{?? ?????????public?static?void?main(String[]?args)?? ?????????{?? ?????????????????try?{?? ?????????????????InputStream?is?=?new?FileInputStream("test.xls");?? ?????????????????Workbook?book?=?Workbook.getWorkbook(is);?? ?????????????????Sheet?sheet?=?book.getSheet(0);?? ?????????????????Cell?cell?=?sheet.getCell(2,2);?? ?????????????????String?result?=?cell.getContents();?? ?????????????????System.out.println(result);?? ?????????????????book.close();?? ?????????????????}?? ?????????????????catch(Exception?e)?{?? ?????????????????System.out.println(e);?? ?????????????????}?? ???????????? ?????????}?? ?}??? ~~~ 居然報錯： ~~~ linc:~/workspace/java/test-excel$?javac?-cp?jxl.jar?Test.java??? linc:~/workspace/java/test-excel$?ls?? jxl.jar??Test.class??Test.java??test.xls?? linc:~/workspace/java/test-excel$?java?Test?? Exception?in?thread?"main"?java.lang.NoClassDefFoundError:?jxl/Workbook?? ????at?Test.main(Test.java:9)?? Caused?by:?java.lang.ClassNotFoundException:?jxl.Workbook?? ????at?java.net.URLClassLoader$1.run(URLClassLoader.java:202)?? ????at?java.security.AccessController.doPrivileged(Native?Method)?? ????at?java.net.URLClassLoader.findClass(URLClassLoader.java:190)?? ????at?java.lang.ClassLoader.loadClass(ClassLoader.java:306)?? ????at?sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)?? ????at?java.lang.ClassLoader.loadClass(ClassLoader.java:247)?? ????...?1?more?? ~~~ 網上搜尋方法，并沒有解決此問題，沒辦法，直接用Eclipse來做練習，正常的加入JARS就可以了。練習的代碼如下，讀取Excel內容并顯示在textview中，textview可以上下滾動。大概代碼如下： main.xml ~~~ <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" 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=".MainActivity" > <TextView android:id="@+id/txt_show" android:layout_width="wrap_content" android:layout_height="wrap_content" android:singleLine="false" android:scrollbars="vertical" android:text="@string/hello_world" /> </RelativeLayout> ~~~ MainActivity.java ~~~ package?com.linc.readdata;?? ?? import?java.io.FileInputStream;?? import?java.io.InputStream;?? ?? import?android.os.Bundle;?? import?android.app.Activity;?? import?android.text.method.ScrollingMovementMethod;?? import?android.view.Menu;?? import?android.widget.TextView;?? ?? import?jxl.*;?? ?? public?class?MainActivity?extends?Activity?{?? ????TextView?txt?=?null;?? ?????? ????@Override?? ????protected?void?onCreate(Bundle?savedInstanceState)?{?? ????????super.onCreate(savedInstanceState);?? ????????setContentView(R.layout.activity_main);?? ????????txt?=?(TextView)findViewById(R.id.txt_show);?? ????????txt.setMovementMethod(ScrollingMovementMethod.getInstance());?? ????????readExcel();?? ????}?? ?? ????@Override?? ????public?boolean?onCreateOptionsMenu(Menu?menu)?{?? ????????//?Inflate?the?menu;?this?adds?items?to?the?action?bar?if?it?is?present.?? ????????getMenuInflater().inflate(R.menu.main,?menu);?? ????????return?true;?? ????}?? ?????? ????public?void?readExcel()?{?? ??????????try?{?? ???????????InputStream?is?=?new?FileInputStream("mnt/sdcard/test.xls");?? ???????????//Workbook?book?=?Workbook.getWorkbook(new?File("mnt/sdcard/test.xls"));?? ???????????Workbook?book?=?Workbook.getWorkbook(is);?? ???????????int?num?=?book.getNumberOfSheets();?? ???????????txt.setText("the?num?of?sheets?is?"?+?num+?"\n");?? ???????????//?獲得第一個工作表對象?? ???????????Sheet?sheet?=?book.getSheet(0);?? ???????????int?Rows?=?sheet.getRows();?? ???????????int?Cols?=?sheet.getColumns();?? ???????????txt.append("the?name?of?sheet?is?"?+?sheet.getName()?+?"\n");?? ???????????txt.append("total?rows?is?"?+?Rows?+?"\n");?? ???????????txt.append("total?cols?is?"?+?Cols?+?"\n");?? ???????????for?(int?i?=?0;?i? ????????????for?(int?j?=?0;?j? ?????????????//?getCell(Col,Row)獲得單元格的值?? ????????????????txt.append("contents:"?+?sheet.getCell(i,j).getContents()?+?"\n");?? ????????????}?? ???????????}?? ???????????book.close();?? ??????????}?catch?(Exception?e)?{?? ???????????System.out.println(e);?? ??????????}?? ????????}?? ?? }?? ~~~ 完整項目（帶jxl.jar）請猛擊[這里](http://download.csdn.net/detail/lincyang/6618417)。而其他操作本人并沒有驗證，請自行去讀官方教程或參考以下網絡中的實現： ~~~ public?void?createExcel()?{?? ??try?{?? ???//?創建或打開Excel文件?? ???WritableWorkbook?book?=?Workbook.createWorkbook(new?File(?? ?????"mnt/sdcard/test.xls"));?? ???//?生成名為“第一頁”的工作表,參數0表示這是第一頁?? ???WritableSheet?sheet1?=?book.createSheet("第一頁",?0);?? ???WritableSheet?sheet2?=?book.createSheet("第三頁",?2);?? ???//?在Label對象的構造函數中,元格位置是第一列第一行(0,0)以及單元格內容為test?? ???Label?label?=?new?Label(0,?0,?"test");?? ???//?將定義好的單元格添加到工作表中?? ???sheet1.addCell(label);?? ???/*? ????*?生成一個保存數字的單元格.必須使用Number的完整包路徑,否則有語法歧義? ????*/?? ???jxl.write.Number?number?=?new?jxl.write.Number(1,?0,?555.12541);?? ???sheet2.addCell(number);?? ???//?寫入數據并關閉文件?? ???book.write();?? ???book.close();?? ??}?catch?(Exception?e)?{?? ???System.out.println(e);?? ??}?? }?? /**? ??*?jxl暫時不提供修改已經存在的數據表,這里通過一個小辦法來達到這個目的,不適合大型數據更新!?這里是通過覆蓋原文件來更新的.? ??*? ??*?@param?filePath? ??*/?? public?void?updateExcel(String?filePath)?{?? ??try?{?? ???Workbook?rwb?=?Workbook.getWorkbook(new?File(filePath));?? ???WritableWorkbook?wwb?=?Workbook.createWorkbook(new?File(?? ?????"d:/new.xls"),?rwb);//?copy?? ???WritableSheet?ws?=?wwb.getSheet(0);?? ???WritableCell?wc?=?ws.getWritableCell(0,?0);?? ???//?判斷單元格的類型,做出相應的轉換?? ???Label?label?=?(Label)?wc;?? ???label.setString("The?value?has?been?modified");?? ???wwb.write();?? ???wwb.close();?? ???rwb.close();?? ??}?catch?(Exception?e)?{?? ???e.printStackTrace();?? ??}?? }?? public?static?void?writeExcel(String?filePath)?{?? ??try?{?? ???//?創建工作薄?? ???WritableWorkbook?wwb?=?Workbook.createWorkbook(new?File(filePath));?? ???//?創建工作表?? ???WritableSheet?ws?=?wwb.createSheet("Sheet1",?0);?? ???//?添加標簽文本?? ???//?Random?rnd?=?new?Random((new?Date()).getTime());?? ???//?int?forNumber?=?rnd.nextInt(100);?? ???//?Label?label?=?new?Label(0,?0,?"test");?? ???//?for?(int?i?=?0;?i??? ???//?ws.addCell(label);?? ???//?ws.addCell(new?jxl.write.Number(rnd.nextInt(50),?rnd?? ???//?.nextInt(50),?rnd.nextInt(1000)));?? ???//?}?? ???//?添加圖片(注意此處jxl暫時只支持png格式的圖片)?? ???//?0,1分別代表x,y?2,5代表寬和高占的單元格數?? ???ws.addImage(new?WritableImage(5,?5,?2,?5,?new?File(?? ?????"mnt/sdcard/nb.png")));?? ???wwb.write();?? ???wwb.close();?? ??}?catch?(Exception?e)?{?? ???System.out.println(e.toString());?? ??}?? }?? } ? ~~~