Chart type Column, shows negative data values as positive values in chart.

[bengordon]

Scenario.
I use Python openpyxl to manipulate and analyse data from my energy company and solar system and
store the results in .xlsx worbooks/spreadsheets and sometimes generate column charts from the data.
For one set of data, the data contains both positive and negative values. When the column chart is
generated through openpyxl, and I open the workbook with LibreOffice Calc, the chart shows all the
negative values as positive values in the chart.
Note, that the y-axis does show negative values.
To analyse this issue, I generated some simple data and generated a column chart from this data to
to try and understand what the problem is.

Analysis.
As you can see in example above, the data contains both positive and negative values, but the column
chart only shows positive columns. The y-axis does show negative values, so somehow it is aware
that the data is negative.
If I manually change a positive value to a different value and make it negative, the column changes
according to the value, but remains positive, i.e. above the x-axis.

When I open the workbook with OpenOffice Spreadsheets or with MS Excel, the chart displays as
expected, the chart shows the negative values with columns being below the x-axis.

When I open the above workbook with LibreOffice Calc, and then manually create a second column chart
of the data, this second chart displays as expected, the column chart shows the negative values with
columns being below the x-axis.

If I run the Python script again, but generate a Line chart, the line chart does show the negative
values below the x-axis.

If I manually change the type of chart from the original generated column chart to line chart, the
line chart does show the negative values below the x-axis.
If I manually change the type of chart from the original generated column chart to bar chart, the
bar chart shows the negative values as positive bars, to the right of y-axis.

I have been checking the properties and any other information I could find within LibreOffice and
googled for the issue but have not been able to determine why the generated column chart is
displaying the results incorrect.

I have upgraded my LibreOffice to the latest release, v26.2.4.2 (X86_64) build 26200.
I run this on Windows 11.

Because the generated workbook opens as expected within OpenOffice and MS Excel, I believe the
problem is within LibreOffice Calc.

Any assistance or suggestions welcome to resolve this issue.

Thanks,
Ben

[karolus]

I miss your code which creates the .xlsx-file and the .xlsx-file itself with the issue!

[bengordon]

Hi Karolus,

I did not add it as it didn’t seem important. Find it below:

# import openpyxl module
import openpyxl

# import BarChart class from openpyxl.chart sub_module
from openpyxl.chart import BarChart,LineChart,Reference

# Call a Workbook() function of openpyxl
# to create a new blank Workbook object
wb = openpyxl.Workbook()

# Get workbook active sheet
# from the active attribute.
sheet = wb.active

# write -5 to 6 in 1st column of the active sheet
for i in range(-5, 6 ):
    sheet.append([i])

# create data for plotting
values = Reference(sheet, min_col = 1, min_row = 1,
                         max_col = 1, max_row = 11)

# Create object of BarChart class
chart = BarChart()

# adding data to the Bar chart object
chart.add_data(values)


# add chart to the sheet
# the top-left corner of a chart
# is anchored to cell E2 .
sheet.add_chart(chart, "E2")

# save the file
wb.save("barChart.xlsx")