Repository: wangguanquan/eec
Pull request #173 (2021-04-27 15:05:11)

Fix#168
增加读取excel中的图片功能


Patch:
diff --git a/pom.xml b/pom.xml
index e3e2c85f..ae65fe57 100644
--- a/pom.xml
+++ b/pom.xml
@@ -5,7 +5,7 @@
 
   <groupId>org.ttzero</groupId>
   <artifactId>eec</artifactId>
-  <version>0.4.11</version>
+  <version>0.4.12-SNAPSHOT</version>
   <name>Excel Write/Read Framework</name>
   <packaging>jar</packaging>
   <url>https://www.ttzero.org</url>
diff --git a/src/main/java/org/ttzero/excel/entity/Workbook.java b/src/main/java/org/ttzero/excel/entity/Workbook.java
index 478b80c8..2da92269 100644
--- a/src/main/java/org/ttzero/excel/entity/Workbook.java
+++ b/src/main/java/org/ttzero/excel/entity/Workbook.java
@@ -669,7 +669,7 @@ public class Workbook implements Storable {
             ps.setFetchSize(Integer.MIN_VALUE);
             ps.setFetchDirection(ResultSet.FETCH_REVERSE);
         } catch (SQLException e) {
-            what("Not support fetch size value of " + Integer.MIN_VALUE);
+            watch.what("Not support fetch size value of " + Integer.MIN_VALUE);
         }
         if (pp != null) pp.build(ps);
         sheet.setPs(ps);
diff --git a/src/main/java/org/ttzero/excel/entity/e7/ContentType.java b/src/main/java/org/ttzero/excel/entity/e7/ContentType.java
index 7e9f5fe7..ee868085 100644
--- a/src/main/java/org/ttzero/excel/entity/e7/ContentType.java
+++ b/src/main/java/org/ttzero/excel/entity/e7/ContentType.java
@@ -22,6 +22,7 @@ import org.dom4j.Element;
 import org.ttzero.excel.annotation.TopNS;
 import org.ttzero.excel.entity.Relationship;
 import org.ttzero.excel.entity.Storable;
+import org.ttzero.excel.manager.Const;
 import org.ttzero.excel.manager.RelManager;
 import org.ttzero.excel.util.FileUtil;
 import org.ttzero.excel.util.StringUtil;
@@ -135,6 +136,10 @@ public class ContentType implements Storable {
         set.add(type);
     }
 
+    public boolean hasDrawings() {
+        return set.stream().anyMatch(t -> Const.ContentType.DRAWINGS.equals(((Type) t).contentType));
+    }
+
     @java.lang.Override
     public void writeTo(Path root) throws IOException {
         // relationship
diff --git a/src/main/java/org/ttzero/excel/manager/Const.java b/src/main/java/org/ttzero/excel/manager/Const.java
index 27333b46..5ee28dbf 100644
--- a/src/main/java/org/ttzero/excel/manager/Const.java
+++ b/src/main/java/org/ttzero/excel/manager/Const.java
@@ -65,6 +65,7 @@ public interface Const {
             , DRAWINGS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
             , VMLDRAWING = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"
             , CHARTS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
+            , HYPERLINK = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
             ;
     }
 
diff --git a/src/main/java/org/ttzero/excel/reader/Drawings.java b/src/main/java/org/ttzero/excel/reader/Drawings.java
new file mode 100644
index 00000000..5b1b5057
--- /dev/null
+++ b/src/main/java/org/ttzero/excel/reader/Drawings.java
@@ -0,0 +1,93 @@
+/*
+ * Copyright (c) 2017-2021, guanquan.wang@yandex.com All Rights Reserved.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+package org.ttzero.excel.reader;
+
+import org.ttzero.excel.util.StringUtil;
+
+import java.nio.file.Path;
+import java.util.List;
+
+/**
+ * Drawings resources
+ *
+ * @author guanquan.wang at 2021-04-24 19:53
+ */
+public interface Drawings {
+    /**
+     * List all picture in excel
+     *
+     * @return list of {@link Picture}, or null if not exists.
+     */
+    List<Picture> listPictures();
+
+    /**
+     * List all picture in specify worksheet
+     *
+     * @return list of {@link Picture}, or null if not exists.
+     */
+    List<Picture> listPictures(Sheet sheet);
+
+    class Picture {
+        /**
+         * Specify the {@link Sheet} which contains the picture
+         */
+        Sheet sheet;
+        /**
+         * Dimension of picture
+         */
+        Dimension dimension;
+        /**
+         * The local temporary path
+         */
+        Path localPath;
+        /**
+         * If it is an online picture, the {@code srcUrl} is the source address of the picture
+         */
+        String srcUrl;
+        /**
+         * Is background picture
+         */
+        boolean background;
+
+        public Sheet getSheet() {
+            return sheet;
+        }
+
+        public Dimension getDimension() {
+            return dimension;
+        }
+
+        public Path getLocalPath() {
+            return localPath;
+        }
+
+        public String getSrcUrl() {
+            return srcUrl;
+        }
+
+        public boolean isBackground() {
+            return background;
+        }
+
+        @Override
+        public String toString() {
+            return background ? "Background picture [" + localPath + "] in worksheet " + sheet.getName() + (StringUtil.isNotEmpty(srcUrl) ? " from internet url " + srcUrl : "")
+                    : "Picture [" + localPath + "] in worksheet " + sheet.getName() + " at " + dimension + (StringUtil.isNotEmpty(srcUrl) ? " from internet url " + srcUrl : "");
+        }
+    }
+}
diff --git a/src/main/java/org/ttzero/excel/reader/ExcelReader.java b/src/main/java/org/ttzero/excel/reader/ExcelReader.java
index 827182d2..fe3132d4 100644
--- a/src/main/java/org/ttzero/excel/reader/ExcelReader.java
+++ b/src/main/java/org/ttzero/excel/reader/ExcelReader.java
@@ -28,6 +28,7 @@ import org.ttzero.excel.annotation.NS;
 import org.ttzero.excel.annotation.TopNS;
 import org.ttzero.excel.entity.IWorkbookWriter;
 import org.ttzero.excel.entity.Relationship;
+import org.ttzero.excel.entity.e7.ContentType;
 import org.ttzero.excel.entity.style.Styles;
 import org.ttzero.excel.manager.Const;
 import org.ttzero.excel.manager.ExcelType;
@@ -133,6 +134,8 @@ public class ExcelReader implements Closeable {
      */
     protected boolean hasFormula;
 
+    private Drawings drawings;
+
     /**
      * Constructor Excel Reader
      *
@@ -440,6 +443,30 @@ public class ExcelReader implements Closeable {
 
     // --- PRIVATE FUNCTIONS
 
+    private ContentType checkContentType(Path root) {
+        SAXReader reader = new SAXReader();
+        Document document;
+        // Read [Content_Types].xml
+        try {
+            document = reader.read(Files.newInputStream(root.resolve("[Content_Types].xml")));
+        } catch (DocumentException | IOException e) {
+            FileUtil.rm_rf(root.toFile(), true);
+            throw new ExcelReadException("The file format is incorrect or corrupted. [[Content_Types].xml]");
+        }
+        ContentType contentType = new ContentType();
+        List<Element> list = document.getRootElement().elements();
+        for (Element e : list) {
+            if ("Override".equals(e.getName())) {
+                ContentType.Override override = new ContentType.Override(e.attributeValue("ContentType"), e.attributeValue("PartName"));
+                if (!Files.exists(root.resolve(override.getPartName().substring(1)))) {
+                    FileUtil.rm_rf(root.toFile(), true);
+                    throw new ExcelReadException("The file format is incorrect or corrupted. [" + override.getPartName() + "]");
+                }
+                contentType.add(override);
+            }
+        }
+        return contentType;
+    }
 
     private ExcelReader(Path path, int bufferSize, int cacheSize, int option) throws IOException {
         // Store template stream as zip file
@@ -448,6 +475,12 @@ public class ExcelReader implements Closeable {
         ZipUtil.unzip(Files.newInputStream(path), tmp);
         LOGGER.debug("Finished decompress. start to check the file integrity.");
 
+        // Check content-type
+        ContentType contentType = checkContentType(tmp);
+        if (contentType.hasDrawings()) {
+            this.drawings = new XMLDrawings(this);
+        }
+
         // Check the file format and parse general information
         try {
             appInfo = getGeneralInfo(tmp);
@@ -523,6 +556,8 @@ public class ExcelReader implements Closeable {
             sheet.setSst(sst);
             // Setting styles
             sheet.setStyles(styles);
+            // Drawings
+            sheet.setDrawings(drawings);
             sheets.add(sheet);
         }
 
@@ -798,4 +833,12 @@ public class ExcelReader implements Closeable {
         return (v & 0x7FFF) | ((long) n) << 16;
     }
 
+    /**
+     * List all pictures in excel
+     *
+     * @return picture list or null if not exists.
+     */
+    public List<Drawings.Picture> listPictures() {
+        return drawings != null ? drawings.listPictures() : null;
+    }
 }
diff --git a/src/main/java/org/ttzero/excel/reader/Sheet.java b/src/main/java/org/ttzero/excel/reader/Sheet.java
index 426bf344..6cce83c7 100644
--- a/src/main/java/org/ttzero/excel/reader/Sheet.java
+++ b/src/main/java/org/ttzero/excel/reader/Sheet.java
@@ -27,6 +27,7 @@ import java.io.OutputStream;
 import java.nio.file.Files;
 import java.nio.file.Path;
 import java.util.Iterator;
+import java.util.List;
 import java.util.Spliterator;
 import java.util.Spliterators;
 import java.util.stream.Stream;
@@ -136,6 +137,13 @@ public interface Sheet extends Closeable {
      */
     Iterator<Row> dataIterator();
 
+    /**
+     * List all pictures in workbook
+     *
+     * @return picture list or null if not exists.
+     */
+    List<XMLDrawings.Picture> listPictures();
+
     /**
      * Reset the {@link Sheet}'s row index to begging
      *
diff --git a/src/main/java/org/ttzero/excel/reader/XMLDrawings.java b/src/main/java/org/ttzero/excel/reader/XMLDrawings.java
new file mode 100644
index 00000000..709fe12c
--- /dev/null
+++ b/src/main/java/org/ttzero/excel/reader/XMLDrawings.java
@@ -0,0 +1,217 @@
+/*
+ * Copyright (c) 2017-2021, guanquan.wang@yandex.com All Rights Reserved.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+
+package org.ttzero.excel.reader;
+
+import org.dom4j.Document;
+import org.dom4j.DocumentException;
+import org.dom4j.Element;
+import org.dom4j.Namespace;
+import org.dom4j.QName;
+import org.dom4j.io.SAXReader;
+import org.ttzero.excel.entity.Relationship;
+import org.ttzero.excel.manager.Const;
+import org.ttzero.excel.manager.RelManager;
+import org.ttzero.excel.util.FileUtil;
+
+import java.io.IOException;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.stream.Collectors;
+
+/**
+ * Drawings resources
+ *
+ * @author guanquan.wang at 2021-04-24 16:18
+ */
+public class XMLDrawings implements Drawings {
+    /**
+     * Root path
+     */
+    private ExcelReader excelReader;
+    /**
+     * Source
+     */
+    private List<Drawings.Picture> pictures;
+    /**
+     * Mark parse flag
+     */
+    private boolean parsed;
+
+    XMLDrawings(ExcelReader reader) {
+        this.excelReader = reader;
+    }
+
+    /**
+     * List all picture in excel
+     *
+     * @return list of {@link Drawings.Picture}, or null if not exists.
+     */
+    @Override
+    public List<Drawings.Picture> listPictures() {
+        return parsed ? pictures : parse();
+    }
+
+    /**
+     * List all picture in specify worksheet
+     *
+     * @return list of {@link Drawings.Picture}, or null if not exists.
+     */
+    @Override
+    public List<Drawings.Picture> listPictures(Sheet sheet) {
+        if (!parsed) parse();
+        if (pictures != null)
+            return pictures.stream().filter(p -> p.sheet.getIndex() == sheet.getIndex()).collect(Collectors.toList());
+        return null;
+    }
+
+    private List<Drawings.Picture> parse() {
+        parsed = true;
+        // Empty excel, maybe throw exception here
+        if (excelReader.sheets == null) return null;
+
+        SAXReader reader = new SAXReader();
+        Document document;
+
+        List<Picture> pictures = new ArrayList<>();
+        for (Sheet sheet : excelReader.sheets) {
+            XMLSheet xmlSheet = (XMLSheet) sheet;
+            Path relsPath = xmlSheet.path.getParent().resolve("_rels/" + xmlSheet.path.getFileName() + ".rels");
+            if (!Files.exists(relsPath)) continue;
+            try {
+                document = reader.read(Files.newInputStream(relsPath));
+            } catch (DocumentException | IOException e) {
+                FileUtil.rm_rf(excelReader.self.toFile(), true);
+                throw new ExcelReadException("The file format is incorrect or corrupted. [/xl/worksheets/_rels/" + xmlSheet.path.getFileName() + ".rels]");
+            }
+
+            List<Element> list = document.getRootElement().elements();
+            for (Element e : list) {
+                String target = e.attributeValue("Target"), type = e.attributeValue("Type");
+                // Background
+                if (Const.Relationship.IMAGE.equals(type)) {
+                    Picture picture = new Picture();
+                    pictures.add(picture);
+                    picture.sheet = sheet;
+                    picture.background = true;
+                    picture.localPath = xmlSheet.path.getParent().resolve(target);
+                    // Drawings
+                } else if (Const.Relationship.DRAWINGS.equals(type)) {
+                    Path drawingsPath = xmlSheet.path.getParent().resolve(target);
+                    List<Picture> subPictures = parseDrawings(drawingsPath);
+                    if (subPictures != null) {
+                        for (Picture picture : subPictures) {
+                            picture.sheet = sheet;
+                            pictures.add(picture);
+                        }
+                    }
+                }
+            }
+        }
+
+        return !pictures.isEmpty() ? (this.pictures = pictures) : null;
+    }
+
+    // Parse drawings.xml
+    private List<Picture> parseDrawings(Path path) {
+        SAXReader reader = new SAXReader();
+        Document document;
+        try {
+            document = reader.read(Files.newInputStream(path.getParent().resolve("_rels/" + path.getFileName() + ".rels")));
+        } catch (DocumentException | IOException e) {
+            FileUtil.rm_rf(excelReader.self.toFile(), true);
+            throw new ExcelReadException("The file format is incorrect or corrupted. [/xl/drawings/_rels/" + path.getFileName() + ".rels]");
+        }
+        List<Element> list = document.getRootElement().elements();
+        Relationship[] rels = new Relationship[list.size()];
+        int i = 0;
+        for (Element e : list) {
+            rels[i++] = new Relationship(e.attributeValue("Id"), e.attributeValue("Target"), e.attributeValue("Type"));
+        }
+        RelManager relManager = RelManager.of(rels);
+
+        try {
+            document = reader.read(Files.newInputStream(path));
+        } catch (DocumentException | IOException e) {
+            FileUtil.rm_rf(excelReader.self.toFile(), true);
+            throw new ExcelReadException("The file format is incorrect or corrupted. [/xl/drawings/" + path.getFileName() + "]");
+        }
+
+        Element root = document.getRootElement();
+        Namespace xdr = root.getNamespaceForPrefix("xdr"), a = root.getNamespaceForPrefix("a");
+
+        List<Element> elements = root.elements();
+        List<Picture> pictures = new ArrayList<>(elements.size());
+        for (Element e : root.elements()) {
+            Element pic = e.element(QName.get("pic", xdr));
+            // Not a picture
+            if (pic == null) continue;
+
+            Element blipFill = pic.element(QName.get("blipFill", xdr));
+            if (blipFill == null) continue;
+
+            Element blip = blipFill.element(QName.get("blip", a));
+            if (blip == null) continue;
+
+            Namespace r = blip.getNamespaceForPrefix("r");
+            String embed = blip.attributeValue(QName.get("embed", r));
+            Relationship rel = relManager.getById(embed);
+            if (r != null && Const.Relationship.IMAGE.equals(rel.getType())) {
+                Picture picture = new Picture();
+                pictures.add(picture);
+                picture.localPath = path.getParent().resolve(rel.getTarget());
+                picture.dimension = dimension(e, xdr);
+
+                Element extLst = blip.element(QName.get("extLst", a));
+                if (extLst == null) continue;
+
+                for (Element ext : extLst.elements()) {
+                    Element srcUrl = ext.element("picAttrSrcUrl");
+                    // hyperlink
+                    if (srcUrl != null) {
+                        rel = relManager.getById(srcUrl.attributeValue(QName.get("id", r)));
+                        if (rel != null && Const.Relationship.HYPERLINK.equals(rel.getType())) {
+                            picture.srcUrl = rel.getTarget();
+                        }
+                    }
+                }
+            }
+        }
+        return !pictures.isEmpty() ? pictures : null;
+    }
+
+    Dimension dimension(Element e, Namespace xdr) {
+        Element fromEle = e.element(QName.get("from", xdr));
+        int[] f = dimEle(fromEle, xdr);
+        Element toEle = e.element(QName.get("to", xdr));
+        int[] t = dimEle(toEle, xdr);
+
+        return new Dimension(f[0] + 1, (short) (f[1] + 1), t[0] + 1, (short) (t[1] + 1));
+    }
+
+    private int[] dimEle(Element e, Namespace xdr) {
+        int c = 0, r = 0;
+        if (e != null) {
+            String col = e.element(QName.get("col", xdr)).getText(), row = e.element(QName.get("row", xdr)).getText();
+            c = Integer.parseInt(col);
+            r = Integer.parseInt(row);
+        }
+        return new int[] { r, c };
+    }
+}
diff --git a/src/main/java/org/ttzero/excel/reader/XMLSheet.java b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
index 1e630b44..7675d078 100644
--- a/src/main/java/org/ttzero/excel/reader/XMLSheet.java
+++ b/src/main/java/org/ttzero/excel/reader/XMLSheet.java
@@ -64,6 +64,8 @@ class XMLSheet implements Sheet {
 
     // Range address of the used area in the current sheet
     Dimension dimension;
+    // XMLDrawings
+    Drawings drawings;
 
 
     /**
@@ -127,6 +129,10 @@ class XMLSheet implements Sheet {
         this.index = index;
     }
 
+    void setDrawings(Drawings drawings) {
+        this.drawings = drawings;
+    }
+
     /**
      * size of rows.
      *
@@ -458,6 +464,16 @@ class XMLSheet implements Sheet {
         return nIter;
     }
 
+    /**
+     * List all pictures in workbook
+     *
+     * @return picture list or null if not exists.
+     */
+    @Override
+    public List<Drawings.Picture> listPictures() {
+        return drawings != null ? drawings.listPictures(this) : null;
+    }
+
     /**
      * close reader
      *


Test patch:
diff --git a/src/test/java/org/ttzero/excel/entity/StatementSheetTest.java b/src/test/java/org/ttzero/excel/entity/StatementSheetTest.java
index b3dcea6a..0e2c77e4 100644
--- a/src/test/java/org/ttzero/excel/entity/StatementSheetTest.java
+++ b/src/test/java/org/ttzero/excel/entity/StatementSheetTest.java
@@ -60,6 +60,7 @@ public class StatementSheetTest extends SQLWorkbookTest {
     private void testWrite(boolean autoSize) {
         try (Connection con = getConnection()) {
             new Workbook("statement", author)
+                .watch(Print::println)
                 .setAutoSize(autoSize)
                 .setConnection(con)
                 .addSheet("select id, name, age from student order by age"
@@ -76,6 +77,7 @@ public class StatementSheetTest extends SQLWorkbookTest {
     private void testStyleProcessor(boolean autoSize) {
         try (Connection con = getConnection()) {
             new Workbook("statement style processor", author)
+                .watch(Print::println)
                 .setAutoSize(autoSize)
                 .setConnection(con)
                 .addSheet("select id, name, age from student"
diff --git a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
index a5eaf81c..ed3899e9 100644
--- a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
+++ b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
@@ -29,8 +29,10 @@ import org.ttzero.excel.util.FileUtil;
 import java.io.File;
 import java.io.IOException;
 import java.net.URL;
+import java.nio.file.Files;
 import java.nio.file.Path;
 import java.nio.file.Paths;
+import java.nio.file.StandardCopyOption;
 import java.util.Arrays;
 import java.util.Date;
 import java.util.Iterator;
@@ -45,7 +47,6 @@ import static org.ttzero.excel.reader.ExcelReader.COPY_ON_MERGED;
 import static org.ttzero.excel.reader.ExcelReader.VALUE_AND_CALC;
 import static org.ttzero.excel.reader.ExcelReader.VALUE_ONLY;
 import static org.ttzero.excel.reader.ExcelReader.cellRangeToLong;
-import static org.ttzero.excel.reader.ExcelReader.read;
 import static org.ttzero.excel.util.StringUtil.swap;
 
 /**
@@ -536,6 +537,36 @@ public class ExcelReaderTest {
         }
     }
 
+    @Test public void testReadDrawings() {
+        try (ExcelReader reader = ExcelReader.read(testResourceRoot().resolve("drawing.xlsx"))) {
+            reader.sheets().peek(sheet -> println(sheet.getName() + ": " + sheet.getDimension())).flatMap(Sheet::rows).forEach(Print::println);
+
+            // From workbook`
+            List<Drawings.Picture> pictures = reader.listPictures();
+            assert pictures.size() == 5;
+            pictures.forEach(Print::println);
+
+            // Copy images
+            for (Drawings.Picture pic : pictures) {
+                Path dest = Paths.get("target/excel/drawing/", pic.sheet.getName(), pic.localPath.getFileName().toString());
+                if (!Files.exists(dest.getParent())) FileUtil.mkdir(dest.getParent());
+                Files.copy(pic.localPath, dest, StandardCopyOption.REPLACE_EXISTING);
+                assert Files.size(pic.localPath) == Files.size(dest);
+            }
+
+            // From worksheet
+            reader.sheets().forEach(sheet -> {
+                List<Drawings.Picture> pictures1 = sheet.listPictures();
+                if (sheet.getName().equals("Sheet1")) {
+                    assert pictures1.size() == 4;
+                } else assert !sheet.getName().equals("Sheet2") || pictures1.size() == 1;
+                pictures1.forEach(Print::println);
+            });
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+    }
+
     public static class Customer {
         @ExcelColumn("客户编码")
         private String code;