【NPOI】导出Excel复杂表格并支持导出图片

最新推荐文章于 2024-03-25 10:48:16 发布

beeter1

最新推荐文章于 2024-03-25 10:48:16 发布

阅读量1k

点赞数 22

文章标签： c# excel

本文链接：https://blog.csdn.net/beeter1/article/details/135019095

版权

这是效果附上源码

public static MemoryStream ToExcel(params DataTable[] table)
{
MemoryStream ms = new();
//IWorkbook wb = new HSSFWorkbook();
//创建表
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sh = wb.CreateSheet("zhiyuan");
//设置单元的宽度
sh.SetColumnWidth(8, 15 * 256);
sh.SetColumnWidth(9, 35 * 256);
sh.SetColumnWidth(10, 15 * 256);
sh.SetColumnWidth(11, 15 * 256);
#region 练习合并单元格
sh.AddMergedRegion(new CellRangeAddress(0, 0, 8, 11));
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sh.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(8);
icell1top0.CellStyle = GetCellStyle(wb, Stylexls.头);
icell1top0.SetCellValue("网站汇总");
#endregion
#region 设置表头
IRow row1 = sh.CreateRow(1);
row1.Height = 20 * 20;

ICell icell1top = row1.CreateCell(8);
icell1top.CellStyle = GetCellStyle(wb, Stylexls.头);
icell1top.SetCellValue("网站名");

ICell icell2top = row1.CreateCell(9);
icell2top.CellStyle = GetCellStyle(wb, Stylexls.头);
icell2top.SetCellValue("网址");

ICell icell3top = row1.CreateCell(10);
icell3top.CellStyle = GetCellStyle(wb, Stylexls.头);
icell3top.SetCellValue("快照");

ICell icell4top = row1.CreateCell(11);
icell4top.CellStyle = GetCellStyle(wb, Stylexls.头);
icell4top.SetCellValue("百度收录");
#endregion
#region //给行的单元格填充数据
List<Infos> list = new()
{
new Infos
{
Webname = "百度",
Website = "https://www.baidu.com/",
Picture = "https://alifei05.cfp.cn/creative/vcg/800/new/VCG41N1061717050.jpg",
EmployInfo = new List<EmployInfos>()
{
new EmployInfos { Employ = "百度1"},
new EmployInfos { Employ = "百度2"},
new EmployInfos { Employ = "百度3"},
new EmployInfos { Employ = "百度4"},
new EmployInfos { Employ = "百度5"},

}
},
new Infos
{
Webname = "淘宝",
Website = "https://www.taobao.com/",
Picture = "https://alifei05.cfp.cn/creative/vcg/800/new/VCG211140531510.jpg",
EmployInfo = new List<EmployInfos>()
{
new EmployInfos { Employ = "淘宝1"},
new EmployInfos { Employ = "淘宝2"},
new EmployInfos { Employ = "淘宝3"},
new EmployInfos { Employ = "淘宝4"},
new EmployInfos { Employ = "淘宝5"},

}
},
new Infos
{
Webname = "京东",
Website = "https://www.jd.com/",
Picture = "https://tenfei05.cfp.cn/creative/vcg/800/new/VCG41N763174519.jpg",
EmployInfo = new List<EmployInfos>()
{
new EmployInfos { Employ = "京东1"},
new EmployInfos { Employ = "京东2"},
new EmployInfos { Employ = "京东3"},
new EmployInfos { Employ = "京东4"},
new EmployInfos { Employ = "京东5"},
}
},
new Infos
{
Webname = "拼多多",
Website = "https://www.pinduoduo.com/",
Picture = "https://tenfei04.cfp.cn/creative/vcg/800/new/VCG211160972299.jpg",
EmployInfo = new List<EmployInfos>()
{
new EmployInfos { Employ = "拼多多1"},
new EmployInfos { Employ = "拼多多2"},
new EmployInfos { Employ = "拼多多3"},
new EmployInfos { Employ = "拼多多4"},
new EmployInfos { Employ = "拼多多5"},

}
},
};
int EmployRowNum = 2;
int StartNum = 2;
for (int i = 0; i < list.Count; i++)
{
CellRangeAddress region = new(StartNum, StartNum + 4, 8, 8);
CellRangeAddress region1 = new(StartNum, StartNum + 4, 9, 9);
CellRangeAddress region2 = new(StartNum, StartNum + 4, 10, 10);
sh.AddMergedRegion(region);
sh.AddMergedRegion(region1);
sh.AddMergedRegion(region2);

foreach (var item in list[i].EmployInfo)
{
IRow dataRow = sh.CreateRow(EmployRowNum);
ICell cell = dataRow.CreateCell(8);
ICell cell1 = dataRow.CreateCell(9);
ICell cell2 = dataRow.CreateCell(10);
cell.CellStyle = GetContentrStyle(wb, Stylexls.默认);
cell1.CellStyle = GetContentrStyle(wb, Stylexls.url);
cell2.CellStyle = GetContentrStyle(wb, Stylexls.默认);
if (EmployRowNum == StartNum) //给合并的单元格填充数据
{
cell.SetCellValue(list[i].Webname);
cell1.SetCellValue(list[i].Website);
AddCellPicture(sh, wb, list[i].Picture, StartNum, 10, 5);
//cell2.SetCellValue(list[i].Picture);
}
ICell cell3 = dataRow.CreateCell(11);
cell3.CellStyle = GetContentrStyle(wb, Stylexls.默认);
cell3.SetCellValue(item.Employ);
EmployRowNum++;
}

StartNum += 5;
//给行的单元格填充数据
}
#endregion
wb.Write(ms);
return ms;
}
//信息类
public class Infos
{

public string Webname { get; set; }
public string Website { get; set; }
public string Picture { get; set; }
public List<EmployInfos> EmployInfo { get; set; }

}
/// <summary>
/// 向sheet插入图片
/// </summary>
private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, string imgPath, int row, int col, int add)
{
Uri uri = new(imgPath); //imgPath :网络图片地址
WebRequest webRequest = WebRequest.Create(uri);

using WebResponse webResponse = webRequest.GetResponse();
Bitmap bitmap = new(webResponse.GetResponseStream()); //读取图片流
Bitmap OldImage = new(bitmap);//将图片流复制到新的图片流中
bitmap.Dispose(); //将原来的图片流释放，将图片文件进行解锁。
using MemoryStream ms = new();
OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.ToArray();
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); //添加图片
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
// 1.解释XSSFClientAnchor
//XSSFClientAnchor anchor = new XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
// 2解释
//dx1：表示图片左上角相对于单元格左边界的X轴偏移量，单位为英寸 / EMU；
//dy1：表示图片左上角相对于单元格顶部边界的Y轴偏移量，单位为英寸 / EMU；
//dx2：表示图片右下角相对于单元格左下角的X轴偏移量，单位为英寸 / EMU；
//dy2：表示图片右下角相对于单元格左下角的Y轴偏移量，单位为英寸 / EMU；
//col1：表示该锚点对应的起始单元格列号，从0开始计数；
//row1：表示该锚点对应的起始单元格行号，从0开始计数；
//col2：表示该锚点对应的结束单元格列号，从0开始计数；
//row2：表示该锚点对应的结束单元格行号，从0开始计数。
XSSFClientAnchor anchor = new(0, 0, 0, 0, col, row, col + 1, row + add);
//图片位置，图片左上角为（col, row）
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize(); //用图片原始大小来显示
}
public class EmployInfos
{
public string Employ { get; set; }
}
#region 定义单元格常用到样式的枚举
public enum Stylexls
{
头,
url,
时间,
数字,
钱,
百分比,
中文大写,
科学计数法,
默认
}
#endregion
#region 定义单元格常用到样式
private static ICellStyle GetContentrStyle(IWorkbook wb, Stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.FillPattern = (FillPattern)1; // 设置填充模式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center; //上下垂直对齐
cellStyle.Alignment = HorizontalAlignment.Center; //左右对齐

IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontHeightInPoints = 12;//字体大小
fontcolorblue.FontName = "微软雅黑";

IFont font = wb.CreateFont();
font.FontHeightInPoints = 12;//字体大小
font.FontName = "微软雅黑";

//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case Stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();

cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case Stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case Stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case Stylexls.url:
fontcolorblue.Underline = (FontUnderlineType)1;
cellStyle.SetFont(fontcolorblue);
break;
case Stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case Stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case Stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case Stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
private static ICellStyle GetCellStyle(IWorkbook wb, Stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();

//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.Color = IndexedColors.White.Index; // 字体颜色为白色
font12.FontHeightInPoints = 14;//字体大小
font12.IsBold = true; //字体加粗
//font12.FontName = "微软雅黑";

IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线

//行头样式
cellStyle.FillForegroundColor = IndexedColors.White.Index; // 设置字体颜色为白色
cellStyle.FillPattern = (FillPattern)1; // 设置填充模式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center; //上下垂直对齐
cellStyle.Alignment = HorizontalAlignment.Center; //左右对齐
//边框颜色
//cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Yellow.Index;
//cellStyle.TopBorderColor = HSSFColor.OliveGreen.Yellow.Index;

//背景图形
cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;

//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;

//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;

cellStyle.FillForegroundColor = IndexedColors.Green.Index; // 设置填充颜色
//自动换行
cellStyle.WrapText = true;

//缩进;
cellStyle.Indention = 0;

//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case Stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case Stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;

}
#endregion

beeter1

关注

22
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
【NPOI】导出Excel复杂表格并支持导出图片

cell.CellStyle = GetContentrStyle(wb, Stylexls.默认);icell1top0.CellStyle = GetCellStyle(wb, Stylexls.头);icell1top.CellStyle = GetCellStyle(wb, Stylexls.头);icell3top.CellStyle = GetCellStyle(wb, Stylexls.头);
复制链接

扫一扫