java导入导出excel

代码语言：javascript复制

/**
 * 导入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);
    }
}

代码语言：javascript复制

常见问题：jxl.read.biff.BiffException: Unable to recognize OLE streamat 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

java excel

0 人点赞