Category Labels missing for Vertical Bar chart, need to increase chart width dynamically based on category axis labels and series data points

Hi Team,
We want to create vertical bar charts for multiple series with distinct category names.
It is observed that , as series data points increase , category axis labels are not displayed properly. Need to know if any way possible to increase the width of chart dynamically.
Attaching zip file consisting of excel generated and corresponding code snippet.
Thanks!
VerticalBarChart.zip (4.7 KB)

@jnachi1699,

Thanks for the sample file and details.

Since there are lots of category axis ticket labels to be displayed on the x-axis, so you may try to do the following measures to show them properly:

  1. Extend chart area’s width and height a bit.
  2. Set tick labels rotation angle. so the labels text should be displayed fully.

See the following updated sample code segment and try it, it will work for your needs:
e.g.
Sample code:

        // ExStart:1
        // The path to the documents directory.
        String dataDir = "f:\\files\\";

        // Instantiating a Workbook object
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        Cells cells = worksheet.getCells();
        cells.get("A2").setValue("Category Data For A");
        cells.get("A3").setValue("Category Data For b");
        cells.get("A4").setValue("Category Data For c");
        cells.get("A5").setValue("Category Data For d");

        cells.get("A6").setValue("Category Data For e");
        cells.get("A7").setValue("Category Data For f");
        cells.get("A8").setValue("Category Data For g");
        cells.get("A9").setValue("Category Data For h");

        cells.get("A10").setValue("Category Data For i");
        cells.get("A11").setValue("Category Data For j");
        cells.get("A12").setValue("Category Data For k");
        cells.get("A13").setValue("Category Data For l");

        cells.get("A14").setValue("Category Data For m");
        cells.get("A15").setValue("Category Data For n");
        cells.get("A16").setValue("Category Data For o");
        cells.get("A17").setValue("Category Data For p");

        cells.get("B1").setValue("Series 1");
        cells.get("C1").setValue("Series 2");
        cells.get("D1").setValue("Series 3");
        cells.get("E1").setValue("Series 4");
        cells.get("F1").setValue("Series 5");
        cells.get("G1").setValue("Series 6");
        cells.get("H1").setValue("Series 7");
        cells.get("I1").setValue("Series 8");
        cells.get("J1").setValue("Series 9");
        cells.get("K1").setValue("Series 10");
        cells.get("L1").setValue("Series 11");
        cells.get("M1").setValue("Series 12");
        cells.get("N1").setValue("Series 13");
        cells.get("O1").setValue("Series 14");
        cells.get("P1").setValue("Series 15");
        cells.get("Q1").setValue("Series 16");

        cells.get("B2").setValue(72.6);
        cells.get("B3").setValue(64.8);
        cells.get("B4").setValue(35.8);
        cells.get("B5").setValue(38.4);
        cells.get("B6").setValue(72.6);
        cells.get("B7").setValue(64.8);
        cells.get("B8").setValue(35.8);
        cells.get("B9").setValue(38.4);
        cells.get("B10").setValue(72.6);
        cells.get("B11").setValue(64.8);
        cells.get("B12").setValue(35.8);
        cells.get("B13").setValue(38.4);
        cells.get("B14").setValue(72.6);
        cells.get("B15").setValue(64.8);
        cells.get("B16").setValue(35.8);
        cells.get("B17").setValue(38.4);

        cells.get("C2").setValue(86.3);
        cells.get("C3").setValue(38.4);
        cells.get("C4").setValue(89.3);
        cells.get("C5").setValue(43.7);
        cells.get("C6").setValue(72.6);
        cells.get("C7").setValue(64.8);
        cells.get("C8").setValue(35.8);
        cells.get("C9").setValue(38.4);
        cells.get("C10").setValue(72.6);
        cells.get("C11").setValue(64.8);
        cells.get("C12").setValue(35.8);
        cells.get("C13").setValue(38.4);
        cells.get("C14").setValue(72.6);
        cells.get("C15").setValue(64.8);
        cells.get("C16").setValue(35.8);
        cells.get("C17").setValue(38.4);

        cells.get("D2").setValue(72.6);
        cells.get("D3").setValue(64.8);
        cells.get("D4").setValue(35.8);
        cells.get("D5").setValue(38.4);
        cells.get("D6").setValue(72.6);
        cells.get("D7").setValue(64.8);
        cells.get("D8").setValue(35.8);
        cells.get("D9").setValue(38.4);
        cells.get("D10").setValue(72.6);
        cells.get("D11").setValue(64.8);
        cells.get("D12").setValue(35.8);
        cells.get("D13").setValue(38.4);
        cells.get("D14").setValue(72.6);
        cells.get("D15").setValue(64.8);
        cells.get("D16").setValue(35.8);
        cells.get("D17").setValue(38.4);

        cells.get("E2").setValue(72.6);
        cells.get("E3").setValue(64.8);
        cells.get("E4").setValue(35.8);
        cells.get("E5").setValue(38.4);
        cells.get("E6").setValue(72.6);
        cells.get("E7").setValue(64.8);
        cells.get("E8").setValue(35.8);
        cells.get("E9").setValue(38.4);
        cells.get("E10").setValue(72.6);
        cells.get("E11").setValue(64.8);
        cells.get("E12").setValue(35.8);
        cells.get("E13").setValue(38.4);
        cells.get("E14").setValue(72.6);
        cells.get("E15").setValue(64.8);
        cells.get("E16").setValue(35.8);
        cells.get("E17").setValue(38.4);

        cells.get("F2").setValue(72.6);
        cells.get("F3").setValue(64.8);
        cells.get("F4").setValue(35.8);
        cells.get("F5").setValue(38.4);
        cells.get("F6").setValue(72.6);
        cells.get("F7").setValue(64.8);
        cells.get("F8").setValue(35.8);
        cells.get("F9").setValue(38.4);
        cells.get("F10").setValue(72.6);
        cells.get("F11").setValue(64.8);
        cells.get("F12").setValue(35.8);
        cells.get("F13").setValue(38.4);
        cells.get("F14").setValue(72.6);
        cells.get("F15").setValue(64.8);
        cells.get("F16").setValue(35.8);
        cells.get("F17").setValue(38.4);

        cells.get("G2").setValue(88.6);
        cells.get("G3").setValue(7.8);
        cells.get("G4").setValue(15.8);
        cells.get("G5").setValue(38.4);
        cells.get("G6").setValue(72.6);
        cells.get("G7").setValue(64.8);
        cells.get("G8").setValue(35.8);
        cells.get("G9").setValue(38.4);
        cells.get("G10").setValue(72.6);
        cells.get("G11").setValue(64.8);
        cells.get("G12").setValue(35.8);
        cells.get("G13").setValue(38.4);
        cells.get("G14").setValue(72.6);
        cells.get("G15").setValue(64.8);
        cells.get("G16").setValue(35.8);
        cells.get("G17").setValue(38.4);

        cells.get("H2").setValue(71.6);
        cells.get("H3").setValue(24.8);
        cells.get("H4").setValue(05.8);
        cells.get("H5").setValue(98.4);
        cells.get("H6").setValue(72.6);
        cells.get("H7").setValue(64.8);
        cells.get("H8").setValue(35.8);
        cells.get("H9").setValue(38.4);
        cells.get("H10").setValue(72.6);
        cells.get("H11").setValue(64.8);
        cells.get("H12").setValue(35.8);
        cells.get("H13").setValue(38.4);
        cells.get("H14").setValue(72.6);
        cells.get("H15").setValue(64.8);
        cells.get("H16").setValue(35.8);
        cells.get("H17").setValue(38.4);

        cells.get("I2").setValue(102.6);
        cells.get("I3").setValue(4.8);
        cells.get("I4").setValue(95.8);
        cells.get("I5").setValue(28.4);
        cells.get("I6").setValue(72.6);
        cells.get("I7").setValue(64.8);
        cells.get("I8").setValue(35.8);
        cells.get("I9").setValue(38.4);
        cells.get("I10").setValue(72.6);
        cells.get("I11").setValue(64.8);
        cells.get("I12").setValue(35.8);
        cells.get("I13").setValue(38.4);
        cells.get("I14").setValue(72.6);
        cells.get("I15").setValue(64.8);
        cells.get("I16").setValue(35.8);
        cells.get("I17").setValue(38.4);

        cells.get("J2").setValue(72.6);
        cells.get("J3").setValue(64.8);
        cells.get("J4").setValue(35.8);
        cells.get("J5").setValue(38.4);
        cells.get("J6").setValue(72.6);
        cells.get("J7").setValue(64.8);
        cells.get("J8").setValue(35.8);
        cells.get("J9").setValue(38.4);
        cells.get("J10").setValue(72.6);
        cells.get("J11").setValue(64.8);
        cells.get("J12").setValue(35.8);
        cells.get("J13").setValue(38.4);
        cells.get("J14").setValue(72.6);
        cells.get("J15").setValue(64.8);
        cells.get("J16").setValue(35.8);
        cells.get("J17").setValue(38.4);


        cells.get("K2").setValue(82.6);
        cells.get("K3").setValue(34.8);
        cells.get("K4").setValue(95.8);
        cells.get("K5").setValue(8.4);
        cells.get("K6").setValue(72.6);
        cells.get("K7").setValue(64.8);
        cells.get("K8").setValue(35.8);
        cells.get("K9").setValue(38.4);
        cells.get("K10").setValue(72.6);
        cells.get("K11").setValue(64.8);
        cells.get("K12").setValue(35.8);
        cells.get("K13").setValue(38.4);
        cells.get("K14").setValue(72.6);
        cells.get("K15").setValue(64.8);
        cells.get("K16").setValue(35.8);
        cells.get("K17").setValue(38.4);

        cells.get("L2").setValue(32.6);
        cells.get("L3").setValue(24.8);
        cells.get("L4").setValue(15.8);
        cells.get("L5").setValue(18.4);
        cells.get("L6").setValue(72.6);
        cells.get("L7").setValue(64.8);
        cells.get("L8").setValue(35.8);
        cells.get("L9").setValue(38.4);
        cells.get("L10").setValue(72.6);
        cells.get("L11").setValue(64.8);
        cells.get("L12").setValue(35.8);
        cells.get("L13").setValue(38.4);
        cells.get("L14").setValue(72.6);
        cells.get("L15").setValue(64.8);
        cells.get("L16").setValue(35.8);
        cells.get("L17").setValue(38.4);

        cells.get("M2").setValue(32.6);
        cells.get("M3").setValue(24.8);
        cells.get("M4").setValue(15.8);
        cells.get("M5").setValue(18.4);
        cells.get("M6").setValue(72.6);
        cells.get("M7").setValue(64.8);
        cells.get("M8").setValue(35.8);
        cells.get("M9").setValue(38.4);
        cells.get("M10").setValue(72.6);
        cells.get("M11").setValue(64.8);
        cells.get("M12").setValue(35.8);
        cells.get("M13").setValue(38.4);
        cells.get("M14").setValue(72.6);
        cells.get("M15").setValue(64.8);
        cells.get("M16").setValue(35.8);
        cells.get("M17").setValue(38.4);

        cells.get("N2").setValue(32.6);
        cells.get("N3").setValue(24.8);
        cells.get("N4").setValue(15.8);
        cells.get("N5").setValue(18.4);
        cells.get("N6").setValue(72.6);
        cells.get("N7").setValue(64.8);
        cells.get("N8").setValue(35.8);
        cells.get("N9").setValue(38.4);
        cells.get("N10").setValue(72.6);
        cells.get("N11").setValue(64.8);
        cells.get("N12").setValue(35.8);
        cells.get("N13").setValue(38.4);
        cells.get("N14").setValue(72.6);
        cells.get("N15").setValue(64.8);
        cells.get("N16").setValue(35.8);
        cells.get("N17").setValue(38.4);

        cells.get("O2").setValue(32.6);
        cells.get("O3").setValue(24.8);
        cells.get("O4").setValue(15.8);
        cells.get("O5").setValue(18.4);
        cells.get("O6").setValue(72.6);
        cells.get("O7").setValue(64.8);
        cells.get("O8").setValue(35.8);
        cells.get("O9").setValue(38.4);
        cells.get("O10").setValue(72.6);
        cells.get("O11").setValue(64.8);
        cells.get("O12").setValue(35.8);
        cells.get("O13").setValue(38.4);
        cells.get("O14").setValue(72.6);
        cells.get("O15").setValue(64.8);
        cells.get("O16").setValue(35.8);
        cells.get("O17").setValue(38.4);

        cells.get("P2").setValue(32.6);
        cells.get("P3").setValue(24.8);
        cells.get("P4").setValue(15.8);
        cells.get("P5").setValue(18.4);
        cells.get("P6").setValue(72.6);
        cells.get("P7").setValue(64.8);
        cells.get("P8").setValue(35.8);
        cells.get("P9").setValue(38.4);
        cells.get("P10").setValue(72.6);
        cells.get("P11").setValue(64.8);
        cells.get("P12").setValue(35.8);
        cells.get("P13").setValue(38.4);
        cells.get("P14").setValue(72.6);
        cells.get("P15").setValue(64.8);
        cells.get("P16").setValue(35.8);
        cells.get("P17").setValue(38.4);

        cells.get("Q2").setValue(32.6);
        cells.get("Q3").setValue(24.8);
        cells.get("Q4").setValue(15.8);
        cells.get("Q5").setValue(18.4);
        cells.get("Q6").setValue(72.6);
        cells.get("Q7").setValue(64.8);
        cells.get("Q8").setValue(35.8);
        cells.get("Q9").setValue(38.4);
        cells.get("Q10").setValue(72.6);
        cells.get("Q11").setValue(64.8);
        cells.get("Q12").setValue(35.8);
        cells.get("Q13").setValue(38.4);
        cells.get("Q14").setValue(72.6);
        cells.get("Q15").setValue(64.8);
        cells.get("Q16").setValue(35.8);
        cells.get("Q17").setValue(38.4);

        worksheet.getCharts().add(ChartType.COLUMN, 4, 4, 48, 25);
        Chart chart = worksheet.getCharts().get(0);

        SeriesCollection serieses = chart.getNSeries();
        serieses.add("B2:Q17", true);
        serieses.setCategoryData("A2:A17");

        chart.getCategoryAxis().getTickLabels().setAutomaticRotation(true);
        chart.getCategoryAxis().getTickLabels().setRotationAngle(45);

        // Saving the Excel file
        workbook.save(dataDir + "Column6.xls");
        // ExEnd:1

        // Print message
        System.out.println("Workbook with chart is successfully created.");

Hope, this helps a bit.

@Amjad_Sahi
Thanks for the update!

We are unable to use the solution With the rotation of tickLabels, due to client requirements.
Is there any other way, suppose based on category names to increase the chartWidth dynamically?

Thanks!

@jnachi1699,

Please note, Aspose.Cells follows MS Excel standards and specifications when rendering charts in Excel spreadsheets. The axis tick labels depend on the source cells values. If there are number of labels to be rendered on the chart, they might not be displayed fully or properly. This is MS Excel’s behavior. Regarding automatic rotation, it is adjusted according to the length and number of the ticklables and the width of the chart plot area. You may check in MS Excel manually as when you reduce the width of plot area via mouse pointer, the tick labels direction will be changed from horizontal to inclined and vice versa if you do the opposite.

Based on category range of cells (e.g., “A2:A17”), you may evaluate and count the rows and if there would be lots of labels created, you may extend the chart width/height accordingly. See the line and check second last and last parameter (which you may change to more higher value accordingly):

worksheet.getCharts().add(ChartType.COLUMN, 4, 4, 48, 25);

Moreover, you may extend the plot area or chart area via the APIs after you have created the chart. You may try the following attributes or other relevant attributes for the task:

chart.getChartObject().setWidth()
chart.getChartObject().setHeight()
chart.getPlotArea().setWidth()
chart.getPlotArea().setHeight()

Hope, this helps a bit.