发布：2023/3/24 22:20:45作者：管理员 来源：本站 浏览次数：778
本次实现技术为.NET Core 3.1
导出的Excel项目中，包含如下的一张图片：
将当前图片导出,代码如下：
控制器代码：
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
[HttpGet]
public async Task<ApiResponse> ExportExcelAchemesDataList(string code, string type)
{
PagedList<dynamic> data = _eventRecordServices.GetQRCodeList(code, type, 1, 10000);
if (data==null)
{
return ApiResponse.Fail("暂无数据");
}
//导出excel
IWorkbook workbook = new HSSFWorkbook();//创建Workbook
workbook.CreateSheet("巡检二维码");//创建sheet
string FileName = "巡检二维码.xls";
string Paths = Path.GetFullPath("..");
string path = "";
foreach (var item in Paths.Split("\\"))
{
if (item == "PandaSewerage")
{
break;
}
path += item + "\\";
}
string guid = Guid.NewGuid().ToString();
string stringPath = path + $"BufFile\\OutFiles\\DownLoadFiles\\ExportExcel\\{guid}\\";
string filePath = $"{stringPath}";
if (!System.IO.Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
using (FileStream fs = System.IO.File.Create(filePath + FileName))
{
#region 考核列表
ISheet sheet = workbook.GetSheetAt(0);//获取sheet
IRow rowHeader = sheet.CreateRow(0);
//创建头部
rowHeader.CreateCell(0).SetCellValue("污水厂名称");
rowHeader.CreateCell(1).SetCellValue("设备编码");
rowHeader.CreateCell(2).SetCellValue("二维码名称");
rowHeader.CreateCell(3).SetCellValue("二维码类型");
rowHeader.CreateCell(4).SetCellValue("二维码");
rowHeader.CreateCell(5).SetCellValue("编制人");
rowHeader.CreateCell(6).SetCellValue("编制时间");
//创建头部样式和列宽度
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center; // 居中
IFont titleFont = workbook.CreateFont();
titleFont.IsBold = true;
titleFont.FontHeightInPoints = 12;
titleFont.Color = HSSFColor.Black.Index;//设置字体颜色
titleStyle.SetFont(titleFont);
sheet.GetRow(0).GetCell(0).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(1).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(2).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(3).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(4).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(5).CellStyle = titleStyle;
sheet.GetRow(0).GetCell(6).CellStyle = titleStyle;
short defHeight = sheet.DefaultRowHeight;
sheet.SetColumnWidth(0, 5000);
sheet.SetColumnWidth(1, 5000);
sheet.SetColumnWidth(2, 5000);
sheet.SetColumnWidth(3, 5000);
sheet.SetColumnWidth(4, 8000);
sheet.SetColumnWidth(5, 5000);
sheet.SetColumnWidth(6, 6000);
//创建内容样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center; ;//垂直居中
IFont font = workbook.CreateFont();
font.IsBold = false;
font.FontHeightInPoints = 12;
font.Color = HSSFColor.Black.Index;
style.SetFont(font);
//赋值
int x = 1;
for (int i = 0; i < data.list.Count; i++)
{
IRow rowData = sheet.CreateRow(i + 1);
rowData.CreateCell(0).SetCellValue(data.list[i].污水厂名称);
rowData.GetCell(0).CellStyle = style;
rowData.CreateCell(1).SetCellValue(data.list[i].关联编码);
rowData.GetCell(1).CellStyle = style;
rowData.CreateCell(2).SetCellValue(data.list[i].二维码名称);
rowData.GetCell(2).CellStyle = style;
rowData.CreateCell(3).SetCellValue(data.list[i].二维码类型);
rowData.GetCell(3).CellStyle = style;
string picurl = data.list[i].二维码; //图片存储路径
if (ImageExists(picurl))
{
rowData.Height = 130 * 20;
AddPieChart(sheet, workbook, picurl, i + 1, 4);
//rowData.GetCell(4).CellStyle = style;
}
else
{
rowData.Height = defHeight;
//rowData.CreateCell(4).SetCellValue(data.list[i].二维码);
rowData.CreateCell(4).SetCellValue("未找到二维码");
rowData.GetCell(4).CellStyle = style;
}
rowData.CreateCell(5).SetCellValue(data.list[i].编制人);
rowData.GetCell(5).CellStyle = style;
rowData.CreateCell(6).SetCellValue(data.list[i].编制时间.ToString());
rowData.GetCell(6).CellStyle = style;
}
#endregion
workbook.Write(fs);
var relativePositioning = $"\\BufFile\\OutFiles\\DownLoadFiles\\ExportExcel\\{guid}\\" + FileName;
return ApiResponse.OK(relativePositioning);
//return Ok(new { code = 0, msg = "成功", data = relativePositioning });
//return File(ms, "application/vnd.ms-excel", fileName);
}
}
关键代码导出图片
private void AddPieChart(ISheet sheet, IWorkbook workbook, string fileurl, int row, int col)
{
try
{
string Paths = Path.GetFullPath("..");
string path = "";
foreach (var item in Paths.Split("\\"))
{
if (item == "PandaSewerage")
{
break;
}
path += item + "\\";
}
string stringPath = path.TrimEnd('\\') + $"{fileurl.Replace("/", "\\")}";
if (System.IO.File.Exists(stringPath))
{
string FileName = stringPath;
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 30, 30, col, row, col+1 , row+1 );
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
// pict.Resize();这句话一定不要，这是用图片原始大小来显示
}
}
}
catch (Exception ex)
{
}
}
效果图：
代码简单说明：
显示图片核心代码为【HSSFClientAnchor】这个方法，具体说明如下：
dx1 dy1 起始单元格中的x,y坐标.
dx2 dy2 结束单元格中的x,y坐标
col1,row1 指定起始的单元格，下标从0开始
col2,row2 指定结束的单元格 ，下标从0开始
