[SOLVED] Problem with page margins.

[wp]

FPSpreadsheet does not do anything with the FitToXXXX values - they are just passed on to Excel and appears in the "PageSetup" dialog, sheet "Paper format", box "Scaling", edit controls "Pages wide" and "Pages high" (these labels are translated from the German Excel version and may vary in the international version). I understand FitWidthToPages = 1 and FitHeightToPages = 1 such that the entire spreadsheet is scaled down to fit on 1 single printed page. FitWidthToPages = 2 and FitHeightToPages = 1 would scale the spreadsheet such that it fits on a virtual sheet which is 2 real pages wide and 1 page high.

I don't see Excel settings such as "fit on one page" or "fit all columns in one page". In my understanding, shouldn't they be the same?

If this does not answer your question please provide a sample data file, source code how you created the file and screenshots of what you want to achieve.

[Edson]

Well, my Excel works in spanish language. This is what I want to achieve:

https://www.youtube.com/watch?v=3acCOfTd4wE

My code is something like this:

Code: Pascal  [Select][+][-]

1.  
2.   w := TsWorkbook.Create;
3.   h := w.AddWorksheet('PRESUPUESTO');
4.  
5. ...
6.  
7.   h.PageLayout.FitWidthToPages:=1;   //Only works for sfOOXML
8.   h.PageLayout.LeftMargin:=14;
9.   h.PageLayout.RightMargin:=15;
10.  

But, for all value I use for FitWidthToPages (<>0), I always obtain "Fit Sheet on one page." 

[wp]

My problem is that I only have Excel2007, and you seem to require a feature of Excel2010+.

Create a simple file, and edit it in Excel such that it has FitWidthToPages correctly set just as you like it. Then do the same with FPSpreadsheet. Post both files here (packed into a zip because the forum software does not like xlsx). Maybe I can learn from the comparison of the xml content what's causing the difference.

[Edson]

I have created these sample files, using Excel and Lazarus. I hope they can be useful.  I have seen some differences but I'm don't know about Excel formats.

I really appreciate your help.

[Edson]

I have discovered some differences in the files: xl\worsheets\sheet1.xml

Here the comparison of the relevant lines:

Code: Pascal  [Select][+][-]

1. Excel - No Scale
2. ==============
3.  
4. <dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0">
5. <sheetData><row r="1" spans="1:1" x14ac:dyDescent="0.25"><c r="A1" t="s"><v>0</v></c></row></sheetData>
6. <pageSetup paperSize="9"                 orientation="portrait" horizontalDpi="0" verticalDpi="0" r:id="rId1"/>
7.  
8. Excel - Fit One Page
9. ==============
10. <sheetPr><pageSetUpPr fitToPage="1"/></sheetPr>
11. <dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0">
12. <sheetData><row r="1" spans="1:1" x14ac:dyDescent="0.25"><c r="A1" t="s"><v>0</v></c></row></sheetData>
13. <pageSetup paperSize="9"                 orientation="portrait" horizontalDpi="0" verticalDpi="0" r:id="rId1"/>
14.  
15. Excel - Fit Columns One Page
16. ====================
17. <sheetPr><pageSetUpPr fitToPage="1"/></sheetPr>
18. <dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0">
19. <sheetData><row r="1" spans="1:1" x14ac:dyDescent="0.25"><c r="A1" t="s"><v>0</v></c></row></sheetData>
20. <pageSetup paperSize="9" fitToHeight="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" r:id="rId1"/>
21.  
22. Lazarus FitWidthToPages=1
23. ====================
24. <sheetPr><pageSetUpPr fitToPage="1" /></sheetPr>
25. <dimension ref="A1" /><sheetViews><sheetView workbookViewId="0"   /></sheetViews>
26. <sheetData><row r="1" spans="1:1"><c r="A1" s="0" t="s"><v>0</v></c></row></sheetData>
27. <pageSetup paperSize="9"                 orientation="portrait" firstPageNumber="1" scale="100" />
28.  

When setting in Excel " Fit Columns One Page", Excel sets the attributes  fitToPage="1" and fitToHeight="0"
Lazarus only sets: fitToPage="1"

Is there some way to control "fitToHeight"?

[wp]

Looking at the sourcecode of the xlsx writer, procedure "WritePageLayout", you'll see this:

Code: Pascal  [Select][+][-]

1.   if poFitPages in AWorksheet.PageLayout.Options then
2.   begin
3.     // Fit width to pages
4.     s := Format('%s fitToWidth="%d"', [s, AWorksheet.PageLayout.FitWidthToPages]);
5.     // Fit height to pages
6.     s := Format('%s fitToHeight="%d"', [s, AWorksheet.PageLayout.FitHeightToPages]);
7.   end else
8.     // Scaling factor
9.     s := Format('%s scale="%d"', [s, AWorksheet.PageLayout.ScalingFactor]);
10.  

You see here that the FitWidthToPages and FitHeightToPages are only prepared for writing if the flag "poFitPages" has been set in PageLayout.Options. Did you do this? Your source code above does not show it.

I see in your files that sometimes the FitXXXXToPages are missing in the xlsx files written by Excel. Probably it is necessary to define a default value for these parameters (it is 0 now, but maybe -1 is better) to prevent writing of these values to the file.

But could you test the poFitPages option first, please.

[Edson]

Good. Setting poFitPages, make it works.

Maybe this may be remarked in the Wiki. Something like "Warning: If you are thinking of using Fit pages options, set the flag poFitPages."


Thanks.

[wp]

I added a paragraph on page scaling to the wiki article. Could you verify that this is the correct code for "Fit all columns on one page":

Code: Pascal  [Select][+][-]

1.   MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];  
2.   MyWorksheet.PageLayout.FitWidthToPages := 1;     // all columns on one page width
3.   MyWorksheet.PageLayout.FitHeightToPages := 0;    // use as many pages as needed

[Edson]

Correct. That's the code.

I must say the Wiki is very complete. I would like all the libraries was so good documented as FPSpreadsheet is. 

It would be appreciated is some information about resizing rows and columns was added.

[wp]

It would be appreciated is some information about resizing rows and columns was added.

Have a look at the new chapters 2.2.4 and 2.8 in the wiki article.

[Edson]

Excellent.

[wp]

In preparation for images in headers and footers, I rearranged the PageLayout code. It was moved to a separate unit (i.e. you may need to add fpsPageLayout to "uses"), and the TsPageLayout record is a class now which simplifies a few things:

• It is no longer required to add the poFitPages option if a value for FitWidthToPages or FitHeightToPages is set.
• Conversely, it is no longer required to remove the poFitPages option if a constant scaling factor is to be used for printing.
• PrintRanges and RepeatedCols/Rows now belong to the PageLayout (no longer to the Worksheet), i.e. you must call "Worksheet.Pagelayout.SetRepeatedCols()" (instead of "Worksheet.SetRepeatedPrintCols").

The wiki will be updated soon.