当你需要ChatAI服务但无法魔法或没有海外手机号码时,Chat8就是你的解决方案。我们基于OpenAi开发,所有用户内容都会加密,欢迎使用!点击使用:
https://chat.chat826.com/#/register?bronk_on=375671
1,引入程序集 EPPlus
2,直接上代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using LicenseContext = OfficeOpenXml.LicenseContext;
List<Person> people = new List<Person>();
people.Add(new Person() { name = "zxb", age = 11, gender = "n" });
people.Add(new Person() { name = "zxb", age = 18, gender = "n1" });
people.Add(new Person() { name = "zxb", age = 19, gender = "m3" });
people.Add(new Person() { name = "zxb1", age = 11, gender = "n4" });
people.Add(new Person() { name = "zxb1", age = 11, gender = "n5" });
people.Add(new Person() { name = "zxb1", age = 11, gender = "n6" });
people.Add(new Person() { name = "zxb2", age = 18, gender = "n71" });
people.Add(new Person() { name = "zxb2", age = 18, gender = "n81" });
people.Add(new Person() { name = "zxb3", age = 19, gender = "m9" });
people.Add(new Person() { name = "zxb3", age = 19, gender = "m10" });
people.Add(new Person() { name = "zxb3", age = 19, gender = "m11" });
List<string> hearderList = new List<string>();
hearderList.Add("姓名");
hearderList.Add("年龄");
hearderList.Add("性别");
WriteToExcel(people, hearderList);
public class Person
{
public string name { get; set; }
public int age { get; set; }
public string gender { get; set; }
}
public static void WriteToExcel(List<Person> people, List<string> header)
{
// 设置 LicenseContext 属性
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
// 创建 ExcelPackage 对象
using (var package = new ExcelPackage())
{
// 获取 Excel 文件的工作表
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
worksheet.Row(1).Height = 26;
// 将工作表中的所有单元格设置为居中对齐
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// 按照 name 分组
var groups = people.GroupBy(p => p.name);
worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1].Value = " 统计月份 ";
worksheet.Cells[1, 2, 1, 10].Merge = true;
worksheet.Cells[1, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Cells[1, 2].Value = "2022年03月";
//头部
for (int i = 0; i < header.Count; i++)
{
worksheet.Cells[2, i + 1].Value = header[i];
}
// 遍历每个分组
int row = 3;
foreach (var group in groups)
{
// 合并单元格
worksheet.Cells[row, 1, row + group.Count() - 1, 1].Merge = true;
// 写入分组名
worksheet.Cells[row, 1].Value = group.Key;
// 写入每个 Person 对象的信息
foreach (var person in group)
{
worksheet.Cells[row, 2].Value = person.age;
worksheet.Cells[row, 3].Value = person.gender;
row++;
}
//row++; // 空一行
}
// 保存 Excel 文件
package.SaveAs(new FileInfo("output2.xlsx"));
}
}
2,写入图片
在 EPPlus 中,可以通过 ExcelPicture 类将图片写入 Excel 工作表。下面是一个示例代码,演示如何将本地磁盘上的图片文件插入到 Excel 工作表中指定的单元格中:
public static void InsertImage(string imagePath, ExcelWorksheet worksheet, int row, int col)
{
// 读取图片文件
var image = Image.FromFile(imagePath);
// 计算图片的大小
var width = image.Width;
var height = image.Height;
// 在单元格中插入图片
var picture = worksheet.Drawings.AddPicture("Picture", image);
picture.SetPosition(row, col);
picture.SetSize(width, height);
}
在上述代码中,我们首先使用 Image.FromFile 方法从磁盘中读取图片文件。然后通过 worksheet.Drawings.AddPicture 方法在工作表中添加图片,并将其命名为 “Picture”。接着,我们可以使用 picture.SetPosition 方法将图片放置到指定的单元格中,使用 picture.SetSize 方法设置图片的大小。
需要注意的是,SetPosition 方法的前两个参数表示图片的起始行和列,而不是单元格的坐标。因此,在将图片插入到单元格之前,需要计算单元格左上角的行和列。如果需要在单元格中心插入图片,可以使用以下代码计算单元格的坐标:
在计算出单元格的坐标之后,我们可以使用以下代码将图片插入到单元格的中心位置:
var cell = worksheet.Cells[row, col];
var left = cell.Left;
var top = cell.Top;
var right = left + cell.Width;
var bottom = top + cell.Height;
其中,picture.SetPosition 方法的最后两个参数分别表示图片的左上角的横坐标和纵坐标。由于我们要将图片放置在单元格的中心位置,因此将其设置为单元格左上角的横坐标和纵坐标即可。
var picture = worksheet.Drawings.AddPicture("Picture", image);
picture.SetPosition(0, 0, left, top);
picture.SetSize(cell.Width, cell.Height);