Repository: wangguanquan/eec
Pull request #322 (2022-12-29 11:59:01)

Fix#320
Hide columns with auto-size


Patch:
diff --git a/src/main/java/org/ttzero/excel/entity/Column.java b/src/main/java/org/ttzero/excel/entity/Column.java
index 9624eabc..79b11ebe 100644
--- a/src/main/java/org/ttzero/excel/entity/Column.java
+++ b/src/main/java/org/ttzero/excel/entity/Column.java
@@ -899,19 +899,19 @@ public class Column {
         return realColIndex;
     }
 
-    /**
-     * Trim tail nodes after write
-     *
-     * @return current
-     */
-    public Column trimTail() {
-        if (next != null) {
-            next.prev = null;
-            next = null;
-            tail = null;
-        }
-        return this;
-    }
+//    /**
+//     * Trim tail nodes after write
+//     * NOTE: Cannot delete the tail columns, otherwise automatic paging loses headers.
+//     * @return current
+//     */
+//    public Column trimTail() {
+//        if (next != null) {
+//            next.prev = null;
+//            next = null;
+//            tail = null;
+//        }
+//        return this;
+//    }
 
     /**
      * Returns hide flag
diff --git a/src/main/java/org/ttzero/excel/entity/ICellValueAndStyle.java b/src/main/java/org/ttzero/excel/entity/ICellValueAndStyle.java
index 264f443b..d16e6eb3 100644
--- a/src/main/java/org/ttzero/excel/entity/ICellValueAndStyle.java
+++ b/src/main/java/org/ttzero/excel/entity/ICellValueAndStyle.java
@@ -96,11 +96,14 @@ public interface ICellValueAndStyle {
             conversion(row, cell, e, hc);
             return;
         }
-
         if (e == null) {
             setNullValue(row, cell, hc);
             return;
         }
+        if (clazz == null) {
+            clazz = e.getClass();
+            hc.setClazz(clazz);
+        }
         if (isString(clazz)) {
             cell.setSv(e.toString());
         } else if (isDate(clazz)) {
diff --git a/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java b/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java
index a0ce4556..1d6d2fbb 100644
--- a/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java
+++ b/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java
@@ -220,6 +220,8 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         this.bw = new ExtBufferedWriter(Files.newBufferedWriter(
             sheetPath, StandardCharsets.UTF_8));
 
+        if (sst == null) this.sst = sheet.getSst();
+
         return sheetPath;
     }
 
@@ -312,10 +314,14 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         writeSheetViews();
 
         // Default row height and width
-        int fillSpace = 6;
+        int fillSpace = 17; // Column width xxx.xx (6byte) + hidden property (11byte)
         BigDecimal width = BigDecimal.valueOf(!nonHeader ? sheet.getDefaultWidth() : 8.38D);
+        // Overflow column width limit
+        if (width.compareTo(new BigDecimal(Const.Limit.COLUMN_WIDTH)) > 0) {
+            width = new BigDecimal(Const.Limit.COLUMN_WIDTH);
+        }
         String defaultWidth = width.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
-        writeSheetFormat(fillSpace, defaultWidth);
+//        writeSheetFormat(fillSpace, defaultWidth);
 
         // cols
         writeCols(fillSpace, defaultWidth);
@@ -338,7 +344,7 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             columnsArray[i] = columns[i].toArray();
 
             // Free memory after write
-            columns[i].trimTail();
+//            columns[i].trimTail();
         }
         // Merge cells if exists
         @SuppressWarnings("unchecked")
@@ -835,8 +841,11 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             else if (isBool(clazz)) {
                 len = 5.0D;
             }
+            else if (hc.getNumFmt() != null) {
+                len = hc.getNumFmt().calcNumWidth(0);
+            }
             else {
-                len = 10.0D;
+                len = hc.o > 0 ? hc.o : 10.0D;
             }
             double width = Math.max(_l, len) + 1.86D;
             if (hc.width > 0.000001D) width = Math.min(width, hc.width);
@@ -847,7 +856,7 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         }
         // resize each column width ...
         try (SeekableByteChannel channel = Files.newByteChannel(path.toPath(), StandardOpenOption.WRITE, StandardOpenOption.READ)) {
-            ByteBuffer buffer = ByteBuffer.allocate(387 + columns.length * 73);
+            ByteBuffer buffer = ByteBuffer.allocate(316 + columns.length * 80);
             buffer.order(ByteOrder.LITTLE_ENDIAN);
 
             int n = channel.read(buffer);
@@ -876,12 +885,18 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             // Rewrite cols
             position = findPosition(buffer, "<cols>");
             if (position > 0) {
-                for (String s : widths) {
+                for (int i = 0; i < columns.length; i++) {
+                    String s = widths[i];
                     position = findPosition(buffer, "width=\"");
                     if (position == -1) continue;
                     buffer.put(s.getBytes(StandardCharsets.US_ASCII));
                     buffer.put((byte) '"');
-                    for (int j = 6 - s.length(); j-- > 0; ) buffer.put((byte) 32); // Fill space
+                    int fillSpace = 17;
+                    if (columns[i].isHide()) {
+                        buffer.put(" hidden=\"1\"".getBytes(StandardCharsets.US_ASCII));
+                        fillSpace -= 11;
+                    }
+                    for (int j = fillSpace - s.length(); j-- > 0; ) buffer.put((byte) 32); // Fill space
                 }
             }
 
@@ -1048,34 +1063,24 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
     protected void writeCols(int fillSpace, String defaultWidth) throws IOException {
         if (columns.length > 0) {
             bw.write("<cols>");
-//            if (sheet.isAutoSize()) {
             for (int i = 0; i < columns.length; i++) {
                 Column col = columns[i];
+                int w = defaultWidth.length();
                 bw.write("<col customWidth=\"1\" width=\"");
                 if (col.isHide()) {
-                    bw.writeInt(0);
+                    bw.write(defaultWidth);
                     bw.write("\" hidden=\"1");
+                    w += 11;
                 }
                 else bw.write(col.width > 0.0000001D ? new BigDecimal(col.width).setScale(2, BigDecimal.ROUND_HALF_UP).toString() : defaultWidth);
                 bw.write('"');
-                for (int j = fillSpace - defaultWidth.length(); j-- > 0; ) bw.write(32); // Fill space
+                for (int j = fillSpace - w; j-- > 0; ) bw.write(32); // Fill space
                 bw.write(" min=\"");
                 bw.writeInt(col.getRealColIndex());
                 bw.write("\" max=\"");
-                bw.writeInt(col.getRealColIndex() + 1);
+                bw.writeInt(col.getRealColIndex());
                 bw.write("\" bestFit=\"1\"/>");
             }
-//            } else {
-//                bw.write("<col customWidth=\"1\" width=\"");
-//                bw.write(columns[i].width >= 1.0 ? new BigDecimal(columns[i].width).setScale(2, BigDecimal.ROUND_HALF_UP).toString() : stringWidth);
-//                bw.write('"');
-//                for (int j = n; j-- > 0; ) bw.write(32); // Fill space
-//                bw.write(" min=\"");
-//                bw.writeInt(columns[0].getRealColIndex());
-//                bw.write("\" max=\"");
-//                bw.writeInt(columns[columns.length - 1].getRealColIndex() + 1);
-//                bw.write("\" bestFit=\"1\"/>");
-//            }
             bw.write("</cols>");
         }
     }
@@ -1090,8 +1095,8 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         bw.write("<sheetData>");
 
         int headerRow = 1;
-        // The first header row
-        if (!nonHeader) {
+        // Write header rows
+        if (!nonHeader && columns.length > 0) {
             headerRow = writeHeaderRow();
         }
         startRow = headerRow + (sheet.getNonHeader() ^ 1);


Test patch:
diff --git a/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java b/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
index 2eb98afb..75ac2316 100644
--- a/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
+++ b/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
@@ -95,6 +95,29 @@ public class MultiHeaderColumnsTest extends SQLWorkbookTest {
         }
     }
 
+    @Test public void testAutoSizeAndHideCol() throws IOException {
+        new Workbook("Auto Size And Hide Column").setAutoSize(true)
+            .addSheet(new ListSheet<>("期末成绩", ListObjectSheetTest.Student.randomTestData()
+                , new Column().addSubColumn(new ListSheet.EntryColumn("共用表头")).addSubColumn(new Column("学号", "id").setHeaderComment(new Comment("abc", "content")))
+                , new ListSheet.EntryColumn("共用表头").addSubColumn(new Column("姓名", "name").setColIndex(1000).hide())
+                , new Column("成绩", "score")
+            )).writeTo(defaultTestPath);
+    }
+
+    @Test public void testAutoSizeAndHideColPaging() throws IOException {
+        new Workbook("Auto Size And Hide Column").setAutoSize(true)
+            .addSheet(new ListSheet<>("期末成绩", ListObjectSheetTest.Student.randomTestData()
+                , new Column().addSubColumn(new ListSheet.EntryColumn("共用表头")).addSubColumn(new Column("学号", "id").setHeaderComment(new Comment("abc", "content")))
+                , new ListSheet.EntryColumn("共用表头").addSubColumn(new Column("姓名", "name"))
+                , new Column("成绩", "score").hide()
+            ).setSheetWriter(new XMLWorksheetWriter() {
+                @Override
+                public int getRowLimit() {
+                    return 10;
+                }
+            })).writeTo(defaultTestPath);
+    }
+
     public static final String[] provinces = {"江苏省", "湖北省", "浙江省", "广东省"};
     public static final String[][] cities = {{"南京市", "苏州市", "无锡市", "徐州市"}
         , {"武汉市", "黄冈市", "黄石市", "孝感市", "宜昌市"}