读取并创建excel文件(.xls)

时间:2020-12-01 12:54:38
第三方库,附件
缺点:该库只支持.xls文件的操作


1、读取excel文件


例子:
  1. try {
  2. /**
  3. * 后续考虑问题,比如Excel里面的图片以及其他数据类型的读取
  4. **/
  5. InputStream is = new FileInputStream(path);
  6. Workbook book = Workbook.getWorkbook(is);
  7. int num = book.getNumberOfSheets();
  8. publishProgress("the num of sheets is " + num+ "\n");
  9. if (num <2) {
  10. return -2;
  11. }
  12. // 获得第一个工作表对象:时间,电流值
  13. Sheet sheet = book.getSheet(0);
  14. int Rows = sheet.getRows();
  15. int Cols = sheet.getColumns();读取并创建excel文件(.xls)
  16. publishProgress("the name of sheet 1 is " + sheet.getName() + "\n");
  17. publishProgress("total rows is " + Rows + "\n");
  18. publishProgress("total cols is " + Cols + "\n");
  19. if (Cols != 2) {
  20. publishProgress("第一个工作表格式错误,第一列为时间(13位时间戳),第二列为电流值,导入停止");
  21. return -2;
  22. }
  23. for (int i = 0; i < Rows; i++) {
  24. String str1 = sheet.getCell(0,i).getContents();
  25. if (str1 == null || str1.isEmpty() || str1.length()!=13) {
  26. continue;
  27. }
  28. long longStr1 = -1L;
  29. try {
  30. longStr1 = Long.parseLong(str1);
  31. } catch (Exception e) {
  32. }
  33. String str2 = sheet.getCell(1,i).getContents();
  34. if (str2 == null || str2.isEmpty()) {
  35. continue;
  36. }
  37. float floatStr2 = -2F;
  38. try {
  39. floatStr2 = Float.parseFloat(str2);
  40. } catch (Exception e) {
  41. }
  42. if (longStr1 == -1 || floatStr2 == -2F) {
  43. continue;
  44. }
  45. publishProgress("contents:" + longStr1+","+ floatStr2 + "\n");
  46. //插入血糖
  47. GlucoseData vData = new GlucoseData();
  48. vData.setGlucoseElecValue(floatStr2);
  49. vData.setGlucoseTime(new Date(longStr1));
  50. vData.setGlucoseValue(-1F);
  51. vDataDao.insert(vData);
  52. }
  53. // 获得第二个工作表对象:时间,血糖值
  54. sheet = book.getSheet(1);
  55. Rows = sheet.getRows();
  56. Cols = sheet.getColumns();
  57. publishProgress("the name of sheet 2 is " + sheet.getName() + "\n");
  58. publishProgress("total rows is " + Rows + "\n");
  59. publishProgress("total cols is " + Cols + "\n");
  60. if (Cols != 2) {
  61. publishProgress("第二个工作表格式错误,第一列为时间(13位时间戳),第二列为参比血糖值,导入停止");
  62. return -2;
  63. }
  64. for (int i = 0; i < Rows; i++) {
  65. String str1 = sheet.getCell(0,i).getContents();
  66. if (str1 == null || str1.isEmpty() || str1.length()!=13) {
  67. continue;
  68. }
  69. long longStr1 = -1L;
  70. try {
  71. longStr1 = Long.parseLong(str1);
  72. } catch (Exception e) {
  73. }
  74. String str2 = sheet.getCell(1,i).getContents();
  75. if (str2 == null || str2.isEmpty()) {
  76. continue;
  77. }
  78. float floatStr2 = -2F;
  79. try {
  80. floatStr2 = Float.parseFloat(str2);
  81. } catch (Exception e) {
  82. }
  83. if (longStr1 == -1 || floatStr2 == -2F) {
  84. continue;
  85. }
  86. publishProgress("contents:" + longStr1+","+ floatStr2 + "\n");
  87. //插入血糖
  88. GlucoseRefData vData = new GlucoseRefData();
  89. vData.setGlucoseRefValue(floatStr2);
  90. vData.setGlucoseRefTime(new Date(longStr1));
  91. vGlucoseRefData.insert(vData);
  92. }
  93. book.close();
  94. publishProgress("完成数据导入\n");
  95. return 0;
  96. } catch (Exception e) {
  97. publishProgress(e.toString());
  98. return -1;
  99. }

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

附件列表