我正在使用 ExcelDataReader
在C#中读取Excel工作簿中的数据。
但是我的Excel表的结构使得要读取的数据可以从任何特定单元格开始,不一定是A1
。
请问有没有人能建议如何使用ExcelDataReader
来实现这个目标?
我正在使用 ExcelDataReader
在C#中读取Excel工作簿中的数据。
但是我的Excel表的结构使得要读取的数据可以从任何特定单元格开始,不一定是A1
。
请问有没有人能建议如何使用ExcelDataReader
来实现这个目标?
AsDataSet()
方法,您需要安装另一个包ExcelDataReader.DataSet
,然后您就可以使用AsDataSet()
方法。IsFirstRowAsColumnNames
属性,您需要将其设置在ExcelDataSetConfiguration
中。using (var stream = File.Open(originalFileName, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
// Create Reader - old until 3.4+
////var file = new FileInfo(originalFileName);
////if (file.Extension.Equals(".xls"))
//// reader = ExcelDataReader.ExcelReaderFactory.CreateBinaryReader(stream);
////else if (file.Extension.Equals(".xlsx"))
//// reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream);
////else
//// throw new Exception("Invalid FileName");
// Or in 3.4+ you can only call this:
reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream)
//// reader.IsFirstRowAsColumnNames
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
// Now you can get data from each sheet by its index or its "name"
var dataTable = dataSet.Tables[0];
//...
}
示例代码:
var cellStr = "AB2"; // var cellStr = "A1";
var match = Regex.Match(cellStr, @"(?<col>[A-Z]+)(?<row>\d+)");
var colStr = match.Groups["col"].ToString();
var col = colStr.Select((t, i) => (colStr[i] - 64) * Math.Pow(26, colStr.Length - i - 1)).Sum();
var row = int.Parse(match.Groups["row"].ToString());
for (var i = row; i < dataTable.Rows.Count; i++)
{
for (var j = col; j < dataTable.Columns.Count; j++)
{
var data = dataTable.Rows[i][j];
}
}
更新:
您可以使用以下配置在读取Excel表格时筛选行和列:
var i = 0;
var conf = new ExcelDataSetConfiguration
{
UseColumnDataType = true,
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
FilterRow = rowReader => fromRow <= ++i - 1,
FilterColumn = (rowReader, colIndex) => fromCol <= colIndex,
UseHeaderRow = true
}
};
为了更加清晰,我将从一开始开始。
我将依赖于在https://github.com/ExcelDataReader/ExcelDataReader找到的示例代码,但是对其进行了一些修改以避免不便。
以下代码检测文件格式,可以是xls或xlsx。
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
//1. Reading Excel file
if (Path.GetExtension(filePath).ToUpper() == ".XLS")
{
//1.1 Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//1.2 Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
//2. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//3. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = false;
现在我们可以更便捷地访问文件内容。我使用 DataTable 来实现这一点。以下是一个访问特定单元格并将其值打印到控制台的示例:
现在我们可以以更加方便的方式访问文件内容。为此,我使用了 DataTable。下面是一个访问特定单元格并在控制台中打印其值的示例:
DataTable dt = result.Tables[0];
Console.WriteLine(dt.Rows[rowPosition][columnPosition]);
如果您不想使用DataTable,可以按照以下方式操作:
Console.WriteLine(result.Tables[0].Rows[rowPosition][columnPosition]);
重要的是不要尝试超出表格范围阅读,你可以通过以下方式查看行数和列数:
Console.WriteLine(result.Tables[0].Rows.Count);
Console.WriteLine(result.Tables[0].Columns.Count);
最后,当您完成时,应关闭阅读器并释放资源:
//5. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
我希望您觉得这很有用。
(我知道这个问题很老,但我做出了这个贡献来增强知识库,因为关于此库的特定实现的材料很少。)
HttpRequest
请求体中处理而不是使用文件流?我有一个负载通过 POST 请求的请求体传入。 - Alex Gordon适用于ExcelDataReader v3.6.0及以上版本。 在迭代行方面我遇到了一些问题。所以这里有一些以上代码的补充说明。希望对至少有些人有所帮助。
using (var stream = System.IO.File.Open(copyPath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelDataReader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration()
{
ConfigureDataTable = a => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataSet dataSet = excelDataReader.AsDataSet(conf);
//DataTable dataTable = dataSet.Tables["Sheet1"];
DataRowCollection row = dataSet.Tables["Sheet1"].Rows;
//DataColumnCollection col = dataSet.Tables["Sheet1"].Columns;
List<object> rowDataList = null;
List<object> allRowsList = new List<object>();
foreach (DataRow item in row)
{
rowDataList = item.ItemArray.ToList(); //list of each rows
allRowsList.Add(rowDataList); //adding the above list of each row to another list
}
}
一种方法是:
FileStream stream = File.Open(@"c:\working\test.xls", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
result.Tables
包含工作表,result.tables[0].Rows
包含单元格行。
FileStream stream = File.Open(@"C:\Users\Desktop\ExcelDataReader.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.IsFirstRowAsColumnNames = true;
DataTable dt = result.Tables[0];
string text = dt.Rows[1][0].ToString();
using (var openFileDialog1 = new OpenFileDialog { Filter = "Excel Workbook|*.xls;*.xlsx;*.xlsm", ValidateNames = true })
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
var fs = File.Open(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
var reader = ExcelReaderFactory.CreateBinaryReader(fs);
var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true // Use first row is ColumnName here :D
}
});
if (dataSet.Tables.Count > 0)
{
var dtData = dataSet.Tables[0];
// Do Something
}
}
}
public static DataTable ConvertExcelToDataTable(string filePath, bool isXlsx = false)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
//open file and returns as Stream
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
// Now you can get data from each sheet by its index or its "name"
var dataTable = dataSet.Tables[0];
Console.WriteLine("Total no of rows " + dataTable.Rows.Count);
Console.WriteLine("Total no of Columns " + dataTable.Columns.Count);
return dataTable;
}
}
}
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; data source={path of your excel file}; Extended Properties=Excel 12.0;";
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
//Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
objConn.Open();
//Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sql = string.Format("select * from [{0}$]", sheetName);
var adapter = new System.Data.OleDb.OleDbDataAdapter(sql, ConnectionString);
var ds = new System.Data.DataSet();
string tableName = sheetName;
adapter.Fill(ds, tableName);
System.Data.DataTable data = ds.Tables[tableName];
在将数据放入数据表之后,您可以像使用 DataTable 类一样访问它们。