[SOLVED] Problem with page margins. lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- melwinek · 2015-04-29 09:23 I have a big problem with margins. Fpspreadsheet does not support the page margins? I open the file (MyWorkbook.ReadFromFile) with margins on each side 1cm. I change the contents of the file. I save a file (MyWorkbook.WriteToFile). I get the margins: 2.5 cm top and bottom 1.9 cm left and right. Why fpspreadsheet change the margins? ---------------------------------------------------------------------- wp · 2015-04-29 09:38 So far, fpspreadsheet does not support page margins at all, and I am not sure if it ever will. fpspreadsheet is not intended to be a replacement of the original spreadsheet applications. If it is important for you to have everything in place do not use fpspreadsheet. There are many features that are not supported by fpspreadsheet (macros, all the printing stuff, security, gradients, conditional formatting, embedded images, OLE, ...). Therefore, you will inevitably see loss of data, formatting, macros etc. if you load an original spreadsheetfile into fpspreadsheet, change something and save it back. ---------------------------------------------------------------------- melwinek · 2015-04-29 10:45 Well, it is generally not good. I need something that can be a generator of reports and allow user editing of the generated file. Lazreport is not good because you can export a PDF. But the PDF edition is difficult. ---------------------------------------------------------------------- rvk · 2015-04-29 12:17 If it's just the margins you want you could quite easily edit the .xlsx file (from fpspreadsheet) and add these margins. Unzip (in code) the filename.xlsx. Goto the subdirectory xl\worksheets directory and open up sheet1.xml. All the way at the end you have . In between these tags you can fill in your pageMargins: Those 0.3937 are inches so 0.3937 is 1cm. Save the file again to filename.zip and rename it to filename.xlsx and you have your desired margins. This could all be done with a simple procedure-call. (this is of course only if your using .xlsx files ) ---------------------------------------------------------------------- felipemdc · 2015-04-29 13:17 A patch that adds margins support would be welcome. Its probably not hard. I could do it, but I really have absolutely no free time. ---------------------------------------------------------------------- wp · 2015-04-29 13:36 ok, I'll add it, but I know there's more to come: header, footer, logos, page breaks, ... ---------------------------------------------------------------------- rvk · 2015-04-29 13:40 Yeah... one thing leads to another You're never done until fpspreadsheet can be used as back-end for a full-blown LibreOffice clone (and even then....) ---------------------------------------------------------------------- wp · 2015-04-29 20:45 Added a PageLayout record to fpspreadsheet (subject to change) and implemented reading and writing of page margins for xlsx. Other parameters, however, will be more difficult to deal with because the "pageSetup" node has a relationship to an embedded binary file "printerSettings.bin". Does anybody know how ods handles page margins? There are two page- layout styles in the files written by Open/LibreOffice named "Mpm1" and "Mpm2", but I don't know what they are for. Who said that xml files are self-documenting? ---------------------------------------------------------------------- rvk · 2015-04-29 21:25 mpm1 is for Default page. mpm2 is for Report. Look for Mpm1 en Mpm2 in the styles.xml:

Changing the margins only changes the following line with page-layout- properties:

Not sure if the Mpm2 is really needed in the file. (probably not) I did find some documentation (see below). So for example about the Master-pages you can look at this: http://books.evc-cit.info/odbook/ch03.html#page-master-section Sources: OpenOffice.org XML File Format 1.0 http://xml.openoffice.org/xml_specification.pdf OpenOffice.org XML File Format 1.2 http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2.html http://www.schemacentral.com/sc/odf/ss.html OASIS OpenDocument Essentials (J. David Eisenberg) http://books.evc-cit.info/odbook/book.html (edit: changed the v1.1 link of oasis to v1.2) ---------------------------------------------------------------------- melwinek · 2015-04-30 12:39 Is it possible to implement insert pictures? ---------------------------------------------------------------------- wp · 2015-04-30 14:37 This will be tough because I saw when doing the comments that there is a lot of poorly documented stuff in the Excel file formats regarding embedded images, maybe ods is easier but this format has its shortcomings as well. Could you list the file features that you need to be preserved for your application? Which spreadsheet format are you using? Have a look at the wiki (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Current_Progress) to see what is already there. PageLayout is not yet listed, but will soon be available, at least to some degree. ---------------------------------------------------------------------- wp · 2015-04-30 21:52 @rvk: Thanks for the infos. But I see now that it is more complicated: In the "contents.xml", there is a "style:style" of "style:family=table" which refers to a "style:master-page" and overrides some properties of this master-page. The master-page style itself is found in "styles.xml", however, it only turns on/off some features. It, in turn, refers again to another node in the "styles.xml", the "page-layout", which is one of the "Mpm*" nodes. ---------------------------------------------------------------------- wp · 2015-05-13 11:00 Finally completed the PageLayout for TsWorksheet. It allows to define print margins, header, footers, etc. See the wiki for details. Adding pictures to the header/footer, however, is not supported. @melwinek: Is that sufficient for your requiremens? ---------------------------------------------------------------------- melwinek · 2015-05-14 07:31 Pagelayout satisfies me completely. ---------------------------------------------------------------------- Edson · 2016-02-24 03:20 Hi. I would like to know: How "PageLayout.FitWidthToPages" property works. I have using several values, but all I obtain is "Fit in one page". I want to have "Fit all the columns in one page". I'm using FPSpreadsheet 1.6.0 with the format "sfOOXML". ---------------------------------------------------------------------- wp · 2016-02-24 13:08 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 · 2016-02-24 14:09 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i w := TsWorkbook.Create; h := w.AddWorksheet('PRESUPUESTO'); ... h.PageLayout.FitWidthToPages:=1; //Only works for sfOOXML h.PageLayout.LeftMargin:=14; h.PageLayout.RightMargin:=15; But, for all value I use for FitWidthToPages (<>0), I always obtain "Fit Sheet on one page." ---------------------------------------------------------------------- wp · 2016-02-24 14:21 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 · 2016-02-24 15:43 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 · 2016-02-24 16:12 I have discovered some differences in the files: xl\worsheets\sheet1.xml Here the comparison of the relevant lines: Code: Pascal [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Excel - No Scale============== 0 Excel - Fit One Page==============0 Excel - Fit Columns One Page====================0 Lazarus FitWidthToPages=1====================0 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 · 2016-02-24 16:22 Looking at the sourcecode of the xlsx writer, procedure "WritePageLayout", you'll see this: Code: Pascal [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i if poFitPages in AWorksheet.PageLayout.Options then begin // Fit width to pages s := Format('%s fitToWidth="%d"', [s, AWorksheet.PageLayout.FitWidthToPages]); // Fit height to pages s := Format('%s fitToHeight="%d"', [s, AWorksheet.PageLayout.FitHeightToPages]); end else // Scaling factor s := Format('%s scale="%d"', [s, AWorksheet.PageLayout.ScalingFactor]); 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 · 2016-02-24 16:43 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 · 2016-02-24 16:56 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages]; MyWorksheet.PageLayout.FitWidthToPages := 1; // all columns on one page width MyWorksheet.PageLayout.FitHeightToPages := 0; // use as many pages as needed ---------------------------------------------------------------------- Edson · 2016-02-24 19:24 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 · 2016-02-25 16:53 Quote from: Edson on February 24, 2016, 08:24:34 pmIt 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 · 2016-02-25 19:16 Excellent. ---------------------------------------------------------------------- wp · 2016-03-04 23:08 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.