Search Results for

    Show / Hide Table of Contents

    Reading Excel files (C# / netframework)

    Note

    This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\20.Reading Files and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​csharp/​VS2022/​netframework/​Modules/​10.​API/​20.​Reading Files

    Overview

    A demo showing how to read the contents of an Excel file using FlexCel.

    Concepts

    • To read an Excel file you use the XlsFile class, from where you can read and write to any Excel 2.0 or newer file.

    • To get the value for a single cell, use XlsFile.GetCellValue.

    • To get the value for a cell when looping a full sheet, use XlsFile.GetCellValueIndexed. It is faster than using GetCellValue since you will only read the used cells.

    • XlsFile.GetCellValue and XlsFile.GetCellValueIndexed can return one of the following objects:

      • null

      • Double

      • Boolean

      • String

      • TRichString

      • TFormula

    • With GetCellValue and GetCellValueIndexed you will get the actual values. But if you want to actually display formatted data (for example if you have the number 2 with 2 decimals, and you want to display 2.00 instead of 2), you need to use other methods. There are 2 ways to do it:

      1. XlsFile.GetStringFromCell will return a rich string with the cell formatted.

      2. FormatValue will format an object with a specified format and then return the corresponding rich string. TFlxNumberFormat.FormatValue is used internally by GetStringFromCell.

    • In Excel, Dates are doubles. The only difference between a date and a double is on the format on the cell. With FormatValue you can get the actual string that is displayed on Excel. Also, to convert this double to a DateTime, you can use FlxDateTime.FromOADate.

    Files

    AssemblyInfo.cs

    using System.Reflection;
    using System.Runtime.CompilerServices;
    
    //
    // General Information about an assembly is controlled through the following 
    // set of attributes. Change these attribute values to modify the information
    // associated with an assembly.
    //
    [assembly: AssemblyTitle("")]
    [assembly: AssemblyDescription("")]
    [assembly: AssemblyConfiguration("")]
    [assembly: AssemblyCompany("")]
    [assembly: AssemblyProduct("")]
    [assembly: AssemblyCopyright("(c) 2002 - 2025 TMS Software")]
    [assembly: AssemblyTrademark("")]
    [assembly: AssemblyCulture("")]
    
    //
    // Version information for an assembly consists of the following four values:
    //
    //      Major Version
    //      Minor Version 
    //      Build Number
    //      Revision
    //
    // You can specify all the values or you can default the Revision and Build Numbers 
    // by using the '*' as shown below:
    
    [assembly: AssemblyVersion("7.24.0.0")]
    
    //
    // In order to sign your assembly you must specify a key to use. Refer to the 
    // Microsoft .NET Framework documentation for more information on assembly signing.
    //
    // Use the attributes below to control which key is used for signing. 
    //
    // Notes: 
    //   (*) If no key is specified, the assembly is not signed.
    //   (*) KeyName refers to a key that has been installed in the Crypto Service
    //       Provider (CSP) on your machine. KeyFile refers to a file which contains
    //       a key.
    //   (*) If the KeyFile and the KeyName values are both specified, the 
    //       following processing occurs:
    //       (1) If the KeyName can be found in the CSP, that key is used.
    //       (2) If the KeyName does not exist and the KeyFile does exist, the key 
    //           in the KeyFile is installed into the CSP and used.
    //   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
    //       When specifying the KeyFile, the location of the KeyFile should be
    //       relative to the project output directory which is
    //       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
    //       located in the project directory, you would specify the AssemblyKeyFile 
    //       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
    //   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
    //       documentation for more information on this.
    //
    [assembly: AssemblyDelaySign(false)]
    [assembly: AssemblyKeyFile("")]
    [assembly: AssemblyKeyName("")]
    

    Form1.cs

    using System;
    using System.Drawing;
    using System.Collections;
    using System.ComponentModel;
    using System.Windows.Forms;
    using System.Data;
    
    using FlexCel.Core;
    using FlexCel.XlsAdapter;
    
    namespace ReadingFiles
    {
        /// <summary>
        /// A demo on how to read a file from FlexCel and display the results.
        /// </summary>
        public partial class mainForm: System.Windows.Forms.Form
        {
            public mainForm()
            {
                InitializeComponent();
                ResizeToolbar(mainToolbar);
            }
    
            private void ResizeToolbar(ToolStrip toolbar)
            {
    
                using (Graphics gr = CreateGraphics())
                {
                    double xFactor = gr.DpiX / 96.0;
                    double yFactor = gr.DpiY / 96.0;
                    toolbar.ImageScalingSize = new Size((int)(24 * xFactor), (int)(24 * yFactor));
                    toolbar.Width = 0; //force a recalc of the buttons.
                }
            }
    
            private void btnExit_Click(object sender, System.EventArgs e)
            {
                Close();
            }
    
            private void btnOpenFile_Click(object sender, System.EventArgs e)
            {
                if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
                ImportFile(openFileDialog1.FileName, btnFormatValues.Checked);
            }
    
            private void ImportFile(string FileName, bool Formatted)
            {
                try
                {
                    //Open the Excel file.
                    XlsFile xls = new XlsFile(false);
                    DateTime StartOpen = DateTime.Now;
                    xls.Open(FileName);
                    DateTime EndOpen = DateTime.Now;
    
                    //Set up the Grid
                    DisplayGrid.DataBindings.Clear();
                    DisplayGrid.DataSource = null;
                    DisplayGrid.DataMember = null;
                    DataSet dataSet1 = new DataSet();
                    sheetCombo.Items.Clear();
    
                    //We will create a DataTable "SheetN" for each sheet on the Excel sheet.
                    for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
                    {
                        xls.ActiveSheet = sheet;
    
                        sheetCombo.Items.Add(xls.SheetName);
    
                        DataTable Data = dataSet1.Tables.Add("Sheet" + sheet.ToString());
                        Data.BeginLoadData();
                        try
                        {
                            int ColCount = xls.ColCount;
                            //Add one column on the dataset for each used column on Excel.
                            for (int c = 1; c <= ColCount; c++)
                            {
                                Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String));  //Here we will add all strings, since we do not know what we are waiting for.
                            }
    
                            string[] dr = new string[ColCount];
    
                            int RowCount = xls.RowCount;
                            for (int r = 1; r <= RowCount; r++)
                            {
                                Array.Clear(dr, 0, dr.Length);
                                //This loop will only loop on used cells. It is more efficient than looping on all the columns.
                                for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--)  //reverse the loop to avoid calling ColCountInRow more than once.
                                {
                                    int Col = xls.ColFromIndex(r, cIndex);
    
                                    if (Formatted)
                                    {
                                        TRichString rs = xls.GetStringFromCell(r, Col);
                                        dr[Col - 1] = rs.Value;
                                    }
                                    else
                                    {
                                        int XF = 0; //This is the cell format, we will not use it here.
                                        object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
    
                                        TFormula Fmla = val as TFormula;
                                        if (Fmla != null)
                                        {
                                            //When we have formulas, we want to write the formula result. 
                                            //If we wanted the formula text, we would not need this part.
                                            dr[Col - 1] = Convert.ToString(Fmla.Result);
                                        }
                                        else
                                        {
                                            dr[Col - 1] = Convert.ToString(val);
                                        }
                                    }
                                }
                                Data.Rows.Add(dr);
                            }
                        }
                        finally
                        {
                            Data.EndLoadData();
                        }
    
                        DateTime EndFill = DateTime.Now;
                        statusBar.Text = String.Format("Time to load file: {0}    Time to fill dataset: {1}     Total time: {2}", (EndOpen - StartOpen).ToString(), (EndFill - EndOpen).ToString(), (EndFill - StartOpen).ToString());
    
                    }
    
                    //Set up grid.
                    DisplayGrid.DataSource = dataSet1;
                    DisplayGrid.DataMember = "Sheet1";
                    sheetCombo.SelectedIndex = 0;
                    DisplayGrid.CaptionText = FileName;
    
                }
                catch
                {
                    DisplayGrid.CaptionText = "Error Loading File";
                    DisplayGrid.DataSource = null;
                    DisplayGrid.DataMember = "";
                    sheetCombo.Items.Clear();
                    throw;
                }
            }
    
            private void sheetCombo_SelectedIndexChanged(object sender, System.EventArgs e)
            {
                if ((sender as ComboBox).SelectedIndex < 0) return;
                DisplayGrid.DataMember = "Sheet" + ((sender as ComboBox).SelectedIndex + 1).ToString();
            }
    
            private void AnalizeFile(string FileName, int Row, int Col)
            {
                XlsFile xls = new XlsFile();
                xls.Open(FileName);
    
                int XF = 0;
                MessageBox.Show("Active sheet is \"" + xls.ActiveSheetByName + "\"");
                object v = xls.GetCellValue(Row, Col, ref XF);
    
                if (v == null)
                {
                    MessageBox.Show("Cell A1 is empty");
                    return;
                }
    
                //Here we have all the kind of objects FlexCel can return.
                switch (Type.GetTypeCode(v.GetType()))
                {
                    case TypeCode.Boolean:
                        MessageBox.Show("Cell A1 is a boolean: " + (bool)v);
                        return;
                    case TypeCode.Double:  //Remember, dates are doubles with date format.
                        TUIColor CellColor = Color.Empty;
                        bool HasDate, HasTime;
                        String CellValue = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, ref CellColor, xls, out HasDate, out HasTime).ToString();
    
                        if (HasDate || HasTime)
                        {
                            MessageBox.Show("Cell A1 is a DateTime value: " + FlxDateTime.FromOADate((double)v, xls.OptionsDates1904).ToString() + "\n" +
                                "The value is displayed as: " + CellValue);
                        }
                        else
                        {
                            MessageBox.Show("Cell A1 is a double: " + (double)v + "\n" +
                                "The value is displayed as: " + CellValue + "\n");
                        }
                        return;
                    case TypeCode.String:
                        MessageBox.Show("Cell A1 is a string: " + v.ToString());
                        return;
                }
    
                TFormula Fmla = v as TFormula;
                if (Fmla != null)
                {
                    MessageBox.Show("Cell A1 is a formula: " + Fmla.Text + "   Value: " + Convert.ToString(Fmla.Result));
                    return;
                }
    
                TRichString RSt = v as TRichString;
                if (RSt != null)
                {
                    MessageBox.Show("Cell A1 is a formatted string: " + RSt.Value);
                    return;
                }
    
                if (v is TFlxFormulaErrorValue)
                {
                    MessageBox.Show("Cell A1 is an error: " + TFormulaMessages.ErrString((TFlxFormulaErrorValue)v));
                    return;
                }
    
                throw new Exception("Unexpected value on cell");
    
            }
    
            private void btnInfo_Click(object sender, System.EventArgs e)
            {
                MessageBox.Show("This demo shows how to read the contents of an xls file\n" +
                    "The 'Open File' button will load an Excel file into a dataset. Depending on the button 'Format Values' it will load the actual values (this is the fastest) or the formatted values.\n" +
                    "The 'Format Values' button will modify how the files are read when you press 'Open File'. Formated values are slower, but they will look just how Excel shows them.\n" +
                    "The 'Value in Cell A1' button will load an Excel file and show the contents of cell a1 on the active sheet.");
            }
    
            /// <summary>
            /// This method will not do anything truly useful, but it alows you to see how to 
            /// process the different types of objects that GetCellValue can return
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void btnValueInCurrentCell_Click(object sender, EventArgs e)
            {
                if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
                AnalizeFile(openFileDialog1.FileName, 1, 1);
            }
        }
    }
    

    Form1.Designer.cs

    using System;
    using System.Drawing;
    using System.Collections;
    using System.ComponentModel;
    using System.Windows.Forms;
    using System.Data;
    using FlexCel.Core;
    using FlexCel.XlsAdapter;
    namespace ReadingFiles
    {
        public partial class mainForm: System.Windows.Forms.Form
        {
            private System.Windows.Forms.OpenFileDialog openFileDialog1;
            private System.Windows.Forms.DataGrid DisplayGrid;
            private System.Windows.Forms.Panel panel1;
            private System.Windows.Forms.Label label1;
            private System.Windows.Forms.ComboBox sheetCombo;
            private System.Windows.Forms.StatusBar statusBar;
            private System.ComponentModel.IContainer components = null;
    
            /// <summary>
            /// Clean up any resources being used.
            /// </summary>
            protected override void Dispose(bool disposing)
            {
                if (disposing)
                {
                    if (components != null)
                    {
                        components.Dispose();
                    }
                }
                base.Dispose(disposing);
            }
    
            #region Windows Form Designer generated code
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {
                System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(mainForm));
                this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
                this.DisplayGrid = new System.Windows.Forms.DataGrid();
                this.panel1 = new System.Windows.Forms.Panel();
                this.sheetCombo = new System.Windows.Forms.ComboBox();
                this.label1 = new System.Windows.Forms.Label();
                this.statusBar = new System.Windows.Forms.StatusBar();
                this.mainToolbar = new System.Windows.Forms.ToolStrip();
                this.btnOpenFile = new System.Windows.Forms.ToolStripButton();
                this.toolStripSeparator1 = new System.Windows.Forms.ToolStripSeparator();
                this.btnFormatValues = new System.Windows.Forms.ToolStripButton();
                this.toolStripSeparator2 = new System.Windows.Forms.ToolStripSeparator();
                this.btnValueInCellA1 = new System.Windows.Forms.ToolStripButton();
                this.btnExit = new System.Windows.Forms.ToolStripButton();
                this.btnInfo = new System.Windows.Forms.ToolStripButton();
                ((System.ComponentModel.ISupportInitialize)(this.DisplayGrid)).BeginInit();
                this.panel1.SuspendLayout();
                this.mainToolbar.SuspendLayout();
                this.SuspendLayout();
                // 
                // openFileDialog1
                // 
                this.openFileDialog1.DefaultExt = "xls";
                this.openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All " +
        "files|*.*";
                this.openFileDialog1.Title = "Open an Excel File";
                // 
                // DisplayGrid
                // 
                this.DisplayGrid.DataMember = "";
                this.DisplayGrid.Dock = System.Windows.Forms.DockStyle.Fill;
                this.DisplayGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
                this.DisplayGrid.Location = new System.Drawing.Point(0, 67);
                this.DisplayGrid.Name = "DisplayGrid";
                this.DisplayGrid.Size = new System.Drawing.Size(880, 372);
                this.DisplayGrid.TabIndex = 5;
                // 
                // panel1
                // 
                this.panel1.BackColor = System.Drawing.SystemColors.ControlDark;
                this.panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
                this.panel1.Controls.Add(this.sheetCombo);
                this.panel1.Controls.Add(this.label1);
                this.panel1.Dock = System.Windows.Forms.DockStyle.Top;
                this.panel1.Location = new System.Drawing.Point(0, 38);
                this.panel1.Name = "panel1";
                this.panel1.Size = new System.Drawing.Size(880, 29);
                this.panel1.TabIndex = 6;
                // 
                // sheetCombo
                // 
                this.sheetCombo.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
                this.sheetCombo.Location = new System.Drawing.Point(65, 3);
                this.sheetCombo.Name = "sheetCombo";
                this.sheetCombo.Size = new System.Drawing.Size(391, 21);
                this.sheetCombo.TabIndex = 1;
                this.sheetCombo.SelectedIndexChanged += new System.EventHandler(this.sheetCombo_SelectedIndexChanged);
                // 
                // label1
                // 
                this.label1.ForeColor = System.Drawing.SystemColors.HighlightText;
                this.label1.Location = new System.Drawing.Point(8, 8);
                this.label1.Name = "label1";
                this.label1.Size = new System.Drawing.Size(40, 23);
                this.label1.TabIndex = 0;
                this.label1.Text = "Sheet:";
                // 
                // statusBar
                // 
                this.statusBar.Location = new System.Drawing.Point(0, 439);
                this.statusBar.Name = "statusBar";
                this.statusBar.Size = new System.Drawing.Size(880, 22);
                this.statusBar.TabIndex = 7;
                // 
                // mainToolbar
                // 
                this.mainToolbar.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
                this.btnOpenFile,
                this.toolStripSeparator1,
                this.btnFormatValues,
                this.toolStripSeparator2,
                this.btnValueInCellA1,
                this.btnExit,
                this.btnInfo});
                this.mainToolbar.Location = new System.Drawing.Point(0, 0);
                this.mainToolbar.Name = "mainToolbar";
                this.mainToolbar.Size = new System.Drawing.Size(880, 38);
                this.mainToolbar.TabIndex = 11;
                this.mainToolbar.Text = "mainToolbar";
                // 
                // btnOpenFile
                // 
                this.btnOpenFile.Image = ((System.Drawing.Image)(resources.GetObject("btnOpenFile.Image")));
                this.btnOpenFile.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.btnOpenFile.Name = "btnOpenFile";
                this.btnOpenFile.Size = new System.Drawing.Size(59, 35);
                this.btnOpenFile.Text = "Open file";
                this.btnOpenFile.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
                this.btnOpenFile.Click += new System.EventHandler(this.btnOpenFile_Click);
                // 
                // toolStripSeparator1
                // 
                this.toolStripSeparator1.Name = "toolStripSeparator1";
                this.toolStripSeparator1.Size = new System.Drawing.Size(6, 38);
                // 
                // btnFormatValues
                // 
                this.btnFormatValues.CheckOnClick = true;
                this.btnFormatValues.Image = ((System.Drawing.Image)(resources.GetObject("btnFormatValues.Image")));
                this.btnFormatValues.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.btnFormatValues.Name = "btnFormatValues";
                this.btnFormatValues.Size = new System.Drawing.Size(85, 35);
                this.btnFormatValues.Text = "&Format values";
                this.btnFormatValues.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
                // 
                // toolStripSeparator2
                // 
                this.toolStripSeparator2.Name = "toolStripSeparator2";
                this.toolStripSeparator2.Size = new System.Drawing.Size(6, 38);
                // 
                // btnValueInCellA1
                // 
                this.btnValueInCellA1.Image = ((System.Drawing.Image)(resources.GetObject("btnValueInCellA1.Image")));
                this.btnValueInCellA1.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.btnValueInCellA1.Name = "btnValueInCellA1";
                this.btnValueInCellA1.Size = new System.Drawing.Size(91, 35);
                this.btnValueInCellA1.Text = "&Value in cell A1";
                this.btnValueInCellA1.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
                this.btnValueInCellA1.Click += new System.EventHandler(this.btnValueInCurrentCell_Click);
                // 
                // btnExit
                // 
                this.btnExit.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right;
                this.btnExit.Image = ((System.Drawing.Image)(resources.GetObject("btnExit.Image")));
                this.btnExit.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.btnExit.Name = "btnExit";
                this.btnExit.Size = new System.Drawing.Size(59, 35);
                this.btnExit.Text = "     E&xit     ";
                this.btnExit.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
                this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
                // 
                // btnInfo
                // 
                this.btnInfo.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right;
                this.btnInfo.Image = ((System.Drawing.Image)(resources.GetObject("btnInfo.Image")));
                this.btnInfo.ImageTransparentColor = System.Drawing.Color.Magenta;
                this.btnInfo.Name = "btnInfo";
                this.btnInfo.Size = new System.Drawing.Size(74, 35);
                this.btnInfo.Text = "Information";
                this.btnInfo.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
                this.btnInfo.Click += new System.EventHandler(this.btnInfo_Click);
                // 
                // mainForm
                // 
                this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
                this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
                this.ClientSize = new System.Drawing.Size(880, 461);
                this.Controls.Add(this.DisplayGrid);
                this.Controls.Add(this.panel1);
                this.Controls.Add(this.statusBar);
                this.Controls.Add(this.mainToolbar);
                this.Name = "mainForm";
                this.Text = "Reading Excel Files";
                ((System.ComponentModel.ISupportInitialize)(this.DisplayGrid)).EndInit();
                this.panel1.ResumeLayout(false);
                this.mainToolbar.ResumeLayout(false);
                this.mainToolbar.PerformLayout();
                this.ResumeLayout(false);
                this.PerformLayout();
    
            }
            #endregion
    
            private ToolStrip mainToolbar;
            private ToolStripButton btnOpenFile;
            private ToolStripSeparator toolStripSeparator1;
            private ToolStripButton btnInfo;
            private ToolStripButton btnExit;
            private ToolStripButton btnValueInCellA1;
            private ToolStripSeparator toolStripSeparator2;
            private ToolStripButton btnFormatValues;
        }
    }
    

    Program.cs

    using System;
    using System.Windows.Forms;
    
    namespace ReadingFiles
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new mainForm());
            }
        }
    }
    
    In This Article
    Back to top FlexCel Studio for the .NET Framework v7.24.0.0
    © 2002 - 2025 tmssoftware.com