Bug: quote_sheetname Function Does Not Correctly Quote Sheet Names with Hyphens and Other Characters

[kennethsantos27]

Current behavior

When opening the workbook in Excel, the print titles are not set correctly for sheets with hyphens (e.g., Sheet-A, Sheet-B-C). The image attached demonstrates that the print titles are not showing up in the Page Setup dialog. Analysis:

The function quote_sheetname currently quotes worksheet names only if they contain spaces, exclamation points, or single quotes. However, worksheet names with hyphens and other characters also need to be quoted to work correctly in Excel.

Current quote_sheetname function:

pub(crate) fn quote_sheetname(sheetname: &str) -> String {
    let mut sheetname = sheetname.to_string();

    // Ignore strings that are already quoted.
    if !sheetname.starts_with('\'') {
        // double quote and other single quotes.
        sheetname = sheetname.replace('\'', "''");

        // Single quote the worksheet name if it contains any of the characters
        // that Excel quotes when using the name in a formula.
        if sheetname.contains(' ') || sheetname.contains('!') || sheetname.contains('\'') {
            sheetname = format!("'{sheetname}'");
        }
    }

    sheetname
}

Expected behavior

Quoting Sheet Names:

The quote_sheetname function should quote sheet names that contain hyphens (-) and other special characters in addition to spaces, exclamation points, and single quotes.

Sheet names like "Sheet-A" and "Sheet-B-C" should be quoted correctly when needed.

Setting Print Titles:

The XML output should properly reflect the quoted sheet names in the elements. When opening the workbook in Excel, the print titles should be correctly recognized and applied, allowing the user to see and use them in the Page Layout -> Print Titles dialog.

Handling Special Characters:

The function should handle and quote a variety of special characters as mentioned in this StackOverflow discussion, including hyphens and brackets.

Sample code to reproduce

use rust_xlsxwriter::{Workbook, Worksheet, XlsxError};
use std::fs::File;
use zip::read::ZipArchive;
use std::io::Read;

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

    // Add worksheets
    for _ in 0..3 {
        workbook.add_worksheet();
    }

    // Set worksheet names
    let sheet_names = ["Sheet-A", "Sheet-B-C", "Sheet C"];
    for (index, sheet_name) in sheet_names.iter().enumerate() {
        let worksheet = workbook.worksheets_mut().get_mut(index).ok_or("Worksheet not found")?;
        worksheet.set_name(sheet_name.to_string())?;
    }

    // Set print titles for specific sheets
    let print_titles = [
        ("Sheet-A", "$1:$2"),
        ("Sheet-B-C", "$1:$2"),
        ("Sheet C", "$1:$2"),
    ];

    for (sheet_name, titles) in &print_titles {
        if let Some(worksheet) = get_worksheet_by_name(&mut workbook, 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
    let file_path = "workbook.xlsx";
    workbook.save(file_path)?;

    // Print XML content for debugging
    print_xml_content(file_path)?;

    Ok(())
}

// Helper function to find a worksheet by name
fn get_worksheet_by_name<'a>(workbook: &'a mut Workbook, name: &str) -> Option<&'a mut Worksheet> {
    for worksheet in workbook.worksheets_mut().iter_mut() {
        if worksheet.name() == name {
            return Some(worksheet);
        }
    }
    None
}

// Helper function to read and print XML content from the workbook file
fn print_xml_content(file_path: &str) -> Result<(), Box<dyn std::error::Error>> {
    let zip_file = File::open(file_path)?;
    let mut zip = ZipArchive::new(zip_file)?;
    for i in 0..zip.len() {
        let mut file = zip.by_index(i)?;
        if file.name().ends_with(".xml") {
            println!("File: {}", file.name());
            let mut contents = String::new();
            file.read_to_string(&mut contents)?;
            println!("{}", contents);
        }
    }
    Ok(())
}

### Environment

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

Any other information

We have identified an issue with the quote_sheetname function in the rust-xlsxwriter library where sheet names containing hyphens and certain other characters are not being correctly quoted. This results in Excel not recognizing the print titles specified in the workbook.

Steps to Reproduce:

Create a new workbook using the rust-xlsxwriter library.
Add worksheets with names containing hyphens and spaces.
Set print titles for these sheets.
Save the workbook and inspect the generated XML.
Open the workbook in Excel and check if the print titles are applied.

workbook.xlsx

[jmcnamara]

Thanks for the detailed report. That is a bug. I'll look into it.

[jmcnamara]

I've pushed a fix for this issue to main.

I also want to add support for adding user-quoted worksheet names via Worksheet::set_name() for cases where the current automatic handling isn't sufficient such as worksheet names that contain A1 or RC style reference. So I will leave this open until I fix that as well.

[jmcnamara]

I've pushed a more comprehensive fix to main that also takes into account A1 and RC cell references in the name and also emojis. Basically all the cases in the StackOverflow link you posted.

[jmcnamara]

Fixed on main and in v0.69.0. Thanks for the report.