ExcelDataReader
===============
Lightweight and fast library written in C# for reading Microsoft Excel files (2.0-2007).
Please feel free to fork and submit pull requests to the develop branch.
If you are reporting an issue it is really useful if you can supply an example Excel file as this makes debugging much easier and without it we may not be able to resolve any problems.
[![Build status](https://ci.appveyor.com/api/projects/status/ii6hbs9otpbg1nqh/branch/master?svg=true)](https://ci.appveyor.com/project/andersnm/exceldatareader/branch/master) [![Build status](https://ci.appveyor.com/api/projects/status/ii6hbs9otpbg1nqh/branch/develop?svg=true)](https://ci.appveyor.com/project/andersnm/exceldatareader/branch/develop)
## Supported file formats and versions
| File Type | Container Format | File Format | Excel Version(s) |
| --------- | ---------------- | ----------- | ---------------- |
| .xlsx | ZIP, CFB+ZIP | OpenXml | 2007 and newer |
| .xls | CFB | BIFF8 | 97, 2000, XP, 2003<br>98, 2001, v.X, 2004 (Mac) |
| .xls | CFB | BIFF5 | 5.0, 95 |
| .xls | - | BIFF4 | 4.0 |
| .xls | - | BIFF3 | 3.0 |
| .xls | - | BIFF2 | 2.0, 2.2 |
| .csv | - | CSV | (All) |
## Finding the binaries
It is recommended to use NuGet. F.ex through the VS Package Manager Console `Install-Package <package>` or using the VS "Manage NuGet Packages..." extension.
As of ExcelDataReader version 3.0, the project was split into multiple packages:
Install the `ExcelDataReader` base package to use the "low level" reader interface. Compatible with net20, net45, netstandard1.3 and netstandard2.0.
Install the `ExcelDataReader.DataSet` extension package to use the `AsDataSet()` method to populate a `System.Data.DataSet`. This will also pull in the base package. Compatible with net20, net45 and netstandard2.0.
## How to use
```c#
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)
using (var reader = ExcelReaderFactory.CreateReader(stream)) {
// Choose one of either 1 or 2:
// 1. Use the reader methods
do {
while (reader.Read()) {
// reader.GetDouble(0);
}
} while (reader.NextResult());
// 2. Use the AsDataSet extension method
var result = reader.AsDataSet();
// The result of each spreadsheet is in result.Tables
}
}
```
### Reading .CSV files
Use `ExcelReaderFactory.CreateCsvReader` instead of `CreateReader` to parse a stream of plain text with comma separated values.
See also the configuration options `FallbackEncoding` and `AutodetectSeparators`.
The input CSV is always parsed once completely to set FieldCount, RowCount, Encoding, Separator (or twice if the CSV lacks BOM and is not UTF8), and then parsed once again while iterating the row records. Throws `System.Text.DecoderFallbackException` if the input cannot be parsed with the specified encoding.
The reader returns all CSV field values as strings and makes no attempts to convert the data 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, but is not always available or desirable to use. IExcelDataReader extends the `System.Data.IDataReader` and `IDataRecord` interfaces to navigate and retrieve data at a lower level. The most important reader methods and properties:
- `Read()` reads a row from the current sheet.
- `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 name identifier of the current sheet.
- `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().
- `HeaderFooter` returns an object with information about the headers and footers, or `null` if there are none.
- `MergeCells` returns an array of merged cell ranges in the current sheet.
- `RowHeight` returns the visual height of the current row in points. May be 0 if the row is hidden.
- `GetFieldType()` returns the type of a value in the current row. Always one of the types supported by Excel: `double`, `int`, `bool`, `DateTime`, `TimeSpan`, `string`, or `null` if there is no value.
- `IsDBNull()` checks if a value in the current row is null.
- `GetValue()` returns a value from the current row as an `object`, or `null` if there is no value.
- `GetDouble()`, `GetInt32()`, `GetBoolean()`, `GetDateTime()`, `GetString()` return a value from the current row cast to their respective type.
- `GetNumberFormatString()` returns a string containing the formatting codes for a value in the current row, or `null` if there is no value. See also the Formatting section below.
- The typed `Get*()` methods throw `InvalidCastException` unless the types match exactly.
### CreateReader() configuration options
The `ExcelReaderFactory.CreateReader()`, `CreateBinaryReader()`, `CreateOpenXmlReader()`, `CreateCsvReader()` methods accept an optional configuration object to modify the behavior of the reader:
```c#
var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration() {
// Gets or sets the encoding to use when the input XLS lacks a CodePage
// record, or when the input CSV lacks a BOM and does not parse as UTF8.
// Default: cp1252. (XLS BIFF2-5 and CSV only)
FallbackEncoding = Encoding.GetEncoding(1252),
// Gets or sets the password used to open password protected workbooks.
Password = "password",
// Gets or sets an array of CSV separator candidates. The reader
// autodetects which best fits the input data. Default: , ; TAB | #
// (CSV only)
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' };
});
```
### AsDataSet() configuration options
The `AsDataSet()` method accepts an optional configuration object to modify the behavior of the DataSet conversion:
```c#
var result = reader.AsDataSet(new ExcelDataSetConfiguration() {
// Gets or sets a value indicating whether to set the DataColumn.DataType
// property in a second pass.
UseColumnDataType = 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 = "Column",
// 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 called when UseHeaderRow = true.
ReadHeaderRow = (rowReader) => {
// F.ex skip the first row and use the 2nd row as column 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;
}
}
});
```
## Formatting
ExcelDataReader does not support formatting directly. Users may retreive the number format string for a cell through `IExcelDataReader.GetNumberFormatString(i)` and use the third party ExcelNumberFormat library for formatting purposes.
Example helper method using ExcelDataReader and ExcelNumberFormat to format a value:
```c#
string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture) {
var value = reader.GetValue(col
没有合适的资源?快使用搜索试试~ 我知道了~
专门为读取excel表格的程序源码的插件
共342个文件
cs:148个
xls:84个
xlsx:75个
需积分: 5 0 下载量 171 浏览量
2024-04-18
14:52:25
上传
评论
收藏 16.73MB ZIP 举报
温馨提示
专门读取excel表格的程序案例,为程序开发,内含各版本的excel表格的dll引用等
资源推荐
资源详情
资源评论
收起资源包目录
专门为读取excel表格的程序源码的插件 (342个子文件)
ExcelBinaryReaderTest.cs 75KB
ExcelOpenXmlReaderTest.cs 63KB
FormatReaderTest.cs 24KB
XlsWorksheet.cs 21KB
XlsxWorksheet.cs 19KB
XlsBiffStream.cs 14KB
Configuration.cs 14KB
AgileEncryption.cs 14KB
ExcelCsvReaderTest.cs 13KB
CompoundDocument.cs 13KB
EncodingHelper.cs 12KB
XlsxWorkbook.cs 12KB
Form1.cs 12KB
Parser.cs 11KB
StandardEncryption.cs 11KB
XlsWorkbook.cs 9KB
ExcelReaderFactory.cs 9KB
ExcelDataReader.cs 8KB
CsvParser.cs 8KB
ExcelDataReaderExtensions.cs 7KB
FractionSection.cs 7KB
XorManaged.cs 7KB
Enums.cs 6KB
XlsSSTReader.cs 6KB
CsvAnalyzer.cs 6KB
AssertUtilities.cs 5KB
CompoundStream.cs 5KB
ZipWorker.cs 4KB
AgileEncryptedPackageStream.cs 4KB
CsvWorksheet.cs 4KB
CryptoHelpers.cs 4KB
CompoundHeader.cs 4KB
XlsBiffFormulaCell.cs 4KB
Token.cs 4KB
DecimalSection.cs 3KB
RC4Encryption.cs 3KB
DateTimeHelper.cs 3KB
Tokenizer.cs 3KB
XlsBiffRecord.cs 3KB
RC4Managed.cs 3KB
XlsSSTStringHeader.cs 3KB
BuiltinNumberFormat.cs 3KB
ExcelOpenXmlReaderLocaleTest.cs 3KB
CompoundDirectoryEntry.cs 3KB
StandardEncryptedPackageStream.cs 3KB
XlsBiffRow.cs 3KB
Helpers.cs 3KB
EncryptionInfo.cs 2KB
NumberFormatString.cs 2KB
XlsBiffBoundSheet.cs 2KB
XlsBiffBlankCell.cs 2KB
ExcelReaderFactoryTests.cs 2KB
ILog.cs 2KB
HeaderFooter.cs 2KB
IExcelDataReader.cs 2KB
XlsBiffWindow1.cs 2KB
XlsBiffBOF.cs 2KB
XlsBiffSST.cs 2KB
XlsBiffFormatString.cs 2KB
DataTable.cs 2KB
NunitLogFactory.cs 2KB
ExcelReaderException.cs 2KB
Log.cs 2KB
XlsBiffFilePass.cs 2KB
HeaderException.cs 2KB
ZipArchive.cs 2KB
Func.cs 2KB
CellRange.cs 1KB
ReferenceHelper.cs 1KB
XlsBiffMulRKCell.cs 1KB
SeekErrorMemoryStream.cs 1KB
XlsBiffIndex.cs 1KB
Errors.cs 1KB
ExponentialSection.cs 1KB
XorEncryption.cs 1KB
XlsBiffLabelCell.cs 1KB
XlsxDimension.cs 1KB
XlsBiffDimensions.cs 1KB
ExcelDataTableConfiguration.cs 1KB
DataColumnCollection.cs 1KB
XlsBiffFormulaString.cs 1KB
XlsUnicodeString.cs 1KB
Row.cs 1KB
XlsShortUnicodeString.cs 1KB
XlsByteString.cs 1KB
XlsBiffRKCell.cs 1KB
DataTableCollection.cs 1KB
XlsBiffDefaultRowHeight.cs 1KB
XlsBiffMergeCells.cs 1KB
ExcelReaderConfiguration.cs 1KB
XlsBiffDbCell.cs 1008B
XlsBiffMulBlankCell.cs 989B
XlsBiffHeaderFooterString.cs 985B
XlsShortByteString.cs 963B
NullLogFactory.cs 911B
DataRow.cs 878B
DataRowCollection.cs 824B
XlsBiffXF.cs 811B
CsvWorkbook.cs 767B
ExcelBinaryReader.cs 738B
共 342 条
- 1
- 2
- 3
- 4
资源评论
紫龙大侠
- 粉丝: 566
- 资源: 32
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- pta题库答案c语言之排序4统计工龄.zip
- pta题库答案c语言之树结构7堆中的路径.zip
- pta题库答案c语言之树结构3TreeTraversalsAgain.zip
- pta题库答案c语言之树结构2ListLeaves.zip
- pta题库答案c语言之树结构1树的同构.zip
- 基于C++实现民航飞行与地图简易管理系统可执行程序+说明+详细注释.zip
- pta题库答案c语言之复杂度1最大子列和问题.zip
- 三维装箱问题(Three-Dimensional Bin Packing Problem,3D-BPP)是一个经典的组合优化问题
- 以下是一些关于Linux线程同步的基本概念和方法.txt
- 以下是一个简化的示例,它使用pygame库来模拟烟花动画的框架.txt
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功