1.先定义一个结构,用来存放单元格的属性。
private class CurCell{
int row;
int col;
String content;
}
2.定义一个ArrayList用来存放excel中的内容.
private ArrayList<CurCell> mArrayList = new ArrayList<MainActivity.CurCell>();
3.读取excel中的内容(excel的Assets中)。
private void ReadExcel(String fileName){
try {
InputStream mInputStream = getResources().getAssets().open(fileName);
Workbook wb = Workbook.getWorkbook(mInputStream);
Sheet mSheet = wb.getSheet(0);
int row = mSheet.getRows();
int columns = mSheet.getColumns();
Log.i("W","Total Row: " + row + ", Total Columns: " + columns);
for(int i= 0 ; i < row ; i ++){
int cols = mSheet.getRow(i).length;
for(int j = 0 ; j < cols ; j ++){
Cell temp = mSheet.getCell(j, i);
String content = temp.getContents();
Log.i("W",j + " ," + i + " ," + content);
CurCell mCell = new CurCell();
mCell.row = i;
mCell.col = j;
mCell.content = content;
mArrayList.add(mCell);
}
}
wb.close();
mInputStream.close();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
4.写excel文件。
private void WriteExcel(){
try {
WritableWorkbook mWorkbook = Workbook.createWorkbook(new File("/mnt/sdcard/test.xls"));
WritableSheet mSheet = mWorkbook.createSheet("hello", 0);
WritableSheet mSheet2 = mWorkbook.createSheet("test", 2);
for(CurCell mCurCell : mArrayList){
Label mLabel = new Label(mCurCell.col, mCurCell.row, mCurCell.content);
mSheet.addCell(mLabel);
}
int row = mArrayList.get(mArrayList.size() - 1).row;
int col = mArrayList.get(mArrayList.size() - 1).col;
jxl.write.Number number = new jxl.write.Number(col,row,55.123);
mSheet2.addCell(number);
mWorkbook.write();
mWorkbook.close();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
5.更新单元格的内容。
private void UpdateExcel(){
try {
Workbook mWorkbook = Workbook.getWorkbook(new File("/mnt/sdcard/test.xls"));
WritableWorkbook mWritableWorkbook = Workbook.createWorkbook(new File("/mnt/sdcard/test.xls"), mWorkbook);
WritableSheet mWritableSheet = mWritableWorkbook.getSheet(0);
WritableCell mWritableCell = mWritableSheet.getWritableCell(1, 0);
Label mLabel = (Label)mWritableCell;
mLabel.setString("modify");
mWritableWorkbook.write();
mWritableWorkbook.close();
mWorkbook.close();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}