Insert a new worksheet into a spreadsheet

Inserting a worksheet requires creating a worksheet part, adding a workbook relationship, and adding a <sheet/> entry to the workbook's <sheets/> collection.

The package must already be opened for editing. In ooxmlsdk terms, the operation belongs at the workbook part boundary: create a worksheet part, initialize it with an empty worksheet and sheetData, add a relationship from the workbook part to that new part, then append the corresponding sheet entry in workbook XML.

Workbook markup

<sheets>
  <sheet name="Summary" sheetId="1" r:id="rId1"/>
  <sheet name="New Sheet" sheetId="2" r:id="rId2"/>
</sheets>

The new sheetId must be unique in the workbook, and the r:id must resolve to the new worksheet part.

Choose the next id by scanning the existing workbook sheetId values and adding one to the maximum. The worksheet name is independent from the part path; a typical generated name is Sheet{sheet_id}, but production code should also avoid collisions with existing sheet names.

Rust workflow

#![allow(unused)]
fn main() {
pub fn insert_new_worksheet(
  path: &Path,
  sheet_name: &str,
) -> Result<Vec<u8>, Box<dyn std::error::Error>> {
  let mut document = SpreadsheetDocument::new_from_file_with_settings(path, lazy_settings())?;
  let workbook_part = document.workbook_part()?;
  let worksheet_part = workbook_part.add_new_part_auto_id::<_, WorksheetPart>(&mut document)?;
  worksheet_part.set_data(
    &mut document,
    br#"<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData/></worksheet>"#.to_vec(),
  )?;
  let relationship_id = workbook_part
    .get_id_of_part(&document, &worksheet_part)
    .expect("worksheet relationship id")
    .to_string();
  let workbook_xml = workbook_part.data_as_str(&document)?.unwrap_or_default();
  let updated_workbook_xml = append_sheet(workbook_xml, sheet_name, &relationship_id)?;

  workbook_part.set_data(&mut document, updated_workbook_xml.into_bytes())?;

  let mut buffer = Cursor::new(Vec::new());
  document.save(&mut buffer)?;
  Ok(buffer.into_inner())
}
}

The listing creates the worksheet part from the workbook part, initializes it with an empty <sheetData/>, reads the new relationship id, appends the matching <sheet/> entry, and saves the package. The important invariant is that workbook XML, workbook relationships, and package content types are saved together; updating only <sheets/> leaves a workbook entry that points nowhere.