After converting excel to pdf- table is moving the left side

Aspose.Cells Product Family
1
When we are converting excel to pdf, we are using the following code to set print area.

public static void SetPrintArea(Workbook workbook, int maxCol, int maxRow)
{
foreach (Worksheet sheet in workbook.Worksheets)
{
maxCol = sheet.Cells.MaxColumn;
maxRow = sheet.Cells.MaxRow;
if (sheet.Cells.MaxDataColumn - 1 > sheet.Cells.MaxColumn)
maxCol = sheet.Cells.MaxDataColumn - 1;
if (sheet.Cells.MaxDataRow - 1 > sheet.Cells.MaxRow)
maxRow = sheet.Cells.MaxDataRow - 1;
if (sheet.Cells.MaxDisplayRange.ColumnCount - 1 > maxCol)
maxCol = sheet.Cells.MaxDisplayRange.ColumnCount - 1;
if (sheet.Cells.MaxDisplayRange.RowCount - 1 > maxRow)
maxRow = sheet.Cells.MaxDisplayRange.RowCount - 1;
if (maxCol > 0 && maxRow > 0)
{
Aspose.Cells.PageSetup pageSetup = sheet.PageSetup;
pageSetup.PrintArea = "A1:" + CellsHelper.CellIndexToName(maxRow, maxCol);
}

}
}
But we have a problem now. I attached original file and screenshot for you.
When we are convert attached excel file to pdf, table is moving the left side.
Could you please check it ?
Before, we asked you similar issue. You can find to links at below.

<a href="https://forum.aspose.com/t/108260</a></div><div><br></div><div><br></div>
2

Hi Ahmet,


Thank you for sharing the code & sample.

I have evaluated the presented scenario and found the problem cause as to be the value of MaxDisplayRange. Please note, your spreadsheet originally has the Print Area set to A1:R634 whereas after running the above code, the Print Area is set to A1:AM634. Due to the same reason, it seems that the page has been moved to left because more columns have been added to the right. I believe some cell(s) in column AM has been initialized by setting formatting. I will further investigate the matter, and try to devise a logic that could exclude such columns from the Print Area.
3
Hi again,

This is to update you that we are able to identify the problem cause. Please note, there are several hidden comments in the provided spreadsheet, whereas one of the comments (Comment 44) span till column AM due to which the MaxDisplayRange calculates as A1:AM634. You can fix this problem dynamically by checking the Shape.LowerRightColumn property. If the returned column index exceeds the existing PrintArea then you can move the shape so that it does not exceed the PrintArea. Please check the following piece of code and its resultant PDF.

Please note, the code has been provided for demonstration purposes only therefore you should amend/improve it according to the application requirements.

C#

var book = new Workbook(dir + "ÇZ-BS-0010_6.xlsx");
Worksheet sheet = book.Worksheets[0];
var printArea = sheet.PageSetup.PrintArea.Split(':');
var numAlpha = new Regex("(?[a-zA-Z]*)(?[0-9]*)");
var match = numAlpha.Match(printArea[1]);

var alpha = match.Groups["Alpha"].Value;
var num = match.Groups["Numeric"].Value;
var currentMaxColumn = CellsHelper.ColumnNameToIndex(alpha);

foreach (Shape shape in sheet.Shapes)
{
Console.WriteLine(shape.LowerRightColumn);
if (shape.LowerRightColumn > currentMaxColumn)
{
shape.MoveToRange(shape.UpperLeftRow, shape.UpperLeftColumn, shape.LowerRightRow, currentMaxColumn);
}
}
maxCol = sheet.Cells.MaxColumn;
maxRow = sheet.Cells.MaxRow;
if (sheet.Cells.MaxDataColumn - 1 > sheet.Cells.MaxColumn)
maxCol = sheet.Cells.MaxDataColumn - 1;
if (sheet.Cells.MaxDataRow - 1 > sheet.Cells.MaxRow)
maxRow = sheet.Cells.MaxDataRow - 1;
if (sheet.Cells.MaxDisplayRange.ColumnCount - 1 > maxCol)
maxCol = sheet.Cells.MaxDisplayRange.ColumnCount - 1;
if (sheet.Cells.MaxDisplayRange.RowCount - 1 > maxRow)
maxRow = sheet.Cells.MaxDisplayRange.RowCount - 1;
if (maxCol > 0 && maxRow > 0)
{
Aspose.Cells.PageSetup pageSetup = sheet.PageSetup;
pageSetup.PrintArea = "A1:" + CellsHelper.CellIndexToName(maxRow, maxCol);
}
book.Save(dir + "output.pdf");

By the way, if you wish to render only the display area, you can simply clear the PageSetup.PrintArea property and let the API calculate the print area internally.
4

Hi,


Thanks for using Aspose.Cells.

As Babar has also told you in the previous post, if you just need to render the display content to PDF, you can just clear the PrintArea using the line of code:
e.g
Sample code:

…
sheet.PageSetup.PrintArea = “”;

Now Aspose.Cells will calculate the internal area while rendering to PDF for your requirements.

Thank you.
5
Thank you for answer. But It couldn't help us. Because we don't know
workbooks of our customers, so we calculate print area.
Before you suggested several code for similar issue and
we wrote SetPrintArea method. When we use SetPrintArea(), some documents moved left side.

6

Hi Ahmet,


Thank you for writing back.

As discussed earlier, you can either let the Aspose.Cells APIs to decide what area should be printed or you have to enhance your SetPrintArea method to handle situations where objects are hidden in the spreadsheet and may exceed the expected print area. I have already provided the code snippet to detect and move such objects in one of my previous posts. I hope you have given it a try on your side.

7

Hello again,

I have tried the sample code that you shared. But, sadly it couldn’t estimate printed area. In the code part below sheet.PageSetup.PrintArea value is set null in runtime.

“var printArea = sheet.PageSetup.PrintArea.Split(’:’);”

As a result of that I couldn’t try that sample.Can you please tell
me why that returns null value.
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>
8

Hi Ahmet,


First of all, the code shared here is based on the first worksheet of your provided sample only. You need to amend it to be used in general. Secondly, it is quite possible that a worksheet does not have the PageSetup.PrintArea property set. If you get null that means the said property is not set. It is best that you either leave it null or assign an empty string to it so that the Aspose.Cells APIs could internally calculate the optimal print area for that particular worksheet.

Please try following snippet.

C#

var book = new Workbook(dir + “ÇZ-BS-0010_6.xlsx”);
foreach (Worksheet sheet in book.Worksheets)
{
if(sheet.PageSetup.PrintArea != null && sheet.PageSetup.PrintArea != “”)
{
var printArea = sheet.PageSetup.PrintArea.Split(‘:’);
var numAlpha = new Regex(“(?[a-zA-Z])(?[0-9])”);
var match = numAlpha.Match(printArea[1]);

var alpha = match.Groups[“Alpha”].Value;
var num = match.Groups[“Numeric”].Value;
var currentMaxColumn = CellsHelper.ColumnNameToIndex(alpha);

foreach (Shape shape in sheet.Shapes)
{
Console.WriteLine(shape.LowerRightColumn);
if (shape.LowerRightColumn > currentMaxColumn)
{
shape.MoveToRange(shape.UpperLeftRow, shape.UpperLeftColumn, shape.LowerRightRow, currentMaxColumn);
}
}
maxCol = sheet.Cells.MaxColumn;
maxRow = sheet.Cells.MaxRow;
if (sheet.Cells.MaxDataColumn - 1 > sheet.Cells.MaxColumn)
maxCol = sheet.Cells.MaxDataColumn - 1;
if (sheet.Cells.MaxDataRow - 1 > sheet.Cells.MaxRow)
maxRow = sheet.Cells.MaxDataRow - 1;
if (sheet.Cells.MaxDisplayRange.ColumnCount - 1 > maxCol)
maxCol = sheet.Cells.MaxDisplayRange.ColumnCount - 1;
if (sheet.Cells.MaxDisplayRange.RowCount - 1 > maxRow)
maxRow = sheet.Cells.MaxDisplayRange.RowCount - 1;
if (maxCol > 0 && maxRow > 0)
{
Aspose.Cells.PageSetup pageSetup = sheet.PageSetup;
pageSetup.PrintArea = “A1:” + CellsHelper.CellIndexToName(maxRow, maxCol);
}
}
else
{
sheet.PageSetup.PrintArea = “”;
}
}
book.Save(dir + “output.pdf”);

9
Hi,
Are you try this sample with our workbook the one with .xlsx extension(original format for this document) ?
When I try it with .xlsx extension, sheet.PageSetup.PrintArea returns null.
But when I change workbook extension from ".xlsx" to ".xls" and I try it with ".xls" extension,
sheet.PageSetup.PrintArea returns "A1:R634".
10

Hi Ahmet,


I have tested your provided sample spreadsheet as it is, without any modifications. The first worksheet “ÇZ-BS-0010” has the print area set whereas other worksheets in the same spreadsheet has the print area set to null. More importantly, I have been testing against the latest revisions of the API, that is; 8.8.2.4 at the moment. If you are getting the null for a valid print area, it could be due to some bug in your current version of the API. Please give the latest version a try on your side and feed us back. Thank you.

11

Hi again,


You may also try the following alternative solution, that does not require to retrieve the PrintArea of the worksheet. Moreover, this solution is based on the Aspose.Cells internal mechanism to calculate the MaxDisplayRange with some modifications to suit your needs.

C#

var book = new Workbook(dir + “ÇZ-BS-0010_6.xlsx”);
foreach (Worksheet sheet in book.Worksheets)
{
var maxDisplayRange = GetMaxDisplayRange(sheet);
maxCol = sheet.Cells.MaxColumn;
maxRow = sheet.Cells.MaxRow;
if (sheet.Cells.MaxDataColumn - 1 > sheet.Cells.MaxColumn)
maxCol = sheet.Cells.MaxDataColumn - 1;
if (sheet.Cells.MaxDataRow - 1 > sheet.Cells.MaxRow)
maxRow = sheet.Cells.MaxDataRow - 1;
if (maxDisplayRange.ColumnCount - 1 > maxCol)
maxCol = maxDisplayRange.ColumnCount - 1;
if (maxDisplayRange.RowCount - 1 > maxRow)
maxRow = maxDisplayRange.RowCount - 1;
if (maxCol > 0 && maxRow > 0)
{
Aspose.Cells.PageSetup pageSetup = sheet.PageSetup;
pageSetup.PrintArea = “A1:” + CellsHelper.CellIndexToName(maxRow, maxCol);
Console.WriteLine(pageSetup.PrintArea);
}
}
book.Save(dir + “output.pdf”);

internal const int MaxRow = 0x100000 - 1;//0xFFFFF
internal const int MaxColumn = 16383;//0x3FFF
public static Aspose.Cells.Range GetMaxDisplayRange(Worksheet sheet)
{
Cells cells = sheet.Cells;
int maxRow = cells.MaxRow;
int maxColumn = cells.MaxColumn;
ArrayList mergeAreaList = cells.MergedCells;
for (int i = 0; i < mergeAreaList.Count; i++)
{
CellArea ca = (CellArea)mergeAreaList[i];
if (ca.EndRow > maxRow)
{
maxRow = ca.EndRow;
}
if (ca.EndColumn > maxColumn)
{
maxColumn = ca.EndColumn;
}
}
for (int i = 0; i < sheet.Shapes.Count; i++)
{
Aspose.Cells.Drawing.Shape shape = sheet.Shapes[i];
if (shape.IsHidden)
{
continue;
}
int endRow = shape.LowerRightRow;
int endColumn = shape.LowerRightColumn;
if (endRow > maxRow)
{
bool flag = true;
if (endRow >= MaxRow)
{
int topRow = shape.UpperLeftRow;
// int deltaY = shape.UpperDeltaY;
if (topRow >= MaxRow)
{
flag = false;
}
}
if (flag)
{
maxRow = endRow;
}
}
if (endColumn > maxColumn)
{
maxColumn = endColumn;
}
}
if (maxRow < 0 || maxColumn < 0)
{
return cells.CreateRange(0, 0, 1, 1);
}
return cells.CreateRange(0, 0, maxRow == MaxRow + 1 ? maxRow : maxRow + 1,
maxColumn == MaxColumn + 1 ? maxColumn : maxColumn + 1);
}