{"id":1168,"date":"2020-09-17T14:41:21","date_gmt":"2020-09-17T06:41:21","guid":{"rendered":"http:\/\/diuut.com\/?p=1168"},"modified":"2020-09-17T14:46:57","modified_gmt":"2020-09-17T06:46:57","slug":"poi%e8%af%bb%e5%8f%96excel%e8%87%aa%e5%8a%a8%e5%b0%81%e8%a3%85javabean","status":"publish","type":"post","link":"https:\/\/diuut.com\/?p=1168","title":{"rendered":"POI\u8bfb\u53d6Excel\u81ea\u52a8\u5c01\u88c5JavaBean"},"content":{"rendered":"\n

\u9700\u6c42\uff1a<\/p>\u901a\u8fc7\u63a5\u53e3\u4e0a\u4f20\u4e00\u4e2aexcel\u6587\u4ef6\uff0c\u6587\u4ef6\u5185\u4e3a\u65b0\u589e\u7684\u9700\u8981\u6dfb\u52a0\u81f3\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\uff0c\u8bfb\u53d6\u5c01\u88c5\u5e76\u5b58\u81f3\u6570\u636e\u5e93\u4e2d\u3002<\/cite><\/blockquote>\n\n\n\n

\u5f15\u5165maven\u4f9d\u8d56\uff1a<\/p>\n\n\n

\n<dependency>\n    <groupId>org.apache.poi<\/groupId>\n    <artifactId>poi<\/artifactId>\n    <version>4.0.1<\/version>\n<\/dependency>\n<dependency>\n    <groupId>org.apache.poi<\/groupId>\n    <artifactId>poi-ooxml<\/artifactId>\n    <version>4.0.1<\/version>\n<\/dependency>\n<\/pre><\/div>\n\n\n
\u65b0\u5efa\u4e2d\u95f4\u5c01\u88c5\u7c7b\uff1a<\/p>\n\n\n
\n@Data\n@AllArgsConstructor\n@NoArgsConstructor\npublic class ExcelHead {\n    private String excelName;             \/\/Excel\u540d\n    private String entityName;            \/\/\u5b9e\u4f53\u7c7b\u5c5e\u6027\u540d\n    private boolean required=false;       \/\/\u503c\u5fc5\u586b\n\n    public ExcelHead(String excelName, String entityName) {\n        this.excelName = excelName;\n        this.entityName = entityName;\n    }\n}\n\n<\/pre><\/div>\n\n\n
\u521b\u5efaExcelUtils\u5de5\u5177\u7c7b\uff1a<\/p>\n\n\n
\nimport com.miracle.qaodo.pojo.ExcelHead;\nimport org.apache.poi.hssf.usermodel.HSSFWorkbook;\nimport org.apache.poi.ss.usermodel.*;\nimport org.apache.poi.xssf.usermodel.XSSFWorkbook;\nimport org.springframework.stereotype.Component;\nimport org.springframework.util.CollectionUtils;\nimport org.springframework.util.StringUtils;\n\nimport java.io.IOException;\nimport java.io.InputStream;\nimport java.lang.reflect.Field;\nimport java.lang.reflect.Method;\nimport java.math.BigDecimal;\nimport java.util.ArrayList;\nimport java.util.Date;\nimport java.util.List;\n\n\n@Component\npublic class ExcelUtils {\n    private static final String FULL_DATA_FORMAT = "yyyy\/MM\/dd  HH:mm:ss";\n    private static final String SHORT_DATA_FORMAT = "yyyy\/MM\/dd";\n\n\n    \/**\n     * Excel\u8868\u5934\u5bf9\u5e94Entity\u5c5e\u6027 \u89e3\u6790\u5c01\u88c5javabean\n     *\n     * @param classzz    \u7c7b\n     * @param in         excel\u6d41\n     * @param fileName   \u6587\u4ef6\u540d\n     * @param excelHeads excel\u8868\u5934\u4e0eentity\u5c5e\u6027\u5bf9\u5e94\u5173\u7cfb\n     * @param <T>\n     * @return\n     * @throws Exception\n     *\/\n    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads) throws Exception {\n        checkFile(fileName);    \/\/\u662f\u5426EXCEL\u6587\u4ef6\n        Workbook workbook = getWorkBoot(in, fileName); \/\/\u517c\u5bb9\u65b0\u8001\u7248\u672c\n        List<T> excelForBeans = readExcel(classzz, workbook, excelHeads);  \/\/\u89e3\u6790Excel\n        return excelForBeans;\n    }\n\n    \/**\n     * \u89e3\u6790Excel\u8f6c\u6362\u4e3aEntity\n     *\n     * @param classzz  \u7c7b\n     * @param in       excel\u6d41\n     * @param fileName \u6587\u4ef6\u540d\n     * @param <T>\n     * @return\n     * @throws Exception\n     *\/\n    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName) throws Exception {\n        return readExcelToEntity(classzz, in, fileName, null);\n    }\n\n    \/**\n     * \u6821\u9a8c\u662f\u5426\u662fExcel\u6587\u4ef6\n     *\n     * @param fileName\n     * @throws Exception\n     *\/\n    public static void checkFile(String fileName) throws Exception {\n        if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {\n            throw new Exception("\u4e0d\u662fExcel\u6587\u4ef6\uff01");\n        }\n    }\n\n    \/**\n     * \u517c\u5bb9\u65b0\u8001\u7248Excel\n     *\n     * @param in\n     * @param fileName\n     * @return\n     * @throws IOException\n     *\/\n    private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {\n        if (fileName.endsWith(".xlsx")) {\n            return new XSSFWorkbook(in);\n        } else {\n            return new HSSFWorkbook(in);\n        }\n    }\n\n    \/**\n     * \u89e3\u6790Excel\n     *\n     * @param classzz    \u7c7b\n     * @param workbook   \u5de5\u4f5c\u7c3f\u5bf9\u8c61\n     * @param excelHeads excel\u4e0eentity\u5bf9\u5e94\u5173\u7cfb\u5b9e\u4f53\n     * @param <T>\n     * @return\n     * @throws Exception\n     *\/\n    private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {\n        List<T> beans = new ArrayList<T>();\n        int sheetNum = workbook.getNumberOfSheets();\n        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {\n            Sheet sheet = workbook.getSheetAt(sheetIndex);\n            String sheetName = sheet.getSheetName();\n            int firstRowNum = sheet.getFirstRowNum();\n            int lastRowNum = sheet.getLastRowNum();\n            Row head = sheet.getRow(firstRowNum);\n            if (head == null) {\n                continue;\n            }\n            short firstCellNum = head.getFirstCellNum();\n            short lastCellNum = head.getLastCellNum();\n            Field[] fields = classzz.getDeclaredFields();\n            for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {\n                Row dataRow = sheet.getRow(rowIndex);\n                if (dataRow == null){\n                    continue;}\n                T instance = classzz.newInstance();\n                if (CollectionUtils.isEmpty(excelHeads)) {  \/\/\u975e\u5934\u90e8\u6620\u5c04\u65b9\u5f0f\uff0c\u9ed8\u8ba4\u4e0d\u6821\u9a8c\u662f\u5426\u4e3a\u7a7a\uff0c\u63d0\u9ad8\u6548\u7387\n                    firstCellNum = dataRow.getFirstCellNum();\n                    lastCellNum = dataRow.getLastCellNum();\n                }\n                for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {\n                    Cell headCell = head.getCell(cellIndex);\n                    if (headCell == null) {\n                        continue;\n                    }\n                    Cell cell = dataRow.getCell(cellIndex);\n                    headCell.setCellType(CellType.STRING);\n                    String headName = headCell.getStringCellValue().trim();\n                    if (StringUtils.isEmpty(headName)) {\n                        continue;\n                    }\n                    ExcelHead eHead = null;\n                    if (!CollectionUtils.isEmpty(excelHeads)) {\n                        for (ExcelHead excelHead : excelHeads) {\n                            if (headName.equals(excelHead.getExcelName())) {\n                                eHead = excelHead;\n                                headName = eHead.getEntityName();\n                                break;\n                            }\n                        }\n                    }\n                    for (Field field : fields) {\n                        if (headName.equalsIgnoreCase(field.getName())) {\n                            String methodName = MethodUtils.setMethodName(field.getName());\n                            Method method = classzz.getMethod(methodName, field.getType());\n                            if (isDateFied(field)) {\n                                Date date = null;\n                                if (cell != null) {\n                                    date = cell.getDateCellValue();\n                                }\n                                if (date == null) {\n                                    volidateValueRequired(eHead, sheetName, rowIndex);\n                                    break;\n                                }\n                                method.invoke(instance, cell.getDateCellValue());\n                            } else {\n                                String value = null;\n                                if (cell != null) {\n                                    cell.setCellType(CellType.STRING);\n                                    value = cell.getStringCellValue();\n                                }\n                                if (StringUtils.isEmpty(value)) {\n                                    volidateValueRequired(eHead, sheetName, rowIndex);\n                                    break;\n                                }\n                                method.invoke(instance, convertType(field.getType(), value.trim()));\n                            }\n                            break;\n                        }\n                    }\n                }\n                beans.add(instance);\n            }\n        }\n        return beans;\n    }\n\n    \/**\n     * \u662f\u5426\u65e5\u671f\u5b57\u6bb5\n     *\n     * @param field\n     * @return\n     *\/\n    private static boolean isDateFied(Field field) {\n        return (Date.class == field.getType());\n    }\n\n    \/**\n     * \u7a7a\u503c\u6821\u9a8c\n     *\n     * @param excelHead\n     * @throws Exception\n     *\/\n    private static void volidateValueRequired(ExcelHead excelHead, String sheetName, int rowIndex) throws Exception {\n        if (excelHead != null && excelHead.isRequired()) {\n            throw new Exception("\u300a" + sheetName + "\u300b\u7b2c" + (rowIndex + 1) + "\u884c:\\"" + \nexcelHead.getExcelName() + "\\"\u4e0d\u80fd\u4e3a\u7a7a\uff01");\n        }\n    }\n\n    \/**\n     * \u7c7b\u578b\u8f6c\u6362\n     *\n     * @param classzz\n     * @param value\n     * @return\n     *\/\n    private static Object convertType(Class classzz, String value) {\n        if (Integer.class == classzz || int.class == classzz) {\n            return Integer.valueOf(value);\n        }\n        if (Short.class == classzz || short.class == classzz) {\n            return Short.valueOf(value);\n        }\n        if (Byte.class == classzz || byte.class == classzz) {\n            return Byte.valueOf(value);\n        }\n        if (Character.class == classzz || char.class == classzz) {\n            return value.charAt(0);\n        }\n        if (Long.class == classzz || long.class == classzz) {\n            return Long.valueOf(value);\n        }\n        if (Float.class == classzz || float.class == classzz) {\n            return Float.valueOf(value);\n        }\n        if (Double.class == classzz || double.class == classzz) {\n            return Double.valueOf(value);\n        }\n        if (Boolean.class == classzz || boolean.class == classzz) {\n            return Boolean.valueOf(value.toLowerCase());\n        }\n        if (BigDecimal.class == classzz) {\n            return new BigDecimal(value);\n        }\n       \/* if (Date.class == classzz) {\n            SimpleDateFormat formatter = new SimpleDateFormat(FULL_DATA_FORMAT);\n            ParsePosition pos = new ParsePosition(0);\n            Date date = formatter.parse(value, pos);\n            return date;\n        }*\/\n        return value;\n    }\n\n    \/**\n     * \u83b7\u53d6properties\u7684set\u548cget\u65b9\u6cd5\n     *\/\n    static class MethodUtils {\n        private static final String SET_PREFIX = "set";\n        private static final String GET_PREFIX = "get";\n\n        private static String capitalize(String name) {\n            if (name == null || name.length() == 0) {\n                return name;\n            }\n            return name.substring(0, 1).toUpperCase() + name.substring(1);\n        }\n\n        public static String setMethodName(String propertyName) {\n            return SET_PREFIX + capitalize(propertyName);\n        }\n\n        public static String getMethodName(String propertyName) {\n            return GET_PREFIX + capitalize(propertyName);\n        }\n    }\n}\n<\/pre><\/div>\n\n\n
Service\u5c42\uff1a<\/p>\n\n\n
\n    \/**\n     * \u4e0a\u4f20excel \u56fd\u5b66\n     *\n     * @param filePart sinology\n     * @return result\n     * @throws Exception\n     *\/\n    public Object uploadExcel_sinology(FilePart filePart) {\n\n        List<Sinology> sinologies = null;\n        try {\n            Path tempFile = Files.createTempFile("excel", filePart.filename());\n            filePart.transferTo(tempFile.toFile()).subscribe();\n            File file = tempFile.toFile();\n            FileInputStream fileInputStream = new FileInputStream(file);\n            sinologies = ExcelUtils.readExcelToEntity(Sinology.class, fileInputStream, filePart.filename());\n\/\/\u81ea\u5df1\u6839\u636e\u4e0a\u4f20\u7684\u4e0d\u540c\u7c7b\u5b9a\u4e49\u4e0d\u540c\u63a5\u53e3\n        } catch (Exception e) {\n            e.printStackTrace();\n        }\n        log.info("sinologies:{}", sinologies);\n        int count = 0;\n        if (sinologies != null) {\n            for (Sinology sinology : sinologies) {\n                sinologyDao.save(sinology);\n                count++;\n            }\n        }\n        return "\u6210\u529f\u63d2\u5165" + count + "\u6761\u6570\u636e";\n    }\n\n<\/pre><\/div>\n\n\n
Controller\u5c42\uff1a<\/p>\n\n\n
\n    @RequestMapping("\/uploadexcel")\n    public Object uploadExcel(@RequestPart("file") FilePart filePart)  {\n        log.info(filePart.filename());\n        return chessService.uploadExcel_sinology(filePart);\n    }\n\n<\/pre><\/div>\n\n\n
\u8fd9\u91cc\u9879\u76ee\u4f7f\u7528\u7684\u662fwebFlux\uff0c\u6240\u4ee5\u4f7f\u7528\u65f6@RequestPart\u6ce8\u89e3\u83b7\u53d6form-data\u4e2d\u7684file\uff0c\u5982\u679c\u4f7f\u7528\u7684\u662fweb\u7684\u8bdd\uff0c\u53ef\u4ee5\u662f\u7528@RequestParam\u6765\u83b7\u53d6\u4e0a\u4f20\u6587\u4ef6\uff0c\u81ea\u5df1\u8f6c\u6362\u6210inputStream\u6d41\u4f20\u5165\u5de5\u5177\u7c7b\u5373\u53ef\u3002<\/p>\n\n\n\n
Excel\u5de5\u5177\u7c7b\u90e8\u5206\u8f6c\u8f7d\u81ea\uff1a\u901a\u7528POI\u8bfb\u53d6Excel\u5c01\u88c5\u6210JavaBean<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u9700\u6c42\uff1a \u901a\u8fc7\u63a5\u53e3\u4e0a\u4f20\u4e00\u4e2aexcel\u6587\u4ef6\uff0c\u6587\u4ef6\u5185\u4e3a\u65b0\u589e\u7684\u9700\u8981\u6dfb\u52a0\u81f3\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\uff0c\u8bfb\u53d6\u5c01\u88c5\u5e76\u5b58\u81f3\u6570\u636e\u5e93\u4e2d\u3002 \u5f15\u5165view all . . .POI\u8bfb\u53d6Excel\u81ea\u52a8\u5c01\u88c5JavaBean<\/span><\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":1169,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/posts\/1168"}],"collection":[{"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/diuut.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1168"}],"version-history":[{"count":3,"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/posts\/1168\/revisions"}],"predecessor-version":[{"id":1173,"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/posts\/1168\/revisions\/1173"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/diuut.com\/index.php?rest_route=\/wp\/v2\/media\/1169"}],"wp:attachment":[{"href":"https:\/\/diuut.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/diuut.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/diuut.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}