Visual Studio 2017 Read Excel File in Vb.net

ExcelDataReader

Lightweight and fast library written in C# for reading Microsoft Excel files (two.0-2007).

Delight feel complimentary to fork and submit pull requests to the develop branch.

If you are reporting an issue it is really useful if you tin supply an case Excel file every bit this makes debugging much easier and without it we may non be able to resolve any problems.

Continuous integration

Co-operative	Build status
develop
chief

Supported file formats and versions

File Type	Container Format	File Format	Excel Version(south)
.xlsx	ZIP, CFB+ZIP	OpenXml	2007 and newer
.xlsb	ZIP, CFB	OpenXml	2007 and newer
.xls	CFB	BIFF8	97, 2000, XP, 2003 98, 2001, v.10, 2004 (Mac)
.xls	CFB	BIFF5	5.0, 95
.xls	-	BIFF4	four.0
.xls	-	BIFF3	3.0
.xls	-	BIFF2	2.0, two.two
.csv	-	CSV	(All)

Finding the binaries

It is recommended to use NuGet through the VS Package Manager Console Install-Package <package> or using the VS "Manage NuGet Packages..." extension.

Equally of ExcelDataReader version iii.0, the project was separate into multiple packages:

Install the ExcelDataReader base bundle to utilize the "low level" reader interface. Compatible with net20, net45, netstandard1.iii and netstandard2.0.

Install the ExcelDataReader.DataSet extension packet to employ the AsDataSet() method to populate a System.Data.DataSet. This will also pull in the base package. Uniform with net20, net45 and netstandard2.0.

How to utilize

              using              (var              stream              =              File.Open(filePath,              FileMode.Open up,              FileAccess.Read)) {                              //                Motorcar-notice format, supports:                              //                - Binary Excel files (ii.0-2003 format; *.xls)                              //                - OpenXml Excel files (2007 format; *.xlsx, *.xlsb)              using              (var              reader              =              ExcelReaderFactory.CreateReader(stream))     {                              //                Choose one of either one or 2:                              //                i. Use the reader methods              exercise              {              while              (reader.Read())             {                              //                reader.GetDouble(0);              }         }              while              (reader.NextResult());                              //                two. Employ the AsDataSet extension method              var              consequence              =              reader.AsDataSet();                              //                The result of each spreadsheet is in upshot.Tables              } }

Reading .CSV files

Apply ExcelReaderFactory.CreateCsvReader instead of CreateReader to parse a stream of plain text with comma separated values.

See likewise the configuration options FallbackEncoding and AutodetectSeparators.

The input CSV is ever parsed one time completely to fix FieldCount, RowCount, Encoding, Separator (or twice if the CSV lacks BOM and is not UTF8), and and then parsed once over again while iterating the row records. Throws Arrangement.Text.DecoderFallbackException if the input cannot exist parsed with the specified encoding.

The reader returns all CSV field values as strings and makes no attempts to convert the information to numbers or dates. This caller is responsible for interpreting the CSV data.

Using the reader methods

The AsDataSet() extension method is a convenient helper for quickly getting the data, simply is not always available or desirable to utilise. IExcelDataReader extends the Organisation.Data.IDataReader and IDataRecord interfaces to navigate and retrieve information at a lower level. The almost of import reader methods and properties:

• Read() reads a row from the current canvas.
• NextResult() advances the cursor to the next sheet.
• ResultsCount returns the number of sheets in the current workbook.
• Name returns the name of the current sheet.
• CodeName returns the VBA code proper noun identifier of the electric current sail.
• FieldCount returns the number of columns in the current sheet.
• RowCount returns the number of rows in the current sheet. This includes terminal empty rows which are otherwise excluded by AsDataSet(). Throws InvalidOperationException on CSV files when used with AnalyzeInitialCsvRows.
• HeaderFooter returns an object with information about the headers and footers, or null if there are none.
• MergeCells returns an assortment of merged jail cell ranges in the electric current sheet.
• RowHeight returns the visual superlative of the current row in points. May be 0 if the row is hidden.
• GetColumnWidth() returns the width of a column in graphic symbol units. May be 0 if the column is hidden.
• GetFieldType() returns the blazon of a value in the current row. Ever ane of the types supported by Excel: double, int, bool, DateTime, TimeSpan, string, or goose egg if there is no value.
• IsDBNull() checks if a value in the current row is aught.
• GetValue() returns a value from the electric current row as an object, or null if there is no value.
• GetDouble(), GetInt32(), GetBoolean(), GetDateTime(), GetString() render a value from the current row cast to their corresponding type.
• GetNumberFormatString() returns a string containing the formatting codes for a value in the current row, or null if there is no value. Meet also the Formatting section below.
• GetNumberFormatIndex() returns the number format alphabetize for a value in the current row. Alphabetize values below 164 refer to built-in number formats, otherwise indicate a custom number format.
• GetCellStyle() returns an object containing fashion information for a cell in the current row: indent, horizontal alignment, hidden, locked.
• The typed Get*() methods throw InvalidCastException unless the types friction match exactly.

CreateReader() configuration options

The ExcelReaderFactory.CreateReader(), CreateBinaryReader(), CreateOpenXmlReader(), CreateCsvReader() methods accept an optional configuration object to alter the behavior of the reader:

              var              reader              =              ExcelReaderFactory.CreateReader(stream,              new              ExcelReaderConfiguration() {                              //                Gets or sets the encoding to apply when the input XLS lacks a CodePage                              //                tape, or when the input CSV lacks a BOM and does not parse as UTF8.                                            //                Default: cp1252 (XLS BIFF2-v and CSV merely)              FallbackEncoding              =              Encoding.GetEncoding(1252),                              //                Gets or sets the countersign used to open up password protected workbooks.              Countersign              =                              "password"              ,                              //                Gets or sets an array of CSV separator candidates. The reader                                            //                autodetects which best fits the input data. Default: , ; TAB | #                                            //                (CSV merely)              AutodetectSeparators              =              new              char[] {              ',',              ';',              '\t',              '|',              '#'              },                              //                Gets or sets a value indicating whether to leave the stream open afterward                              //                the IExcelDataReader object is disposed. Default: fake              LeaveOpen              =              false,                              //                Gets or sets a value indicating the number of rows to clarify for                              //                encoding, separator and field count in a CSV. When fix, this option                              //                causes the IExcelDataReader.RowCount property to throw an exception.                              //                Default: 0 - analyzes the unabridged file (CSV only, has no issue on other                              //                formats)              AnalyzeInitialCsvRows              =              0, });

AsDataSet() configuration options

The AsDataSet() method accepts an optional configuration object to alter the behavior of the DataSet conversion:

              var              outcome              =              reader.AsDataSet(new              ExcelDataSetConfiguration() {                              //                Gets or sets a value indicating whether to set the DataColumn.DataType                                            //                belongings in a 2d pass.              UseColumnDataType              =              true,                              //                Gets or sets a callback to determine whether to include the electric current canvass                              //                in the DataSet. Called once per sheet before ConfigureDataTable.              FilterSheet              =              (tableReader,              sheetIndex)              =>              true,                              //                Gets or sets a callback to obtain configuration options for a DataTable.                            ConfigureDataTable              =              (tableReader)              =>              new              ExcelDataTableConfiguration()     {                              //                Gets or sets a value indicating the prefix of generated column names.              EmptyColumnNamePrefix              =                              "Cavalcade"              ,                              //                Gets or sets a value indicating whether to use a row from the                                            //                data as column names.              UseHeaderRow              =              false,                              //                Gets or sets a callback to determine which row is the header row.                                            //                Only chosen when UseHeaderRow = true.              ReadHeaderRow              =              (rowReader)              =>              {                              //                F.ex skip the get-go row and use the 2nd row as cavalcade headers:              rowReader.Read();         },                              //                Gets or sets a callback to determine whether to include the                                            //                current row in the DataTable.              FilterRow              =              (rowReader)              =>              {              return              true;         },                              //                Gets or sets a callback to determine whether to include the specific                              //                column in the DataTable. Called once per column after reading the                                            //                headers.              FilterColumn              =              (rowReader,              columnIndex)              =>              {              return              true;         }     } });

Setting upwardly AsDataSet() configuration, employ the FilterRow callback to implement a "progress indicator" while loading, east.g.:

              var              consequence              =              reader.AsDataSet(new              ExcelDataSetConfiguration() {              ConfigureDataTable              =              (tableReader)              =>              new              ExcelDataTableConfiguration()     {              FilterRow              =              (rowReader)              =>              {              int              progress              =              (int)Math.Ceiling((decimal)rowReader.Depth              /              (decimal)rowReader.RowCount              *              (decimal)100);                              //                progress is in the range 0..100              return              true;         }     } });

Formatting

ExcelDataReader does not support formatting directly. Users may retreive the number format string for a cell through IExcelDataReader.GetNumberFormatString(i) and use the 3rd political party ExcelNumberFormat library for formatting purposes.

Example helper method using ExcelDataReader and ExcelNumberFormat to format a value:

              string              GetFormattedValue(IExcelDataReader              reader,              int              columnIndex,              CultureInfo              culture) {              var              value              =              reader.GetValue(columnIndex);              var              formatString              =              reader.GetNumberFormatString(columnIndex);              if              (formatString              !=              zero)     {              var              format              =              new              NumberFormat(formatString);              return              format.Format(value,              culture);     }              return              Convert.ToString(value,              civilization); }

Come across also:

• https://github.com/andersnm/ExcelNumberFormat
• https://www.nuget.org/packages/ExcelNumberFormat

Important note when upgrading from ExcelDataReader 2.x

ExcelDataReader three had some breaking changes, and older code may produce fault letters similar to:

              'IExcelDataReader' does non contain a definition for 'AsDataSet'... 'IExcelDataReader' does not contain a definition for 'IsFirstRowAsColumnNames'...                          

To gear up:

1. Make sure to rename whatsoever Excel namespace references in the code to the new namespace ExcelDataReader

2. Make sure the project has a reference to the ExcelDataReader.DataSet package to use AsDataSet()

3. Remove the line of code with IsFirstRowAsColumnNames and change the telephone call to AsDataSet() to something similar this:

              var              result              =              reader.AsDataSet(new              ExcelDataSetConfiguration() {              ConfigureDataTable              =              (_)              =>              new              ExcelDataTableConfiguration()     {              UseHeaderRow              =              true              } });

Of import note on .Internet Core

Past default, ExcelDataReader throws a NotSupportedException "No information is available for encoding 1252." on .NET Core.

To set, add a dependency to the package System.Text.Encoding.CodePages and then add code to register the code folio provider during application initialization (f.ex in Startup.cs):

              System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

This is required to parse strings in binary BIFF2-five Excel documents encoded with DOS-era lawmaking pages. These encodings are registered past default in the full .NET Framework, but not on .NET Core.

mcdonoughandeverien.blogspot.com

Source: https://github.com/ExcelDataReader/ExcelDataReader

Share this post