AllColumnsInOnePagePerSheet together with paper width

[marco.wittwer]

Hi

Why the paper width will be ignored, if I set AllColumnsInOnePagePerSheet? I want to set the paper width and I want to stretch all columns, so that it fits the paper width. Can can I do this?

Thank you

Marco

[babar.raza]

Hi Marco,

Thank you for contacting Aspose support.

Please note, while using the AllColumnsInOnePagePerSheet option, the API will ignore the page settings and stretch the page width to render all columns of the worksheet on single page. If you wish to keep the page width intact, I would suggest you to use the PageSetup.FitToPagesWide property as demonstrated below. Please also check the detailed article on this subject.

C#

Workbook workbook = new Workbook(dir + “book1.xlsx”);
Worksheet sheet = workbook.Worksheets[0];
sheet.PageSetup.FitToPagesWide = 1;
workbook.Save(dir + “output.pdf”, new PdfSaveOptions());

[marco.wittwer]

Thats not what I want. With Book2.xlsx I would expect output2.pdf. Is this possible?

[babar.raza]

Hi Marco,

In order to achieve your desired results, you have to increase the column widths as per specified page size and then render the spreadsheet to PDF. Please check the following piece of code for demonstration purposes and amend it to suit your application requirements.

C#

Workbook book = new Workbook(dir + “Book2.xlsx”);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
PageSetup pageSetup = sheet.PageSetup;
double paperWidthInch = GetPaperWidthInch(pageSetup.PaperSize, pageSetup.Orientation);

int indexA = CellsHelper.ColumnNameToIndex(“A”);
int indexB = CellsHelper.ColumnNameToIndex(“B”);
int indexC = CellsHelper.ColumnNameToIndex(“C”);

double columnAWidthInch = cells.GetColumnWidthInch(indexA);
double columnBWidthInch = cells.GetColumnWidthInch(indexB);
double columnCWidthInch = cells.GetColumnWidthInch(indexC);

double scale = paperWidthInch / (columnAWidthInch + columnBWidthInch + columnCWidthInch);

cells.SetColumnWidthInch(indexA, columnAWidthInch * scale);
cells.SetColumnWidthInch(indexB, columnAWidthInch * scale);
cells.SetColumnWidthInch(indexC, columnAWidthInch * scale);

pageSetup.FitToPagesWide = 1;
pageSetup.FitToPagesTall = 0;

book.Save(dir + “output.pdf”);

Here is the GetPaperWidthInch method for your reference.

private static double GetPaperWidthInch(PaperSizeType type, PageOrientationType orientation)

{
//in inch
double width, height;
//Assume paper A4 is used, you should update some other paper size you used.
switch (type)
{
//paper A4 is 210mm X 297mm
case PaperSizeType.PaperA4:
width = 210 / 25.4;
height = 297 / 25.4;
break;
//A4
default:
width = 210 / 25.4;
height = 297 / 25.4;
break;
}
if (orientation == PageOrientationType.Landscape)
{
return height;
}
return width;
}