PrintArea Issue

Aspose.Cells Product Family
1

Hi,


Rendering part of excel file gives incorrect result for attached file. For print area “A1:D18”, the result image is cropped and has much left margin. When I set area “B1:D18” result image is empty (white).

My code is as follows :

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(@“C:\TEMP\test4\t1.xls”);

Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
imgOptions.HorizontalResolution = 96;
imgOptions.VerticalResolution = 96;
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
imgOptions.OnePagePerSheet = true;
imgOptions.AllColumnsInOnePagePerSheet = true;
Aspose.Cells.Worksheet sheet = wb.Worksheets[1];
sheet.PageSetup.PrintArea = “A1:D18”;

Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);

System.Drawing.Bitmap bitmap = sr.ToImage(0);
bitmap.Save(@“C:\TEMP\test4\sonuc6.png”, System.Drawing.Imaging.ImageFormat.Png);

Thank you,
2

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells
for .NET v8.5.0.4 with the following code and it will fix your issue.

I have attached the output png image generated by the code for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\t1.xls”;


//Create workbook from source file.

Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[1];


//Set the print area with your desired range

worksheet.PageSetup.PrintArea = “A1:D18”;


//Set all margins as 0

worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;

worksheet.PageSetup.TopMargin = 0;

worksheet.PageSetup.BottomMargin = 0;


//Set OnePagePerSheet option as true

ImageOrPrintOptions options = new ImageOrPrintOptions();

options.OnePagePerSheet = true;

options.ImageFormat = ImageFormat.Png;

options.OnlyArea = true;


//Take the image of your worksheet

SheetRender sr = new SheetRender(worksheet, options);

sr.ToImage(0, filePath + “.out.png”);

1 Like
3

Hi,


Thank you. You’ve solved my case.

I’ve another one. When I set print area “A56:G96” result image shows only lines till 93.
I guess it is because the first line is set to be repeated each page and image height is calculated by ignoring this setting.

Thank you again,
4

Hi,


Well, yes, as you have specified some rows to be repeated at the top for the worksheet, so it may affect the rendering image a bit. I think you may try to set FitToPages Tall and Wide options before rendering your desired range of cells (area) to image file format, see the updated sample code that works fine as I tested:
e.g
Sample code:

string filePath = @“e:\test2\t1.xls”;


//Create workbook from source file.

Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[1];


//Set the print area with your desired range

worksheet.PageSetup.PrintArea = “A56:G96”;

worksheet.PageSetup.FitToPagesTall = 1;
worksheet.PageSetup.FitToPagesWide = 1;

//Set all margins as 0

worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;

worksheet.PageSetup.TopMargin = 0;

worksheet.PageSetup.BottomMargin = 0;


//Set OnePagePerSheet option as true

ImageOrPrintOptions options = new ImageOrPrintOptions();

options.ImageFormat = ImageFormat.Png;

//Take the image of your worksheet

SheetRender sr = new SheetRender(worksheet, options);

sr.ToImage(0, filePath + “.out1.png”);


Hope, this helps a bit.

Thank you.
1 Like
5

Thank you, you helped much.


Just for information; if I set OnePagePerSheet = true, above method don’t have any effect.

Kind regards,
6

Hi,


Well, when you specify FitToPages Tall/Wide option(s), it will work somewhat similar to OnePagePerSheet option. I think for better results, you should remove the line involving OnePagePerSheet option and retain/ add the line as following, it will remove the unnecessary blank left/right margins and render image for the area only:
e.g
Sample code:
…
options.OnlyArea = true;

Thank you.
7

Hi,


I’ve tried and it worked.

Thank you,
8

Hi Huseyin,


It is good to know that the presented solution helped in your scenario. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
9

Hi,


Is there a way to prevent repeted rows/columns when exporting a part (area) of the worksheet as image?

Thanks,
10

Hi,


Please add the following lines of code to your code segment:
e.g
Sample code:

…
//Set the print area with your desired range
worksheet.PageSetup.PrintArea = “A56:G96”;
//Set the repeated rows/columns off
worksheet.PageSetup.PrintTitleColumns = “”;
worksheet.PageSetup.PrintTitleRows = “”;
…


Thank you.
11

Hi,


Thank you for quick response.

I tried and it worked.

Best regards,
12

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved by setting the repeated rows and columns off. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

13

Hi,


When I extract image of part of sheet by setting print area, the content in merged cells is not included.
For attached file there is a text on B1 and B1:O1 are merged. I need to get related part of the text (cropped), when I set print area as G1:L20.
Is there a way to achieve this goal?

Thanks
14

Hi,


Thanks for the template file and some details.

After an initial test, I observed the issue as you mentioned. I found the content (Title) in merged cells (at the top) excluded in the rendered image for printable area. It should render the top cropped title (as per print preview after setting the Printable area to “G1:L20” in MS Excel). I used the following sample code with your newly attached template file.
e.g
Sample code:

string filePath = @“e:\test2\01±+T9+ilerleme+miktarlar-+(2).xls”;


//Create workbook from source file.

Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Set the print area with your desired range

worksheet.PageSetup.PrintArea = “G1:L20”;



//Set all margins as 0

worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;

worksheet.PageSetup.TopMargin = 0;

worksheet.PageSetup.BottomMargin = 0;


ImageOrPrintOptions options = new ImageOrPrintOptions();

// options.OnePagePerSheet = true;

options.ImageFormat = ImageFormat.Png;

options.OnlyArea = true;


//Take the image of your worksheet

SheetRender sr = new SheetRender(worksheet, options);

sr.ToImage(0, filePath + “.out1.png”);

I have logged a ticket with an id “CELLSNET-43787” for your issue. We will look into it to figure it out soon.

Thank you.
15

Hi,


We have evaluated your issue in details. Well, if the top left cell of the merged cells is out of
the print area, I am afraid, the merged cells will not be rendered in the output file. It is actually related to complex
layout problem and I am afraid, we cannot fix it for now or any time soon. If we could figure it out in future, we
will let you know here.

We are sorry for any inconvenience caused!