Sheet.merge breaks the document

[migueldma]

Hi,

Context: I made a copy of an Excel file located in assets directory and saved it in the Temporary folder of the device. Then, I tried to update/change that file.

When I use Sheet.merge it correctly applies the merge but breaks my document, removing all the previous merged cells(the ones that came with the file) and throw an error when you tried to open the file in google docs. If you remove the Sheet.merge or replace it with a simple Cell value insertion, the file works fine. Here's some code:

    Sheet firstSheet = excel['Sheet1'];    
    int rowStart = 9;    
    String colStart = "A";
    String colEnd = "D";

    int oldActionId = -1;
    int row = rowStart;
    String col = colStart;

    data.forEach((element) {
      if(element["action_id"] != oldActionId){
        oldActionId = element["action_id"];
        
        firstSheet.merge(CellIndex.indexByString("$colStart$row"), CellIndex.indexByString("$colEnd$row"), customValue: element["action_name"]); //Breaks the file.        
        row++;
      }
    });

[justkawal]

@migueldma Thanks for posting the issue, I'll have a look into it

[justkawal]

@migueldma Can you provide me a sample structured file? by removing your important data from it.

It would be helpful to know the edge and corner cases.

[justkawal]

@migueldma You can mail on [email protected]

[migueldma]

@migueldma You can mail on [email protected]

Hi, do you need the excel file, the project or both?

[justkawal]

@migueldma I only need your sample excel file for testing, be sure to remove your important and private data from the file, if any.

[migueldma]

Hi, did the file help?

[maboumra]

I don't know if this is a different issue but I've also noticed that editing contents of a merged cell also breaks the document.

[amigne]

Hello,

the following code breaks the document:

  var month='2023-03'; 
  var excel = Excel.createExcel();
  excel.rename(excel.getDefaultSheet()!, month);
  excel.setDefaultSheet(month);
  Sheet sheet = excel[month];

  // Title
  sheet.merge(CellIndex.indexByString('A1'), CellIndex.indexByString('E1'), customValue: 'Timestamps - ${_monthName(month)} ${_year(month)}');
  sheet.cell(CellIndex.indexByString('A1')).cellStyle = CellStyle(fontSize: 18, bold: true, horizontalAlign: HorizontalAlign.Center,);

 var fileBytes = excel.save();
  var directory = (await getApplicationDocumentsDirectory()).path;

  File(join("$directory\\output_file_name.xlsx"))
    ..createSync(recursive: true)
    ..writeAsBytesSync(fileBytes!);

[amigne]

When specifying a string value as customValue parameter, the generated OpenXML code in xl\worksheets\sheet1.xml is

<c r="A1" s="1"><v>Timestamps - Janvier 2023</v></c>

When specifying the value to cell A1, then merging the cells, the generated OpenXML code is

<c r="A1" s="1" t="s"><v>0</v></c>

The second option looks more conform to the specs, as for strings, the <v>...</v> tag should not contain the text itself, but a reference to the string, stored in xl\sharedStrings.xml.

I think this bug is caused by https://github.com/justkawal/excel/blob/12529d8472e8e6ab36e551b090f2205f85ba2916/lib/src/sheet/sheet.dart#L719

The value is directly assigned to the private property _value, instead of calling its setter value, with value.value = customValue.

I don't know if using the private _value property instead of calling its public setter value was made by intent (to prevent other side effects caused by the setter). @justkawal, could you please give me some light on it? Would it be safe to fix this by changing the value._value by value.value?

[FauconSpartiate]

Fixed in #218