Hi,
I’m using Asponse.Excel .NET component. Using this component I had to generated some excel reports. The problem that I encountered relates to the PageSetup Excel class. There are properties in this class: LeftMargin, RightMargin, TopMargin, BottomMargin, FooterMargin, HeaderMargin. Using these properties I’m trying to set programmatically their values. Here is the code that does it:
sheet.PageSetup.LeftMargin = 0.25D;
sheet.PageSetup.TopMargin = 0.25D;
sheet.PageSetup.RightMargin = 0.25D;
sheet.PageSetup.BottomMargin = 0.5D;
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 0;
sheet.PageSetup.FooterMargin = 0.25D;
sheet.PageSetup.HeaderMargin = 0.25D;
When my application finishes running I have excel files, but the page setup doesn’t reflect my values for the margins. Maybe this happens because I’m using FitToPagesWide and FitToPagesTall properties of the same class. My Aspose.Excel.dll Assembly version is: 1.8.8.1
Could you please explain why this happen?
Thanks
Hi carbon,
I used the following code to test this issue.
Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];
sheet.PageSetup.LeftMargin = 0.25D;
sheet.PageSetup.TopMargin = 0.25D;
sheet.PageSetup.RightMargin = 0.25D;
sheet.PageSetup.BottomMargin = 0.5D;
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 0;
sheet.PageSetup.FooterMargin = 0.25D;
sheet.PageSetup.HeaderMargin = 0.25D;
excel.Save(“abc.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);
It worked fine.
Do you use a designer file? And could you post more code? If possible, could you send me your project?
Hi, I sent the result excel file and my project to excel@aspose.com , please check it out
Carbon
Hi Carbon,
In your file, margins of top, left, right, header, footer are all 0.2 and bottom margin is 0.5, right?
If you mannually set margin to 0.25 in MS Excel, margin will be changed to 0.3 automatically. So you can set margins to 0.3 directly.
Hi Laurence,
I think I got it. The properties for margins in excel uses doubles. I played with different double values and finally I achieved the settings that I need, in my case if I need to have 0.25 inches for a margin, the double representation in double should be 0.635D.
Maybe in future release you might want to have something similar to MS Excel ActiveX component where there is method InchesToPoints(double d) in Application class.
Thanks for your assistance.
Hi Carbon,
Thanks for your advice. I will think of this issue.
I think I have found what the problem is. I did a simple test. I put 1 in all the margins, and when I opened my workbook, all the values got changed to:0.3937. I then divided 1 by this the result to see what the ratio was, and got: 2.54. Which is the Inch to Centimeter ratio. Which leads me to beleive that Aspose.Excel is somehow storeing the value entered in centimeters, and when we open the file in our local config, Excel is translating the value to inches by dividing by 2.54.
The margin unit is centimeter. When you install Aspose.Excel, you can find correct api description in the chm. However, in our web site, those descriptions are not updated.
I will fix it. Thank you for the report.
@EricP,
Aspose.Excel is discontinued now and is replaced by Aspose.Cells that is quite advanced and rich in features as compared to its predecessor. Aspose.Cells provides enhanced features to set different type of margins in Excel workbooks as demonstrated in the following code snippets:
Page Margins
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create a workbook object
Workbook workbook = new Workbook();
// Get the worksheets in the workbook
WorksheetCollection worksheets = workbook.Worksheets;
// Get the first (default) worksheet
Worksheet worksheet = worksheets[0];
// Get the pagesetup object
PageSetup pageSetup = worksheet.PageSetup;
// Set bottom,left,right and top page margins
pageSetup.BottomMargin = 2;
pageSetup.LeftMargin = 1;
pageSetup.RightMargin = 1;
pageSetup.TopMargin = 3;
// Save the Workbook.
workbook.Save("SetMargins_out.xls");
Output Page Margins
Notice that margins are set in centimeters in the sample code.
Center on Page
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create a workbook object
Workbook workbook = new Workbook();
// Get the worksheets in the workbook
WorksheetCollection worksheets = workbook.Worksheets;
// Get the first (default) worksheet
Worksheet worksheet = worksheets[0];
// Get the pagesetup object
PageSetup pageSetup = worksheet.PageSetup;
// Specify Center on page Horizontally and Vertically
pageSetup.CenterHorizontally = true;
pageSetup.CenterVertically = true;
// Save the Workbook.
workbook.Save("CenterOnPage_out.xls");
Output Center on Page
Note the top, bottom, left and right margins.
Header Footer Margins
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create a workbook object
Workbook workbook = new Workbook();
// Get the worksheets in the workbook
WorksheetCollection worksheets = workbook.Worksheets;
// Get the first (default) worksheet
Worksheet worksheet = worksheets[0];
// Get the pagesetup object
PageSetup pageSetup = worksheet.PageSetup;
// Specify Header / Footer margins
pageSetup.HeaderMargin = 2;
pageSetup.FooterMargin = 2;
// Save the Workbook.
workbook.Save("HeaderFooterMargins.xls");
Output Header Footer Margins
Note that the header footer margin is set to 2cm in code and shown as 20mm in the property dialog.
For more information on setting margins, refer to the link below:
Setting Margins
Here is a link to the free latest trial version of Aspose.Cells:
Aspose.Cells for .NET(Latest version)
A detailed runnable solution containing lot of ready to run examples is available here.