C#导出Excel表格，实现跨行/跨列...支持各种组合样式

C#生成Excel跨行/跨列…支持各种样式

效果示例

基本能满足各种样式需求
在这里插入图片描述

安装包

首先在项目中安装包：DocumentFormat.OpenXml
在这里插入图片描述

使用

部分使用方法

		//Cells[3, 1, 3, 2]：这是用于指定 Excel 单元格的范围。//3, 1：表示开始的单元格，位于第 3 行，第 1 列。//3, 2：表示结束的单元格，位于第 3 行，第 2 列。// 合并 [2,1] 到 [3,1]，使船名占两行一列worksheet.Cells[2, 1, 3, 1].Merge = true;// 设置水平和垂直居中对齐worksheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;worksheet.Cells[2, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;// 设置单元格边框（注意边框区域和字体设置）worksheet.Cells[2, 1, 3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);// 设置字体为华文楷体worksheet.Cells[2, 1].Style.Font.Name = "华文楷体";//这行代码的作用是启用单元格中的自动换行功能。worksheet.Cells[3, 1].Style.WrapText = true; //字体加粗worksheet.Cells[2, 1, 3, 21].Style.Font.Bold = true;// 冻结前三行----在第四行和第一列的位置冻结窗格worksheet.View.FreezePanes(4, 1);// 冻结左边四列---从第一行和第五列开始滚动，因此左边的四列（1到4列）会被固定worksheet.View.FreezePanes(1, 5);// 冻结前三行和左边四列--组合使用worksheet.View.FreezePanes(4, 5);// 设置背景颜色worksheet.Cells[3, 20].Style.Fill.PatternType = ExcelFillStyle.Solid;                      // 设置填充类型为实心worksheet.Cells[3, 20].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#fce4d6")); // 设置背景颜色为 #fce4d6//设置行高度worksheet.Row(1).Height = 50;// 自动调整列宽foreach (var col in worksheet.Columns){col.AutoFit();}//设置列宽度worksheet.Column(3).Width = 13;

后端代码

using Microsoft.Extensions.Hosting;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;private readonly TokenBLL _tokenBLL;private IHostEnvironment _hostingEnvironment;public WharfInfoBLL(TokenBLL tokenBLL, IHostEnvironment hostingEnvironment){_hostingEnvironment = hostingEnvironment;_tokenBLL = tokenBLL;}/// <summary>/// 测试Excel/// </summary>/// <param name="json">前端参数集成json</param>/// <returns>数据集</returns> public object ExportToExcel(string json){ExcelPackage.LicenseContext = LicenseContext.NonCommercial;using (var package = new ExcelPackage()){// 创建一个新的工作表var worksheet = package.Workbook.Worksheets.Add("ReportSummary");worksheet.Cells[1, 1, 1, 5].Merge = true;worksheet.Cells[1, 1].Value = "测试Excel";worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;worksheet.Cells[1, 1].Style.Font.Size = 14;worksheet.Cells[1, 1].Style.Font.Name = "华文仿宋";worksheet.Cells[1, 1].Style.Font.Bold = true;worksheet.Cells[2, 1].Merge = true;worksheet.Cells[2, 1].Value = "一行一列";worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);worksheet.Cells[2, 2, 2, 3].Merge = true;worksheet.Cells[2, 2, 2, 3].Value = "合并2列";worksheet.Cells[2, 2, 2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);worksheet.Cells[2, 4, 3, 5].Merge = true;worksheet.Cells[2, 4, 3, 5].Value = "合并2行";worksheet.Cells[2, 1, 2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);worksheet.Cells[3, 1, 3, 3].Merge = true;worksheet.Cells[3, 1, 3, 3].Value = "变背景色";worksheet.Cells[3, 1, 3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);// 设置背景颜色worksheet.Cells[3, 1, 3, 3].Style.Fill.PatternType = ExcelFillStyle.Solid;worksheet.Cells[3, 1, 3, 3].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#fce4d6"));//整体样式worksheet.Cells[2, 1, 3, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;worksheet.Cells[2, 1, 3, 5].Style.VerticalAlignment = ExcelVerticalAlignment.Center;worksheet.Cells[2, 1, 3, 5].Style.Font.Name = "华文楷体";worksheet.Cells[2, 1, 3, 5].Style.WrapText = true;// 冻结前一行和左边一列worksheet.View.FreezePanes(2, 2);worksheet.Row(1).Height = 50;worksheet.Row(5).Height = 35;// 自动调整列宽foreach (var col in worksheet.Columns){col.AutoFit();}worksheet.Column(3).Width = 13;worksheet.Column(4).Width = 14;worksheet.Column(5).Width = 17;string fileName = "测试Excel" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";// 保存到文件string strPath = Path.Combine(_hostingEnvironment.ContentRootPath, "wwwroot").ReplacePath() + "/PExcel/" + fileName;FileInfo file = new FileInfo(strPath);package.SaveAs(file);return fileName.Replace(@"\", @"\\");}}

前端代码

//导出
function Export() {proxy.$refs["formRef"].validate((valid) => {if (valid) {ExportShipSoilExcel(form.value).then((res) => {const url =import.meta.env.VITE_APP_SERVER_API +"/tool/file/downloadapkfile/PExcel/" +res.data;const link = document.createElement("a");link.style.display = "none";link.href = url;link.setAttribute("download", res.data); // 文件名link.setAttribute("target", "_black");document.body.appendChild(link);link.click();document.body.removeChild(link); // 下载完成移除元素window.URL.revokeObjectURL(url); // 释放掉blob对象});}});
}