在开发中有这样一个需求，邮件预警的时候，要求邮件主体内容是一个Chart 图表（生成后的img），邮件需要有附件，且附件是Excel列表加图表，图表类型是混合图。
回顾：在之前一篇讲到如何使用 EPPlus创建excel 、批量填充、设置套用表格格式、创建chart 图表、设置chart 图表主题。
优点处理简单，生成速度快，批量填充很好用、灵活设置表格样式，自动填充数据和数据格式，对于单一生成excel 表格很友好，唯一不足的地方是chart 图表只能设置单一图表类型，不能设置混合图表，不能把Excel chart 图表读取出来生成img 图片。
回到需求，要实习的功能
一、图表创建、批量填充 、套用表格格式
二、在创建的Excel里面创建 chart 混合图表，设置双Y轴
三、读取 Excel里面的图表，把图表生成img 图片
四、Excel 附件和图片不做本地保存直接通过邮件发送出去
代码：
using Spire.Xls;
using Spire.Xls.Charts;
using System;
using System.Collections.Generic;
using System.Drawing.Imaging;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data;
using System.IO;
using System.Net.Mail;
using System.Net;
namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
if (false)
{
Main3();
return;
}
//创建一个Workbook类实例，加载Excel文档
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\XXXXXXXXXXXX\bin\Debug\Line.xlsx");
//获取第一个工作表
Worksheet sheet = workbook.Worksheets[0];
//设置工作表的名称
sheet.Name = "柱状图";
sheet.GridLinesVisible = false;
//创建柱状图
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
//指定用于生成图表的数据区域
chart.DataRange = sheet.Range["A2:E4"];
chart.SeriesDataFromRange = false;
//指定图表所在的位置
chart.LeftColumn = 1;
chart.TopRow = 9;
chart.RightColumn = 12;
chart.BottomRow = 26;
//设置图表的名称及字体格式
chart.ChartTitle = "上半年产品销售情况(单位：万美元)";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
//设置X轴坐标名称及字体格式
chart.PrimaryCategoryAxis.Title = "产品类别";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = false;
//设置Y轴坐标名称及字体格式
chart.PrimaryValueAxis.Title = "销售额";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 0.5;
chart.PrimaryValueAxis.TitleArea.IsBold = false;
//设置图例的位置
chart.Legend.Position = LegendPositionType.Right;
//保存文档
workbook.SaveToFile("ColumnChart.xlsx", ExcelVersion.Version2013);
//加载生成图表后的Excel文档
workbook.LoadFromFile("ColumnChart.xlsx");
//遍历工作簿，诊断是否包含图表
Image[] images = workbook.SaveChartAsImage(sheet);
for (int i = 0; i < images.Length; i++)
{
//将图表保存为图片
images[i].Save(string.Format("img-{0}.png", i), ImageFormat.Png);
}
}
public static void Main2()
{
// 创建Workbook对象
Workbook workbook = new Workbook();
// 获取第一个工作表
Worksheet sheet = workbook.Worksheets[0];
// 创建一个DataTable并填充数据
DataTable table = new DataTable();
table.Columns.Add("Category", typeof(string));
table.Columns.Add("Value1", typeof(int));
table.Columns.Add("Value2", typeof(int));
table.Columns.Add("Value3", typeof(int));
table.Columns.Add("Value4", typeof(int));
table.Rows.Add("A", 7, 950, 680, 980);
table.Rows.Add("B", 8, 500, 720, 1070);
table.Rows.Add("C", 9, 900, 890, 1200);
//Category Value1 Value2 Value3 Value4
// 将DataTable中的数据批量填充到Excel表格中
sheet.InsertDataTable(table, true, 1, 1);
设置数据
//sheet.Range["A1"].Text = "Category";
//sheet.Range["A2"].Text = "A";
//sheet.Range["A3"].Text = "B";
//sheet.Range["A4"].Text = "C";
//sheet.Range["B1"].Text = "Value1";
//sheet.Range["B2"].NumberValue = 7;
//sheet.Range["B3"].NumberValue = 8;
//sheet.Range["B4"].NumberValue = 9;
//sheet.Range["C1"].Text = "Value2";
//sheet.Range["C2"].NumberValue = 950;
//sheet.Range["C3"].NumberValue = 500;
//sheet.Range["C4"].NumberValue = 900;
//sheet.Range["D1"].Text = "Value3";
//sheet.Range["D2"].NumberValue = 680;
//sheet.Range["D3"].NumberValue = 720;
//sheet.Range["D4"].NumberValue = 890;
//sheet.Range["E1"].Text = "Value4";
//sheet.Range["E2"].NumberValue = 980;
//sheet.Range["E3"].NumberValue = 1070;
//sheet.Range["E4"].NumberValue = 1200;
// 添加柱状图
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:E4"];
chart.SeriesDataFromRange = false;
chart.TopRow = 7;
chart.BottomRow = 28;
chart.LeftColumn = 3;
chart.RightColumn = 11;
var cs1 = (ChartSerie)chart.Series[0];
cs1.SerieType = ExcelChartType.ColumnClustered;
var cs2 = (ChartSerie)chart.Series[1];
cs2.SerieType = ExcelChartType.ColumnClustered;
var cs3 = (ChartSerie)chart.Series[2];
cs3.SerieType = ExcelChartType.Line;
chart.SecondaryCategoryAxis.IsMaxCross = true;
cs3.UsePrimaryAxis = false;
// 保存Excel文件
workbook.SaveToFile("Line.xlsx", ExcelVersion.Version2010);
}
public static void Main3()
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
DataTable table = new DataTable();
table.Columns.Add("Category", typeof(string));
table.Columns.Add("Value1", typeof(int));
table.Columns.Add("Value2", typeof(int));
table.Columns.Add("Value3", typeof(int));
table.Columns.Add("Value4", typeof(int));
table.Rows.Add("A", 7, 950, 680, 980);
table.Rows.Add("B", 8, 500, 720, 1070);
table.Rows.Add("C", 9, 900, 890, 1200);
// Category Value1 Value2 Value3 Value4
// 将DataTable中的数据批量填充到Excel表格中
sheet.InsertDataTable(table, true, 1, 1);
// 添加柱状图
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:E4"];
chart.SeriesDataFromRange = false;
chart.TopRow = 7;
chart.BottomRow = 28;
chart.LeftColumn = 3;
chart.RightColumn = 11;
var cs1 = (ChartSerie)chart.Series[0];
cs1.SerieType = ExcelChartType.ColumnClustered;
var cs2 = (ChartSerie)chart.Series[1];
cs2.SerieType = ExcelChartType.ColumnClustered;
var cs3 = (ChartSerie)chart.Series[2];
cs3.SerieType = ExcelChartType.Line;
chart.SecondaryCategoryAxis.IsMaxCross = true;
cs3.UsePrimaryAxis = false;
//保存Excel文件
//workbook.SaveToFile("Line.xlsx", ExcelVersion.Version2010);
workbook.Version = ExcelVersion.Version2013;
//遍历工作簿，诊断是否包含图表
Image[] images = workbook.SaveChartAsImage(sheet);
/*
for (int i = 0; i < images.Length; i++)
{
//将图表保存为图片
//images[i].Save(string.Format("img-{0}.png", i), ImageFormat.Png);
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string imagePath = Path.Combine(desktopPath, string.Format("img-{0}.png", i));
images[i].Save(imagePath, ImageFormat.Png);
}
*/
string[] base64Strings = new string[images.Length];
for (int i = 0; i < images.Length; i++)
{
using (MemoryStream ms = new MemoryStream())
{
images[i].Save(ms, ImageFormat.Png);
byte[] imageBytes = ms.ToArray();
base64Strings[i] = Convert.ToBase64String(imageBytes);
}
}
// 创建 HTML 内容，将图片嵌入其中
string htmlBody = "<html><body>";
htmlBody += "<p>邮件正文</p>";
htmlBody += string.Format("<img src='data:image/png;base64,{0}' />", base64Strings[0]);
htmlBody += "</body></html>";
// 发送邮件并包含 HTML 内容
MailMessage mail = new MailMessage();
mail.From = new MailAddress("qy.dan@.com.hk");
mail.To.Add("qy.dan.com.hk");
mail.Subject = "包含图片的邮件";
mail.IsBodyHtml = true;
mail.Body = htmlBody;
SmtpClient smtp = new SmtpClient("XXX.XXX.105.69");
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.EnableSsl = false;
smtp.Host = "XXX.XXX.105.69";
smtp.Port = XXX;
smtp.UseDefaultCredentials = true;
smtp.Send(mail);
}
}
}
效果:
插件：
Spire.XLS 下载
Spire.XLS 文档
Spire.XLS
https://www.cnblogs.com/landeanfen/p/5888973.html
csdn：
Spire.XLS 系列教程1
Spire.XLS 系列教程2
Spire.XLS 系列教程3
Spire.XLS 系列教程4
扩展参考：
https://www.cnblogs.com/asxinyu/p/4374015.html
https://www.cnblogs.com/asxinyu/p/Bolg_Category_For_TotalAll.html
![[Java并发编程] Java开发必须知道的synchronized关键字](https://img-blog.csdnimg.cn/direct/33c9bf88d8fa46fa8d4f635a0c7dac2e.png)
