java poi导出excel数据

最新推荐文章于 2021-09-14 17:50:57 发布

云的笔记

最新推荐文章于 2021-09-14 17:50:57 发布

阅读量174

点赞数

分类专栏： java 文章标签： poi

本文链接：https://blog.youkuaiyun.com/weixin_43487218/article/details/103730029

版权

java 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了如何使用Java的POI库来导出Excel数据，包括创建工作簿、设置样式、填充数据等步骤，适用于大量数据的报表生成和数据导出场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

private BaseDataEntity() {
        //TODO 此处通过poi将数据获取至initData 作为原始数据
        XSSFWorkbook support = null;
        try {
            support = new XSSFWorkbook(new FileInputStream("/C:/Users/wshh/Desktop/support_station.xlsx"));
            Map<String, Integer> entityToIndexMap = getEntityToIndexMap(CartEntity.class);
            XSSFSheet cartSheet = support.getSheet("cart");
            initCart(entityToIndexMap, cartSheet);
            XSSFSheet slotSheet = support.getSheet("cart_slot");
            Map<String, Integer> slotEntityToIndexMap = getEntityToIndexMap(CartSlotEntity.class);
            initSlot(slotEntityToIndexMap, slotSheet);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static synchronized BaseDataEntity getInstance() {
        if (singleBaseData == null) {
            singleBaseData = new BaseDataEntity();
        }
        return singleBaseData;
    }

    public void initCart(Map<String, Integer> columns, XSSFSheet sheet) throws NoSuchFieldException, IllegalAccessException {
        XSSFRow firstRow = sheet.getRow(0);
        short lastCellNum = firstRow.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            XSSFCell cell = firstRow.getCell(i);
            for (String st : columns.keySet()) {//for循环遍历列将列中key存入set
                if (st.equals(cell.getStringCellValue())) {//单
                    columns.put(st, i);
                }
            }
        }

        XSSFRow row = sheet.getRow(1);
        Class<CartEntity> cartEntityClass = CartEntity.class;
        CartEntity cartEntity = new CartEntity();
        for (String str : columns.keySet()) {
            XSSFCell cell = row.getCell(columns.get(str));
            Object cellValue = getCellValue(cell);
            Field declaredField = cartEntityClass.getDeclaredField(str);
            TypeCheck annotation = declaredField.getAnnotation(TypeCheck.class);
            Class name = annotation.type();
            if (!name.isInstance(cellValue)) {
                throw new RuntimeException("excel所读取到的字段：" + str + "，类型不符合规则，应为：" + name);
            }
            declaredField.setAccessible(true);
            declaredField.set(cartEntity, cellValue);
        }
        cart = cartEntity;
    }

    public void initSlot(Map<String, Integer> columns, XSSFSheet sheet) throws NoSuchFieldException, IllegalAccessException {
        XSSFRow row = sheet.getRow(0);
        short lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            XSSFCell cell = row.getCell(i);
            String stringCellValue = cell.getStringCellValue();
            for (String mStr : columns.keySet()) {
                if (mStr.equals(stringCellValue)) {
                    columns.put(mStr, i);
                }
            }
        }

        Class<CartSlotEntity> cartSlotEntityClass = CartSlotEntity.class;
        int totalRowNum = sheet.getLastRowNum() + 1;
        for (int i = 1; i < totalRowNum; i++) {
            XSSFRow dataRow = sheet.getRow(i);
            CartSlotEntity cartSlotEntity = new CartSlotEntity();
            for (String mStr : columns.keySet()) {
                XSSFCell cell = dataRow.getCell(columns.get(mStr));
                Object cellValue = getCellValue(cell);
                Field field = cartSlotEntityClass.getDeclaredField(mStr);
                TypeCheck annotation = field.getAnnotation(TypeCheck.class);
                Class name = annotation.type();
                if (!name.isInstance(cellValue)) {
                    throw new RuntimeException("excel所读取到的字段：" + mStr + "，类型不符合规则，应为：" + name);
                }
                field.setAccessible(true);
                field.set(cartSlotEntity, cellValue);
            }
            cartSlotEntities.add(cartSlotEntity);
        }
    }

    public Map<String, Integer> getEntityToIndexMap(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Integer> maps = new HashMap<>();
        for (Field f : fields) {
            String name = f.getName();
            maps.put(name, null);
        }
        return maps;
    }


    public static Object getCellValue(XSSFCell cell) {
        if (cell != null) {


            int cellType = cell.getCellType();
            switch (cellType) {
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                case Cell.CELL_TYPE_NUMERIC:
                    return (int) cell.getNumericCellValue();
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                case Cell.CELL_TYPE_BLANK:
                    return "";
            }
        }
        return "";
    }
}