NPOI C# set columns to fit on one page

Question

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.

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.

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.

Answer 1

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.

Answer 2

1) You could be missing autoSizeColumn, if I understood you correctly. Please configure/setup to autosize the cols to fit you need to use youExcelSheet.AutoSizeColumn() and, that in 2) combination with the autofit to page/sheet FitToPage should make it work for you.

Source code from Apache Github, since the calculation of the col & page sizes the order of calls maybe important between autoSizeCol and autofit page you can try both combos

From the apache ref. can autosize your cols ref from Apache here,

---

// create some cells
for (int r=0; r < 10; r++) {
    Row row = sheet.createRow(r);
    for (int c; c < 10; c++) {
        Cell cell = row.createCell(c);
        cell.setCellValue("Cell " + c.getAddress().formatAsString());
    }
}


// 1 ** AUTO-Size the columns individually like below.
// 2 Or get/fit ALL, then loop through the cols, divide the sheet by number of cols


sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);

---

and an older sample in SO here

HSSFWorkbook spreadsheet = new HSSFWorkbook();

DataSet results = GetSalesDataFromDatabase();

//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
HSSFSheet sheet1 = spreadsheet.CreateSheet("Sheet1");

foreach (DataColumn column in results.Tables[0].Columns)
{
    int rowIndex = 0;
    foreach (DataRow row in results.Tables[0].Rows)
    {
        HSSFRow dataRow = sheet1.CreateRow(rowIndex);
        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
        rowIndex++;
    }
    sheet1.AutoSizeColumn(column.Ordinal);
}

//Write the stream data of workbook to the file 'test.xls' in the temporary directory
FileStream file = new FileStream(Path.Combine(Path.GetTempPath(), "test.xls") , FileMode.Create);
spreadsheet.Write(file);
file.Close();