Search code examples
c#apache-poinpoi

NPOI C# set columns to fit on one page

I am using NPOI Version 2.5.3 in my C# application and am trying to set the scaling option (Fit All Columns on 1 Page). Which seems easy enough to do from these questions here and here.

The problem:

So, my problem occurs when using the code below. All that does is configures; both the width and height to fit to one page. I assumed it was because of the sheet.FitToPage = true.

private void SetPrintSettings(XSSFSheet sheet)
{
    sheet.SetMargin(MarginType.BottomMargin, 0.5);
    sheet.SetMargin(MarginType.TopMargin, 0.5);
    sheet.SetMargin(MarginType.LeftMargin, 0.45);
    sheet.SetMargin(MarginType.RightMargin, 0.45);
    sheet.SetMargin(MarginType.HeaderMargin, 0.3);
    sheet.SetMargin(MarginType.FooterMargin, 0.3);

    sheet.Autobreaks = true; //auto breaks
    sheet.FitToPage = true;  //THIS SETS IT TO ALL FIT ON ONE PAGE

    var PrintSetup = sheet.PrintSetup;
    PrintSetup.FitWidth = 1; //fit width onto 1 page
    PrintSetup.FitHeight = 0; //don't care about height
    PrintSetup.Landscape = true;
    PrintSetup.PaperSize = 3; //paper size 11x17
}

When doing the code above I get the following output in Excel.

enter image description here

So after that did not work I tried setting it to false like shown below.

 private void SetPrintSettings(XSSFSheet sheet)
 {
    sheet.SetMargin(MarginType.BottomMargin, 0.5);
    sheet.SetMargin(MarginType.TopMargin, 0.5);
    sheet.SetMargin(MarginType.LeftMargin, 0.45);
    sheet.SetMargin(MarginType.RightMargin, 0.45);
    sheet.SetMargin(MarginType.HeaderMargin, 0.3);
    sheet.SetMargin(MarginType.FooterMargin, 0.3);

    sheet.Autobreaks = true; //auto breaks
    sheet.FitToPage = false; 

    var PrintSetup = sheet.PrintSetup;
    PrintSetup.FitWidth = 1; //fit width onto 1 page
    PrintSetup.FitHeight = 0; //don't care about height
    PrintSetup.Landscape = true;
    PrintSetup.PaperSize = 3; //paper size 11x17
}

When change the configuration, it renders "No Scaling" as shown below.

enter image description here

No matter what I try I can't seem to get this to work. I've tried a variety of settings and nothing seems to work. I am beginning to think its a bug with the version I am using. It doesn't help that almost all the examples I find are for Java POI so I am unsure if it is just an approach issue.

Desired Output:

Below is what I am trying to do. Just set the scaling option to Fit columns onto 1 page. If anyone could help me out or point me in the right direction that would be fantastic.

enter image description here

Solution

  • It looks like this is a bug in NPOI.

    In order to achieve the setting you are after you need the PageSetup element in the sheet XML to have the fitToHeight attribute set to 0. E.g.:

    <pageSetup orientation="landscape" fitToHeight="0"/>

    Unfortunately, if I've read the NPOI code correctly, it looks like NPOI isn't outputting the attribute because it thinks it's a blank value.

    When you call PrintSetup.FitHeight = 0; it sets the fitToHeight property in Sheet.cs. When writing the file, in Sheet.cs there is the following:

    XmlHelper.WriteAttribute(sw, "fitToHeight", this.fitToHeight, 1);

    The WriteAttribute code looks like this:

    public static void WriteAttribute(StreamWriter sw, string attributeName, uint value, uint defaultValue, bool writeIfBlank = false)
{
    if(value != defaultValue)
        WriteAttribute(sw, attributeName, (int)value, writeIfBlank);
    else if(writeIfBlank)
        WriteAttribute(sw, attributeName, (int)value, writeIfBlank);
}

    value (0) is not equal to defaultValue (1) in our case so we enter the first if. That calls another overload of WriteAttribute which looks like this:

    public static void WriteAttribute(StreamWriter sw, string attributeName, int value, bool writeIfBlank)
{
    if (value == 0 && !writeIfBlank)
        return;

    WriteAttribute(sw, attributeName, value.ToString(CultureInfo.InvariantCulture));
}

    value is equal to 0 and writeIfBlank is false so, again, the first if is true and thus the return; is hit and no value is written out.