How to add a chart fit the page width?

Aspose.Cells Product Family
1

i want to add some chart to workbook, then i need to print all of these chart, so i want to set each of chart’ size to fit the page to ensure to printing beautiful result.


how can i do it?
2

Hi Pyntia,


Thank you for contacting Aspose support.

Could you please clarify how are you printing the charts? Means, are you printing the individual chart or the complete Workbook? By the way, you can change the chart size within the Workbook according to your required dimensions, however, you will not be able to auto-fit the chart according to the printing page size. Please check the article on Changing the Chart Position & Size.
3

hi,Babar


let me clarify my question.

my source chart like this
source chart

when in the excel’s print preview panel, it will be like this
print preview

the problem is: in the preview, the chart’s size out of the page. i want to make sure the chart’s size is fit the pagewith so can print the whole chart other than part of chart.

thanks
4

Hi,


I have checked the snapshots, you are referring to the Print Preview of the Excel application and probably printing the spreadsheet manually. Please note, in Print Preview, the Excel application splits the Worksheets based on the page breaks according to the paper size set in the Page Setup. In this scenario, you have to resize the chart according to the paper width. The article shared in previous response has the code snippets to change the dimensions of the chart. Please give it a try on your side.
5

hi


"you have to resize the chart according to the paper width. " yes, i really want to do this, but the PageSetup class have no ‘PaperWidth’ prop, i don’t want to hard coding a number for this.
6

Hi,

Thanks for your posting and using Aspose.Cells.

You can find the page size of any page in unit of pixels using the SheetRender.GetPageSize() method. Please see the following sample code that explains how to make use of this method. I have also attached the source excel file used in this code and shown the console output of the code for your reference.

C#


Workbook workbook = new Workbook(“source.xlsx”);


Worksheet worksheet = workbook.Worksheets[0];


SheetRender sr = new SheetRender(worksheet, new ImageOrPrintOptions());

Size sz = sr.GetPageSize(0);


Console.WriteLine(sz);

Console Output
{Width=795, Height=1124}

7

hi,Faiz


thank you very much.
8

hi


there is another question.

in your code, the sheet should need some value in cell. if there has nothing in a sheet, the code will be throw a exception.

let me clearfy my requrement.

if create a new workbook, there will be a empty sheet,then i change the sheet pagesetup, for example set pagesize to A4, and set Orientation to Landscape, etc, finally, the sheet look like below picture.
sheet

now, this sheet is exactly empty, how can i get the pagewidth?

i want to draw some chart on sheet, i need to set the chart width equals to pageWidth, so when print the charts, all of the chart will be fit the A4 paper.

how can i do it?
thanks.
9

Hi,

Thanks for your posting and using Aspose.Cells.

If the sheet is totally empty, then you will have to put some value in it. You can put value in cell A1 of the sheet so that it does not remain empty, then you will be able to get the page width of the sheet successfully.

10

hi


thank you for your reply, i got it. put a blank string value will be ok.
11

Hi,

Yes putting a blank string will be ok and serve the purpose.

12

hi


after my test, i found there has some mistake about this api.

please see the below picture.

fetch pageWidth

not exactly equals to the really pageWidth.

thanks
13

or you can see the attchement file.

14

Hi,

Thanks for your feedback and using Aspose.Cells.

SheetRender.GetPageSize() returns correct page width but when this width is applied to chart, it gets exceeded the page width even when the left and right margins are set to zero. I have tested this issue with the following sample code and attached the source excel file used in this code as well as output excel file generated by it for a reference.

We have therefore logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43840 - Chart width exceeds Page width derived from SheetRender.GetPageSize

C#

Workbook workbook = new Workbook(“source.xlsx”);


Worksheet worksheet = workbook.Worksheets[0];

Chart chart = worksheet.Charts[0];


worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;


ImageOrPrintOptions opts = new ImageOrPrintOptions();


SheetRender sr = new SheetRender(worksheet, opts);

Size sz = sr.GetPageSize(0);


chart.ChartObject.Width = sz.Width;


workbook.Save(“output.xlsx”);


15

Hi,

Thanks for using Aspose.Cells.

Please see the following sample code that fixes your issue. After execution of this code, chart occupies the entire width of the page that lies inside the left and right margins and it also does not exceed to next page.

I have attached the source excel file used in this code and the output excel file generated by it for your reference.

C#


Workbook workbook = new Workbook(“source.xlsx”);


Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;


Chart chart = worksheet.Charts[0];


//Add a cell to calc page breaks

int cellRowIndex = 100, cellColumnIndex = 100;

cells[cellRowIndex, cellColumnIndex].Value = “Add a cell to calc page breaks”;


ImageOrPrintOptions opts = new ImageOrPrintOptions();

CellArea[] pageBreaks = worksheet.GetPrintingPageBreaks(opts);


CellArea pageBreak = pageBreaks[0];

int pageContentWidth = 0;

for (int i = pageBreak.StartColumn; i <= pageBreak.EndColumn; i++)

{

pageContentWidth += cells.GetColumnWidthPixel(i);

}


//a litte smaller than page content width

chart.ChartObject.Width = pageContentWidth - 1;


//clear the cell

cells[cellRowIndex, cellColumnIndex].Value = null;


//open the generated file in excel printview, it will be ok.

workbook.Save(“output.xlsx”);