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();
}
}
}