Repository: wangguanquan/eec
Pull request #393 (2024-09-21 14:16:48)

工作表支持设置自定义属性


Patch:
diff --git a/src/main/java/org/ttzero/excel/entity/Workbook.java b/src/main/java/org/ttzero/excel/entity/Workbook.java
index 931f164a..d8385b4c 100644
--- a/src/main/java/org/ttzero/excel/entity/Workbook.java
+++ b/src/main/java/org/ttzero/excel/entity/Workbook.java
@@ -23,6 +23,7 @@ import org.ttzero.excel.entity.style.Fill;
 import org.ttzero.excel.entity.style.PatternType;
 import org.ttzero.excel.entity.style.Styles;
 import org.ttzero.excel.manager.docProps.Core;
+import org.ttzero.excel.manager.docProps.CustomProperties;
 import org.ttzero.excel.util.FileUtil;
 import org.ttzero.excel.util.StringUtil;
 
@@ -35,6 +36,7 @@ import java.nio.charset.Charset;
 import java.nio.file.Files;
 import java.nio.file.Path;
 import java.util.Arrays;
+import java.util.Map;
 import java.util.function.BiConsumer;
 
 import static org.ttzero.excel.util.FileUtil.exists;
@@ -158,6 +160,10 @@ public class Workbook implements Storable {
      * 全局多媒体记数器（当前仅支持图片）
      */
     private int mediaCounter;
+    /**
+     * 自定义属性
+     */
+    private CustomProperties customProperties;
 
     /**
      * 创建一个未命名工作薄
@@ -879,4 +885,63 @@ public class Workbook implements Storable {
     public int getMediaCounter() {
         return mediaCounter;
     }
+
+    /**
+     * 添加自定义属性，自定义属性可以从"信息"-&gt;"属性"-&gt;"自定义属性"查看
+     *
+     * <p>注意：只支持{@code "文本"}、{@code "数字"}、{@code "日期"}以及{@code "布尔值"}，其它数据类型将使用{@code toString}强转换为文本</p>
+     *
+     * @param key 属性名，不超过{@code 256}个字符
+     * @param value 属性值，
+     * @return 当前工作表
+     */
+    public Workbook putCustomProperty(String key, Object value) {
+        if (customProperties == null) customProperties = new CustomProperties();
+        customProperties.put(key, value);
+        return this;
+    }
+
+    /**
+     * 添加自定义属性，自定义属性可以从"信息"-&gt;"属性"-&gt;"自定义属性"查看
+     *
+     * <p>注意：只支持{@code "文本"}、{@code "数字"}、{@code "日期"}以及{@code "布尔值"}，其它数据类型将使用{@code toString}强转换为文本</p>
+     *
+     * @param properties 批量属性
+     * @return 当前工作表
+     */
+    public Workbook putCustomProperties(Map<String, Object> properties) {
+        if (customProperties == null) customProperties = new CustomProperties();
+        customProperties.putAll(properties);
+        return this;
+    }
+
+    /**
+     * 删除自定义属性
+     *
+     * @param key 指定属性名
+     * @return 如果属性存在则返回属性值否则返回 {@code null}
+     */
+    public Object removeCustomProperty(String key) {
+        return customProperties != null ? customProperties.remove(key) : null;
+    }
+
+    /**
+     * 获取自定义属性类
+     *
+     * @return {@code Custom}自定义属性类
+     */
+    public CustomProperties getCustomProperties() {
+        return customProperties;
+    }
+
+    /**
+     * 文档保护-标记只读
+     *
+     * @return 当前工作表
+     */
+    public Workbook markAsReadOnly() {
+        if (customProperties == null) customProperties = new CustomProperties();
+        customProperties.markAsReadOnly();
+        return this;
+    }
 }
diff --git a/src/main/java/org/ttzero/excel/entity/e7/XMLWorkbookWriter.java b/src/main/java/org/ttzero/excel/entity/e7/XMLWorkbookWriter.java
index ed95118d..de1c0238 100644
--- a/src/main/java/org/ttzero/excel/entity/e7/XMLWorkbookWriter.java
+++ b/src/main/java/org/ttzero/excel/entity/e7/XMLWorkbookWriter.java
@@ -39,6 +39,7 @@ import org.ttzero.excel.manager.Const;
 import org.ttzero.excel.manager.RelManager;
 import org.ttzero.excel.manager.docProps.App;
 import org.ttzero.excel.manager.docProps.Core;
+import org.ttzero.excel.manager.docProps.CustomProperties;
 import org.ttzero.excel.util.FileUtil;
 import org.ttzero.excel.util.StringUtil;
 import org.ttzero.excel.util.ZipUtil;
@@ -140,7 +141,7 @@ public class XMLWorkbookWriter implements IWorkbookWriter {
         relManager.add(rel);
     }
 
-    private void writeGlobalAttribute(Path root) throws IOException {
+    protected void writeGlobalAttribute(Path root) throws IOException {
 
         // Content type
         ContentType contentType = workbook.getContentType();
@@ -156,6 +157,9 @@ public class XMLWorkbookWriter implements IWorkbookWriter {
         // Write core
         writeCore(root);
 
+        // Write custom properties
+        writeCustomProperties(root);
+
         Path themeP = root.resolve("theme");
         if (!exists(themeP)) {
             Files.createDirectory(themeP);
@@ -224,7 +228,7 @@ public class XMLWorkbookWriter implements IWorkbookWriter {
         }
     }
 
-    private void writeApp(Path root) throws IOException {
+    protected void writeApp(Path root) throws IOException {
 
         // docProps
         App app = new App();
@@ -254,7 +258,7 @@ public class XMLWorkbookWriter implements IWorkbookWriter {
             .addContentTypeRel(new Relationship("docProps/app.xml", Const.Relationship.APP));
     }
 
-    private void writeCore(Path root) throws IOException {
+    protected void writeCore(Path root) throws IOException {
         Core core = workbook.getCore() != null ? workbook.getCore() : new Core();
         if (StringUtil.isEmpty(core.getCreator())) {
             if (workbook.getCreator() != null) {
@@ -272,7 +276,22 @@ public class XMLWorkbookWriter implements IWorkbookWriter {
             .addContentTypeRel(new Relationship("docProps/core.xml", Const.Relationship.CORE));
     }
 
-    private void writeSelf(Path root) throws IOException {
+    /**
+     * 写自定义属性
+     *
+     * @param root root路径
+     * @throws IOException If I/O error occur.
+     */
+    protected void writeCustomProperties(Path root) throws IOException {
+        CustomProperties custom = workbook.getCustomProperties();
+        if (custom != null && custom.hasProperty()) {
+            custom.writeTo(root.getParent().resolve("docProps/custom.xml"));
+            workbook.addContentType(new ContentType.Override(Const.ContentType.CUSTOM, "/docProps/custom.xml"))
+                .addContentTypeRel(new Relationship("docProps/custom.xml", Const.Relationship.CUSTOM));
+        }
+    }
+
+    protected void writeSelf(Path root) throws IOException {
         DocumentFactory factory = DocumentFactory.getInstance();
         //use the factory to create a root element
         Element rootElement = null;
diff --git a/src/main/java/org/ttzero/excel/manager/Const.java b/src/main/java/org/ttzero/excel/manager/Const.java
index b0ce4b0e..6cee8c7f 100644
--- a/src/main/java/org/ttzero/excel/manager/Const.java
+++ b/src/main/java/org/ttzero/excel/manager/Const.java
@@ -55,6 +55,7 @@ public interface Const {
             IMAGE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
             , APP = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"
             , CORE = "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"
+            , CUSTOM = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties"
             , OFFICE_DOCUMENT = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
             , SHARED_STRING = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
             , STYLE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"
@@ -87,6 +88,7 @@ public interface Const {
             , SHEET = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
             , APP = "application/vnd.openxmlformats-officedocument.extended-properties+xml"
             , CORE = "application/vnd.openxmlformats-package.core-properties+xml"
+            , CUSTOM = "application/vnd.openxmlformats-officedocument.custom-properties+xml"
             , SHAREDSTRING = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"
             , PRINTSETTING = "application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings"
             , COMMENTS ="application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"
diff --git a/src/main/java/org/ttzero/excel/manager/docProps/CustomProperties.java b/src/main/java/org/ttzero/excel/manager/docProps/CustomProperties.java
new file mode 100644
index 00000000..0a4dd0f0
--- /dev/null
+++ b/src/main/java/org/ttzero/excel/manager/docProps/CustomProperties.java
@@ -0,0 +1,236 @@
+/*
+ * Copyright (c) 2017-2024, guanquan.wang@hotmail.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.manager.docProps;
+
+import org.dom4j.Element;
+import org.dom4j.Namespace;
+import org.dom4j.QName;
+import org.ttzero.excel.manager.TopNS;
+import org.ttzero.excel.util.DateUtil;
+import org.ttzero.excel.util.StringUtil;
+
+import java.math.BigDecimal;
+import java.text.ParsePosition;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * 自定义属性
+ *
+ * <p>注意：只支持{@code "文本"}、{@code "数字"}、{@code "日期"}以及{@code "布尔值"}，其它数据类型将使用{@code toString}强转换为文本</p>
+ *
+ * @author guanquan.wang
+ * @since 2024-09-19
+ */
+@TopNS(prefix = {"", "vt"}, uri = {"http://schemas.openxmlformats.org/officeDocument/2006/custom-properties"
+    , "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"}, value = "Properties")
+public class CustomProperties extends XmlEntity {
+    /**
+     * 自定义属性的GUID值{D5CDD505-2E9C-101B-9397-08002B2CF9AE}
+     */
+    public static final String FORMAT_ID = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}";
+    /**
+     * 文档保护-只读标记
+     */
+    private static final String MARK_AS_READ_ONLY = "_MarkAsFinal";
+    /**
+     * 自定义属性
+     * key: 属性名
+     * value: v1: 属性值 v2: 值类型
+     */
+    private final Map<String, Tuple2<Object, Integer>> properties;
+
+    public CustomProperties() {
+        this.properties = new LinkedHashMap<>();
+    }
+
+    /**
+     * 将指定的键值对添加到属性集合中
+     *
+     * @param key 属性的键名
+     * @param value 属性的值
+     */
+    public void put(String key, Object value) {
+        check(key, value);
+        properties.put(key, toProValue(value));
+    }
+
+    /**
+     * 将指定的属性集合添加到当前对象中
+     *
+     * @param properties 属性名称和值的映射表
+     */
+    public void putAll(Map<String, Object> properties) {
+        for (Map.Entry<String, Object> entry : properties.entrySet()) {
+            check(entry.getKey(), entry.getValue());
+            this.properties.put(entry.getKey(), toProValue(entry.getValue()));
+        }
+    }
+
+    /**
+     * 文档保护-只读标记
+     */
+    public void markAsReadOnly() {
+        properties.put(MARK_AS_READ_ONLY, Tuple2.of(true, 4));
+    }
+
+    /**
+     * 判断文档是否受为只读
+     *
+     * @return true: 工作表为只读
+     */
+    public boolean hasReadOnlyMark() {
+        Tuple2<Object, Integer> v = properties.remove(MARK_AS_READ_ONLY);
+        return v != null && (Boolean) v.v1;
+    }
+
+    /**
+     * 移除指定属性
+     *
+     * @param key 指定需要移除的Key
+     * @return 如果Key存在则返回对应的值否则返回 {@code null}
+     */
+    public Object remove(String key) {
+        Tuple2<Object, Integer> v = properties.remove(key);
+        return v != null ? v.v1 : null;
+    }
+
+    /**
+     * 获取所有自定义属性的副本
+     *
+     * @return 自定义属性列表
+     */
+    public Map<String, Object> getAll() {
+        Map<String, Object> result = new HashMap<>(properties.size());
+        for (Map.Entry<String, Tuple2<Object, Integer>> entry : properties.entrySet()) {
+            // Ignore build-in key
+            if (MARK_AS_READ_ONLY.equals(entry.getKey())) continue;
+            result.put(entry.getKey(), entry.getValue().v1);
+        }
+        return result;
+    }
+
+    /**
+     * 获取自定义属性的值
+     *
+     * @param key 属性Key
+     * @return 如果Key存在则返回对应的值否则返回 {@code null}
+     */
+    public Object get(String key) {
+        Tuple2<Object, Integer> v = properties.get(key);
+        return v != null ? v.v1 : null;
+    }
+
+    /**
+     * 测试是否包含自定义属性
+     *
+     * @return true: 包含自定义属性
+     */
+    public boolean hasProperty() {
+        return !properties.isEmpty();
+    }
+
+    @Override
+    void toDom(Element root, Map<String, Namespace> namespaceMap) {
+        int id = 2; // beginning pid
+        Namespace vt = namespaceMap.get("vt");
+        for (Map.Entry<String, Tuple2<Object, Integer>> entry : properties.entrySet()) {
+            Element property = root.addElement("property").addAttribute("fmtid", FORMAT_ID)
+                .addAttribute("pid", Integer.toString(id++)).addAttribute("name", entry.getKey());
+            Tuple2<Object, Integer> val = entry.getValue();
+            switch (val.v2) {
+                case 0: property.addElement(QName.get("lpwstr", vt)).addText(val.v1.toString());                   break;
+                case 1: property.addElement(QName.get("filetime", vt)).addText(DateUtil.toTString((Date) val.v1)); break;
+                case 2: property.addElement(QName.get("i4", vt)).addText(val.v1.toString());                       break;
+                case 3: property.addElement(QName.get("r8", vt)).addText(val.v1.toString());                       break;
+                case 4: property.addElement(QName.get("bool", vt)).addText(val.v1.toString());                     break;
+                default:
+            }
+        }
+    }
+
+    /**
+     * Dom转Custom对象
+     *
+     * @param root dom树
+     * @return Custom对象
+     */
+    public static CustomProperties domToCustom(Element root) {
+        List<Element> list = root.elements();
+        if (list == null || list.isEmpty()) return null;
+        CustomProperties custom = new CustomProperties();
+        Element val;
+        List<Element> sub;
+        for (Element e : list) {
+            String key = e.attributeValue("name");
+            sub = e.elements();
+            if (sub != null && !sub.isEmpty()) {
+                val = sub.get(0);
+                String type = val.getName(), value = val.getText();
+                switch (type) {
+                    case "lpwstr": custom.properties.put(key, Tuple2.of(value, 0));                break;
+                    case "filetime": custom.properties.put(key, Tuple2.of(DateUtil.utcDateTimeFormat.get().parse(value, new ParsePosition(0)), 1)); break;
+                    case "i4":
+                    case "i2": custom.properties.put(key, Tuple2.of(Integer.parseInt(value), 2));  break;
+                    case "i8": custom.properties.put(key, Tuple2.of(Long.parseLong(value), 3));    break;
+                    case "r8":
+                    case "r4":
+                    case "ui8": custom.properties.put(key, Tuple2.of(new BigDecimal(value), 3));   break;
+                    case "bool": custom.properties.put(key, Tuple2.of(Boolean.valueOf(value), 4)); break;
+                    default: custom.properties.put(key, Tuple2.of(value, 0)); // Origin value
+                }
+            } else custom.properties.put(key, Tuple2.of(null, 0)); // Put a null value
+        }
+        return custom;
+    }
+
+    /**
+     * 检查属性的合法性
+     *
+     * @param key 属性名
+     * @param val 属性值
+     */
+    protected static void check(String key, Object val) {
+        if (StringUtil.isEmpty(key))
+            throw new IllegalArgumentException("Property name is required.");
+        if (key.length() > 256)
+            throw new IllegalArgumentException("Property name is too long, max=256 current=" + key.length());
+        if (val == null)
+            throw new IllegalArgumentException("Property value is required.");
+    }
+
+    /**
+     * 将属性值转换为可识别的结果
+     *
+     * @param val 外部属性值
+     * @return 内部属性值 v1: 原值 v2：类型
+     */
+    protected static Tuple2<Object, Integer> toProValue(Object val) {
+        int t;
+        if (val instanceof String) t = 0;
+        else if (val instanceof Date) t = 1;
+        else if (val instanceof Integer || val instanceof Short) t = 2;
+        else if (val instanceof Long || val instanceof Double || val instanceof Float) t = 3;
+        else if (val instanceof Boolean) t = 4;
+        else t = 0;
+        return Tuple2.of(val, t);
+    }
+}
diff --git a/src/main/java/org/ttzero/excel/reader/ExcelReader.java b/src/main/java/org/ttzero/excel/reader/ExcelReader.java
index b4c20896..dba09dce 100644
--- a/src/main/java/org/ttzero/excel/reader/ExcelReader.java
+++ b/src/main/java/org/ttzero/excel/reader/ExcelReader.java
@@ -36,6 +36,7 @@ import org.ttzero.excel.manager.ExcelType;
 import org.ttzero.excel.manager.RelManager;
 import org.ttzero.excel.manager.docProps.App;
 import org.ttzero.excel.manager.docProps.Core;
+import org.ttzero.excel.manager.docProps.CustomProperties;
 import org.ttzero.excel.util.DateUtil;
 import org.ttzero.excel.util.FileUtil;
 import org.ttzero.excel.util.StringUtil;
@@ -846,4 +847,30 @@ public class ExcelReader implements Closeable {
         else if (path.charAt(0) == '/' || path.charAt(0) == '\\') i = 1;
         return i > 0 ? path.substring(i) : path;
     }
+
+    /**
+     * 获取自定义属性
+     *
+     * <p>返回数据类型说明，时间返回{@code java.util.Date}，布尔值返回{@code Boolean}，
+     * 整数类型分情况返回{@code Integer}或{@code Long}，浮点数返回{@code BigDecimal}</p>
+     *
+     * @return 存在时返回键值对否则返回 {@code null}
+     */
+    public CustomProperties getCustomProperties() {
+        // Load custom.xml
+        SAXReader reader = SAXReader.createDefault();
+        ZipEntry entry = getEntry("docProps/custom.xml");
+        if (entry == null) return null;
+        Document document = null;
+        try {
+            document = reader.read(zipFile.getInputStream(entry));
+        } catch (DocumentException | IOException e) {
+            LOGGER.warn("The file format is incorrect or corrupted. [docProps/custom.xml]");
+        }
+        if (document == null) return null;
+        Element root = document.getRootElement();
+        List<Element> list = root.elements();
+        if (list == null || list.isEmpty()) return null;
+        return CustomProperties.domToCustom(root);
+    }
 }


Test patch:
diff --git a/src/test/java/org/ttzero/excel/entity/ListObjectSheetTest2.java b/src/test/java/org/ttzero/excel/entity/ListObjectSheetTest2.java
index fe15a482..dad614d2 100644
--- a/src/test/java/org/ttzero/excel/entity/ListObjectSheetTest2.java
+++ b/src/test/java/org/ttzero/excel/entity/ListObjectSheetTest2.java
@@ -29,6 +29,7 @@ import org.ttzero.excel.entity.style.Horizontals;
 import org.ttzero.excel.entity.style.PatternType;
 import org.ttzero.excel.entity.style.Styles;
 import org.ttzero.excel.manager.Const;
+import org.ttzero.excel.manager.docProps.CustomProperties;
 import org.ttzero.excel.processor.Converter;
 import org.ttzero.excel.processor.StyleProcessor;
 import org.ttzero.excel.reader.Cell;
@@ -52,11 +53,14 @@ import java.lang.annotation.Target;
 import java.lang.reflect.AccessibleObject;
 import java.lang.reflect.Array;
 import java.math.BigDecimal;
+import java.sql.Timestamp;
 import java.time.LocalDate;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
+import java.util.Date;
 import java.util.Iterator;
+import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.HashMap;
@@ -687,6 +691,46 @@ public class ListObjectSheetTest2 extends WorkbookTest {
         })).writeTo(defaultTestPath.resolve("tree style.xlsx"));
     }
 
+    @Test public void testCustomProperties() throws IOException {
+        Map<String, Object> properties = new LinkedHashMap<>();
+        properties.put("办公室", "24F");
+        properties.put("记录日期", new Timestamp(System.currentTimeMillis()));
+        properties.put("工作组", 9527);
+        properties.put("批复", true);
+        properties.put("工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工工", "1");
+        properties.put("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa", 2);
+        properties.put(" ", Integer.MAX_VALUE);
+        properties.put("short", (short) 12);
+        properties.put("long", System.currentTimeMillis());
+        properties.put("double", 124353.234354D);
+        properties.put("负数", -1234);
+        properties.put("负double", -1234.123445D);
+        new Workbook()
+            .markAsReadOnly()
+            .putCustomProperties(properties)     // <- 设置多组属性
+            .putCustomProperty("追加属性", "abc") // <- 设置单组属性
+            .addSheet(new ListSheet<>(ListObjectSheetTest.Item.randomTestData())).writeTo(defaultTestPath.resolve("custom property.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("custom property.xlsx"))) {
+            CustomProperties customProperties = reader.getCustomProperties();
+            for (Map.Entry<String, Object> entry : properties.entrySet()) {
+                Object expect = entry.getValue(), val = customProperties.get(entry.getKey());
+                if (expect instanceof String || expect instanceof Boolean) {
+                    assertEquals(expect, val);
+                } else if (expect instanceof Date) {
+                    assertEquals(((Date) expect).getTime() / 1000, ((Date) val).getTime() / 1000);
+                } else if (expect instanceof Short || expect instanceof Integer) {
+                    assertEquals(Integer.valueOf(expect.toString()), val);
+                } else if (expect instanceof Long) {
+                    assertEquals(expect, ((BigDecimal) val).longValue());
+                } else if (expect instanceof Float || expect instanceof Double) {
+                    assertEquals(expect, ((BigDecimal) val).doubleValue());
+                } else assertEquals(expect.toString(), val.toString());
+            }
+            assertEquals("abc", customProperties.get("追加属性"));
+        }
+    }
+
     @TreeStyle
     public static class TreeNode {
         @ExcelColumn
diff --git a/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java b/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
index 789bcbce..fbd63308 100644
--- a/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
+++ b/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
@@ -21,6 +21,9 @@ import org.junit.Test;
 import org.ttzero.excel.annotation.ExcelColumn;
 import org.ttzero.excel.annotation.HeaderComment;
 import org.ttzero.excel.entity.e7.XMLWorksheetWriter;
+import org.ttzero.excel.entity.style.Border;
+import org.ttzero.excel.entity.style.BorderStyle;
+import org.ttzero.excel.entity.style.Fill;
 import org.ttzero.excel.entity.style.Styles;
 import org.ttzero.excel.reader.Cell;
 import org.ttzero.excel.reader.ExcelReader;
@@ -576,6 +579,111 @@ public class MultiHeaderColumnsTest extends SQLWorkbookTest {
         }
     }
 
+    @Test public void testMultiHeaderAndStyles() throws IOException {
+        Workbook workbook = new Workbook();
+        Styles styles = workbook.getStyles();
+        Font fontYahei20Red = new Font("微软雅黑", 20, Color.RED);
+        Font fontYahei16Black = new Font("微软雅黑", 16, Color.BLACK);
+        Font fontYahei25Blue = new Font("微软雅黑", 25, Color.BLUE);
+        Fill fillGrey = new Fill(Styles.toColor("#E9EAEC"));
+        Fill fillYellow = new Fill(Color.YELLOW);
+        Fill fillCyan = new Fill(Color.CYAN);
+        Border borderGary = new Border(BorderStyle.THIN, Color.GRAY);
+        int borderIndex = styles.addBorder(borderGary);
+
+        workbook.addSheet(new ListSheet<>(new Column("headerlonglong").setHeaderStyle(styles.addFont(fontYahei20Red) | styles.addFill(fillGrey) | borderIndex | Horizontals.LEFT).setHeaderHeight(40)
+            .addSubColumn(new Column("middle column").setHeaderStyle(styles.addFont(fontYahei16Black) | styles.addFill(fillYellow) | borderIndex | Horizontals.CENTER).setHeaderHeight(30))
+            .addSubColumn(new Column("StuName", "stuName").setHeaderStyle(styles.addFont(fontYahei25Blue) | styles.addFill(fillGrey) | borderIndex | Horizontals.CENTER).setHeaderHeight(20).setWidth(50))
+            , new Column("header3").setHeaderStyle(styles.addFont(fontYahei16Black) | styles.addFill(fillCyan) | borderIndex | Horizontals.CENTER)
+            .addSubColumn(new Column("header2").setHeaderStyle(styles.addFont(fontYahei16Black) | styles.addFill(fillGrey) | borderIndex | Horizontals.CENTER))
+            .addSubColumn(new Column("header3").setHeaderStyle(styles.addFont(fontYahei20Red) | styles.addFill(fillGrey) | borderIndex | Horizontals.CENTER)).setWidth(30)));
+
+        workbook.writeTo(defaultTestPath.resolve("multiheader-multistyles.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("multiheader-multistyles.xlsx"))) {
+            // Global styles
+            Styles style = reader.getStyles();
+            Iterator<Row> iter = reader.sheet(0).iterator();
+            // Row1
+            assertTrue(iter.hasNext());
+            Row row = iter.next();
+
+            // Row1 Cell1
+            int xf = row.getCellStyle(0);
+            Font font = style.getFont(xf);
+            assertEquals(font, fontYahei20Red);
+            Fill fill = style.getFill(xf);
+            assertEquals(fill, fillGrey);
+            Border border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            int horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.LEFT);
+
+            // Row1 Cell2
+            xf = row.getCellStyle(1);
+            font = style.getFont(xf);
+            assertEquals(font, fontYahei16Black);
+            fill = style.getFill(xf);
+            assertEquals(fill, fillCyan);
+            border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.CENTER);
+
+            // Row2
+            assertTrue(iter.hasNext());
+            row = iter.next();
+
+            // Row2 Cell1
+            xf = row.getCellStyle(0);
+            font = style.getFont(xf);
+            assertEquals(font, fontYahei16Black);
+            fill = style.getFill(xf);
+            assertEquals(fill, fillYellow);
+            border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.CENTER);
+
+            // Row2 Cell2
+            xf = row.getCellStyle(1);
+            font = style.getFont(xf);
+            assertEquals(font, fontYahei16Black);
+            fill = style.getFill(xf);
+            assertEquals(fill, fillGrey);
+            border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.CENTER);
+
+            // Row3
+            assertTrue(iter.hasNext());
+            row = iter.next();
+
+            // Row3 Cell1
+            xf = row.getCellStyle(0);
+            font = style.getFont(xf);
+            assertEquals(font, fontYahei25Blue);
+            fill = style.getFill(xf);
+            assertEquals(fill, fillGrey);
+            border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.CENTER);
+
+            // Row3 Cell2
+            xf = row.getCellStyle(1);
+            font = style.getFont(xf);
+            assertEquals(font, fontYahei20Red);
+            fill = style.getFill(xf);
+            assertEquals(fill, fillGrey);
+            border = style.getBorder(xf);
+            assertEquals(border, borderGary);
+            horizontals = style.getHorizontal(xf);
+            assertEquals(horizontals, Horizontals.CENTER);
+        }
+    }
+
     public static final String[] provinces = {"江苏省", "湖北省", "浙江省", "广东省"};
     public static final String[][] cities = {{"南京市", "苏州市", "无锡市", "徐州市"}
         , {"武汉市", "黄冈市", "黄石市", "孝感市", "宜昌市"}