Bug: Incorrect `localSheetId` Generation for `<definedName>` Elements Affecting `set_repeat_rows` Functionality

[kennethsantos27]

Current behavior

Current Behavior:

• The localSheetId values are off by 1 compared to the sheetId, leading to incorrect sheet references in the defined names.
• The print titles are not applied correctly when the file is opened, even if specified correctly in the XML.

Expected behavior

Expected Behavior:

• The localSheetId should be zero-based and correctly correspond to the sheetId minus one from the <sheets> section.
• The print titles specified using set_repeat_rows should be correctly reflected in the opened file.

Sample code to reproduce

use rust_xlsxwriter::{Workbook, Worksheet, DefinedName, DefinedNameType};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Create a new workbook and add worksheets
    let mut workbook = Workbook::new();

    // Add worksheets
    let sheet_names = ["SheetA", "SheetB", "SheetC"];
    for sheet_name in &sheet_names {
        workbook.add_worksheet(Some(sheet_name))?;
    }

    // Set print titles for specific sheets
    let print_titles = [
        ("SheetA", "$1:$2"),
        ("SheetB", "$1:$2"),
        ("SheetC", "$1:$2"),
    ];

    for (sheet_name, titles) in &print_titles {
        if let Some(worksheet) = workbook.worksheet_by_name_mut(sheet_name) {
            let titles = titles.split(':').collect::<Vec<&str>>();
            if titles.len() == 2 {
                let first_row = titles[0].trim_start_matches('$').parse::<u32>().unwrap_or(0) - 1;
                let last_row = titles[1].trim_start_matches('$').parse::<u32>().unwrap_or(0) - 1;
                worksheet.set_repeat_rows(first_row, last_row)?;
            }
        }
    }

    // Save the workbook to an XML string
    let xml_output = workbook.to_string()?;
    println!("{}", xml_output);

    Ok(())
}

### Environment

```text
-Rust version: 1.78.0
-rust-xlsxwriter version: 0.68.0
-Operating System: Windows 11 Pro

Any other information

Here is an example of the problematic XML output:

<sheets>
    <sheet name="SheetA" sheetId="1" r:id="rId1"/>
    <sheet name="SheetB" sheetId="2" r:id="rId2"/>
    <sheet name="SheetC" sheetId="3" r:id="rId3"/>
    <!-- More sheets -->
</sheets>
<definedNames>
    <definedName name="_xlnm.Print_Titles" localSheetId="0">SheetA!$1:$2</definedName>
    <definedName name="_xlnm.Print_Titles" localSheetId="1">SheetB!$1:$2</definedName>
    <definedName name="_xlnm.Print_Titles" localSheetId="2">SheetC!$1:$2</definedName>
    <!-- More defined names -->
</definedNames>

[jmcnamara]

Thanks for the detailed report. I'll look into it.

[jmcnamara]

The syntax of the example code doesn't look correct. Should this be a bug report against xlsxwriter.rs and not rust_xlsxwriter.rs?

[jmcnamara]

Also, for what it is worth (since I wrote the C libxlsxwriter library that xlsxwriter.rs uses and any bug there would probably come back to me) the output xml looks correct. Here is the same section from a file created in Excel:

  <sheets>
    <sheet name="SheetA" sheetId="1" r:id="rId1"/>
    <sheet name="SheetB" sheetId="2" r:id="rId2"/>
    <sheet name="SheetC" sheetId="3" r:id="rId3"/>
  </sheets>
  <definedNames>
    <definedName name="_xlnm.Print_Area" localSheetId="0">SheetA!$1:$2</definedName>
    <definedName name="_xlnm.Print_Area" localSheetId="1">SheetB!$1:$2</definedName>
    <definedName name="_xlnm.Print_Area" localSheetId="2">SheetC!$1:$2</definedName>
  </definedNames>

[kennethsantos27]

Thank you for the feedback, @jmcnamara

I apologize for any confusion. It appears that this issue may indeed be more relevant to the xlsxwriter.rs crate. I will close this issue here and report it to the appropriate repository if necessary.

For additional context, we were seeing issues with print titles not being set correctly when opened in Excel, despite being correctly defined in the XML output.

We will double-check our integration with the xlsxwriter.rs crate to ensure that the localSheetId and other attributes are aligned correctly.

Thank you for your assistance.

[jmcnamara]

If you like you can attach a problematic xlsx file here and I can take a look.