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.License
package: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.LicenseException
will 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.