How to add new sheets to a spreadsheet and give them relationships?

Jul 27, 2009 at 3:22 PM

Hello James,

I have a dataset I create from SQL that I am looping through to create separate spreadsheets. My problem is that unless I use the relationshipid from one of the spreadsheets I cannot save the doc with spreadsheetwriter.save(doc). Even if I use an existing relationshipid it doesn't work if I have more than 1 extra sheet and excel has to fix it before I can read it.

So how do I make extra sheets on a workbook and populate them with good relationship values.

Feb 18, 2010 at 5:57 PM

Daniel,

       I'm in the same boat. Did you ever manage to figure out how to add more worksheets?

       I've examined the unit tests and, based on those, I've determined that new sheets can't be added. Only one worksheet is allowed and it MUST be called "Sheet1".  ;-)

       Seriously though, if anyone can tell me how to add a new worksheet, it'd be greatly appreciated!

Don

Feb 19, 2010 at 7:03 PM

I never got around this.

Jun 24, 2010 at 5:10 PM
Edited Jun 24, 2010 at 5:20 PM

Hi,

I've made a quick workaround, tune it, I haven't done any serious stresstest. This method creates a new sheet if it does not exists. And returns it.

public WorksheetPart Worksheet(string sheetname)
{
	// Get sheets where sheetname is the provided text.
	IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetname);

	// If the specified worksheet does not exist, create it.
	if (sheets.Count() == 0)
	{
		// Find out the next id
		uint newId = (uint)(doc.Package.GetRelationships().Count() + 1);
		// do not set this to rId...
		string rId = "relId" + newId;

		int sheetnumber = doc.WorkbookPart.WorksheetParts.Count() + 1;

		// Create the new worksheetpart
		WorksheetPart wsp = doc.WorkbookPart.AddNewPart<WorksheetPart>(rId);

		// Add important stuff :-)
		doc.WorkbookPart.Workbook.Save();
		doc.WorkbookPart.Workbook.Sheets.AppendChild<Sheet>(new Sheet() { Id = rId, SheetId = newId, Name = sheetname });
		doc.WorkbookPart.Workbook.Save();
		wsp.Worksheet = new Worksheet();
		wsp.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
		wsp.Worksheet.AppendChild<SheetDimension>(new SheetDimension());
		doc.WorkbookPart.Workbook.Save();
		wsp.Worksheet.AppendChild<SheetViews>(new SheetViews()).AppendChild<SheetView>(new SheetView() { WorkbookViewId = 0 });
		doc.WorkbookPart.Workbook.Save();
		wsp.Worksheet.AppendChild<SheetFormatProperties>(new SheetFormatProperties() { DefaultRowHeight = 15 });
		doc.WorkbookPart.Workbook.Save();
wsp.Worksheet.AppendChild<SheetData>(new SheetData()); doc.WorkbookPart.Workbook.Save(); wsp.Worksheet.AppendChild<PageMargins>(new PageMargins() { Left = 0.7, Right = 0.7, Top = 0.75, Bottom = 0.75, Header = 0.3, Footer = 0.3 }); doc.WorkbookPart.Workbook.Save(); // Store the relationship of the workbook and the sheet doc.Package.CreateRelationship(new Uri("worksheets/sheet" + sheetnumber + ".xml", UriKind.Relative), System.IO.Packaging.TargetMode.Internal, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet", rId); doc.Package.Flush(); // !!! doc.WorkbookPart.Workbook.Save(); // return the new worksheetpart return wsp; } // Return the sheet we found return (WorksheetPart)doc.WorkbookPart.GetPartById(sheets.First().Id); }

This is how I use it:

public string this[string sheet, string col, int row]
{
	get
	{
		return WorksheetReader.GetCell(col, (uint)row, Worksheet(sheet)).CellValue.Text;
	}
	set
	{
		new WorksheetWriter(doc, Worksheet(sheet)).PasteText(col + row, value);
	}
}

The variable named doc is a SpreadsheetDocument.

Hope that helps.

Coordinator
Jul 9, 2010 at 10:16 PM
This has been tested and added to the next release. Many thanks.
Dec 29, 2010 at 12:20 PM

Hi koshinae and thanks for the code...however, when using this code the spreadsheet document seems to become corrupt in cases when a new worksheets are created.
In other words, passing the names of the default worksheets (Sheet1..3) works fine but if I pass a name that causes the if-statement to be true the generated spreadsheet document becomes corrupt.

I used the OpenXmlValidator-class to get some more information, it says:
"The relationship 'relId4' referenced by attribute 'http://schemas.openxmlformats.org/officeDocument/2006/relationships:id' does not exist."

Have you got any clue?

 

Dec 29, 2010 at 1:05 PM

I think I found the problem, I changed the extension from "xlsx" to "zip" to inspect what was in the package..
It seems that the relationships for the "new" worksheets are stored at the wrong place..the relationships for the original worksheets (1..3) are stored in the xml-file "xl/_rels/workbook.xml.rels" while the relationships "rel4" worksheets was stored in the file "_rels/.rels".

I modified the content of thouse files manually and then the file wasn't corrupt anymore and could be opened in Excel.

However, I have no clue how to do this programmatically...I guess the line below needs to be changed somehow

doc.Package.CreateRelationship(new Uri("worksheets/sheet" + sheetnumber + ".xml", UriKind.Relative), System.IO.Packaging.TargetMode.Internal, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet", rId);