Printing data using aspose by splitting columns and rows data dynamically which fits to page

Aspose.Cells Product Family
1

Hi

We need to print the details displayed in the excel. While doing so, if we have data displayed in 15 column in the excel with the first column fixed, then we have to dynamically display only those column which are visible/fit in one page with corresponding row data with fixed first column. And the next columns along with corresponding row data to be displayed in the next page with repeated fixed first column(i.e header) and should continue in the same manner unless all those 15 columns are being displayed in the page one after the other.

Thanking you in advance.Your help would be greatful to us.

Regards

Anitha

2

Hi,


Well, you may try to use PageSetup.PrintTitleColumns attribute to render first column (as repeated column) in every page of the sheet, see the sample line of code below for your reference:
e.g
Sample code:
worksheet.PageSetup.PrintTitleColumns = “$A:$A”;

Regarding display data dynamically for only those columns which are visible/fit in each page, well, you may only specify printable area for the whole sheet, you may also try to insert page breaks (both horizontal and vertical), but how could you do this in Ms Excel manually. Please create a sample Excel file manually in MS Excel having a worksheet with a sample data filled and set according to your desired arrangements for each page, so we could understand you and help you through if possible.

Thank you.
3

Hi

Thanks for the response. Here attaching the sample input excel seeking detailed help in using page break. My printing rule here is "The data for each peer (a total of 3 columns) must always be featured together and must not run over multiple pages".

I am using below code to break at column P. But its not working. Please do help which would be greatful for us.

sheet.getVerticalPageBreaks().add(16);

Regards

Anitha

4

Hi,


Thanks for the template file.

How could you accomplish the task in Ms Excel manually and build a logic for your rule. Aspose.Cells follows Ms Excel standards and specifications, so if Ms Excel can’t do it, Aspose.Cells cannot do it.

As requested earlier, please create your expected sample Excel file in MS Excel manually using your input file (you attached in your previous post) according to your desired arrangements of data for each page, we will check it on how to do it via Aspose.Cells APIs.

Thank you.
5

Hi,

Thanks for the response. Sharing the output print excel. In this i placed data by spliting for 3 peers at a time. Hope you understand the format how the excel needs to be printed. Please let me know if attachment is not clear. Also let me know how the sample excel for output to be provided if this is not the format expected. Thanking you in advance.

Regards

Anitha

6

Hi

Could you please respond to the previous post. Also i need to display header part i.e A1:K11 in all printing pages. Please help me out in this area. I tried with below code and it is not working.

pageSetup.setPrintTitleRows("$1:$11");

Regards

Anitha

7

Hi,

Sorry for attaching wrong sample print output excel. Please find the correct one. And need to display output in this way using page breaks in aspose cells. Please do the needful. Please ignore print output excel in the previous post.

8

Hi,


Thanks for providing us your expected file.

I have written a sample code for your task, please refer to it, it would almost mimic your output file (you attached in your previous post) with a few line breaks that you may manually insert via the relevant Aspose.Cells APIs. Please refer to it and create/update your own codes accordingly.
e.g
Sample code:

String filePath = “Print+Input.xlsx”;

Workbook workbook = new Workbook(filePath);

//Accessing the first worksheet in the Excel file

Worksheet sheet = workbook.getWorksheets().get(0);

PageSetup pageSetup = sheet.getPageSetup();

//Set print title columns
pageSetup.setPrintTitleColumns(“$A:$A”);
//Set print title rows
pageSetup.setPrintTitleRows(“$1:$4”);
//Set the scaling factor
pageSetup.setZoom(100);

workbook.save(“out1PrintOutput1.xlsx”);

I have also attached the output file for your reference. Also, please see the document on how to insert page breaks in the sheet for your reference:
http://www.aspose.com/docs/display/cellsjava/Managing+Page+Breaks


Hope, this helps a bit.

Thank you.

9

Hi,

Thank you for the response. I am able to set page breaks using aspose cells api. But as per the attachment provided by you(in your previous post) the values 'Currency/Country' are not repeated for all pages in print preview. I need that header information of rows 1 to 4 i.e A1 to F3 in all print pages. Could you please help me out. Thanking you in advance.

Regards

Anitha

10

Hi,


Well, in your expected file you attached previously, the header text (Country/Currency) is also not repeated except for the first two pages, so it works as per your provided output file that you created in MS Excel manually. If you still have any confusion or issue, kindly do attach your expected file updating your input file manually in MS Excel and we will check it on how to do it via Aspose.Cells APIs.

Thank you.
11

Hi

In excel file to be printed, header text (Country/Currency) is available only once. But we need to repeat it in all pages while printing with help of pageSetup after giving vertical page break.

For this to implement can we create name range for header information and convert it to image. And use this converted image in pageSetUp setHeader method as below.

pageSetup.setHeader(1, "&G");

Thnking you in advance.

Regards

Anitha

12

Hi,


I have further evaluated your scenario.
Well. yes, we can render an image based on B1:F4 data range in the sheet and later set the image header via Aspose.Cells APIs, but what about the first two pages, I mean, this would duplicate the headers contents (one is from header picture and other is original data header), so it might not work very good for your needs.

Thank you.
13

Hi

Thank you for the response. But how to convert B1:F4 data range as image. Because B1:F4 data range contains dynamic data which is rendered while generating excel. Isit possible to convert name range data of a part of worksheet to image using aspose api.

Thaning you in advance.

Regards

Anitha

14

Hi Anitha,

Thanks for your posting and using Aspose.Cells.

Yes, you can take the image of specific range inside the worksheet. Please see the following documentation article that illustrates how to take the image of the range.