X axis labels not exporting to and from Excel as displayed

Tagged: 

  • AaronH FP 1 month, 3 weeks ago

    Hello,
    I found two issues with exporting and then opening charts (to and from Excel).

    Issue #1

    When I export a chart to Excel via the SaveExcel method (

    fpSpread1.SaveExcel($@”c:\temp\SpreadwithChart{DateTime.Now.TimeOfDay.Ticks}.xlsx”, FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);

    ), X axis labels are not the same as they were displaying in Spread. When I open the Excel file, the Major Units is set to 5 months and Base is set to Months. If I change those to 3 Months and Days, the labels display as they did in Spread; they show the exact values in the category range of cells.

    Issue #2

    When I try to open the same file that I created via Spread, the X Axis labels are all black.

    Attached is a sample app to generate the issues and two image files that show the issues.

    Thanks,
    Aaron

    Attachments:
    You must be logged in to view attched files.
  • AaronH FP 1 month, 2 weeks ago

    I’m using Spread 9.

  • 23p
    GinaDan23p 1 month, 2 weeks ago

    Hi,

    I was not able to unzip the file. Could you try uploading the file again? If you have a small code sample for the chart, you could also copy and paste that code. That would probably be enough to test this.

    Thanks, Gina

  • AaronH FP 1 month, 2 weeks ago

    Apparently I cannot upload files, at least not from behind my company’s firewall. So here’s the code:
    Form1.cs

    using FarPoint.Win.Chart;
    using FarPoint.Win.Spread.Chart;
    using FarPoint.Win.Spread.Model;
    using System;
    using System.Linq;
    using System.Collections.Generic;
    using System.Drawing;
    using System.Windows.Forms;
    using System.IO;
    
    namespace TestChart
    {
        public partial class Form1 : Form
        {
            private int rowIndex  = 0;
            List<Tuple> Data = new List<Tuple>();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void CreateData()
            {
                //Create some data
                rowIndex = 0;
                Data.Clear();
                Random r = new Random();
                DateTime dt = new DateTime(2001, 3, 31);
                for (int i = 0; i < 40; i++)
                {
                   dt = dt.AddDays(1).AddMonths(3).AddDays(-1);
                    Data.Add(new Tuple(dt, Math.Round( i + 1 * r.NextDouble(), 2),
                        i > 33 ? (double?)Math.Round((i + 1 * r.NextDouble()), 2) : null,
                        i > 27 ? (double?)Math.Round((i + 1 * r.NextDouble()), 2) : null,
                        i > 19 ? (double?)Math.Round((i + 1 * r.NextDouble()), 2) : null));
                }
    
                for (int i = 0; i < Data.Count; i++)
                {
                    sv.SetText(rowIndex, 0, Data[i].Item1.ToShortDateString());
                    sv.SetValue(rowIndex, 1, Data[i].Item2);
                    sv.SetValue(rowIndex, 2, Data[i].Item3);
                    sv.SetValue(rowIndex, 3, Data[i].Item4);
                    sv.SetValue(rowIndex, 4, Data[i].Item5);
                    rowIndex++;
                }
            }
    
            private void Success_Click(object sender, EventArgs e)
            {
                CreateData();
    
                if (sender == btnFail)
                {
                    int increment = 5;
                    sv.Rows.Add(0, increment);
                    rowIndex += increment;
                }
                
                CellRange rng = new CellRange(rowIndex – Data.Count, 1, Data.Count, 4);
                SpreadChart crt = sv.AddChart(rng, typeof(LineSeries), 800, 600, 0, 0);
    
                string categoryForumla = $"Sheet1!$A${rowIndex – Data.Count + 1 }:$A${rowIndex }";
                try
                {
                    crt.CategoryFormula = categoryForumla;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                crt.DataSetting.EmptyValueStyle = EmptyValueStyle.Gaps; //must have this for  t.Item3.HasValue) } pt.”;
                y.Series[2].SeriesName = $”{Data.Count(t => t.Item4.HasValue) } pt.”;
                y.Series[3].SeriesName = $”{Data.Count(t => t.Item5.HasValue) } pt.”;
                crt.Model.LegendAreas[0].LegendPos = LegendPos.Bottom;
                crt.Model.LegendAreas[0].Vertical = false;
            }
    
            private void Save_Click(object sender, EventArgs e)
            {
                fpSpread1.SaveExcel($@”c:\temp\SpreadwithChart{DateTime.Now.TimeOfDay.Ticks}.xlsx”, FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
            }
    
            private void openToolStripButton_Click(object sender, EventArgs e)
            {
                OpenFileDialog fd = new OpenFileDialog();
                fd.ShowDialog(this);
                if (File.Exists(fd.FileName))
                {
                    fpSpread1.OpenExcel(fd.FileName);
                }
                
            }
        }
    }

    Form1.Designer.cs code

    namespace TestChart
    {
        partial class Form1
        {
            /// 
            /// Required designer variable.
            /// 
            private System.ComponentModel.IContainer components = null;
    
            /// 
            /// Clean up any resources being used.
            /// 
            /// true if managed resources should be disposed; otherwise, false.
            protected override void Dispose(bool disposing)
            {
                if (disposing && (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()
            {
                System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
                this.fpSpread1 = new FarPoint.Win.Spread.FpSpread();
                this.sv = new FarPoint.Win.Spread.SheetView();
                this.btnSuccess = new System.Windows.Forms.Button();
                this.btnFail = new System.Windows.Forms.Button();
                this.toolStrip1 = new System.Windows.Forms.ToolStrip();
                this.tsbCreateData = new System.Windows.Forms.ToolStripButton();
                this.saveToolStripButton = new System.Windows.Forms.ToolStripButton();
                this.openToolStripButton = new System.Windows.Forms.ToolStripButton();
                ((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).BeginInit();
                ((System.ComponentModel.ISupportInitialize)(this.sv)).BeginInit();
                this.toolStrip1.SuspendLayout();
                this.SuspendLayout();
                // 
                // fpSpread1
                // 
                this.fpSpread1.AccessibleDescription = “”;
                this.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill;
                this.fpSpread1.Location = new System.Drawing.Point(0, 0);
                this.fpSpread1.Name = “fpSpread1″;
                this.fpSpread1.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
                this.sv});
                this.fpSpread1.Size = new System.Drawing.Size(958, 698);
                this.fpSpread1.TabIndex = 0;
                // 
                // sv
                // 
                this.sv.Reset();
                this.sv.SheetName = “Sheet1″;
                // 
                // btnSuccess
                // 
                this.btnSuccess.Location = new System.Drawing.Point(492, 223);
                this.btnSuccess.Name = “btnSuccess”;
                this.btnSuccess.Size = new System.Drawing.Size(75, 23);
                this.btnSuccess.TabIndex = 1;
                this.btnSuccess.Text = “Create Data”;
                this.btnSuccess.UseVisualStyleBackColor = true;
                this.btnSuccess.Click += new System.EventHandler(this.Success_Click);
                // 
                // btnFail
                // 
                this.btnFail.Location = new System.Drawing.Point(388, 200);
                this.btnFail.Name = “btnFail”;
                this.btnFail.Size = new System.Drawing.Size(75, 23);
                this.btnFail.TabIndex = 1;
                this.btnFail.Text = “Fail”;
                this.btnFail.UseVisualStyleBackColor = true;
                this.btnFail.Click += new System.EventHandler(this.Success_Click);
                // 
                // toolStrip1
                // 
                this.toolStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
                this.tsbCreateData,
                this.saveToolStripButton,
                this.openToolStripButton});
                this.toolStrip1.Location = new System.Drawing.Point(0, 0);
                this.toolStrip1.Name = “toolStrip1″;
                this.toolStrip1.Size = new System.Drawing.Size(958, 25);
                this.toolStrip1.TabIndex = 2;
                this.toolStrip1.Text = “toolStrip1″;
                // 
                // tsbCreateData
                // 
                this.tsbCreateData.Image = ((System.Drawing.Image)(resources.GetObject(“tsbCreateData.Image”)));
                this.tsbCreateData.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.tsbCreateData.Name = “tsbCreateData”;
                this.tsbCreateData.Size = new System.Drawing.Size(88, 22);
                this.tsbCreateData.Text = “Create Data”;
                this.tsbCreateData.Click += new System.EventHandler(this.Success_Click);
                // 
                // saveToolStripButton
                // 
                this.saveToolStripButton.Image = ((System.Drawing.Image)(resources.GetObject(“saveToolStripButton.Image”)));
                this.saveToolStripButton.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.saveToolStripButton.Name = “saveToolStripButton”;
                this.saveToolStripButton.Size = new System.Drawing.Size(51, 22);
                this.saveToolStripButton.Text = “&Save”;
                this.saveToolStripButton.Click += new System.EventHandler(this.Save_Click);
                // 
                // openToolStripButton
                // 
                this.openToolStripButton.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
                this.openToolStripButton.Image = ((System.Drawing.Image)(resources.GetObject(“openToolStripButton.Image”)));
                this.openToolStripButton.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.openToolStripButton.Name = “openToolStripButton”;
                this.openToolStripButton.Size = new System.Drawing.Size(23, 22);
                this.openToolStripButton.Text = “&Open”;
                this.openToolStripButton.Click += new System.EventHandler(this.openToolStripButton_Click);
                // 
                // Form1
                // 
                this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
                this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
                this.ClientSize = new System.Drawing.Size(958, 698);
                this.Controls.Add(this.toolStrip1);
                this.Controls.Add(this.fpSpread1);
                this.Controls.Add(this.btnFail);
                this.Controls.Add(this.btnSuccess);
                this.Name = “Form1″;
                this.Text = “Form1″;
                ((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).EndInit();
                ((System.ComponentModel.ISupportInitialize)(this.sv)).EndInit();
                this.toolStrip1.ResumeLayout(false);
                this.toolStrip1.PerformLayout();
                this.ResumeLayout(false);
                this.PerformLayout();
    
            }
    
            #endregion
    
            private FarPoint.Win.Spread.FpSpread fpSpread1;
            private FarPoint.Win.Spread.SheetView sv;
            private System.Windows.Forms.Button btnSuccess;
            private System.Windows.Forms.Button btnFail;
            private System.Windows.Forms.ToolStrip toolStrip1;
            private System.Windows.Forms.ToolStripButton tsbCreateData;
            private System.Windows.Forms.ToolStripButton saveToolStripButton;
            private System.Windows.Forms.ToolStripButton openToolStripButton;
        }
    }
  • 23p
    GinaDan23p 1 month, 2 weeks ago

    Hi,

    Is there any other code related to this?

    List<Tuple> Data = new List<Tuple>();

    I added most of the code to my test project, but I got errors about static types not being used as arguments with this line.

    You could also try sending a zip file to the support mailbox (if you are allowed to email files). The support email is spread.support@grapecity.com.

    Thanks, Gina

  • 23p
    GinaDan23p 1 month, 2 weeks ago

    Hi,

    I did get the project and I am seeing the same issues. I will check with the product manager and see if he thinks this is a bug.

    Thanks, Gina

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to create new topics.