Repository: wangguanquan/eec
Pull request #349 (2023-03-30 01:09:18)

Fix#345 Specify the first row index


Patch:
diff --git a/src/main/java/org/ttzero/excel/entity/Sheet.java b/src/main/java/org/ttzero/excel/entity/Sheet.java
index fb739024..ef996b4a 100644
--- a/src/main/java/org/ttzero/excel/entity/Sheet.java
+++ b/src/main/java/org/ttzero/excel/entity/Sheet.java
@@ -190,6 +190,10 @@ public abstract class Sheet implements Cloneable, Storable {
      * Specify custom header row height
      */
     protected double headerRowHeight = 20.5D, rowHeight = -1D;
+    /**
+     * Specify the first row index
+     */
+    protected int startRowIndex = 1;
 
     public int getId() {
         return id;
@@ -654,6 +658,29 @@ public abstract class Sheet implements Cloneable, Storable {
         return this;
     }
 
+    /**
+     * Returns the first row index(default 1)
+     *
+     * @return the first row index
+     */
+    public int getStartRowIndex() {
+        return startRowIndex;
+    }
+
+    /**
+     * Specify the first row index, which must be greater than 0
+     *
+     * @param startRowIndex row index
+     */
+    public Sheet setStartRowIndex(int startRowIndex) {
+        if (startRowIndex <= 0)
+            throw new IndexOutOfBoundsException("The start row index must be greater than 0, current = " + startRowIndex);
+        if (sheetWriter != null && sheetWriter.getRowLimit() <= startRowIndex)
+            throw new IndexOutOfBoundsException("The start row index must be less than row-limit, current(" + startRowIndex + ") >= limit(" + sheetWriter.getRowLimit() + ")");
+        this.startRowIndex = startRowIndex;
+        return this;
+    }
+
     /**
      * Returns the columns
      *
@@ -715,7 +742,7 @@ public abstract class Sheet implements Cloneable, Storable {
             }
 
             // Reset Row limit
-            this.rowLimit = sheetWriter.getRowLimit() - (nonHeader == 1 || columns.length == 0 ? 0 : columns[0].subColumnSize());
+//            this.rowLimit = sheetWriter.getRowLimit() - (nonHeader == 1 || columns.length == 0 ? 0 : columns[0].subColumnSize()) - startRowIndex + 1
 
             // Mark ext-properties
             markExtProp();
@@ -1313,7 +1340,7 @@ public abstract class Sheet implements Cloneable, Storable {
      * @return the limit
      */
     protected int getRowLimit() {
-        return rowLimit > 0 ? rowLimit : (rowLimit = sheetWriter.getRowLimit() - (nonHeader == 1 || columns.length == 0 ? 0 : columns[0].subColumnSize()));
+        return rowLimit > 0 ? rowLimit : (rowLimit = sheetWriter.getRowLimit() - (nonHeader == 1 || columns.length == 0 ? 0 : columns[0].subColumnSize()) - startRowIndex + 1);
     }
 
     /**
@@ -1531,6 +1558,14 @@ public abstract class Sheet implements Cloneable, Storable {
                 lastCol.headerComment = headerComment;
             }
 
+            if (startRowIndex > 1) {
+                List<Dimension> tmp = new ArrayList<>();
+                for (Dimension dim : mergeCells) {
+                    tmp.add(new Dimension(dim.firstRow + startRowIndex - 1, dim.firstColumn, dim.lastRow + startRowIndex - 1, dim.lastColumn));
+                }
+                mergeCells = tmp;
+            }
+
             @SuppressWarnings("unchecked")
             List<Dimension> existsMergeCells = (List<Dimension>) getExtPropValue(Const.ExtendPropertyKey.MERGE_CELLS);
             if (existsMergeCells != null && !existsMergeCells.isEmpty()) existsMergeCells.addAll(mergeCells);
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 97ba98e6..f7cf4b99 100644
--- a/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java
+++ b/src/main/java/org/ttzero/excel/entity/e7/XMLWorksheetWriter.java
@@ -97,7 +97,8 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
     protected Column[] columns;
     protected SharedStrings sst;
     protected Comments comments;
-    protected int startRow;
+    protected int startRow // The first data-row index
+        , startHeaderRow; // The first header row index
     protected long pStart, pEnd; // The position dimension to sheetData
 
     public XMLWorksheetWriter() { }
@@ -223,6 +224,13 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
 
         if (sst == null) this.sst = sheet.getSst();
 
+        // Check the first row index
+        startHeaderRow = sheet.getStartRowIndex();
+        if (startHeaderRow <= 0)
+            throw new IndexOutOfBoundsException("The start row index must be greater than 0, current = " + startHeaderRow);
+        if (getRowLimit() <= startHeaderRow)
+            throw new IndexOutOfBoundsException("The start row index must be less than row-limit, current(" + startHeaderRow + ") >= limit(" + getRowLimit() + ")");
+        startRow = startHeaderRow;
         return sheetPath;
     }
 
@@ -339,7 +347,7 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
      */
     protected int writeHeaderRow() throws IOException {
         // Write header
-        int row = 0, subColumnSize = columns[0].subColumnSize(), defaultStyleIndex = sheet.defaultHeadStyleIndex();
+        int rowIndex = 0, subColumnSize = columns[0].subColumnSize(), defaultStyleIndex = sheet.defaultHeadStyleIndex();
         Column[][] columnsArray = new Column[columns.length][];
         for (int i = 0; i < columns.length; i++) {
             columnsArray[i] = columns[i].toArray();
@@ -352,20 +360,10 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         List<Dimension> mergeCells = (List<Dimension>) sheet.getExtPropValue(Const.ExtendPropertyKey.MERGE_CELLS);
         Grid mergedGrid = mergeCells != null && !mergeCells.isEmpty() ? GridFactory.create(mergeCells) : null;
         for (int i = subColumnSize - 1; i >= 0; i--) {
-
-            row++;
-            bw.write("<row r=\"");
-            bw.writeInt(row);
             // Custom row height
             double ht = getHeaderHeight(columnsArray, i);
             if (ht < 0) ht = sheet.getHeaderRowHeight();
-            if (ht >= 0D) {
-                bw.write("\" customHeight=\"1\" ht=\"");
-                bw.write(ht);
-            }
-            bw.write("\" spans=\"1:");
-            bw.writeInt(columns[columns.length - 1].getRealColIndex());
-            bw.write("\">");
+            int row = startRow(rowIndex++, columns.length, ht);
 
             String name;
             if (sheet.isAutoSize()) {
@@ -392,7 +390,7 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             }
             bw.write("</row>");
         }
-        return row;
+        return subColumnSize;
     }
 
     /**
@@ -492,8 +490,10 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             bw.write("\" customHeight=\"1\" ht=\"");
             bw.write(rowHeight);
         }
-        bw.write("\" spans=\"1:");
-        bw.writeInt(columns);
+        bw.write("\" spans=\"");
+        bw.writeInt(this.columns[0].realColIndex);
+        bw.write(':');
+        bw.writeInt(this.columns[this.columns.length - 1].realColIndex);
         bw.write("\">");
         return r;
     }
@@ -912,14 +912,16 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
             int position = findPosition(buffer, "<dimension ");
             // Get it
             if (position > 0) {
-                buffer.put("ref=\"A1".getBytes(StandardCharsets.US_ASCII));
+                buffer.put("ref=\"".getBytes(StandardCharsets.US_ASCII));
+                char[] col = columns.length > 0 ?int2Col(columns[0].getRealColIndex()) : new char[] { 'A' };
+                buffer.put((new String(col) + (startHeaderRow - columns[0].subColumnSize())).getBytes(StandardCharsets.US_ASCII));
                 int fill = 11; // fill 11 space
                 buffer.put((byte) ':');
                 fill--;
-                char[] col = int2Col(columns[columns.length - 1].getRealColIndex());
-                buffer.put((new String(col) + (rows + 1)).getBytes(StandardCharsets.US_ASCII));
+                col = int2Col(columns[columns.length - 1].getRealColIndex());
+                buffer.put((new String(col) + (rows + startRow)).getBytes(StandardCharsets.US_ASCII));
                 fill -= col.length;
-                fill -= stringSize(rows + 1);
+                fill -= stringSize(rows + startRow);
                 buffer.put((byte) '"');
                 for (; fill-->0;) buffer.put((byte) 32); // Fill space
             }
@@ -1010,16 +1012,19 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
      */
     protected void writeDimension() throws IOException {
         pStart = bw.getWrittenChars();
-        bw.append("<dimension ref=\"A1"); // FIXME Setting the column or row's start-index
+        bw.append("<dimension ref=\"");
+        if (columns.length > 0) bw.write(int2Col(columns[0].getRealColIndex()));
+        else bw.write('A');
+        bw.writeInt(startHeaderRow);
         int n = 11, size = sheet.size(); // fill 11 space
-        if (size > 0) {
+        if (size > 0 && columns.length > 0) {
             bw.write(':');
             n--;
             char[] col = int2Col(columns[columns.length - 1].getRealColIndex());
             bw.write(col);
             n -= col.length;
-            bw.writeInt(size + 1);
-            n -= stringSize(size + 1);
+            bw.writeInt(size + startRow);
+            n -= stringSize(size + startRow);
         }
         bw.write('"');
         for (; n-->0;) bw.write(32); // Fill space
@@ -1075,7 +1080,23 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
                 }
                 bw.write("</sheetView>");
             }
-        } else {
+        }
+        // Move the head row to the top
+        else if (startHeaderRow > 1) {
+            bw.write(" topLeftCell=\"");
+            char[] cols = int2Col(columns[0].realColIndex);
+            bw.write(cols);
+            bw.writeInt(startHeaderRow);
+            bw.write("\">");
+            bw.write("<selection activeCell=\"");
+            bw.write(cols);
+            bw.writeInt(startHeaderRow);
+            bw.write("\" sqref=\"");
+            bw.write(cols);
+            bw.writeInt(startHeaderRow);
+            bw.write("\"/></sheetView>");
+        }
+        else {
             bw.write("/>"); // Empty tag
         }
         bw.write("</sheetViews>");
@@ -1138,12 +1159,12 @@ public class XMLWorksheetWriter implements IWorksheetWriter {
         pEnd = bw.getWrittenChars();
         bw.write("<sheetData>");
 
-        int headerRow = 1;
+        int headerRow = 0;
         // Write header rows
         if (!nonHeader && columns.length > 0) {
             headerRow = writeHeaderRow();
         }
-        startRow = headerRow + (sheet.getNonHeader() ^ 1);
+        startRow = startHeaderRow + headerRow;
     }
 
     /**
diff --git a/src/main/java/org/ttzero/excel/reader/XMLRow.java b/src/main/java/org/ttzero/excel/reader/XMLRow.java
index 9cf2d335..f0566400 100644
--- a/src/main/java/org/ttzero/excel/reader/XMLRow.java
+++ b/src/main/java/org/ttzero/excel/reader/XMLRow.java
@@ -200,13 +200,13 @@ public class XMLRow extends Row {
         // find type
         // n=numeric (default), s=string, b=boolean, str=function string
         char t = NUMERIC; // default
-        int xf = 0, i;
+        int xf = 0, i = 0;
         for (; cb[cursor] != '>'; cursor++) {
             // Cell index
             if (cb[cursor] <= ' ' && cb[cursor + 1] == 'r' && cb[cursor + 2] == '=') {
                 int a = cursor += 4;
                 for (; cb[cursor] != '"'; cursor++) ;
-                i = unknownLength ? (lc = toCellIndex(cb, a, cursor)) : toCellIndex(cb, a, cursor);
+                i = toCellIndex(cb, a, cursor);
                 // The `spans` attribute is not be set
                 if (i - 1 >= cells.length) {
                     // Bound check
@@ -245,6 +245,7 @@ public class XMLRow extends Row {
         // The style index
         cell.xf = xf;
         cell.t = t;
+        if (lc < i) lc = i;
 
         return cell;
     }


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 0f2570bd..55854676 100644
--- a/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
+++ b/src/test/java/org/ttzero/excel/entity/MultiHeaderColumnsTest.java
@@ -294,6 +294,38 @@ public class MultiHeaderColumnsTest extends SQLWorkbookTest {
             ).writeTo(defaultTestPath);
     }
 
+    @Test public void testRepeatColumnFromN() throws IOException {
+        List<RepeatableEntry4> list = RepeatableEntry4.randomTestData();
+        int startRowIndex = 7;
+        new Workbook().setAutoSize(true)
+            .addSheet(new ListSheet<>(list).setStartRowIndex(startRowIndex))
+            .writeTo(defaultTestPath.resolve("Repeat Columns From 7.xlsx"));
+
+        try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("Repeat Columns From 7.xlsx"))) {
+            List<RepeatableEntry4> readList = reader.sheet(0).header(startRowIndex, startRowIndex + 1).bind(RepeatableEntry4.class).rows()
+                .map(row -> (RepeatableEntry4) row.get()).collect(Collectors.toList());
+
+            assert list.size() == readList.size();
+            for (int i = 0, len = list.size(); i < len; i++)
+                assert list.get(i).equals(readList.get(i));
+
+            // Row to Map
+            List<Map<String, Object>> mapList = reader.sheet(0).header(startRowIndex, startRowIndex + 1).rows().map(Row::toMap).collect(Collectors.toList());
+            assert list.size() == mapList.size();
+            for (int i = 0, len = list.size(); i < len; i++) {
+                Map<String, Object> sub = mapList.get(i);
+                RepeatableEntry4 src = list.get(i);
+
+                assert sub.get("订单号").equals(src.orderNo);
+                assert sub.get("收件人").equals(src.recipient);
+                assert sub.get("收件地址:省").equals(src.province);
+                assert sub.get("收件地址:市").equals(src.city);
+                assert sub.get("收件地址:区").equals(src.area);
+                assert sub.get("收件地址:详细地址").equals(src.detail);
+            }
+        }
+    }
+
     public static final String[] provinces = {"江苏省", "湖北省", "浙江省", "广东省"};
     public static final String[][] cities = {{"南京市", "苏州市", "无锡市", "徐州市"}
         , {"武汉市", "黄冈市", "黄石市", "孝感市", "宜昌市"}
@@ -505,4 +537,94 @@ public class MultiHeaderColumnsTest extends SQLWorkbookTest {
             return orderNo + " | " + recipient + " | " + province + " | " + city + " | " + area + " | " + detail;
         }
     }
+
+    public static class RepeatableEntry4 {
+        @ExcelColumn(value = "订单号", colIndex = 3)
+        private String orderNo;
+        @ExcelColumn(value = "收件人", colIndex = 4)
+        private String recipient;
+        @ExcelColumn("收件地址")
+        @ExcelColumn(value = "省", colIndex = 5)
+        private String province;
+        @ExcelColumn("收件地址")
+        @ExcelColumn(value = "市", colIndex = 6)
+        private String city;
+        @ExcelColumn("收件地址")
+        @ExcelColumn(value = "区", colIndex = 7)
+        private String area;
+        @ExcelColumn("收件地址")
+        @ExcelColumn(value = "详细地址", colIndex = 8)
+        private String detail;
+
+        public RepeatableEntry4() {}
+
+        public RepeatableEntry4(String orderNo, String recipient, String province, String city, String area, String detail) {
+            this.orderNo = orderNo;
+            this.recipient = recipient;
+            this.province = province;
+            this.city = city;
+            this.area = area;
+            this.detail = detail;
+        }
+
+        public static List<RepeatableEntry4> randomTestData(int n) {
+            List<RepeatableEntry4> list = new ArrayList<>(n);
+            for (int i = 0, p, c; i < n; i++) {
+                list.add(new RepeatableEntry4(Integer.toString(Math.abs(random.nextInt())), getRandomString(8) + 2, provinces[p = random.nextInt(provinces.length)], cities[p][c = random.nextInt(cities[p].length)], areas[p][c][random.nextInt(areas[p][c].length)], "xx街" + (random.nextInt(10) + 1) + "号"));
+            }
+            return list;
+        }
+
+        public static List<RepeatableEntry4> randomTestData() {
+            int n = random.nextInt(100) + 1;
+            return randomTestData(n);
+        }
+
+        public String getOrderNo() {
+            return orderNo;
+        }
+
+        public String getRecipient() {
+            return recipient;
+        }
+
+        public String getProvince() {
+            return province;
+        }
+
+        public String getCity() {
+            return city;
+        }
+
+        public String getArea() {
+            return area;
+        }
+
+        public String getDetail() {
+            return detail;
+        }
+
+        @Override
+        public int hashCode() {
+            return orderNo.hashCode();
+        }
+
+        @Override
+        public boolean equals(Object o) {
+            if (o instanceof RepeatableEntry4) {
+                RepeatableEntry4 other = (RepeatableEntry4) o;
+                return Objects.equals(orderNo, other.orderNo)
+                    && Objects.equals(recipient, other.recipient)
+                    && Objects.equals(province, other.province)
+                    && Objects.equals(city, other.city)
+                    && Objects.equals(detail, other.detail);
+            }
+            return false;
+        }
+
+        @Override
+        public String toString() {
+            return orderNo + " | " + recipient + " | " + province + " | " + city + " | " + area + " | " + detail;
+        }
+    }
 }