using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using OfficeOpenXml;
using OfficeOpenXml.Style;
namespace HZCC.Common
{
public class ExcelHelper
{
/// <summary>
/// List导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataList">数据</param>
/// <param name="headers">表头</param>
/// <param name="hasBorlder">单元格是否有边框</param>
/// <returns></returns>
public static string CreateExcelFromListEx<T>(List<T> dataList, string sheetName, List<ExcelHeader> headers, bool hasBorlder = false)
{
if (headers == null)
{
throw new Exception("表头信息不能为空!");
}
string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "wwwroot\\Download");//如果用浏览器url下载的方式 存放excel的文件夹一定要建在网站首页的同级目录下!!!
if (!Directory.Exists(sWebRootFolder))
{
Directory.CreateDirectory(sWebRootFolder);
}
string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
var path = Path.Combine(sWebRootFolder, sFileName);
FileInfo file = new FileInfo(path);
if (file.Exists)
{
file.Delete();
file = new FileInfo(path);
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage(file);
//创建sheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
var headerRowCount = 1;//标题占的行数
headers.ForEach(p =>
{
var rowNum = int.Parse(Regex.Replace(p.Adress, "[a-z]", "", RegexOptions.IgnoreCase));
if (rowNum > headerRowCount)
{
headerRowCount = rowNum;
}
});
if (hasBorlder)//边框
{
var dataRows = dataList.Count() + headerRowCount;
string modelRange = "A1:" + Regex.Replace(headers.LastOrDefault().Adress, "[0-9]", "", RegexOptions.IgnoreCase) + dataRows.ToString();
var modelTable = worksheet.Cells[modelRange];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
worksheet.Cells[headerRowCount, 1].LoadFromCollection(dataList, true);
//表头字段
for (int i = 0; i < headers.Count; i++)
{
worksheet.Cells[headers[i].Adress].Value = headers[i].Value;
if (!string.IsNullOrWhiteSpace(headers[i].MergeArea))
{
worksheet.Cells[headers[i].MergeArea].Merge = true;
}
worksheet.Cells[headers[i].Adress].Style.Font.Size = 12; //字体大小
worksheet.Cells[headers[i].Adress].Style.Font.Bold = true; //设置粗体
worksheet.Cells[headers[i].Adress].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中对齐
worksheet.Cells[headers[i].Adress].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中对齐
if (headers[i].Width > 0)
{
worksheet.Column(ColumnIndex(headers[i].Adress)).Width = headers[i].Width;
}
}
var objs = worksheet.Cells;
for (int i = 1; i <= dataList.Count + 2; i++)
{
for (int j = 1; j <= headers.Count + 1; j++)
{
objs[i, j].Value = objs[i, j].Value == null ? "" : objs[i, j].Value.ToString(); //null值设置为""
}
}
package.Save();
return path;//返回文件路径
}
/// <summary>
/// 根据单元格地址计算出单元格所在列的索引
/// </summary>
/// <param name="reference">单元格地址,示例A1,AB2</param>
/// <returns></returns>
private static int ColumnIndex(string reference)
{
int ci = 0;
reference = reference.ToUpper();
for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)
ci = (ci * 26) + ((int)reference[ix] - 64);
return ci;
}
}
public class ExcelHeader
{
/// <summary>
/// 单元格地址A1,B2等
/// </summary>
public string Adress { get; set; }
/// <summary>
/// 单元格值
/// </summary>
public string Value { get; set; }
/// <summary>
/// 单元格合并区域,示例A1:B2,为空表示不合并
/// </summary>
public string MergeArea { get; set; }
/// <summary>
/// 列宽度,合并列不需要指定宽度,示例:
/// | A |
/// |B|C|
/// 表头A是单元格B和单元格C合并而来,不需要指定宽度,只指定B和C宽度即可
/// </summary>
public int Width { get; set; }
}
}
关键代码:
worksheet.Cells[headerRowCount, 1].LoadFromCollection(dataList, true);
指定List数据从Excel什么位置开始写入,worksheet.Cells默认从Excel左上角写入数据,使用worksheet.Cells[Row, Col]指定数据写入的定位锚点。
表头使用示例:
var headers = new List<ExcelHeader>
{
new ExcelHeader{ Adress="A1", Value="工号", MergeArea="A1:A2" },
new ExcelHeader{ Adress="B1", Value="姓名", MergeArea="B1:B2" },
new ExcelHeader{ Adress="C1", Value="公司", MergeArea="C1:C2", Width=9 },
new ExcelHeader{ Adress="D1", Value="部门", MergeArea="D1:D2", Width=15 },
new ExcelHeader{ Adress="E1", Value="工作日出勤", MergeArea="E1:F1" },
new ExcelHeader{ Adress="E2", Value="出勤天数", Width=11},
new ExcelHeader{ Adress="F2", Value="出差天数", Width=11},
};
导出效果: