Borders misplaced when converting excel to pdf using Aspose.Cells (21.1.0) for .NET

Aspose.Cells Product Family
1

Hi,

We have noticed that when we are converting excel to pdf using Aspose.Cells (21.1.0) then column borders in the excel file(under zipped folder) are getting misplaced in Pdf.

In the excel file borders are present at both sides of Column5 but when it gets converted to pdf file, it can be noticed that border lines are coming on the left side of Column6 in 2nd page.

Reproducer:-

static void pdfConversion()
{
var workbookStream = new FileStream(“Report.xlsx”, FileMode.Open);
var workbook = new Workbook(workbookStream);
var saveOptions = new PdfSaveOptions();
workbook.Save(@“Report.pdf”, saveOptions);
}

Report.pdf (35.5 KB)
Report.zip (42.6 KB)

2

@dfactset,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47830 - Borders misplaced when converting excel to pdf
3

@dfactset,
We have investigated it bit more. The border lines are coming only on the left side of column6 when Pdf file is created with ms excel. Excel.PNG (25.4 KB)

Could you please elaborate your expected output with screenshots for our analysis.

4

Hi @ahsaniqbalsidiqui

If you look at the excel file attached with the reproducer, the border lines are on both side of Column5. When we save the excel to PDF, we can still see the border lines are on both side of Column5 on the 1st page. But then on the 2nd page the border lines are coming on the left of column6 which is wrong. As borders are already appearing on both side of column5 there is no need of border line on left side of Column6 in page 2.

5

@dfactset,
We have noted your feedback and will share our comments after detailed analysis.

6

@dfactset

Generally, when you set right border of Column5 in Excel, the left border of Column 6 is also set. So when you save file to pdf, both right border of Column 5 and left border of Column 6 appears if the page splits between Column 5 and Column 6.

When you open your shared source file in Excel, the left border of Column 6 also appears in Microsoft Excel Printview: Report_Excel2016.pdf (20.0 KB)

7

Hi @Peyton.Xu

You are right about the Excel Printview observation. We can see the same behavior for the attached excel file. However, if you open the file and remove the right borders from column4 & 5 and then again add the right border in column4 and 5 from MS Excel UI the left border on column6 is not showing up in the print preview. We have created the original excel file using Aspose.Cells API. So, we are suspecting this might be something to do with the way borders are added to excel file using the API.

8

@dfactset,
We have noted your comments and will share our feedback soon.

9

@dfactset

We have created the original excel file using Aspose.Cells API. So, we are suspecting this might be something to do with the way borders are added to excel file using the API.

By default, If you set the right border of a cell, Aspose.Cells API will not set/sync the left border of the next cell except you set workbookObject.Settings.UpdateAdjacentCellsBorder to true.

The following code just set the right border of column 4 and column 5 for file Report-NoBorder.zip (8.5 KB)
, it works as you expect.
Code:

Workbook wb = new Workbook("Report-NoBorder.xlsx");

StyleFlag styleFlag = new StyleFlag();
styleFlag.RightBorder = true;

Style style = wb.CreateStyle();
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

Cells cells = wb.Worksheets[0].Cells;

//apply border of a cell
//Cell cellE3 = cells["E3"];
//cellE3.SetStyle(style, styleFlag);


//apply border of a range
Range range = cells.CreateRange("D2:E19");
range.ApplyStyle(style, styleFlag);

wb.Save("output.xlsx");
10

@Peyton.Xu

Thanks for the help here. I was trying to come up with a reproducer for this issue but somehow missed looking into workbookObject.Settings.UpdateAdjacentCellsBorder. It is indeed set in our code and I think that is the reason why we are observing this behavior.

I guess the above mentioned property is applicable to the entire workbook. So based on the value (true/false) it will be applicable to all or none. What if we would want to update “UpdateAdjacentCellsBorder” for some borders and not for all borders in the workbook. Is there a way to do that?

11

@dfactset

What if we would want to update “UpdateAdjacentCellsBorder” for some borders and not for all borders in the workbook. Is there a way to do that?

You can try the following solutions:

  1. Maintain workbookObject.Settings.UpdateAdjacentCellsBorder carefully: set workbookObject.Settings.UpdateAdjacentCellsBorder to true when you want to update “UpdateAdjacentCellsBorder” for some borders, and set it back to false when the work is done.

  2. Set the borders of cells around the cell that you want to update “UpdateAdjacentCellsBorder” for it’s borders.

12

@Peyton.Xu

Is there a way we can get style of a range?

13

@dfactset,

To get style of a range means you got to get style/formatting of each cell (involved) in the range. You may easily do that using its enumerator via a few lines of code:
e.g.
Sample code:

var range = worksheet.Cells.CreateRange("A14:G20");
IEnumerator enumerator = range.GetEnumerator();
while (enumerator.MoveNext())
{
             var cell = enumerator.Current as Aspose.Cells.Cell;
             Aspose.Cells.Style style = cell.GetStyle();
             //....
             //your code goes here
             //.......
}

Hope, this helps a bit.

14

@Amjad_Sahi

Thanks for the sample code. But I am actually looking for something were I do not have to check for style on each cell individually but the style as a whole for a particulate range.

For example when you select 2 or more cells in an excel sheet and then open the format cells from right click option. There we can check formatting for a range consisting of multiple cells. I want to do something similar to this using Aspose.Cells API.

15

@dfactset,

I did evaluate in MS Excel manually a bit. For example, I have created a range of cells (A1:G13). In this range I have set different fonts for some cells (e.g. A1–> Arial, A2 --> Calibri, A3-> Cambria, etc., with different sizes). Also, I set formatting of some cells as Numbers, some as Scientific notations, some as text, etc. Similarly, I set background color for just a few cells. Similarly, I specify horizontal and vertical alignment differently for a few cells (randomly). I then set borders for a few cells only. Now I select the range name from Name box (it will select all the cells in the range), then I right-click on it to open “Format Cells…” dialog. I see nothing is selected in “Number”, “Alignment”, “Font”, “Border” and “Fill” tabs. In short, since the range involves different cells having different formatting/styles, so MS Excel cannot show common style/formatting attributes set. MS Excel can only show common formatting attributes (if there is any). Do you need to create Style object based on common formatting attribute of the range?

16

@Amjad_Sahi

Sorry for the confusion, I should have been more clear about it. Yes, I am looking for the common attributes(specifically border) in the range. I want to get the Style object based on common formatting attribute of the range.

17

@dfactset,

We had Range.Style attribute but later on we removed it for performance grounds and other factors. I am afraid, currently, there is no option to get/create style/formattings based on common attributes in the range. You have to get style/formatting of each cell in the range accordingly.

18

Thanks @Amjad_Sahi for the information. Just wanted to confirm if there is any plan of adding something like Range.Style attribute in future? Or nothing in the pipeline at all?

19

@dfactset,

Currently, this is not in our roadmap but I have logged a ticket with an id “CELLSNET-47852” for your requirements. We will investigate and check the feasibility if we could support it or there is some other good way around.

Once we have an update on it, we will let you know.

1 Like
20

@dfactset,
For CELLSNET-47852, we can not support to get the style of the range because API does not support mixed value for the same attributes in the style.
For example: In the range A1:A2, the font of the A1 is bold, but the font of the A2 is regular.
We have no plan to support it.