读取并创建excel文件（.xls）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

第三方库，附件

缺点：该库只支持.xls文件的操作

1、读取excel文件

例子：

try {
/**
* 后续考虑问题,比如Excel里面的图片以及其他数据类型的读取
**/
InputStream is = new FileInputStream(path);
Workbook book = Workbook.getWorkbook(is);
int num = book.getNumberOfSheets();
publishProgress("the num of sheets is " + num+ "\n");
if (num <2) {
return -2;
}
// 获得第一个工作表对象:时间，电流值
Sheet sheet = book.getSheet(0);
int Rows = sheet.getRows();
int Cols = sheet.getColumns();
publishProgress("the name of sheet 1 is " + sheet.getName() + "\n");
publishProgress("total rows is " + Rows + "\n");
publishProgress("total cols is " + Cols + "\n");
if (Cols != 2) {
publishProgress("第一个工作表格式错误，第一列为时间（13位时间戳），第二列为电流值，导入停止");
return -2;
}
for (int i = 0; i < Rows; i++) {
String str1 = sheet.getCell(0,i).getContents();
if (str1 == null || str1.isEmpty() || str1.length()!=13) {
continue;
}
long longStr1 = -1L;
try {
longStr1 = Long.parseLong(str1);
} catch (Exception e) {
}
String str2 = sheet.getCell(1,i).getContents();
if (str2 == null || str2.isEmpty()) {
continue;
}
float floatStr2 = -2F;
try {
floatStr2 = Float.parseFloat(str2);
} catch (Exception e) {
}
if (longStr1 == -1 || floatStr2 == -2F) {
continue;
}
publishProgress("contents:" + longStr1+","+ floatStr2 + "\n");
//插入血糖
GlucoseData vData = new GlucoseData();
vData.setGlucoseElecValue(floatStr2);
vData.setGlucoseTime(new Date(longStr1));
vData.setGlucoseValue(-1F);
vDataDao.insert(vData);
}
// 获得第二个工作表对象:时间，血糖值
sheet = book.getSheet(1);
Rows = sheet.getRows();
Cols = sheet.getColumns();
publishProgress("the name of sheet 2 is " + sheet.getName() + "\n");
publishProgress("total rows is " + Rows + "\n");
publishProgress("total cols is " + Cols + "\n");
if (Cols != 2) {
publishProgress("第二个工作表格式错误，第一列为时间（13位时间戳），第二列为参比血糖值，导入停止");
return -2;
}
for (int i = 0; i < Rows; i++) {
String str1 = sheet.getCell(0,i).getContents();
if (str1 == null || str1.isEmpty() || str1.length()!=13) {
continue;
}
long longStr1 = -1L;
try {
longStr1 = Long.parseLong(str1);
} catch (Exception e) {
}
String str2 = sheet.getCell(1,i).getContents();
if (str2 == null || str2.isEmpty()) {
continue;
}
float floatStr2 = -2F;
try {
floatStr2 = Float.parseFloat(str2);
} catch (Exception e) {
}
if (longStr1 == -1 || floatStr2 == -2F) {
continue;
}
publishProgress("contents:" + longStr1+","+ floatStr2 + "\n");
//插入血糖
GlucoseRefData vData = new GlucoseRefData();
vData.setGlucoseRefValue(floatStr2);
vData.setGlucoseRefTime(new Date(longStr1));
vGlucoseRefData.insert(vData);
}
book.close();
publishProgress("完成数据导入\n");
return 0;
} catch (Exception e) {
publishProgress(e.toString());
return -1;
}

2、生成Excel

try {
// 创建或打开Excel文件
WritableWorkbook book = Workbook.createWorkbook(new File(mResultPath));
// 生成名为“第一页”的工作表,参数0表示这是第一页
WritableSheet sheet1 = book.createSheet("result", 0);
List<GlucoseData> vList = vDataDao.loadAll();
if (vList!=null) {
for (int i = 0; i < vList.size(); i++) {
jxl.write.Number number2 = new jxl.write.Number(0, i,vList.get(i).getGlucoseTime().getTime());
sheet1.addCell(number2);
jxl.write.Number number3 = new jxl.write.Number(1, i,vList.get(i).getGlucoseElecValue());
sheet1.addCell(number3);
jxl.write.Number number = new jxl.write.Number(2, i,vList.get(i).getGlucoseValue());
sheet1.addCell(number);
}
}
// 写入数据并关闭文件
book.write();
book.close();
} catch (Exception e) {
Log.d("algorithm", "WritableWorkbook exception: "+e.toString());
publishProgress("WritableWorkbook exception: "+e.toString());
return -3;
}