Cell borders missing when using PrintArea and OnePagePerSheet = true

Aspose.Cells Product Family
1
I am trying out the evaluation version of Aspose.Cells for .NET and I ran into an issue when using PageSetup.PrintArea of a worksheet to create an image with ImageOrPrintOptions.OnePagePerSheet = true. The right borders of the right most cells in the PrintArea are missing. If I make OnePagePerSheet = false, the borders are not missing, but I need it to be true because I want only the selected range to appear without any white space around it. I've attached the Excel file as well as the generated image.

The range I am using is B2:G28 of the first worksheet.

Thanks,
Nick
2

Dear Nick,

Thanks for posting your issue.

Please see this code and the generated image by it. I have run this code on Aspose.Cells for .NET 5.2.1.2.

C#

Workbook wb = new Workbook(@"C:\Downloads\STOCKS+VS.+IMPORTS+Sample.xlsx");<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Worksheet ws = wb.Worksheets.GetSheetByCodeName("Chart");

PageSetup pgSetup = ws.PageSetup;

pgSetup.PrintArea = "B2:G28";

pgSetup.LeftMargin = 0;

pgSetup.RightMargin = 0;

pgSetup.TopMargin = 0;

pgSetup.BottomMargin = 0;

ImageOrPrintOptions opt = new ImageOrPrintOptions();

opt.ImageFormat = ImageFormat.Png;

opt.OnePagePerSheet = true;

SheetRender sr = new SheetRender(ws, opt);

3

Hi,

It is strange, I noticed the right border is shown in the Windows Picture & Fax Viewer but not in Explorer.

4

Thanks for the reply!


This is pretty strange. I’m using Windows Vista and when I view the image in Windows Photo Gallery the border is not shown. Most importantly, when it is viewed in a browser (where my generated images will be viewed) the border is not shown. That is pretty much exactly the code I am using, too.

Do you think this is an issue with Aspose.Cells? If so, is fixing it a possibility?

Thanks a lot,
Nick
5

Dear Nick,

Please see this code, I have included the Hth column and adjusted the right margin. Now the image is perfectly ok.

Workbook wb = new Workbook(@"C:\Downloads\STOCKS+VS.+IMPORTS+Sample.xlsx");

Worksheet ws = wb.Worksheets.GetSheetByCodeName("Chart");

PageSetup pgSetup = ws.PageSetup;
pgSetup.PrintArea = "B2:H28";
pgSetup.LeftMargin = 0;
pgSetup.RightMargin = -1.75;
pgSetup.TopMargin = 0;
pgSetup.BottomMargin = 0;

ImageOrPrintOptions opt = new ImageOrPrintOptions();
opt.ImageFormat = ImageFormat.Png;
opt.OnePagePerSheet = true;

SheetRender sr = new SheetRender(ws, opt);

sr.ToImage(0, @"C:\Downloads\Image2.PNG");

6

That will fix the problem if you know you are working with this exact sheet, but unfortunately those are not my requirements. Users will upload an excel file and I display it to them in the browser with Aspose.Cells.Gridweb. They then can select a range of cells and submit the grid. On the server-side I can access the GridWeb.SelectCells ArrayList property to figure out the range they selected and then generate an image using that range as the PrintArea. I will have no control over what Excel files they upload, it needs to be as generic as possible.


I don’t think this workaround will work for me. Is there anything else you can suggest?

Thanks for the help,
Nick
7

Dear Nick,

I don’t think, this is a problem of Aspose.Cells, but still, I will consult with development team, what they say. To make code generic, I will try few more things then I will let you know.

8

Dear Nick,

This is a generic code, what I did was I set the width of max column width 0.1 before taking a print and then restored its original width. Please see the width of Hth column of all the attached excel files and then see the images generated by them.

C#

string fname = "STOCKS+VS.+IMPORTS+Sample.xlsx";

for (int i = 0; i < 3; i++)

{

Workbook wb = new Workbook(@"C:\Downloads\" + fname);

Worksheet ws = wb.Worksheets.GetSheetByCodeName("Chart");

//last column

int maxColumn = ws.Cells.MaxColumn;

//Set its width 0.1 before taking a print

double oldWidth = ws.Cells.Columns[maxColumn].Width;

ws.Cells.Columns[maxColumn].Width = 0.1;

//double rightMargin =

PageSetup pgSetup = ws.PageSetup;

pgSetup.PrintArea = "B2:H28";

pgSetup.LeftMargin = 0;

pgSetup.RightMargin = 0;

pgSetup.TopMargin = 0;

pgSetup.BottomMargin = 0;


ImageOrPrintOptions opt = new ImageOrPrintOptions();

opt.ImageFormat = ImageFormat.Png;

opt.OnePagePerSheet = true;

SheetRender sr = new SheetRender(ws, opt);

sr.ToImage(0, @"C:\Downloads\" + fname + ".png");

fname = "-" + fname;

//Restore the width

ws.Cells.Columns[maxColumn].Width = oldWidth;

}

9

Thanks for the example code. It wasn’t quite was reusable as I was looking for, but I came up with a solution. I essentially made it so, if I was given a range by the user (in this example “B2:G28”), I checked whether the last cell of the range was in the MaxColumn. If not, then I changed the width of the cell directly to the right of G28 (H28 in this case) to 0.1 and changed the range to “B2:H28”. This should hopefully work for all ranges, not just B2:G28. Here’s the code I got:


public Image RenderWorksheet(Worksheet worksheet, string cellRange = null, ImageOrPrintOptions imageOptions = null)
{
if (imageOptions == null)
{
imageOptions = new ImageOrPrintOptions();
imageOptions.ImageFormat = ImageFormat;
imageOptions.HorizontalResolution = resolution;
imageOptions.VerticalResolution = resolution;
imageOptions.OnePagePerSheet = true;
}

if (!String.IsNullOrEmpty(cellRange))
{
Cell rightCell = worksheet.Cells[cellRange.Split(’:’)[1]];
if (rightCell.Column < worksheet.Cells.MaxColumn) //Begin fix for displaying right border of right most cell
{
worksheet.Cells.Columns[rightCell.Column + 1].Width = 0.1;
Cell nextCell = worksheet.Cells[rightCell.Row, rightCell.Column + 1];
cellRange = String.Format("{0}:{1}", cellRange.Substring(0, cellRange.IndexOf(’:’)), nextCell.Name);
} //End fix
worksheet.PageSetup.PrintArea = cellRange;
}

//an extension method I created that sets all margins to 0
worksheet.ClearMargins();

SheetRender render = new SheetRender(worksheet, imageOptions);
return render.ToImage(0);
}

Thanks for all the help,
Nick
10

Hi Nick,

Congrats.

Both approaches are basically same in a sense that in both of codes, we are setting column width to some number say 0.1 before printing.

11

Hi Everyone,

Please refer to this thread for more robust and generic code to deal with the issues of cell borders missing.

Borders missing on right and bottom of image when using sheetRender.ToImage() method