Hi
I am trying to align two charts to have the same plot area size when the category axis values are different sizes.
how can i force the size of the category data in order to get the same size in both the horizontal axis and the plot area?
@yvowell,
Thank you for your query.
Please share your template file, runnable console application, your current program output (if any) and expected output file created by MS Excel with us for our testing. We will reproduce the problem and provide our feedback after analysis.
@ahsaniqbalsidiqui
Thanks for your reply!
This is my source code and my result file is attached.
ChartCollection charts = sheet.getCharts();
boolean verticalChartAlignment = subChart.getAlignment().equalsIgnoreCase(“vertical”);
int mod = chartIter % subChart.getHorizontalMaxCharts();
int additionalHorizontalGap = verticalChartAlignment ? 0 : mod * (subChart.getHorizontalSize() + subChart.getHorizontalSpacing());
int chartIndex = charts.add(ChartType.COLUMN_STACKED,
chartMarginTop,
subChart.getHorizontalPadding() + additionalHorizontalGap,
chartMarginTop + subChart.getVerticalSize(),
subChart.getHorizontalPadding() + subChart.getHorizontalSize() + additionalHorizontalGap);
Chart chart = charts.get(chartIndex);
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
Axis valueAxis = chart.getValueAxis();
Line majorGridLines = valueAxis.getMajorGridLines();
majorGridLines.setVisible(false);
chart.setShowLegend(subChart.isShowLegend());
Title title = chart.getTitle();
title.setText(String.format(subChart.getName(), chartTitle));
Font font = title.getFont();
font.setSize(12);
SeriesCollection serieses = chart.getNSeries();
for (SystemReportsExportChartBarSeriesToColor barSeries : subChart.getyAxisBar()) {
serieses.add(String.format(“{%s}”, String.join(“,”, series.get(barSeries.getName()))), true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName(barSeries.getName());
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.fromArgb(barSeries.getColor()));
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
}
serieses.setCategoryData(String.format(“{%s}”, String.join(“,”, series.get(subChart.getxAxis()))));
// chart.calculate();
@yvowell,
Also share the source file and runnable code (without undefined variables). We will reproduce the problem and provide our feedback after analysis.
The source file is an empty xls file with a sheet called: “Summary_Top_Level”.
This is the runnable source code:
Workbook workbook = new Workbook(“Path_to_input”);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(“Summary_Top_Level”);
ChartCollection charts = sheet.getCharts();
int chartIndex = charts.add(ChartType.COLUMN_STACKED, 22, 0, 36, 8);
Chart chart = charts.get(chartIndex);
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
Axis valueAxis = chart.getValueAxis();
Line majorGridLines = valueAxis.getMajorGridLines();
majorGridLines.setVisible(false);
chart.setShowLegend(true);
Title title = chart.getTitle();
title.setText("Title 1");
Font font = title.getFont();
font.setSize(12);
SeriesCollection serieses = chart.getNSeries();
serieses.add("{0, 20, 5, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("critical");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{0, 51, 5, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("high");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{0, 23, 0, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("meduim");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{0, 3, 3, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("minor");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{0, 3, 0, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("none");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.setCategoryData("{01 IDENTIFY, 02 PROTECT, 03 DETECT , 04 RESPOND, 05 RECOVER}");
chartIndex = charts.add(ChartType.COLUMN_STACKED, 22, 8, 36, 15);
chart = charts.get(chartIndex);
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
valueAxis = chart.getValueAxis();
majorGridLines = valueAxis.getMajorGridLines();
majorGridLines.setVisible(false);
chart.setShowLegend(true);
title = chart.getTitle();
title.setText("Title 1");
font = title.getFont();
font.setSize(12);
serieses = chart.getNSeries();
serieses.add("{7, 1, 0, 3, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("critical");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getRed());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{2, 5, 1, 15, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("high");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getOrange());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{4, 5, 0, 1, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("meduim");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getYellow());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{0, 0, 3, 0, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("minor");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getGreen());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.add("{1, 0, 0, 0, 0, 0}", true);
serieses.get(serieses.getCount() -1).setGapWidth((short) 20);
serieses.get(serieses.getCount() -1).setName("none");
serieses.get(serieses.getCount() -1).getArea().setForegroundColor(Color.getLightBlue());
serieses.get(serieses.getCount() -1).getArea().setBackgroundColor(Color.getWhite());
serieses.setCategoryData("{pulsesecure, TufinV17, Qualys VM, FortiGate, Cisco_Any_Connect, Cisco_ASA}");@yvowell,
Thanks for details.
I have tried to make same size but it looks like default behavior of MS Excel. Let us know if you can achieve same requirements with Excel.
In Excel i can drag the plot area and control the size. this makes the category data smaller (ellipsis). can this be done using aspose?
Maybe using TextOverflowType? how can i add this ENUM to the horizontal Axis?
@yvowell,
I have tried to set the plot area using following command but desired size is not achievable.
charts.get(0).getChartArea().setWidth(desiredWidth);
Could you please share the expected output file created using MS Excel for our reference? Once we get your expected output file, we may log an investigation ticket for setting the custom size of the plot area. It seems that TextOverflowType may not produce the desired results where the size of the plot area to be set in your case.
@ahsaniqbalsidiqui
This is the expected file:
https://cyberobserver.sharefile.com/d-seb50dbcacc7473bb
I would like two column charts side by side looking the same.
same plot area size and same category series size.
Thanks and happy new year!
Another question,
I changed the page setup to be landscape. bot both charts still don’t fit on one printed page. how can i set the page size so both charts are printed side by side?
this code did not work:
sheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE); sheet.getPageSetup().setFitToPages(0, 0);
sheet.getPageSetup().setPrintArea(“A1:P38”);
@yvowell,
Thank you for sharing the expected output file. We are working to provide you the required sample code for creating two charts of same plot area and have logged the issue in our database for implementation. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-43084 – Sample code to create two charts of same plot area size having category axis values of different size
Regarding printing both the charts on same page, please use following code before saving the output file.
//Your complete code to create charts
....
....
sheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE);
sheet.getPageSetup().setFitToPages(1, 1);
workbook.save("outputPrint.xlsx");@ahsaniqbalsidiqui
Thank u very much!
One more question if i may:
I used the following since I cared about the width of the page
sheet.getPageSetup().setFitToPages(1, 0);
How can I calculate the height of the generated page in my worksheet?
@yvowell,
Could you please share the steps in MS Excel to retrieve the same information for our reference? We will try to provide you assistance to achieve the same functionality using Aspose.Cells.
@yvowell,
Please try the following sample code and share the feedback if it fulfils your requirement or not. It provides the cell area of each page which will be printed.
Workbook workbook = new Workbook("book1.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
sheet.getPageSetup().setFitToPages(2,3);
System.out.println(sheet.getPageSetup().getPaperHeight());
System.out.println(sheet.getPageSetup().getPaperWidth());
CellArea[] cellAreas = null;
try {
cellAreas = sheet.getPrintingPageBreaks(new ImageOrPrintOptions());
} catch (Exception e) {
e.printStackTrace();
}
for(CellArea cellArea : cellAreas)
{
System.out.println("Cell Area:" + cellArea);
}Thanks for your answer, this gives me the data but it is not exact. for some reasom there is a small diff.
For example
In the Domains sheet, the last cell of the first page is S47 and in the code output it is S49
I ran the following code on each sheet:
sheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE);
sheet.getPageSetup().setFitToPages(1, 0);
System.out.println(sheet.getPageSetup().getPaperHeight());
System.out.println(sheet.getPageSetup().getPaperWidth());
CellArea[] cellAreas = null;
try {
cellAreas = sheet.getPrintingPageBreaks(new ImageOrPrintOptions());
} catch (Exception e) {
e.printStackTrace();
}
for(CellArea cellArea : cellAreas)
{
System.out.println("Cell Area for sheet " + sheet.getName() + “:” + cellArea);
}
This is the debug output:
09:42:31,554 INFO [stdout] (default task-4) 8.5
09:42:31,554 INFO [stdout] (default task-4) 11.0
09:42:31,573 INFO [stdout] (default task-4) Cell Area for sheet Tools:Aspose.Cells.CellArea(A1:S49)[0,0,48,18]
09:42:31,574 INFO [stdout] (default task-4) Cell Area for sheet Tools:Aspose.Cells.CellArea(A50:S98)[49,0,97,18]
09:42:31,574 INFO [stdout] (default task-4) Cell Area for sheet Tools:Aspose.Cells.CellArea(A99:S147)[98,0,146,18]
09:42:31,574 INFO [stdout] (default task-4) Cell Area for sheet Tools:Aspose.Cells.CellArea(A148:S170)[147,0,169,18]
09:42:32,801 INFO [stdout] (default task-4) 8.5
09:42:32,801 INFO [stdout] (default task-4) 11.0
09:42:32,802 INFO [stdout] (default task-4) Cell Area for sheet Domains:Aspose.Cells.CellArea(A1:S49)[0,0,48,18]
09:42:32,802 INFO [stdout] (default task-4) Cell Area for sheet Domains:Aspose.Cells.CellArea(A50:S98)[49,0,97,18]
09:42:32,802 INFO [stdout] (default task-4) Cell Area for sheet Domains:Aspose.Cells.CellArea(A99:S147)[98,0,146,18]
09:42:32,802 INFO [stdout] (default task-4) Cell Area for sheet Domains:Aspose.Cells.CellArea(A148:S152)[147,0,151,18]
The output file:
https://cyberobserver.sharefile.com/d-sc69f677b0654c198
@yvowell,
I have logged a ticket with an id “CELLSJAVA-43085” for your issue. We will evaluate your issue thoroughly.
Once we have an update on it, we will let you know.
@yvowell,
The page break of first page in Sheet “Domains” is also S49 (see attachment “pageBreak.png”).
Because the default font of the source workbook is “Calibri”, you should make sure the default edit language of his Microsoft excel is “English(United States)” (see attachment “defaultEditLanguage.png”). Also make sure the DPI setting of his machine is 100%.
defaultEditLanguage.png (36.3 KB)
pageBreak.png (106.1 KB)
Regarding the issue “CELLSJAVA-43084” for sample code, kindly use the following code, this should produce your desired output file:
charts.get(0).getPlotArea().setInnerWidth(3016);
charts.get(0).getPlotArea().setInnerHeight(2757);
charts.get(1).getPlotArea().setInnerWidth(3016);
charts.get(1).getPlotArea().setInnerHeight(2757);
Hope, this helps a bit.
@Amjad_Sahi
Thanks! this helps control the plot area size.
Still, when the horizontal axis labels are long, the plot area is re-sized 
How can i set the angle of the category labels? in excel i set it like in the attached picture:
image.png (8.6 KB)
image.png (4.0 KB)