Excel print not comig properly

Hi Amjad,
i have tried with options you have said in your earlier post,but not able to get the desired output.
Could you please share the actual code to adjust the required settings?

Warp text is not working in my case.
Please share sample code code to get the output you have set manually for the attached document.


Thanks,
Nagamani


Hi,


I did not use any code, I did it in MS Excel but you may achieve it using Aspose.Cells APIs, check the links which I shared for sample code.

For:
"Warp text is not working in my case.
Please share sample code code to get the output you have set manually for the attached document"

Could you share your code and for which column you are doing wrapping text which is not working? We will check and adjust you code accordingly.

Thank you.

Hi amjad,

The following is the bock of code that i am using


Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
if (UseAspose)
{
wb = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet worksheet = wb.Worksheets[0];
worksheet.PageSetup.PaperSize = Aspose.Cells.PaperSizeType.PaperA4;
worksheet.PageSetup.Orientation = Aspose.Cells.PageOrientationType.Landscape;
worksheet.PageSetup.FitToPagesWide = 1;
worksheet.PageSetup.FitToPagesTall = 1;
worksheet.Cells.StandardWidth = 10;

worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, 1000, true, “dd-MMM-yyyy”, true);





Aspose.Cells.Cell cell = worksheet.Cells[1, 13];
Aspose.Cells.Style style = cell.GetStyle();
style.IsTextWrapped = true;
cell.SetStyle(style);

worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
wb.Save(this.Response, “Verbatim.xls”, Aspose.Cells.ContentDisposition.Inline, new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Excel97To2003));

I am trying to set the wrap text for the 13th column in the attached document in last post

Could you please take the document that you sent to me as reference and set the properties thru ASPOSE?

Please do the needful. its very urgent

Thanks,
Nagamani




Hi,


I am trying to set the wrap text for the 13th column in the attached document in last post

To set the wrap text property of the 13th column, please see the following lines of code:

string filePath = @“K:\NewSampleData.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
Column col = worksheet.Cells.Columns[13];
Style style = new Style();
style.IsTextWrapped = true;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
col.ApplyStyle(style, styleFlag);
workbook.Save("K://output.xlsx");

I have attached the source as well as the output files here for your reference. Please have a look at these.

Hi Kasif,
Thanks for your reply.

I rephrase my requirement again.
I have a datable and writing that data into excel using ASPOSE.This part is done doing successfully.

When i take the print
I have to fit all the columns in one page.some case its 13 columns and some times more than 15 columns.

if i don’t use any property then all the columns are not coming in one page.Then i used one property

worksheet.PageSetup.FitToPagesWide = 1;
if i use above property then all the columns fit in one page.
But All the records are coming in one single page.if excel having 30000 records then all these records are coming in one single page hence font is not at all readable.
So please could you please tell me how to fit all the columns in one page and let rows can fit in any number pages ?




Please note that i have also used " worksheet.PageSetup.FitToPagesTall = 1" but this doenst make any difference

Assume that excel having 50,000 records and 15 coulmns and also some text in one or more columns having a very length text as i send a test document to you

How can you do print this excel showing all the columns in one page and let number of pages to accommodate these 50000 records any number of pages?



I hope you understand my requirement



Please let me know if you need anything from my side to fix this issue

Thanks,
Nagamani



Hi,


I think you may try to set FitToPagesWide to “1” and FitToPagesTall to “0”, so MS Excel could cover all the columns on a single page and adjust the records on number of pages.

See the sample code with the attached template file. I have also attached the output file here.

Sample code:

Workbook workbook = new Workbook(“e:\test2\SNewSampleData.xlsx”);
Worksheet ws = workbook.Worksheets[0];
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = 0;
workbook.Save(“e:\test2\out_SNewSampleData.xlsx”);


Thank you.

Hi Support,

Even if i use below “ws.PageSetup.FitToPagesWide = 1;” it is shrinking the columns as 1 page but not able to see the text (it is invisible).

ws.PageSetup.FitToPagesWide = 1;

ws.PageSetup.FitToPagesTall = 0;

Please suggest a workaround asap.

Thank you

Hi,


Thank you for your feedback.

From the above post by Amjad, these parameters are giving correct output as your desired. Can you share with us your output excel file and the complete source code that you are using now? We will look into it and try to assist you further asap.

Hi,

Please try the following code. It should work fine.

Please use the latest version:
Aspose.Cells
for .NET v7.2.2.1


I have attached the output.xlsx file generated by it.

C#


string filePath = @“F:\SNewSampleData.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet ws = workbook.Worksheets[0];


//Clear all horizontal and verticle page breaks

ws.HorizontalPageBreaks.Clear();

ws.VerticalPageBreaks.Clear();


//Clear the print area

ws.PageSetup.PrintArea = null;


//Fit pages wide and tall

ws.PageSetup.FitToPagesWide = 1;

ws.PageSetup.FitToPagesTall = 1;


workbook.Save(filePath + “.out.xlsx”);


Hi,

For:
nagamani:


Even if i use below "ws.PageSetup.FitToPagesWide = 1;" it is shrinking the columns as 1 page but not able to see the text (it is invisible).

ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = 0;

Well, if there are lots of columns and you want all the columns should be there on a single page (no matter if the data goes on multiple pages), it is MS Excel behavior that will always try to decrease the font size (and other attributes), so it might not be worth reading.

As I mentioned earlier, if the data is large/long and you want to make all the columns on single page based, MS Excel will decrease the fonts extensively. For your requirement, if you want the font text should not be decreased (and should be readable well), you should not restrict the columns to one page only.

Thank you.

Hi

We are not able to print attached document in to single page( breaking in to3pages) and font also not readable(see attached print sample.png). Please suggest us solution for this asap.

Thanks,

Nagamani.

Hi,

When I directly save your xls file into pdf using the Ms-Excel 2010, I get the same result.

So, there is some problem with the file. Could you please download the pdf file and check it at your end?


Hi,

I have downloaded the pdf file with same result. Other than difference that “AllQuestions-TopBox(25-06-2012)(8).xls” contains formulas, we are not able to trace ‘problem with the file’.

Please confirm whether aspose support excel sheets with formulas to shrink to one page and also can you please suggest conditions to check (or) give us directions on how to investigate and solve the issue in this excel if there is any.

Thanks,
Nagamani

Hi,

Thanks for your feedback and illustration.

I can notice the issue with your file, we will investigate how this issue could be be resolved.

Once, we have some update for you, we will let you know asap.

Below is a screenshot for a reference.

Screenshot:

Hi,

To further help you understand the difference in this implementation(from earlier example).

AllQuestions-TopBox(25-06-2012)(11).xls contain 3 spreadsheets
1) Visual Report
2) Sample size 3) Top Box %

Visual Report picks up data from 2) and 3) spreadsheets but even when data is not there in pdf page 2, it is showing 257 pages(Screen1.jpg)

Below lines does not give appropriate solution as shown in the earlier conversation screenshot.
ws.PageSetup.FitToPagesWide = 1 and ws.PageSetup.FitToPagesTall = 0

code :–

ws.HorizontalPageBreaks.Clear();
ws.VerticalPageBreaks.Clear();

ws.PageSetup.PrintArea = null;

//Aspose.Cells.Style styles = new Aspose.Cells.Style();
//styles.IsTextWrapped = true;
//Aspose.Cells.StyleFlag sfalgs = new Aspose.Cells.StyleFlag();
//sfalgs.WrapText = true;
//ws.Cells.ApplyStyle(styles, sfalgs);
ws.Cells.SetColumnWidth(0, 30);

//ws.PageSetup.FitToPagesWide = 1;
//ws.PageSetup.FitToPagesTall = 0;

ws.AutoFitRows();

Thanks,
Nagamani


Hi,


Well, Aspose.Cells does nothing more than what MS Excel can do regarding fitting pages wide/tall and other page setup options. It has to follow the MS Excel restrictions as after all, MS Excel will display the Excel (generated by Aspose.Cells component) file and what you will see in the print preview, MS Excel will print it accordingly. We still think as Shakeel Faiz has described, there might be an issue with your template file and so Aspose.Cells cannot do according to your desired needs. Anyways, we have further logged your further comments and one of our main developer will look into it soon.

We highly appreciate if you could format your template file regarding fitting pages tall/wide and other page setup options (and Save it as) accordingly in MS Excel manually. Please take your template file “AllQuestions-TopBox(25-06-2012)(8).xls” that you previously attached, open it in MS Excel and do your desired formatting regarding fitting pages tall/wide and use other page setup options manually. Now save the file and attach it here. We will give you example codes on how to do it using Aspose.Cells. For your information, I have already tried to do some page setup and other row/column formatting options in MS Excel manually but in vain as I could not make the first sheet wide enough to show all the columns no matter how many pages are created (fit to pages tall). So, I would ask you to kindly try the options in MS Excel manually and formulate according to your needs to share the final Excel file here. We will surely check it soon.

By the way, how did you create/get the template file?

Thank you.

Hi,

Thanks for using Aspose.Cells

Please note, your issue has been logged with the id: CELLSNET-40782

We will look into your problem and update you asap.

Hi,

The cells in the range A11:DS685 are not empty. If you do not set the print area, the default pint area is A1: DS685, so the print preview is very small when you want to print so many columns to one page.

If you just want to print the area with data, please try the following code:

I have attached the screenshot for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\AllQuestions-TopBox(25-06-2012)(11).xls”;


Workbook workbook = new Workbook(filePath);

Cells cell = workbook.Worksheets[“Visual Report”].Cells;


string pArea = “A1:” + CellsHelper.CellIndexToName(workbook.Worksheets[“Top Box %”].Cells.MaxRow, workbook.Worksheets[“Top Box %”].Cells.MaxColumn);


workbook.Worksheets[“Visual Report”].PageSetup.PrintArea = pArea;


workbook.Save(filePath + “.out.xls”);

Screenshot:

Hi Team,

Thanks for your reply.Few things we have noticed is

1.The screen shots which you have attached is not the desired output format ,We want all the columns in a single page, but in the screen shots which you have provided 2 columns are in one page and next 4 columns are in another page.

2.We have tried the solution which you have suggested us.But the out put is still same,except for PrintArea added extra at the end of “Visual Report”.Thus results in duplicate data.Please find the attached file.

Thanks
Nagamani.

Hi,


It works fine using the code segments shared earlier. I have tested it using your file “AllQuestions-TopBox(26-06-2012)(10).xls” as template/input file and apply the code segments to generate the output file according to your needs, see the output file and take the print preview for your reference, it works according to your needs.

Sample code:

string filePath = @“e:\test2\AllQuestions-TopBox(26-06-2012)(10).xls”;
Workbook workbook = new Workbook(filePath);
Cells cell = workbook.Worksheets[“Visual Report”].Cells;
//Since your details data starts from 9th row, so I have added 8 to the row count while specifying the cell area for the first row.
string pArea = “A1:” + CellsHelper.CellIndexToName(workbook.Worksheets[“Top Box %”].Cells.MaxDataRow + 9, workbook.Worksheets[“Top Box %”].Cells.MaxDataColumn);
workbook.Worksheets[“Visual Report”].PageSetup.PrintArea = pArea;
workbook.Worksheets[“Visual Report”].PageSetup.FitToPagesWide = 1;
workbook.Worksheets[“Visual Report”].PageSetup.FitToPagesWide = 0;
workbook.Save(filePath + “.out2.xls”);

Thank you.