Problem with AutoFitColumn and with header font size when using charts


#1

Hi,

1st problem: The text in the header row changes its size from 14 to 8 when using charts. In my case that happens, when the worksheet is repainted. After the sheet has been loaded for the first time, its size is correct. But if I make a tab change and go back to the excel sheet again, the size of the header text changed.

I wrote a simple test app, that demonstrates that problem (see code section below)

2nd problem: In the generated document sometimes the autoFitColumn method doesn’t work properly; that means that the first character overlaps the column separation line to the left. But I don’t have any idea why.

I would appreciate any help
Michael Mann

CODE SECTION
============
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Reflection;
using System.Data;
using System.IO;
using System.Diagnostics;

using Excel = Aspose.Excel;

namespace ExcelError
{
///


/// Summary description for Form1.
///

public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
///
/// Required designer variable.
///

private System.ComponentModel.Container components = null;
private System.Windows.Forms.RadioButton rbWithChart;
private System.Windows.Forms.RadioButton rbJustData;

#region members

Excel.Excel _excel = null;

#endregion

#region properties
///
/// Access the excel page
///

public Excel.Excel ExcelSheet
{
get {return _excel;}
set {_excel = value;}
}

#endregion

public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//
}

///
/// Clean up any resources being used.
///

protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.rbWithChart = new System.Windows.Forms.RadioButton();
this.rbJustData = new System.Windows.Forms.RadioButton();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(16, 72);
this.button1.Name = “button1”;
this.button1.TabIndex = 0;
this.button1.Text = “Generate”;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// rbWithChart
//
this.rbWithChart.Checked = true;
this.rbWithChart.Location = new System.Drawing.Point(144, 24);
this.rbWithChart.Name = “rbWithChart”;
this.rbWithChart.TabIndex = 1;
this.rbWithChart.TabStop = true;
this.rbWithChart.Text = “with chart”;
//
// rbJustData
//
this.rbJustData.Location = new System.Drawing.Point(16, 24);
this.rbJustData.Name = “rbJustData”;
this.rbJustData.TabIndex = 1;
this.rbJustData.Text = “just data”;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(472, 109);
this.Controls.Add(this.rbWithChart);
this.Controls.Add(this.button1);
this.Controls.Add(this.rbJustData);
this.Name = “Form1”;
this.Text = “Form1”;
this.ResumeLayout(false);

}
#endregion


#region methods

///
/// The main entry point for the application.
///

[STAThread]
static void Main()
{
Application.Run(new Form1());

Form1 form = new Form1();
}

///
///
///

private void Generate()
{
try
{
string baseDir = @“d:\mywork\excelerror”;
StreamReader rdr = new StreamReader(baseDir + “aspose.excel.lic”, System.Text.Encoding.UTF7);
ExcelSheet = new Excel.Excel(rdr);

string path = baseDir + @“resources\template.xls”;
ExcelSheet.Open(path);
ExcelSheet.Worksheets[0].Cells[0, 0].PutValue(null);

// add header
Excel.Worksheet wks = ExcelSheet.Worksheets[0];

wks.Cells[0, 0].PutValue(“test string that changes its size on tab change”);

// add a chart
wks.Cells[2,1].PutValue(“Income”);
wks.Cells[3,0].PutValue(“Company A”);
wks.Cells[4,0].PutValue(“Company B”);
wks.Cells[5,0].PutValue(“Company C”);

wks.Cells[3,1].PutValue(10000);
wks.Cells[4,1].PutValue(20000);
wks.Cells[5,1].PutValue(30000);

if (this.rbWithChart.Checked == true)
{
int chartIndex = wks.Charts.Add(Excel.ChartType.Column, 9, 4, 20, 12);

Excel.Chart chart = wks.Charts[chartIndex];
chart.NSeries.Add(“B4:B6”, true);
chart.NSeries.CategoryData = “A4:A6”;

Excel.ASeries aSeries = chart.NSeries[0];
aSeries.Name = “=B3”;
chart.IsLegendShown = true;
chart.Title.Text = “Income Analysis”;

chart.Title.Text = “Testchart”;
chart.Legend.Position = Excel.LegendPositionType.Right;
}

ExcelSheet.Save(baseDir + “test.xls”, Excel.FileFormatType.Excel97);

Process proc = new Process();
proc.StartInfo.FileName = baseDir + “test.xls”;
proc.StartInfo.UseShellExecute = true;
proc.Start();
}
catch(Exception x)
{
MessageBox.Show(x.Message);
}
}

///
///
///

///
///
///
///
///
///
///
///
///
///
internal Excel.Style GetXLStyle(Excel.Excel excel, bool bold, bool italic, int format, string customFormat, Excel.TextAlignmentType horzAlignment, Color color, Color backColor, string fontName)
{
string key = String.Format(
“{0}:{6}:{1}:{2}:{5}:{3}:{4}:{7}”,
format.ToString(),
color.ToString(),
bold.ToString(),
backColor.ToString(),
horzAlignment.ToString(),
italic.ToString(),
(customFormat == null ? “” : customFormat),
(fontName == null ? “” : fontName)
);

Excel.Style style = null;

try { style = excel.Styles[key]; }
catch {}

if(style == null)
{
style = excel.Styles[excel.Styles.Add()];

style.Name = key;
style.Number = (byte)format;
style.HorizontalAlignment = horzAlignment;

if(customFormat != null)
{
style.Custom = customFormat;
}

if(backColor != Color.Empty)
{
style.ForegroundColor = backColor;
}

if(fontName != null && fontName.Length > 0)
{
style.Font.Name = fontName;
}
style.Font.IsBold = bold;
style.Font.IsItalic = italic;

if(color != Color.Empty)
{
style.Font.Color = color;
}
}

return(style);
}

#endregion

private void button1_Click(object sender, System.EventArgs e)
{
Generate();
}
}
}


#2

Dear Michael,

Sorry for the problem.

I test your case in my machine and it works fine. What’s the version of your Aspose.Excel? The newest hotfix is 1.8.2.

I also guess your first problem is cause by MS Excel. Have you tried it in another machine?

If it still exists, please email me your template file.

About your second problem, I guess that is cause by custom number format. MS Excel supports more custom number format than Dotnet. So some custom number formats are not supported in Aspose.Excel. So the width of a cell might not be calculated correctly.


#3

Laurence,

Michael is a colleague of mine and I was able to reproduce both problems using version 1.8.1 of Aspose.Excel. However we have not checked the latest version, yet.

I will email you a ZIP archive containing both the template we’re using and an Excel sheet generated by 1.8.1. that shows the aforementioned Font sizing “bug”.

Kai


#4

Hi, Kai and Michael.

Thanks for your email. That helps me detect the problem.

I enhanced Chart API in 1.7 and 1.8. A small bug caused this font size problem. It’s fixed now.

In 1.6.3.3, I used GDI functions to calculate string width. That’s more accurate but slow in dotnet environment. So now I use GDI+. That’s faster with a factor of 10. But the result is not so accurate. I will try to find a fast and accurate approach for AutoFit methods.

These two problem will be solved ASAP. There are some other features to be added, so please wait a few days for the Hotfix 1.8.3. It will be released before the end of next week.


#5

Laurence,

thanks for your reply. Looking forward to the next HotFix

Regards

Kai