Label truncation due to PrintTitlecolumns and verticalsplit while converting XL to PDF

Aspose.Cells Product Family
, ,
1

Hi Team,
I have a requirement where the columns B and C occupy minimal width in excel and I don’t want to increase the width of the columns.
Consider there are 10 rows at the end of 10 th row ,I will be having a page label
So this label in ‘C’ column is not having enough space so what i did is i merged columns from B,C,D,E so that the text is not truncated and appears normal.

This excel is getting converted to PDF using aspose and we are using verticalpagesplit.
Now the problem is, if I have multiple pages ,in which B and C column has row headers and only for the last part(after last data row) it has page label.

So for the first page of PDF i have my page label merged from B to E, but from the second page onwards due to my printitlecolumns mentioned as from B to C, page label which is displayed properly in first page does not remains the same from second page onwards. It is getting truncated.

Note: from Column D onwards,values starts appearing so there is no way i can include D in printtitlecolumns

So can you please help me with this.

2

@prabhu94,

Aspose.Cells follows MS Excel standards and specifications when rendering to PDF file format. I do not think it is an issue with the APIs rather behavior/result of MS Excel due to different attributes/options you set regarding print title columns/rows, merge cells and pagebreaks. For confirmation, please open your Excel file (after setting all those options/attributes) manually, then open your worksheet into print preview and check if you got the same results as per output PDF by Aspose.Cells APIs. If not, kindly do share your input Excel file, sample code and output PDF file for our reference. We will check your issue soon. Also, provide Excel file with your desired data formation (which should give your desired view in print preview of MS Excel), you may create your expected Excel file in MS Excel manually. This will help us really to evaluate your issue and assist you accordingly.

PS. please zip the files prior attaching here.

3

Thanks for your reply Amjad,
Iam hereby attaching the sample XL template with the following guidelines
1.After 2 item(Item1 and Item2 mentioned in template) there is a vertical page break and then next 2 items (Item3 and Item4) will be in the second page.
2.I have mentioned setPrintitlecolumns to have first column alone(A).
3.Now i require copyright message at the bottom to be repeated for second page too rather now its coming as a truncated one.
PFA,SampleXLTemplate.zip (5.9 KB)
kindly provide any code template if you find any solution

4

@prabhu94,

After manually performing 2nd step (set “Columns to repeat at left” option to “$A:$A” in Page Setup|Sheet tab) in MS Excel, I opened the sheet in print preview and found the same truncated copyright message issue in the second page. So, this is not an issue with Aspose.Cells by any means.

For your requirements, there are some ways (workaround) to cope with it.
Method1

  1. Unmerge cell containing “Copyright Message”.
  2. Set horizontal alignment to “Left”.
  3. Select Column A and auto-fit to display the copyright text fully.
  4. You may set the vertical page break at your desired location accordingly now.
    See the attached file for your reference.
    files1.zip (6.8 KB)

Method2
You may set copyright text as footer (see the document on how to set headers and footers) and should remove the row containing your copyright text first. Please note, if you adopt this technique to add footer text, this would paste copyright text right at the bottom of the pages (as footer text).

5

Thanks Amjad for your reply.i have some questions on both of your suggested methods
1.I have already tried your first approach wherein i used autofit column width property but the thing here is the width of column should autofit only based on the data rows of values only(AAAA,BBBB,CCCC) and it should not consider the copyright text.For this purpose i have already written a code like if the copyright text doesnt fit in the columnA alone will do the merge with other columns too dynamically.So by doing this Copyright for sure will be merged with other columns at most cases.So thats y i need a way to print copyright without truncation in other pages too(as i mentioned in my first request).

2.For the second method you suggested curently iam trying that method ,only doubt i have is can you tell me how to use smart marker in footer text like &=$Copyright text like we used to do for cells,so that the copyright text will get populated dynamically from db

6

@prabhu94,

  1. I tried to cope with it manually in MS Excel but to no avail. Please perform your task in MS Excel. If you are succeeded then you may do the same via Aspose.Cells APIs.

  2. I am afraid we do not support smart marker for headers/footers. However we think it should be easy for you to replace the string in the header/footer by yourself and then reset the header/footer accordingly. You can process smart marker in some cell (later you may remove that via the APIs) and then replace the string in your original header/footer for your needs.

7

Thanks Amjad for your reply.
Iam currently working on providing the same content using footer.I have some doubts,

First Solution:

With respect to the first solution i have an idea like after vertical split i will be having 2 data columns in one page and next two in another page,
1.so for pdf alone i have an idea like for first page, i will merge 1 (row header column) with first data column(2) so that it will be displayed without truncation.
2.for the second page where columns will be in the order of 1(header),4 and 5th data column.Is there a way wherein i can merge 1 and 4 for second page and i will get that content without truncation.The columns 2 and 3 which was displayed in the first page will have its columns size reduced while generating the content for pdf of second page.
So can you tell me the feasibility of this approach.

Second Solution:

1.There are only 3 positions left (0),middle(1) and right(2).Is there a way that we can introduce some style to these elements so that element (1) will be more closer to element (0) like left align or right align text or actual style like padding or margin.
2.If i want to add a picture in my footer left column, is there a way to copy my picture from one cell in xl and then how can i paste that picture in my footer and how can i delete the image in cell after copying.
3.Or else ,Is there way to concatenate both image + string and drop it in the footer column index(0) as a single entity

8

@prabhu94,

First Solution

  1. and 2.
    I am not sure about the feasibility of your approach as you have devised it for your custom needs. You can try it if it fits your needs or not. If this does not work, as I requested earlier, kindly perform the task manually in MS Excel and then share your desired Excel file (which should give print preview as per your expectations without any truncation) here, we will check on how to do it via Aspose.Cells APIs.

Second Solution

  1. Please note, in MS Excel there are only three positions of header/footer you got to place it in those positions only. I could not find any such option where left element will become more closer to middle element and so on. We can only set header/footer margin (from top or from bottom) which will determine where the header will be placed from top (of the page) or footer would be placed from bottom in specific unit(s). No padding or left align kind of thing is there when specifying headers/footers.

  2. You can add image directly into header/footer, see the document with example on how to add picture into header or footer for your reference.

  3. Yes, you can add both image and string into header/footer. For example, you can add header image into the first section and add string/other thing into the second section accordingly. For reference, see the example in the document, I referred (above) in 2.

9

Amjad i tried using image in footer,

  1. I have a image in XL template, by which i grabbed the picture and converted to byte array and passed it to setFooter method where in i got the required image in footer i have some questions with repect to footer,
    a.how to manipulate width and height of footer image when i export it to pdf?
    b.When i see the print preview of the generated pdf, right most part of the footer(2) is getting truncated only half of text is visible in print preview.
    c.Is there any way i can give some name to picture in XL and i can access it by its name and can convert to byte array.
10

@prabhu94,

a) In MS Excel Page Setup header/footer, you cannot manipulate height and width of the image. You have to set height and width before converting to byte array and inserting it as header/footer.
b) You should test the final output Excel file in MS Excel manually if you see different view when taking the print preview of the sheet? By the way, in header/footer, if you insert everything (image, long text, etc.) in one flow into it, then the truncation might happen.
c) You may use Name property of the Shape/Picture which is stored in the worksheet cells.

11

Hi Amjad,
I have one more work-around, for which i require your expertise.
first thing is, we should not use footer since it will not solve our problem.
So what we are thinking is, with reference to the above xl file i attached in our initial conversation
1.For xl file all 4 data columns can be in the same sheet.
2.But for PDF and PPT after the column split, in our case it is after 2 data columns with titlecolumns
a)first 2 columns will be in first sheet next two columns along with titlecolumns will move to the next sheet of the same file ,so in PDF and PPT for all the pages i will access each sheet and solve the copyright label truncation issue at the bottom by merging cells.
b)But the xl file should not show two different sheets it should always show in a single sheet, only for pdf and ppt we need to use the multiple sheet approach

So can you please help me what is the feasibility of the above approach and can you help me with any sample.

Note: For your info in real case we can have around 500 data columns.

12

In that case, you should not move/split data to multiple sheets. Or you may save to Excel file first (that contains your original data intact in the single sheet) via Aspose.Cells in code (e.g. Workbook.Save(stringFilePath)). So, you may write code to split data into multiple sheets (for rendering to PDF and PPT) after saving to Excel file.

You may use fit to pages options for it, see the document for your reference.

13

Amjad thanks for your reply.
Is there any sample code that tells me how to convert a single worksheet to multiple sheets for PDF and PPT alone retaining the titlecolumns and titlerows based on page breaks provided

14

@prabhu94,

There is no such sample code available. You have to devise your logic and write your own code using Aspose.Cells APIs to split data from single sheet to multiple sheets for your custom needs. For example, you may use Range.Copy() to copy data from source to your desired location in the new worksheet, see the document on Copying Ranges for your reference.