/** * 导入excel * @param file 输入文件 * @param clz 待转出格式 * @param <T> * @return * @throws APIException */ public static <T> List<T> importExcel(File file, Class<T> clz) throws APIException { try { Workbook rwb = Workbook.getWorkbook(file); Sheet rs = rwb.getSheet(0); int cols = rs.getColumns();//列数 int rows = rs.getRows();//行数 logger.info("cols:{}, rows:{}", cols, rows); List<T> result = new ArrayList<T>(); for (int i = 1; i < rows; i++) {//去掉第一行表头 T item = clz.newInstance(); Field[] fields = clz.getDeclaredFields(); for (int j = 0; j < cols && j < fields.length; j++) { String cellContent = rs.getCell(j, i).getContents(); Field field = fields[j]; String methodName = "set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1); Method method = clz.getMethod(methodName, String.class); method.invoke(item, cellContent); } result.add(item); } logger.info("import result:{}", result); return result; } catch (Exception e) { if (e instanceof java.lang.ClassCastException) { throw new APIException(ResultEnum.APIEnum.PARAM_ERROR, "转出类型的所有字段必须支持String类型的set方法"); } logger.error("importExcel clz:{}, error->", clz, e); throw new APIException(ResultEnum.APIEnum.SERVER_ERROR); } } /** * 导出数据到excel * @param sheetName * @param headers 表头,以英文逗号分隔 * @param dataList 数据列表 * @param targetPath 目标路径 */ public static void exportExcel(String sheetName, String[] headers, List dataList, String targetPath) { try { if (CollectionUtils.isEmpty(dataList)) { return; } WritableWorkbook workbook = Workbook.createWorkbook(new File(targetPath)); WritableSheet sheet = workbook.createSheet(sheetName, 1); Integer startRowIndex = 0; for (int i = 0; i < headers.length; i++) { Label label = new Label(i, startRowIndex, headers[i]); sheet.addCell(label); } //数据输出 int listSize = dataList.size(); if (listSize > 0) { Class clazz = dataList.get(0).getClass(); Field[] fields = clazz.getDeclaredFields(); for (int i = 0; i < listSize; i++) { int j = 0; for (Field field : fields) { String feildName = field.getName(); String methodName = "get" + feildName.substring(0, 1).toUpperCase() + feildName.substring(1); Method method = clazz.getMethod(methodName, new Class[] {}); Object fieldValue = method.invoke(dataList.get(i), new Object[] {}); String fieldValueStr = ""; if (fieldValue != null) { fieldValueStr = fieldValue.toString(); } Label label = new Label(j, i + 1, fieldValueStr); sheet.addCell(label); j++; } } } workbook.write(); workbook.close(); } catch (Exception e) { logger.error("export excel error->", e); } }
常见问题:
jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:111)
at jxl.read.biff.File.<init>(File.java:113)
at jxl.Workbook.getWorkbook(Workbook.java:250)
at jxl.Workbook.getWorkbook(Workbook.java:235)
原因:
jxl.Workbook.getWorkbook不支持.xlsx格式文件,需要转为.xls