Excel生成 chart 混合图表

YANG-Live

已于 2024-03-22 10:34:34 修改

阅读量522

点赞数 10

分类专栏： C# .NetCore 文章标签： excel

于 2024-03-11 11:32:09 首次发布

本文链接：https://blog.csdn.net/qq_38567182/article/details/136617500

版权

C# 同时被 2 个专栏收录

28 篇文章 1 订阅

订阅专栏

.NetCore

16 篇文章 1 订阅

订阅专栏

在开发中有这样一个需求，邮件预警的时候，要求邮件主体内容是一个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);

        }

    }
}

往表里面添加图片
图片位置是 A1:A5

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
FileInfo fileInfo = new FileInfo("C:\\3月基础数据-合计-自动计算.xlsx");
using (ExcelPackage package = new ExcelPackage(fileInfo))
{
    // 选择需要导出的工作表
    ExcelWorksheet worksheet = package.Workbook.Worksheets["分析"];

    // 设置保存路径
    string savePath = "C:\\Images\\output999.png";

    // 将A1到A5单元格区域的内容转换为图片并保存
    var range = worksheet.Cells["A1:A5"];
    ExcelRange excelRange = worksheet.Cells[range.Start.Row, range.Start.Column, range.End.Row + 5, range.End.Column];

    // 从文件加载图片
    using (System.Drawing.Image image = System.Drawing.Image.FromFile(savePath))
    {
        // 将图片添加到工作表
        var excelImage = worksheet.Drawings.AddPicture("Sample", image);
        excelImage.SetPosition(excelRange.Start.Row, 0, excelRange.Start.Column, 0);
        package.Save();
    }
}