Sorting Workbook with sortWorksheets is not reordinging colors

[datarocks-mk]

When sorting a WorkBook with workbook.sortWorksheets(sheetComparator) the original cell styles (in particular color) stays with the index and is not applied to the new order.
Issue noticed in fastexel 0.19

[nchen000]

Hi! I am interested in helping with issue, but I haven't been able to reproduce it on my end. Could you share a reproducible example? That would help me investigate it further.
Thanks!

[datarocks-mk]

Here is a self-contained reproducer. Reproduced on 0.19.0 and on 0.20.2 (latest).

What happens

sortWorksheets reorders the <sheet> entries in xl/workbook.xml, but the r:id -> worksheet-part relationship is assigned when the part is written. If sorting happens after the sheets have been written, every sheet name ends up pointing at a foreign part, so the tab colour and the cell data stay behind.

The tab colour is what you notice first, but the data moves too - a sheet named CCC shows AAA's content.

Reproducer

Single file, no dependencies beyond fastexcel itself:

import java.io.*;
import java.nio.file.*;
import java.util.*;
import java.util.regex.*;
import java.util.zip.*;
import org.dhatim.fastexcel.Workbook;
import org.dhatim.fastexcel.Worksheet;

public class FastexcelSortRepro {
   public static void main(String[] args) throws Exception {
      run("sort AFTER the sheets are finished", true);
      run("sort BEFORE the sheets are finished", false);
    }

   private static void run(String label, boolean finishBeforeSort) throws Exception {
      String[] names = {"AAA", "BBB", "CCC"};
      String[] tabColors = {"FF0000", "00FF00", "0000FF"};
      double[] values = {111, 222, 333};

      Path file = Files.createTempFile("sort-repro", ".xlsx");
      try (OutputStream out = Files.newOutputStream(file)) {
        Workbook workbook = new Workbook(out, "repro", "1.0");
        List<Worksheet> sheets = new ArrayList<>();

        for (int i = 0; i < names.length; i++) {
          Worksheet sheet = workbook.newWorksheet(names[i]);
          sheet.setTabColor(tabColors[i]);
          sheet.value(0, 0, values[i]);
          sheets.add(sheet);
          if (finishBeforeSort) {
            sheet.finish();
          }
        }

        workbook.sortWorksheets(Comparator.comparing(Worksheet::getName).reversed());

        if (!finishBeforeSort) {
          for (Worksheet sheet : sheets) {
            sheet.finish();
          }
        }
        workbook.close();
      }

      System.out.println("=== " + label);
      try (ZipFile zip = new ZipFile(file.toFile())) {
        Map<String, String> rels = new HashMap<>();
        Matcher rel = Pattern.compile("Id=\"(rId\\d+)\" Target=\"(worksheets/[^\"]+)\"")
            .matcher(read(zip, "xl/_rels/workbook.xml.rels"));
        while (rel.find()) {
          rels.put(rel.group(1), rel.group(2));
        }

        Matcher sheet = Pattern.compile("<sheet name=\"([^\"]+)\" r:id=\"(rId\\d+)\"")
            .matcher(read(zip, "xl/workbook.xml"));
        while (sheet.find()) {
          String part = rels.get(sheet.group(2));
          String xml = read(zip, "xl/" + part);
          System.out.printf("  tab %-4s -> %-22s tabColor=%-7s value=%s%n",
              sheet.group(1), part, group(xml, "<tabColor rgb=\"([^\"]+)\""), group(xml, "<v>([^<]+)</v>"));
        }
      }
      Files.deleteIfExists(file);
      System.out.println();
    }

    private static String read(ZipFile zip, String name) throws IOException {
      try (InputStream in = zip.getInputStream(zip.getEntry(name))) {
        return new String(in.readAllBytes());
      }
    }

    private static String group(String text, String pattern) {
      Matcher matcher = Pattern.compile(pattern).matcher(text);
      return matcher.find() ? matcher.group(1) : "?";
    }
}

Run it directly (Java 11+):

java -cp fastexcel-0.20.2.jar:opczip-1.2.0.jar FastexcelSortRepro.java

Output on 0.20.2

=== sort AFTER the sheets are finished
tab CCC  -> worksheets/sheet1.xml  tabColor=FF0000  value=111.0
tab BBB  -> worksheets/sheet2.xml  tabColor=00FF00  value=222.0
tab AAA  -> worksheets/sheet3.xml  tabColor=0000FF  value=333.0

=== sort BEFORE the sheets are finished
tab CCC  -> worksheets/sheet1.xml  tabColor=0000FF  value=333.0
tab BBB  -> worksheets/sheet2.xml  tabColor=00FF00  value=222.0
tab AAA  -> worksheets/sheet3.xml  tabColor=FF0000  value=111.0

The first block is the bug: the tab named CCC carries AAA's colour (FF0000) and AAA's value (111), and vice versa. BBB looks correct only because it is the middle element of a reversal.

The second block is what I would expect in both cases. Identical output on 0.19.0.

Expected

After sortWorksheets, each sheet name should still refer to its own worksheet part, so tab colour and content follow the sheet regardless of when the parts were written.

Notes

• Opening the first file in Excel shows the problem directly: the tab order is correct, but the colours and the data belong to the wrong tabs.
• Workaround for anyone hitting this: call sortWorksheets before the first Worksheet.finish(). That requires every sheet to stay open until the end, which is not viable for a streaming export.
• Flushing all sheets first and sorting before finish() is not an alternative - flush() on a second sheet while another part is open fails with IllegalStateException: not current zip current from OpcOutputStream.closeEntry.

Two things worth knowing beyond the paste:
The reproducer is dependency-free by design - it reads workbook.xml and the rels with plain java.util.zip and regex rather than fastexcel-reader, so the maintainer can run it with just the two jars on the classpath and see the raw package structure that shows the mis-pairing.

I picked numeric cell values (111/222/333) deliberately: fastexcel writes those inline as 111.0, so the value is visible in the sheet part without resolving sharedStrings.xml. That keeps the diagnostic output honest and the script short.