How to create a stacked bar chart with total label?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Dankan1890 · 2026-04-03 02:57

Hi everyone, I'm new to Free Pascal and Lazarus, and I was trying to
create a stacked bar chart with the total displayed at the top using
fpspreadsheet.
For testing, I used the one in the '\other\chart' folder, first
looking for an option that would display the total as the label for
the last series, but it always uses the y-value as the reference.
I also tried inserting an additional series as a line, but when
exporting to xlsx, the internal XML has reference issues regarding the
chart type, telling me that the opening tag doesn't match the closing
tag (exporting to osd works).
I'm open to suggestions on how to resolve this, if possible.

----------------------------------------------------------------------
dsiders · 2026-04-03 03:20

Quote from: Dankan1890 on April 03, 2026, 04:57:34 amHi everyone, I'm
new to Free Pascal and Lazarus, and I was trying to create a stacked
bar chart with the total displayed at the top using fpspreadsheet.
For testing, I used the one in the '\other\chart' folder, first
looking for an option that would display the total as the label for
the last series, but it always uses the y-value as the reference.
I also tried inserting an additional series as a line, but when
exporting to xlsx, the internal XML has reference issues regarding the
chart type, telling me that the opening tag doesn't match the closing
tag (exporting to osd works).
I'm open to suggestions on how to resolve this, if possible.

I don;t know anything about FpSpreadsheet.... but the forum search
does.

https://forum.lazarus.freepascal.org/index.php/topic,65969.msg503558.h
tml#msg503558

----------------------------------------------------------------------
wp · 2026-04-03 09:38

Quote from: Dankan1890 on April 03, 2026, 04:57:34 amI was trying to
create a stacked bar chart with the total displayed at the top using
fpspreadsheet.
Is Excel able to do this itself? Not directly. The idea with the
auxiliary line series is good: I added a line series displaying the
totals and had to switch the chart type to "combined". But
fpspreadsheet is not prepared for this. I can try to implement it, but
combinations of series types in Excel was kind of a mess.

----------------------------------------------------------------------
Dankan1890 · 2026-04-03 15:52

Quote from: wp on April 03, 2026, 11:38:24 amIs Excel able to do this
itself? Not directly.Excel supports combination charts, although
creating them is anything but intuitive and easy.

However, I think I've found a good compromise, moving the bar series
to the primary y-axis and the line to the secondary one.
(The only "flaw" is that you can't remove the series from the legend;
reading the code, I saw that it's not supported yet.)

Below is the code; maybe it can be useful to someone else:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
barchart_stacked_write_demo; {.$DEFINE DARK_MODE} uses  SysUtils, 
fpspreadsheet, fpstypes, fpsUtils, fpschart, xlsxooxml,
fpsopendocument; procedure WriteHelp;begin  WriteLn('SYNTAX:
barchart_stacked_write_demo [rotated]');  WriteLn('  rotated
........... hoizontal bars, otherwise: vertical bar');  WriteLn('
 percentage ........ stacked percentage, otherwise: normal
stacking');  Halt;end; const  FILE_NAME = 'bars-stacked';var  book:
TsWorkbook;  sheet: TsWorksheet;  ch: TsChart;  ser: TsChartSeries; 
fn, dir: String;  i: Integer;  rotated: Boolean = false; 
stackedPercentage: Boolean = false;begin  if (ParamCount = 1) and
((ParamStr(1) = '--help') or (ParamStr(1) = '-h')) then   
WriteHelp;   fn := FILE_NAME;  for i := 1 to ParamCount do    case
lowercase(ParamStr(i)) of      'rotated':        begin         
rotated := true;          fn := fn + '-rotated';        end;     
'percentage':        begin          stackedPercentage := true;       
 fn := fn + '-percentage';        end;    end;   dir :=
ExtractFilePath(ParamStr(0)) + 'files/';  ForceDirectories(dir);  
book := TsWorkbook.Create;  try    // worksheet    sheet :=
book.AddWorksheet('bar_series_stacked');     // Enter data   
sheet.WriteText( 0, 0, 'Stacked bar series demo');    sheet.WriteFont(
0, 0, '', 12, [fssBold], scBlack);    sheet.WriteText( 2, 0,
'Quarters'); sheet.WriteText  ( 2, 1, 'Product A'); sheet.WriteText  (
2, 2, 'Product B'); sheet.WriteText  ( 2, 3, 'Totals');   
sheet.WriteText( 3, 0, 'Q1/2022');  sheet.WriteNumber( 3, 1, 125);   
    sheet.WriteNumber( 3, 2, 207); sheet.WriteFormula( 3, 3,
'=SUM(B4:C4)');    sheet.WriteText( 4, 0, 'Q2/2022');
 sheet.WriteNumber( 4, 1, 176);         sheet.WriteNumber( 4, 2, 199);
sheet.WriteFormula( 4, 3, '=SUM(B5:C5)');    sheet.WriteText( 5, 0,
'Q3/2022');  sheet.WriteNumber( 5, 1, 264);         sheet.WriteNumber(
5, 2, 194); sheet.WriteFormula( 5, 3, '=SUM(B6:C6)');   
sheet.WriteText( 6, 0, 'Q4/2022');  sheet.WriteNumber( 6, 1, 311);   
    sheet.WriteNumber( 6, 2, 183); sheet.WriteFormula( 6, 3,
'=SUM(B7:C7)');     // Create chart: left/top in cell D4, 120 mm x 100
mm    ch := sheet.AddChart(120, 100, 2, 6);     // Chart properties  
if stackedPercentage then      ch.StackMode :=csmStackedPercentage   
else      ch.StackMode := csmStacked;    if rotated then     
ch.RotatedAxes := rotated;    ch.Border.Style := clsNoLine;   
ch.Title.Caption := 'Product Sales';    ch.Title.Font.Style :=
[fssBold];    ch.Legend.Border.Style := clsNoLine;   
ch.XAxis.Title.Caption := '';    ch.YAxis.Title.Caption := 'Quarter'; 
  ch.YAxis.AxisLine.Color := ChartColor(scSilver);   
ch.YAxis.MajorTicks := [];     // Add 1st bar series ("Product A")   
ser := TsBarSeries.Create(ch);    ser.YAxis := calPrimary;   
ser.SetTitleAddr(2, 1);    ser.SetLabelRange(3, 0, 6, 0);   
ser.SetYRange(3, 1, 6, 1);    ser.Fill.Color := ChartColor($3810F3); 
 ser.Line.SelectNoLine;     // Add 2nd bar series ("Product B")    ser
:= TsBarSeries.Create(ch);    ser.YAxis := calPrimary;   
ser.SetTitleAddr(2, 2);    ser.SetLabelRange(3, 0, 6, 0);   
ser.SetYRange(3, 2, 6, 2);    ser.Fill.Color := ChartColor($4200A8); 
 ser.Line.Style := clsNoLine;     // Add 3rd line series ("Totals")  
ser := TsLineSeries.Create(ch);    ser.YAxis := calSecondary;   
ser.SetTitleAddr(2, 3);    ser.SetLabelRange(3, 0, 6, 0);   
ser.SetYRange(3, 3, 6, 3);    ser.Line.Style := clsNoLine;   
ser.DataLabels:= [cdlValue];    ser.LabelPosition:= lpAbove;    
book.WriteToFile(dir + fn + '.xlsx', true);    WriteLn('Data saved
with chart in ', fn + '.xlsx');     book.WriteToFile(dir + fn +
'.ods', true);    WriteLn('Data saved with chart in ', fn + '.ods'); 
finally    book.Free;  end;end.

----------------------------------------------------------------------
wp · 2026-04-06 10:06

In the new commit to the svn version of FPSpreadsheet, writing of xlsx
charts with combined series types is fixed (hopefully). Now there is
not need for a secondary axis. See the new demo
"barchart_stacked_total_write_demo" in the examples/other/chart folder
of the FPSpreadsheet installation.

----------------------------------------------------------------------
Dankan1890 · 2026-04-06 19:48

Thanks!
Tested it and it works perfectly.

Just for info, to hide the standard auxiliary series text displayed in
the legend, I wrote a blank space in the cell pointed to by the title.