.NetCore NPOI 读取excel内容及单元格内图片

由于数据方提供的数据在excel文件中不止有文字内容还包含图片信息，于是编写相关测试代码，读取excel文件内容及图片信息.

本文使用的是 NPOI-2.6.2 版本，此版本持.Net4.7.2;.NetStandard2.0;.NetStandard2.1;.Net6.0+。

测试文档内容，如下图：

保存后的图片：

打开图片显示正常：

编写读取数据方法，代码如：

static public DataTable ReadExcel(string filePath, string _sDirImg)
{using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)){IWorkbook workbook = null;string sExt = Path.GetExtension(filePath).ToLower();if (sExt == ".xlsx"){workbook = new XSSFWorkbook(file);//新版本的Excel（.xlsx） }else{workbook = new HSSFWorkbook(file);//老版本的Excel（.xls） }// 读取第一个 SheetISheet sheet = workbook.GetSheetAt(0);if (sExt == ".xlsx"){fnReadImageXSSF(sheet, _sDirImg);//新版本的Excel（.xlsx） }else{fnReadImageHSSF(sheet, _sDirImg);//老版本的Excel（.xls） }// 创建 DataTableDataTable dataTable = new DataTable(sheet.SheetName);// 读取表头IRow headerRow = sheet.GetRow(0);for (int i = 0; i < headerRow.LastCellNum; i++){ICell cell = headerRow.GetCell(i);dataTable.Columns.Add(cell.ToString(), typeof(string));}// 读取数据行for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++){IRow row = sheet.GetRow(rowNum);DataRow dataRow = dataTable.NewRow();for (int i = 0; i < row.LastCellNum; i++){ICell cell = row.GetCell(i);if (null == cell) continue;dataRow[i] = cell.ToString();}dataTable.Rows.Add(dataRow);}return dataTable;}
}

编写对应不同版本的excel文件，读取图片方法，读取excel .xlsx文件内图片：

/// <summary>
/// 读取excel .xlsx文件内图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="_sDirImg"></param>
static public void fnReadImageXSSF(ISheet sheet, string _sDirImg)
{// 读取图像信息foreach (XSSFShape shape in ((XSSFDrawing)sheet.DrawingPatriarch).GetShapes()){if (shape is XSSFPicture){XSSFPicture picture = (XSSFPicture)shape;// 获取图片所在单元格的行号和列号int rowIndex = picture.GetPreferredSize().Row1;int colIndex = picture.GetPreferredSize().Col1;// 获取图像文件格式string imageFormat = picture.PictureData.MimeType switch{"image/jpeg" => "jpeg","image/png" => "png","image/gif" => "gif","image/bmp" => "bmp",_ => "jpg"};// 保存图像文件string outputFileName = _sDirImg + $"{rowIndex}-{colIndex}-{Guid.NewGuid()}.{imageFormat}";using (FileStream imageFile = new FileStream(outputFileName, FileMode.Create)){imageFile.Write(picture.PictureData.Data, 0, picture.PictureData.Data.Length);}Console.WriteLine($"Saved image: {outputFileName}");}}
}

读取excel .xls文件内图片，代码如下：

/// <summary>
/// 读取excel .xls文件内图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="_sDirImg"></param>
static public void fnReadImageHSSF(ISheet sheet, string _sDirImg)
{// 读取图像信息foreach (HSSFShape shape in ((HSSFPatriarch)sheet.DrawingPatriarch).Children){if (shape is HSSFPicture){HSSFPicture picture = (HSSFPicture)shape;// 获取图片所在单元格的行号和列号int rowIndex = picture.GetPreferredSize().Row1;int colIndex = picture.GetPreferredSize().Col1;// 获取图像文件格式string imageFormat = picture.PictureData.MimeType switch{"image/jpeg" => "jpeg","image/png" => "png","image/gif" => "gif","image/bmp" => "bmp",_ => "jpg"};// 保存图像文件string outputFileName = _sDirImg + $"{rowIndex}-{colIndex}-{Guid.NewGuid()}.{imageFormat}";using (FileStream imageFile = new FileStream(outputFileName, FileMode.Create)){imageFile.Write(picture.PictureData.Data, 0, picture.PictureData.Data.Length);}Console.WriteLine($"Saved image: {outputFileName}");}}
}

正常处理应该是读取到图片保存成功后，处理datatable图片列的相关地址，如：uploads/xxx/xx.jpg ，返回保存在服务器上的地址，以便前端访问或保存到数据库等，本文并没有处理，有需要的伙伴自行处理吧，希望本文对你有帮助。