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
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:
XlsFile.GetStringFromCell will return a rich string with the cell formatted.
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());
}
}
}