尝试用epplus读取excel文件并获取系统.NullException错误
本文关键字:获取 系统 NullException 错误 文件 excel epplus 读取 | 更新日期: 2023-09-27 18:12:10
编辑
根据以下回复,我遇到的错误可能会也可能不会导致我无法读取excel文件。也就是说,在下面给出的for循环中,我没有从worksheet.Cells[row,col].Value
行获取数据。
问题
我正试图返回一个数据表,其中包含excel文件中的信息。具体来说,我相信这是一个来自2013 excel的xlsx文件。请参阅下面的代码:
private DataTable ImportToDataTable(string Path)
{
DataTable dt = new DataTable();
FileInfo fi = new FileInfo(Path);
if(!fi.Exists)
{
throw new Exception("File " + Path + " Does not exist.");
}
using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
//Get the worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.First();
//Obtain the worksheet size
ExcelCellAddress startCell = worksheet.Dimension.Start;
ExcelCellAddress endCell = worksheet.Dimension.End;
//Create the data column
for(int col = startCell.Column; col <= endCell.Column; col++)
{
dt.Columns.Add(col.ToString());
}
for(int row = startCell.Row; row <= endCell.Row; row++)
{
DataRow dr = dt.NewRow(); //Create a row
int i = 0;
for(int col = startCell.Column; col <= endCell.Column; col++)
{
dr[i++] = worksheet.Cells[row, col].Value.ToString();
}
dt.Rows.Add(dr);
}
}
return dt;
}
错误
这就是事情变得奇怪的地方。我可以在startCell
和endCell
中看到合适的值。然而,当我看到worksheet
时,我在Cells
下看了一眼,发现了一些我不理解的东西:
worksheet.Cells.Current' threw an exception of type 'System.NullReferenceException
尝试
- 用一般领域重塑我的特长
- 确保excel中没有任何字段为空
- RTFM-epplus文档。没有任何迹象表明这个错误
- 查看了stackoverflow上的EPPlus错误。我的问题是独一无二的
老实说,我很难弄清楚这个错误到底在说什么?我的格式有问题吗?epplus有什么问题吗?我在这里读到,人们对eeplus的2013 xlsx没有任何问题,我只是试图逐行解析excel文件。如果有人能帮助我阐明这个错误的含义以及如何纠正它,我将不胜感激。我花了很长时间试图弄清楚这一点。
当我们给出时:
dr[i++] = worksheet.Cells[row, col].Value.ToString();
它搜索该列的值,如果该列为空,则会给出Null引用错误。
尝试:
dr[i++] = worksheet.Cells[row, col].Text;
希望这将有助于
正如@Thorians所说,current实际上是用来枚举单元格的。如果你想以最纯粹的形式使用它,并且实际上能够调用current
,那么你需要这样的东西:
using (var pck = new ExcelPackage(existingFile))
{
var worksheet = pck.Workbook.Worksheets.First();
//this is important to hold onto the range reference
var cells = worksheet.Cells;
//this is important to start the cellEnum object (the Enumerator)
cells.Reset();
//Can now loop the enumerator
while (cells.MoveNext())
{
//Current can now be used thanks to MoveNext
Console.WriteLine("Cell [{0}, {1}] = {2}"
, cells.Current.Start.Row
, cells.Current.Start.Column
, cells.Current.Value);
}
}
请注意,您必须创建一种local
集合cells
才能正常工作。否则,如果您尝试`worksheet.cells.current',Current
将为空
但使用ForEach
并让CLR为您完成工作会更简单。
更新:基于评论。你的代码应该可以正常工作,可能是你的excel文件:
[TestMethod]
public void Current_Cell_Test()
{
//http://stackoverflow.com/questions/32516676/trying-to-read-excel-file-with-epplus-and-getting-system-nullexception-error
//Throw in some data
var datatable = new DataTable("tblData");
datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)),new DataColumn("Col3", typeof (object)) });
for (var i = 0; i < 10; i++)
{
var row = datatable.NewRow(); row[0] = i; row[1] = i * 10; row[2] = Path.GetRandomFileName(); datatable.Rows.Add(row);
}
//Create a test file
var fi = new FileInfo(@"c:'temp'test1.xlsx");
if (fi.Exists)
fi.Delete();
using (var pck = new ExcelPackage(fi))
{
var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells.LoadFromDataTable(datatable, true);
pck.Save();
}
var dt = new DataTable();
using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
//Get the worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.First();
//Obtain the worksheet size
ExcelCellAddress startCell = worksheet.Dimension.Start;
ExcelCellAddress endCell = worksheet.Dimension.End;
//Create the data column
for (int col = startCell.Column; col <= endCell.Column; col++)
{
dt.Columns.Add(col.ToString());
}
for (int row = startCell.Row; row <= endCell.Row; row++)
{
DataRow dr = dt.NewRow(); //Create a row
int i = 0;
for (int col = startCell.Column; col <= endCell.Column; col++)
{
dr[i++] = worksheet.Cells[row, col].Value.ToString();
}
dt.Rows.Add(dr);
}
}
Console.Write("{{dt Rows: {0} Columns: {1}}}", dt.Rows.Count, dt.Columns.Count);
}
在输出中给出:
{Rows: 11, Columns: 3}
Current是枚举时的当前范围。
当没有在枚举范围内使用时,在调试检查中抛出异常并没有错。
代码示例:
var range = ws.Cells[1,1,1,100];
foreach (var cell in range)
{
var a = range.Current.Value; // a is same as b
var b = cell.Value;
}
我在读取excel文件时也遇到了同样的问题,提供的解决方案都不适用
public void readXLS(string FilePath)
{
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
//get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colCount = worksheet.Dimension.End.Column; //get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{
Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
}
}
}
}