The NuGet packages of ExcelDataReader and ExcelDataReader.DataSet need to be installed before starting the program
using System;
using System.IO;
using System. Linq;
using ExcelDataReader;
class Program
{
static void Main(string[] args)
{
Console.Write("Please enter the folder path:");
string folderPath = Console.ReadLine(); // Get the folder path entered by the user
string[] files = Directory.GetFiles(folderPath, "*Test*.xlsx"); // Get the Excel files in the folder whose name contains "Test"
foreach (string file in files)
{
using (var stream = File. Open(file, FileMode. Open, FileAccess. Read, FileShare. ReadWrite))
{
using (var reader = ExcelReaderFactory. CreateReader(stream))
{
do
{
while (reader. Read())
{
// Find "star" cells in each row
for (int i = 0; i < reader. FieldCount; i ++ )
{
var cellValue = reader. GetValue(i)?. ToString();
if (!string.IsNullOrEmpty(cellValue) & amp; & amp; cellValue.Contains("star"))
{
// Output cell information containing "star"
Console.WriteLine("file: " + file);
Console.WriteLine("Sheet name: " + reader.Name);
Console.WriteLine("Line number: " + reader.Depth);
Console.WriteLine("Column number: " + i);
Console.WriteLine("Cell content: " + cellValue);
Console. WriteLine();
}
}
}
} while (reader. NextResult());
}
}
}
Console. ReadLine();
}
}
At this time, when there are multiple excel files with “Test” in the folder, a timeout problem may occur. In order to solve this problem, you can try to use the ExcelPackage library to read the Excel file. It More stable handling of large files (requires EPPlus package to be installed).
The modified code is as follows:
using System;
using System.IO;
using System. Linq;
using OfficeOpenXml;
class Program
{
static void Main(string[] args)
{
Console.Write("Please enter the folder path:");
string folderPath = Console.ReadLine(); // Get the folder path entered by the user
string[] files = Directory.GetFiles(folderPath, "*Test*.xlsx"); // Get the Excel files in the folder whose name contains "Test"
foreach (string file in files)
{
using (var package = new ExcelPackage(new FileInfo(file)))
{
foreach (var worksheet in package. Workbook. Worksheets)
{
for (int row = 1; row <= worksheet. Dimension. Rows; row ++ )
{
for (int col = 1; col <= worksheet. Dimension. Columns; col ++ )
{
var cellValue = worksheet.Cells[row, col].Value?.ToString();
if (!string.IsNullOrEmpty(cellValue) & amp; & amp; cellValue.Contains("star"))
{
// Output cell information containing "star"
Console.WriteLine("file: " + file);
Console.WriteLine("Sheet name: " + worksheet.Name);
Console.WriteLine("line number: " + row);
Console.WriteLine("column number: " + col);
Console.WriteLine("Cell content: " + cellValue);
Console. WriteLine();
}
}
}
}
}
}
Console. ReadLine();
}
}
The modified code uses the ExcelPackage class in the EPPlus library to load and process Excel files, which handles large files more stably and avoids the need for ExcelDataReader code> timeout issue.
PS: The EPPlus library requires a valid license key to be set before using it. Please make sure to set a valid license key at the entry point of the program or in the initialization code before using the EPPlus library. Proceed as follows:
- Open the NuGet Manager Console (Tools -> NuGet Package Manager -> Package Manager Console).
- Run the following command in the console to install the
EPPlus.Licensepackage:Install-Package EPPlus.License
- Add the following code to the code to set the license key:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
It is mainly for non-commercial use, otherwise
OfficeOpenXml.LicenseExceptionwill appear.
After this step, the program can only read files in .xlsx format, but cannot read files in .xls format, because the EPPlus library Only the .xlsx format is supported. EPPlus is a dedicated library for working with the Office Open XML Formats and does not support the legacy binary format (.xls). For reading and manipulating files in the .xls format, consider using the NPOI library, which supports multiple Excel file formats, including .xls and .xlsx.
Example code to read cells containing “star” using the NPOI library:
using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
class Program
{
static void Main(string[] args)
{
Console.Write("Please enter the folder path:");
string folderPath = Console.ReadLine(); // Get the folder path entered by the user
string[] files = Directory.GetFiles(folderPath, "*Test*.xls"); // Get the Excel files in the folder whose name contains "Test"
foreach (string file in files)
{
using (var fs = new FileStream(file, FileMode. Open, FileAccess. Read))
{
var workbook = new HSSFWorkbook(fs); // load .xls file using HSSFWorkbook
for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++ )
{
var sheet = workbook. GetSheetAt(sheetIndex);
for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++ )
{
var row = sheet. GetRow(rowIndex);
if (row != null)
{
for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++ )
{
var cell = row. GetCell(cellIndex);
if (cell != null)
{
var cellValue = GetCellValue(cell)?.ToString();
if (!string.IsNullOrEmpty(cellValue) & amp; & amp; cellValue.Contains("star"))
{
Console.WriteLine("file: " + file);
Console.WriteLine("Sheet name: " + sheet.SheetName);
Console.WriteLine("Line number: " + (rowIndex + 1));
Console.WriteLine("Column number: " + (cellIndex + 1));
Console.WriteLine("Cell content: " + cellValue);
Console. WriteLine();
}
}
}
}
}
}
}
}
Console. ReadLine();
}
private static object GetCellValue(ICell cell)
{
switch (cell. CellType)
{
case CellType. String:
return cell. StringCellValue;
case CellType. Numeric:
if (DateUtil.IsCellDateFormatted(cell))
return cell.DateCellValue;
else
return cell.NumericCellValue;
case CellType. Boolean:
return cell. BooleanCellValue;
case CellType.Formula:
return cell. CellFormula;
default:
return null;
}
}
}
Use the HSSFWorkbook class of the NPOI library to load a file in .xls format and iterate over the worksheet, row and cell to find the file containing ” FPGA’s burn-in file (*.bin)” cell.