{"id":686,"date":"2023-02-26T14:44:19","date_gmt":"2023-02-26T06:44:19","guid":{"rendered":"https:\/\/www.appblog.cn\/?p=686"},"modified":"2023-04-29T17:57:51","modified_gmt":"2023-04-29T09:57:51","slug":"using-easyexcel-to-read-and-write-excel","status":"publish","type":"post","link":"https:\/\/www.appblog.cn\/index.php\/2023\/02\/26\/using-easyexcel-to-read-and-write-excel\/","title":{"rendered":"\u4f7f\u7528easyexcel\u8bfb\u5199Excel"},"content":{"rendered":"

Java\u89e3\u6790Excel\u5de5\u5177easyexcel\uff1ahttps:\/\/github.com\/alibaba\/easyexcel<\/a><\/p>\n

Java\u89e3\u6790\u3001\u751f\u6210Excel\u6bd4\u8f83\u6709\u540d\u7684\u6846\u67b6\u6709Apache poi\u3001jxl\u3002\u4f46\u4ed6\u4eec\u90fd\u5b58\u5728\u4e00\u4e2a\u4e25\u91cd\u7684\u95ee\u9898\u5c31\u662f\u975e\u5e38\u7684\u8017\u5185\u5b58\uff0cpoi\u6709\u4e00\u5957SAX\u6a21\u5f0f\u7684API\u53ef\u4ee5\u4e00\u5b9a\u7a0b\u5ea6\u7684\u89e3\u51b3\u4e00\u4e9b\u5185\u5b58\u6ea2\u51fa\u7684\u95ee\u9898\uff0c\u4f46POI\u8fd8\u662f\u6709\u4e00\u4e9b\u7f3a\u9677\uff0c\u6bd4\u598207\u7248Excel\u89e3\u538b\u7f29\u4ee5\u53ca\u89e3\u538b\u540e\u5b58\u50a8\u90fd\u662f\u5728\u5185\u5b58\u4e2d\u5b8c\u6210\u7684\uff0c\u5185\u5b58\u6d88\u8017\u4f9d\u7136\u5f88\u5927\u3002
\neasyexcel\u91cd\u5199\u4e86poi\u5bf907\u7248Excel\u7684\u89e3\u6790\uff0c\u80fd\u591f\u539f\u672c\u4e00\u4e2a3M\u7684excel\u7528POI sax\u4f9d\u7136\u9700\u8981100M\u5de6\u53f3\u5185\u5b58\u964d\u4f4e\u5230KB\u7ea7\u522b\uff0c\u5e76\u4e14\u518d\u5927\u7684excel\u4e0d\u4f1a\u51fa\u73b0\u5185\u5b58\u6ea2\u51fa\uff0c03\u7248\u4f9d\u8d56POI\u7684sax\u6a21\u5f0f\u3002\u5728\u4e0a\u5c42\u505a\u4e86\u6a21\u578b\u8f6c\u6362\u7684\u5c01\u88c5\uff0c\u8ba9\u4f7f\u7528\u8005\u66f4\u52a0\u7b80\u5355\u65b9\u4fbf\u3002<\/p>\n

<\/p>\n

Maven\u4f9d\u8d56<\/h2>\n
<dependency>\n    <groupId>com.alibaba<\/groupId>\n    <artifactId>easyexcel<\/artifactId>\n    <version>{latestVersion}<\/version>\n<\/dependency><\/code><\/pre>\n
\n
easyexcel\u9ed8\u8ba4\u81ea\u5e26POI\uff0c\u6ce8\u610f\u4f9d\u8d56\u51b2\u7a81<\/p>\n<\/blockquote>\n
\u521b\u5efa\u5b9e\u4f53<\/h2>\n
\u5047\u8bbeExcel\u4e2d\u5217\u8868\u4e3a<\/p>\n\n\n\n\n\n
\u59d3\u540d<\/th>\n\u6635\u79f0<\/th>\n\u5bc6\u7801<\/th>\n\u751f\u65e5<\/th>\n\u6027\u522b<\/th>\n\u8eab\u9ad8<\/th>\n<\/tr>\n<\/thead>\n
APP\u5f00\u53d1\u6280\u672f\u535a\u5ba2<\/td>\nAppBlog.CN<\/td>\npassword<\/td>\n2019\/05\/23<\/td>\n\u7537<\/td>\n170<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
@Data\npublic class User extends BaseRowModel {\n    @ExcelProperty(value = "\u59d3\u540d", index = 0)\n    private String name;\n\n    @ExcelProperty(value = "\u6635\u79f0", index = 1)\n    private String nickName;\n\n    @ExcelProperty(value = "\u5bc6\u7801", index = 2)\n    private String password;\n\n    @ExcelProperty(value = "\u751f\u65e5", index = 3, format = "yyyy\/MM\/dd")\n    private Date birthday;\n\n    @ExcelProperty(value = "\u6027\u522b", index = 4, replace = "\u7537_1,\u5973_2")\n    private int sex;\n\n    @ExcelProperty(value = "\u8eab\u9ad8", index = 5)\n    private Double height;\n}<\/code><\/pre>\n
\u6ce8\u610f\uff1a\u8be5\u5b9e\u4f53\u5fc5\u987b\u7ee7\u627f BaseRowModel<\/p>\n
\u76d1\u542c\u7c7b<\/h2>\n
\u7f16\u5199\u76d1\u542c\u7c7b\uff0c\u8be5\u7c7b\u7528\u4e8e\u8fd4\u56de\u8bfb\u53d6\u5230\u7684\u5bf9\u8c61<\/p>\n
\/**\n * \u5904\u7406Excel\uff0c\u5c06\u8bfb\u53d6\u5230\u6570\u636e\u4fdd\u5b58\u4e3a\u5bf9\u8c61\u5e76\u8f93\u51fa\n *\/\npublic class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {\n    \/**\n     * \u81ea\u5b9a\u4e49\u7528\u4e8e\u6682\u65f6\u5b58\u50a8data\u3002\n     * \u53ef\u4ee5\u901a\u8fc7\u5b9e\u4f8b\u83b7\u53d6\u8be5\u503c\n     *\/\n    private final List<T> data = new ArrayList<>();\n\n    @Override\n    public void invoke(T object, AnalysisContext context) {\n        \/\/\u6570\u636e\u5b58\u50a8\n        data.add(object);\n    }\n\n    @Override\n    public void doAfterAllAnalysed(AnalysisContext context) {\n\n    }\n\n    public List<T> getData() {\n        return data;\n    }\n}<\/code><\/pre>\n
\u7f16\u5199\u5de5\u5177\u7c7b<\/h2>\n
\/**\n* \u4eceExcel\u4e2d\u8bfb\u53d6\u6587\u4ef6\uff0c\u8bfb\u53d6\u7684\u6587\u4ef6\u662f\u4e00\u4e2aDTO\u7c7b\uff0c\u8be5\u7c7b\u5fc5\u987b\u7ee7\u627fBaseRowModel\n* \u5177\u4f53\u5b9e\u4f8b\u53c2\u8003\uff1aMemberMarketDto.java\n* \u53c2\u8003\uff1ahttps:\/\/github.com\/alibaba\/easyexcel\n* \u5b57\u7b26\u6d41\u5fc5\u987b\u652f\u6301\u6807\u8bb0\uff0cFileInputStream \u4e0d\u652f\u6301\u6807\u8bb0\uff0c\u53ef\u4ee5\u4f7f\u7528BufferedInputStream \u4ee3\u66ff\n* BufferedInputStream bis = new BufferedInputStream(new FileInputStream(...));\n*\n* @param inputStream  \u6587\u4ef6\u8f93\u5165\u6d41\n* @param clazz  \u7ee7\u627f\u8be5\u7c7b\u5fc5\u987b\u7ee7\u627fBaseRowModel\u7684\u7c7b\n* @return  \u8bfb\u53d6\u5b8c\u6210\u7684list\n*\/\npublic static <T extends BaseRowModel> List<T> readExcel(final InputStream inputStream, final Class<? extends BaseRowModel> clazz) {\n    if (null == inputStream) {\n        throw new NullPointerException("the inputStream is null!");\n    }\n    AnalysisEventListener listener = new ExcelListener();\n    \/\/\u8bfb\u53d6xls \u548c xlxs\u683c\u5f0f\n    \/\/\u5982\u679cPOI\u7248\u672c\u4e3a3.17\uff0c\u53ef\u4ee5\u5982\u4e0b\u58f0\u660e\n    ExcelReader reader = new ExcelReader(inputStream, null, listener);\n    \/\/\u5224\u65ad\u683c\u5f0f\uff0c\u9488\u5bf9POI\u7248\u672c\u4f4e\u4e8e3.17\n    \/\/ExcelTypeEnum excelTypeEnum = valueOf(inputStream);\n    \/\/ExcelReader reader = new ExcelReader(inputStream, excelTypeEnum, null, listener);\n    reader.read(new Sheet(1, 1, clazz));\n\n    return ((ExcelListener) listener).getData();\n}\n\n\/**\n* \u9700\u8981\u5199\u5165\u7684Excel\uff0c\u6709\u6a21\u578b\u6620\u5c04\u5173\u7cfb\n*\n* @param file  \u9700\u8981\u5199\u5165\u7684Excel\uff0c\u683c\u5f0f\u4e3axlsx\n* @param list  \u5199\u5165Excel\u4e2d\u7684\u6240\u6709\u6570\u636e\uff0c\u7ee7\u627f\u4e8eBaseRowModel\n*\/\npublic static void writeExcel(final File file, List<? extends BaseRowModel> list) {\n    OutputStream out = new FileOutputStream(file);\n    try {\n        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);\n        \/\/\u5199\u7b2c\u4e00\u4e2asheet\uff0c\u6709\u6a21\u578b\u6620\u5c04\u5173\u7cfb\n        Class t = list.get(0).getClass();\n        Sheet sheet = new Sheet(1, 0, t);\n        writer.write(list, sheet);\n        writer.finish();\n    } catch (Exception e) {\n        e.printStackTrace();\n    } finally {\n        try {\n            out.close();\n        } catch (IOException e) {\n            e.printStackTrace();\n        }\n    }\n}\n\n\/**\n* \u6839\u636e\u8f93\u5165\u6d41\uff0c\u5224\u65ad\u4e3axls\u8fd8\u662fxlsx\uff0c\u8be5\u65b9\u6cd5\u539f\u672c\u5b58\u5728\u4e8eeasyexcel 1.1.0 \u7684ExcelTypeEnum\u4e2d\n* \u5982\u679cPOI\u7248\u672c\u4e3a3.17\u4ee5\u4e0b\uff0c\u5219FileMagic\u4f1a\u62a5\u9519\uff0c\u627e\u4e0d\u5230\u8be5\u7c7b\uff0c\u6b64\u65f6\u53bb\u5230POI 3.17\u4e2d\u5c06FileMagic\u62bd\u53d6\u51fa\u6765\n*\/\npublic static ExcelTypeEnum valueOf(InputStream inputStream) {\n    try {\n        FileMagic fileMagic = FileMagic.valueOf(inputStream);\n        if (FileMagic.OLE2.equals(fileMagic)) {\n            return ExcelTypeEnum.XLS;\n        }\n        if (FileMagic.OOXML.equals(fileMagic)) {\n            return ExcelTypeEnum.XLSX;\n        }\n        throw new IllegalArgumentException("excelTypeEnum can not null");\n\n    } catch (IOException e) {\n        throw new RuntimeException(e);\n    }\n}<\/code><\/pre>\n
POI\u7248\u672c\u8fc7\u4f4e\u5904\u7406<\/h2>\n
\u6ce8\u610f\uff1a\u5f53POI\u7248\u672c\u4f4e\u4e8eeasyexcel\u4e2d\u5185\u7f6e\u7684POI\u7248\u672c\u65f6\uff0c\u53ea\u80fd\u4f7f\u7528\u88ab\u58f0\u660e\u4e3a\u8fc7\u671f\u7684\u65b9\u6cd5<\/p>\n
ExcelReader reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, new AnalysisEventListener<List<String>>() {...});<\/code><\/pre>\n
\u65e0\u6cd5\u81ea\u52a8\u5224\u65adExcel\u4e3a03\u8fd8\u662f07\u7248\u672c\uff0c\u6b64\u65f6\u53ef\u4ee5\u5c06\u7f3a\u5c11POI 3.17 \u4e2d\u7684\u65b9\u6cd5\u62f7\u8d1d\u51fa\u6765\u4f7f\u7528<\/p>\n
\/**\n * \u5224\u65ad\u683c\u5f0f\uff0c\u8fd9\u4e2a\u679a\u4e3e\u5b58\u5728\u4e8epoi 3.17\uff0c\u4f46\u662f\u76ee\u524d\u7248\u672c\u662f3.15\uff0c\u6240\u4ee5\u4ece3.17\u62bd\u51fa\u6765\u4f7f\u7528\n *\/\npublic enum FileMagic {\n    \/**\n     * OLE2 \/ BIFF8+ stream used for Office 97 and higher documents\n     *\/\n    OLE2(HeaderBlockConstants._signature),\n    \/**\n     * OOXML \/ ZIP stream\n     *\/\n    OOXML(org.apache.poi.poifs.common.POIFSConstants.OOXML_FILE_HEADER),\n    \/**\n     * UNKNOWN magic\n     *\/\n    UNKNOWN(new byte[0]);\n\n    final byte[][] magic;\n\n    FileMagic(long magic) {\n        this.magic = new byte[1][8];\n        LittleEndian.putLong(this.magic[0], 0, magic);\n    }\n\n    FileMagic(byte[]... magic) {\n        this.magic = magic;\n    }\n\n    public static FileMagic valueOf(byte[] magic) {\n        for (FileMagic fm : values()) {\n            int i = 0;\n            boolean found = true;\n            for (byte[] ma : fm.magic) {\n                for (byte m : ma) {\n                    byte d = magic[i++];\n                    if (!(d == m || (m == 0x70 && (d == 0x10 || d == 0x20 || d == 0x40)))) {\n                        found = false;\n                        break;\n                    }\n                }\n                if (found) {\n                    return fm;\n                }\n            }\n        }\n        return UNKNOWN;\n    }\n\n    \/**\n     * @param inp An InputStream which supports either mark\/reset\n     *\/\n    public static FileMagic valueOf(InputStream inp) throws IOException {\n        if (!inp.markSupported()) {\n            throw new IOException("getFileMagic() only operates on streams which support mark(int)");\n        }\n\n        \/\/ Grab the first 8 bytes\n        byte[] data = IOUtils.peekFirst8Bytes(inp);\n\n        return FileMagic.valueOf(data);\n    }\n}<\/code><\/pre>\n
InputStream\u65e0\u6cd5\u6807\u8bb0\u9519\u8beferror for mark(in)<\/code><\/h2>\n
\u56e0\u4e3aFileInputStream\u662f\u65e0\u6cd5\u88ab\u6807\u8bb0\u7684\uff0c\u53ef\u4ee5\u5c06FileInputStream\u66ff\u6362\u6210BufferedInputStream<\/p>\n
try(BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file))) {\n    do something...\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Java\u89e3\u6790Excel\u5de5\u5177easyexcel\uff1ahttps:\/\/github.com\/alibaba\/easye […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[194],"class_list":["post-686","post","type-post","status-publish","format-standard","hentry","category-java-basic","tag-easyexcel"],"_links":{"self":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/686","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/comments?post=686"}],"version-history":[{"count":0,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/686\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/media?parent=686"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/categories?post=686"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/tags?post=686"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}