安装NPOI
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; // 用于处理XLSX格式的文件
//用于读取的辅助函数
public string GetCellValue(ICell cell)
{
if (cell == null)
{
return "";
}
switch (cell.CellType)
{
case CellType.Numeric:
return cell.NumericCellValue.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Formula:
return cell.CellFormula;
default:
return ""; // 对于其他类型或未定义的类型,返回空字符串或适当的默认值
}
}
public string[,] Read()
{
string filePath = "";
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "文本文件|*.XLSX";
fd.RestoreDirectory = true;
fd.FilterIndex = 1;
if (fd.ShowDialog() == DialogResult.OK)
{
try
{
filePath = fd.FileName;
}
catch
{
MessageBox.Show("!", "ERROR");
}
}
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs); // 用于XLSX格式,如果是HSSF则使用HSSFWorkbook
ISheet sheet = workbook.GetSheetAt(0); // 获取第一个Sheet
// 获取行数和列数
int totalRows = sheet.LastRowNum + 1;
int totalCols = 0;
foreach (IRow row in sheet)
{
if (row.LastCellNum > totalCols)
{
totalCols = row.LastCellNum;
}
}
// 创建二维数组
string[,] data = new string[totalRows, totalCols];
// 遍历Sheet并将数据放入二维数组
for (int row = 0; row < totalRows; row++)
{
IRow currentRow = sheet.GetRow(row);
if (currentRow != null)
{
for (int col = 0; col < totalCols; col++)
{
ICell cell = currentRow.GetCell(col);
// 根据单元格类型获取值
data[row, col] = GetCellValue(cell);
}
}
}
// 关闭工作簿
workbook.Close();
return data;
}
}