Importing and Reading Excel files

[Kasper.Vestrup]

This article outlines how to import and read Excel files contents using a custom plugin.

1. First, create a new project using the Plugin template available HERE. If you already have a plugin project, skip this step.

2. It is necessary to install two NuGet packages. Therefore, in the solution explorer of visual studio, use right click on the Dependencies section, and select the option Manage NuGet Packages. Browse and install the following NuGet packages

3. Opening and reading a file with the recently added library can be performed as follows:

/// <summary>
/// Class <c>Excel</c> provides methods to read the content of an Excel file.
/// </summary>
internal static class Reader
{
    /// <summary>
    /// Opens and reads the content of an Excel file
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns>A collection of Content type which contains the content of each Sheet</returns>
    public static List<Content> Open(string filePath)
    {
        try
        {
            using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                // Auto-detect format, supports:
                //  - Binary Excel files (2.0-2003 format; *.xls)
                //  - OpenXml Excel files (2007 format; *.xlsx, *.xlsb)
                DataSet result;

                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    result = reader.AsDataSet();
                }

                var content = new List<Content>();

                foreach (var table in result.Tables)
                {
                    var tableInfo = new List<List<string>>();

                    if (!(table is DataTable sheet))
                    {
                        continue;
                    }

                    for (var i = 0; i <= sheet.Rows.Count - 1; i++)
                    {
                        var rowInfo = new List<string>();

                        for (var j = 0; j <= sheet.Columns.Count - 1; j++)
                        {
                            var value = sheet.Rows[i][j] == null ? string.Empty : sheet.Rows[i][j];
                            rowInfo.Add(value.ToString());
                        }

                        tableInfo.Add(rowInfo);
                    }

                    content.Add(new Content(sheet.TableName, tableInfo));
                }

                return content;
            }
        }
        catch (Exception e)
        {
            Log.Write($"An error has occured during the reading process of the Excel file {filePath}. Please, see Debug.log");
            Log.Debug.Write(e);
            return null;
        }
    }

The Excel file’s content is returned through the variable “content,” which provides a list of items. Each item corresponds to a sheet and its content/data. Notably, the content read from the Excel file is of type string. Developers must perform any necessary type conversions for specific data types.

You can download the attached plugin project, which contains a blue print to implement Excel reading files logic.