Generated file--charts squish when saved in .xls format, ok in .xl

[Jeff Donkersgoed]

I've got a c# program that's used to dynamically create spreadsheets. At the
end, I save the workbook both as an .xlsx file as well as a .xls file. My
problem is that the charts in the .xls file, although the box remains the
same size, the contents (bars, height of the vertical axis) get
"squished"--that is, there is more space in the bottom half then there should
be causing the rest to resize to fit (happens most noticably vertically, but
also slightly horizontally). I've narrowed it down to finding out that the
presence of textboxes in the chart "causes" it to squish. If I don't have
the textboxes, the chart appears normal. Can anyone think of something that
may cause this? I'm opening both files in Excel 2007.

Here is the code relating to text box creation I'm using:

Excel.Shape label =
chartobj.Chart.Shapes.AddLabel(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 75, 15, 140, 140);
label.Select(Type.Missing);
label.TextFrame.Characters(Missing.Value, Missing.Value).Text = "Very High
Risk";
label.TextFrame.AutoSize = true;
label.ControlFormat.PrintObject = true;

chartobj.Chart.ChartArea.AutoScaleFont = false;
Excel.TextBoxes textBoxes =
(Excel.TextBoxes)chartobj.Chart.TextBoxes(Type.Missing);
textBoxes.Font.Name = "Arial";
textBoxes.Font.Size = 16;
textBoxes.AutoScaleFont = false;
textBoxes.PrintObject = true;

 

[Jeff Donkersgoed]

I've found the problem. There seems to be an Excel bug when saving a chart
with the zoom on and a textbox inserted. It can be easily reproduced in
Excel 2007 (not sure if this is specific to 2003 compatability mode or if it
happens in native 2003 as well). This is not an issue saving as .xlsx.
1. Create a couple rows of data and create a bar chart (I used a 2D stacked
bar).
2. From Chart Tools-->Layout, insert a Text Box in the chart.
3. Set View-->Zoom to 50%.
4. Save as 2003 document and close.
5. Open the document
You will see that the while the chart box is the same size, the contents
inside have shrunk.
6. Save, close and open repeatedly and the contents keep shrinking away.
If step 2 is omitted, there is no problem. The issue is with the text box
combined with the zoom.