Excel print not comig properly

nagamani · June 13, 2012, 5:37am

Dear Aspose Team,

I am using Aspose cell for .Net.
I have a problem in printing the downloaded excel using ASPOSE.
Print coming as very small font which is not at all readable
Please see the attached screenshot for your reference that i have taken from print preview.

Please note that this excel got more columns and text also some what big in some columns.

All want is to print that excel download without any issues and should be fit in all the columns in single page irrespective of no of columns or length of the text inside any row

Please see the block of code that i am using currently.

bool UseAspose = Convert.ToBoolean(ConfigurationManager.AppSettings[“UseAspose”]);
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.PaperA4Rotated;
worksheet.PageSetup.Orientation = Aspose.Cells.PageOrientationType.Landscape;
worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, 1000, true, “dd-MMM-yyyy”, true);

worksheet.AutoFitRows();
worksheet.AutoFitColumns();
wb.Save(this.Response, “Verbatim.xls”, Aspose.Cells.ContentDisposition.Inline, new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Excel97To2003));
}

Could you please suggest to fix this issue?

Thanks,
Nagamani

kashif.iqbal · June 13, 2012, 11:44am

Hi,

Please accept my apologies for the delayed response.

Can you please download and use this latest fix: Aspose.Cells for .NET v7.2.1.9 and let us know your feedback. Also, if it’s not a problem for you to share your excel file with us, we will be in need of your excel file to look into it and investigate the issue further.

amjad.sahi · June 13, 2012, 12:36pm

Hi,

As you want:

“All want is to print that excel download without any issues and should be fit in all the columns in single page irrespective of no of columns or length of the text inside any row”

This may result in:

“I have a problem in printing the downloaded excel using ASPOSE.

Print coming as very small font which is not at all readable”

Do you use FitToPages Tall/Wide options or some other options to fit the page on a single page, see the document for your reference:

http://www.aspose.com/docs/display/cellsnet/Setting+Page+Options

Please give us your complete sample code or sample console application with template Excel file(s). Also if you do the same thing in MS Excel manually (use PageSetup options etc.), do you get the similar result?

Well, if the data is large/long and you want to make a single page of it (use PageSetup options etc.), MS Excel may decrease the fonts extensively as per your screen shot. For your requirement, if you want the font text should not be decreased (and should be readable), you should not restrict the contents to one page.

Thank you.

nagamani · June 14, 2012, 12:56am

Hi Amjad ,
Thanks for your reply.
Please find the attached sample excel file for your reference.
I am using the following block of code to download excel

wb = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet worksheet = wb.Worksheets[0];
worksheet.PageSetup.PaperSize = Aspose.Cells.PaperSizeType.PaperA4Rotated;
worksheet.PageSetup.Orientation = Aspose.Cells.PageOrientationType.Landscape;

        worksheet.PageSetup.FitToPagesWide = 1;

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

worksheet.AutoFitRows();
worksheet.AutoFitColumns();

wb.Save(this.Response, “Sample.xls”, Aspose.Cells.ContentDisposition.Inline, new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Excel97To2003));

That is all the code i am using in my application.

Please set the any other printing options so that data should fit in one page.

And one more thing if the font is very small to read after the print as i attached in my earlier post,
You can wrap the text wherever required so that it will show in single page

Please do the needful ,its a matter of very urgency.You can you the sample excel file that i have attached here to test this issue

I look forward to here from you soon

Thanks
Nagamani

nagamani · June 14, 2012, 1:49am

Hi Kasif,

License error is coming if i use the new DLL.Is this issue fixed in this version?
Do i get free update for this? or need to renew the license to use new dll?

Thanks,
Nagamani

amjad.sahi · June 14, 2012, 2:25am

Hi Nagmani,

Well, it is really hard to fit all the data into one page as you have long list of columns in your data. We recommend you to kindly make at least 2 pages to fit your data. If we decrease columns’ widths and then wrap text in the cells (of the columns), may be it will not be acceptable at your end (sometimes the data should not be in the second line when there is one long word or number / value etc.). Anyways, I have tried the following adjustments in MS Excel manually, you may do it using Aspose.Cells APIs accordingly if you want, I have also pasted the documentation topics below too:

1) I decreased columns’ widths of almost all the columns

http://www.aspose.com/docs/display/cellsnet/Adjusting+Row+Height++and++Column+Width

2) I set wrap for some columns cells e.g (Columns B, H, M. N etc.)

http://www.aspose.com/docs/display/cellsnet/Configuring+Alignment+Settings

3) I set the left and right margins to "0"

http://www.aspose.com/docs/display/cellsnet/Setting+Margins

4) I use FitToPage tall/wide to "1". I set the orientation to landscape and paper size to A4

http://www.aspose.com/docs/display/cellsnet/Setting+Page+Options

Note: You can see the font's size is still not very good, it is almost OK though to read.

Thank you.

amjad.sahi · June 14, 2012, 2:30am

Hi,

For:

"License error is coming if i use the new DLL.Is this issue fixed in this version?

Do i get free update for this? or need to renew the license to use new dll?"

Please check your subscription expiry date to know whether you can use latest versions/fixes of the product or not (you may open your license file into notepad and check the expiry date), if new version/fix e.g v7.2.1.x was released after your expiry date, then you need to upgrade your subscription to use it.

Thank you.

nagamani · June 14, 2012, 3:00am

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

amjad.sahi · June 14, 2012, 3:22am

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.

nagamani · June 14, 2012, 4:03am

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

kashif.iqbal · June 14, 2012, 5:51am

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.

nagamani · June 15, 2012, 1:14am

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

amjad.sahi · June 15, 2012, 2:21am

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.

nagamani · June 21, 2012, 8:24am

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

kashif.iqbal · June 21, 2012, 8:30am

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.

shakeel.faiz · June 21, 2012, 8:33am

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”);

amjad.sahi · June 21, 2012, 12:17pm

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.

nagamani · June 25, 2012, 4:01am

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.

shakeel.faiz · June 25, 2012, 4:06am

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?

nagamani · June 25, 2012, 5:55am

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