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对象
      });
    }
  });
}