Set Print Area - OpenXML with Excel

Question

Does anybody know how to set print area through OpenXML SDK in Excel?

I've tried using the following code:

public void XLUpdateDefinedName(string fileName, string definedName, string newRange)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        WorkbookPart wbPart = document.WorkbookPart;

        var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
        DefinedName name = definedNames.Descendants<DefinedName>().Where(m => m.Name == definedName).Single();
        UInt32Value locSheetId = name.LocalSheetId;
        name = null;//.Remove();
        wbPart.Workbook.Save();
        name = new DefinedName() { Name = definedName, LocalSheetId = locSheetId , Text = newRange}
            ;
        wbPart.Workbook.Save();
        //newDefinedName.Text = newRange;
        //definedNames.Append(newDefinedName);

    }
}

UPDATE:

I continue to receive an error from excel saying there is unreadable content in file with the following code.

   public void XLUpdateDefinedName(string fileName, string definedName, string newRange, string sheet, UInt32Value localId)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
        {                
            String sheetName = sheet;
            string topLeft = newRange.Split(':').First();
            string bottomRight = newRange.Split(':').Last();
            WorkbookPart wbPart = document.WorkbookPart;

            var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
            var nameCollection = definedNames.Descendants<DefinedName>().Where(m => m.Text.StartsWith(sheetName));
            DefinedName name = nameCollection.Count() > 0 ? nameCollection.First() : null;
            UInt32Value locSheetId;
            if (name != null)
            {
                locSheetId = name.LocalSheetId;
                name.Remove();
                wbPart.Workbook.Save();
            }
            else
            {
                locSheetId = localId;
            }
            name = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = locSheetId};
            name.Text = String.Format("{0}!{1}:{2}", sheetName,topLeft,bottomRight);
            definedNames.Append(name);
            wbPart.Workbook.Save();
        }}

newRange is of the form ( $A$10:$C$15 )

Answer 1

I've found some information about a method that seems not to be using Interop. You can try something like:

//load the work book
...

myWorkBook.Worksheets.First().PageSetup.PrintAreas.Add("A1:F40");

//save the workbook
//...

See if this helps. I haven't tried it myself yet, but I'm going to verify it.

UPDATE: The first method seems to require an additional library. You can get it from here: http://closedxml.codeplex.com/. I haven't used it myself, so I cannot assure you it works correctly.

Pure OpenXML solution

I've managed to change the print area by manually modifying the xlsx file contents in notepad editor.

In C# you should try to use the follwoing method (it sets printing area to A1:G19):

//first you need to get reference to your workbook, but I assume you already have this
//...
//then you can add an information about desired print area
DefinedNames definedNames = new DefinedNames();
DefinedName printAreaDefName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
printAreaDefName.Text = "Worksheet1!$A$1:$G$19";
definedNames.Append(printAreaDefName);
//then you should append the created element to your workbook
//...
workbook1.Append(definedNames);

The thing you need to change is line: printAreaDefName.Text = "Worksheet1!$A$1:$G$19";.

You should change the Text value to contain information in format: [worksheet_name]![top-left corner of print area]:[bottom-right corner of print area]. It should set your print area to a rectangle with upper-left and bottom-right corners as specified.

If you want to specify print areas for different worksheets, try to add multiple DefinedName objects:

  DefinedName printAreaDefName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
  printAreaDefName.Text = "Worksheet1!$A$1:$G$19";
  definedNames.Append(printAreaDefName);
  DefinedName printAreaDefName2 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)1U };
  printAreaDefName2.Text = "Worksheet2!$B$1:$H$23";
  definedNames.Append(printAreaDefName2);
  DefinedName printAreaDefName3 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)2U };
  printAreaDefName3.Text = "Worksheet3!$A$1:$J$10";
  definedNames.Append(printAreaDefName3);

I also recommend using OpenXML SDK 2.0 Productivity Tool. It allows you to show the contents of a chosen OpenXML file, compare files, validate a file and even show a C# code that you would write in order to recreate the file programatically :). You can download it from here: http://www.microsoft.com/download/en/details.aspx?id=5124

UPDATE II:

I've corrected a mistake in format of the print area value string. Sorry for the confusion. I've also took the code you posted and created a method based on it. It works correctly and after modifying the print area I can open the file in Excel without issues. The code assumes that a print range is already defined and you are now just changing it, but it can be modified to add new print range as well. Here's the code:

private void OpenXmlFileHandling(String fileName)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
        {
            //some sample values
            String definedName = "Worksheet3";
            String topLeft = "$A$3";
            String bottomRight = "$D$7";

            WorkbookPart wbPart = document.WorkbookPart;

            var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
            var namesCollection = definedNames.Descendants<DefinedName>().Where(m => m.Text.StartsWith(definedName));
            DefinedName name = namesCollection != null ? namesCollection.First() : null;

            UInt32Value locSheetId;
            //we assume that name is not null, because print range for this worksheet was defined in the source template file
            //if name was null, we should probably just assign to locSheetId a number definedNames.Count() + 1 and not remove the name node
            locSheetId = name.LocalSheetId;
            name.Remove();

            wbPart.Workbook.Save();
            name = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = locSheetId, Text = String.Format("{0}!{1}:{2}", definedName, topLeft, bottomRight) };


            definedNames.Append(name);

            wbPart.Workbook.Save();
        }
    }

I put the values of worksheet name and print area range inside the method, so you can see what kind of values they should have. I hope this helps.