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 )

Solution 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.

Solution 2

Let me explain my case: I have excel workbook with following sheets (T1,I1,M1). Now my requirement was based on some condition T1,I1,M1 will be copied to same excel workbook multiple times e.g. T2,I2,M2, T3,I3,M3 and so on. For me I2,M2 did not have any issues with Print area but for copied sheet T2,T3... had issue. since it had huge data. excel column goes upto "AG". So here is what i did in code

Once the new sheet is added to workbook

sheets.Append(copiedSheet);

first get the current sheet count

var count = sheets.Count(); 

Get the sheet count, this will be used in LocalsheetId as printarea setting.

Only for copied techical sheet the print area was not set properly. Hence need to be set correctly.

DefinedName printAreaDefName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = Convert.ToUInt32(count) };

Be care full with defName.Text the format is 'T1'!$A$1:$AG$19

printAreaDefName.Text = "'" + copiedSheet.Name + "'!$A$1:$AG$22";

workbookPart.Workbook.DefinedNames.Append(printAreaDefName); 
workbookPart.Workbook.Save();

There was no need for me to add new Definedname in DefinedNames collection. So I just added to workbook definedNames collection and it worked.